SQL: Overview and highlights

COS597D: Principlesof DatabaseandInforma;onSystems

SQL: Overviewandhighlights

Based on slides for Database Management Systems by R. Ramakrishnan and J. Gehrke

TheSQLQueryLanguage

? StructuredQueryLanguage ? DevelopedbyIBM(systemR)inthe1970s ? Needforastandardsinceitisusedbymanyvendors

? ANSI(AmericanNa;onalStandardsIns;tute) ? ISO(Interna;onalOrganiza;onforStandardiza;on) ? Standards: ? SQL-86 ? SQL-92(majorrevision) ? SQL-99(majorextensions) ? SQL2003(XMLSQL) ? SQL2008 ? SQL2011 ? con;nueenhancements

Crea;ngRela;onsinSQL

Observe: ?type (domain) of each attribute specified

?type enforced by DBMS whenever tuples are added or modified.

? CREATETABLEMovie ( nameCHAR(30), producerCHAR(30), rel_dateCHAR(8), ra;ngCHAR, PRIMARYKEY(name,producer,rel_date))

? CREATETABLEEmployee( SS#CHAR(9), nameCHAR(30), addrCHAR(50),

startYrINT, PRIMARYKEY(SS#))

? CREATETABLEAssignment( posi;onCHAR(20), SS#CHAR(9), managerSS#CHAR(9), PRIMARYKEY(posi;on),

FOREIGNKEY(SS#REFERENCESEmployee), FOREIGNKEY(managerSS#REFERENCESEmployee))

Referen;alIntegrityinSQL

? SQL-92onsupportall4op;onsondeletesandupdates. ? DefaultisNOACTION(delete/update is rejected)

? CASCADE(alsodeletealltuplesthatrefertodeletedtuple)

? SETNULL/SETDEFAULT(setsforeignkeyvalueof referencingtuple)

CREATE TABLE Acct (bname CHAR(20) DEFAULT `main', acctn CHAR(20), bal REAL, PRIMARY KEY ( acctn), FOREIGN KEY (bname) REFERENCES Branch ON DELETE SET DEFAULT )

BUT individual implementations may NOT support

PrimaryandCandidateKeysinSQL

? Possiblymanycandidate keys (specifiedusing UNIQUE),oneofwhichischosenastheprimary key.

? at most one book with a given title and edition ? date, publisher and isbn are determined

? Used carelessly, can prevent the storage of database instances that arise in practice! Title and ed suffice?

UNIQUE (title, ed, pub)?

CREATE TABLE Book (isbn CHAR(10) title CHAR(100), ed INTEGER, pub CHAR(30), date INTEGER, PRIMARY KEY (isbn), UNIQUE (title, ed ))

SELECT [DISTINCT] select-list

BasicSQLQuery FROM from-list

WHERE qualification

? from-listAlistofrela;onnames(possiblywitharange- variableahereachname).

? select-listAlistofaiributesofrela;onsinfrom-list ? qualifica;onComparisons(AiropconstorAir1op

Air2,whereopisoneof,=,,,)combined usingAND,ORandNOT. ? DISTINCTisanop;onalkeywordindica;ngthatthe answershouldnotcontainduplicates.Defaultisthat

duplicatesarenoteliminated!

ConceptualEvalua;onStrategy

? Seman;csofanSQLquerydefinedinterms ofthefollowingconceptualevalua;on strategy:

? Computethecross-productoffrom-list. ? Discardresul;ngtuplesiftheyfailqualifica;ons. ? Deleteaiributesthatarenotinselect-list. ? IfDISTINCTisspecified,eliminateduplicaterows.

? Thisstrategyisprobablytheleastefficient waytocomputeaquery!Anop;mizerwill findmoreefficientstrategiestocomputethe same answers.

ExampleInstances

instance of bname bcity assets

Branch

pu

Pton 10

? Wewillusethese

nyu nyc 20

instancesoftheAcct

andBranchrela;ons

time sq nyc 30

inourexamples.

instance of bname acctn bal

Acct pu

33

356

nyu

45

500

ExampleofConceptualEvalua;on

SELECT acctn FROM Branch, Acct WHERE Branch.bname=Acct.bname AND assets, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download