Course Hero Logo

771_SQL.pdf - SQL Chapter 6,7,8,9 History   IBM Sequel...

Course Hero uses AI to attempt to automatically extract content from documents to surface to you and others so you can study better, e.g., in search results, to enrich docs, and more. This preview shows page 1 - 9 out of 51 pages.

Chapter 6,7,8,9SQL
HistoryIBM Sequel language developed as part of System R project at theIBM San Jose Research LaboratoryRenamed Structured Query Language (SQL)ANSI and ISO standard SQL:SQL-86SQL-89SQL-92SQL:1999 (language name became Y2K compliant!)SQL:2003SQL:2008Commercial systems offer most, if not all, SQL-92 features, plusvarying feature sets from later standards and special proprietaryfeatures.
Data Definition LanguageThe schema for each relation.The domain of values associated with each attribute.Integrity constraintsThe set of indices to be maintained for each relations.Security and authorization information for eachrelation.Allows the specification of not only a set of relationsbut also information about each relation, including:
Domain Types in SQLchar(n).Fixed length character string, with user-specifiedlengthn.varchar(n).Variable length character stringsint.Integer (a finite subset of the integers that is machine-dependent).smallint.Small integer (a machine-dependent subset of theinteger domain type).numeric(p,d).A fixed-point number with user-specifiedprecision. The number consists of p digits (plus a sign), and d ofthe p digits are to the decimal point.float(n).Floating point number, with user-specified precisionof at leastndigits.
Create Table ConstructAn SQL relation is defined using thecreate tablecommand:create tabler(A1D1,A2D2, ...,AnDn,(integrity-constraint1),...,(integrity-constraintk))ris the name of the relationeachAiis an attribute name in the schema of relationrDiis the data type of values in the domain of attributeAiExample:create tablebranch(branch_namechar(15)not null,branch_citychar(30),assetsinteger)
Primary KeysPrimary keys are denoted in relations by listing themfirstandunderliningthem:STUDENT (studentID, name, major)PRIMARY KEY (studentID)For PKs made up of several attributes (composite keys),allofthe attributes that are part of the key are underlined:COURSE (departmentID, courseNumber, courseDescription)PRIMARY KEY(departmentID, courseNumber)A PK constraint means that the attribute must be:UniqueNot Null
Foreign KeysForeign keysare added to a relation to show the relationship between two (or more)entities.Example:Department (DeptID,DeptName, Location);Employee (EmpID, EmpName,DeptID);FOREIGN KEY (DeptID) REFERENCES Department(DeptID)or FOREIGN KEY (DeptID) REFERENCES DepartmentReferential action:Actions:ON UPDATEON DELETEOptions:CASCADESET NULLSET DEFAULTNO ACTIONExample:FORGEIGN KEY (DeptID) REFERENCES Department ON UPDATE CASCADE
Drop and Alter Table ConstructsThedrop tablecommand deletes all information about thedropped relation from the database.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 51 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
Tedesco
Tags
perryridge branch, ANSI

Newly uploaded documents

Show More

Newly uploaded documents

Show More

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture