Elm0408.docx

Elm0408.docx

《Elm0408.docx》由会员分享，可在线阅读，更多相关《Elm0408.docx（20页珍藏版）》请在冰豆网上搜索。

Elm0408

CHAPTER8:

SQL-99:

SCHEMADEFINITION,CONSTRAINTS,QUERIES,ANDVIEWS

AnswerstoSelectedExercises

8.7ConsiderthedatabaseshowninFigure1.2,whoseschemaisshowninFigure2.1.

Whatarethereferentialintegrityconstraintsthatshouldholdontheschema?

WriteappropriateSQLDDLstatementstodefinethedatabase.

Answer:

Thefollowingreferentialintegrityconstraintsshouldhold（weusethenotation:

R.（A1,...,An）-->S.（B1,...,Bn）

torepresentaforeignkeyfromtheattributesA1,...,AnofR（thereferencingrelation）

toS（thereferencedrelation））:

PREREQUISITE.（CourseNumber）-->COURSE.（CourseNumber）

PREREQUISITE.（PrerequisiteNumber）-->COURSE.（CourseNumber）

SECTION.（CourseNumber）-->COURSE.（CourseNumber）

GRADE_REPORT.（StudentNumber）-->STUDENT.（StudentNumber）

GRADE_REPORT.（SectionIdentifier）-->SECTION.（SectionIdentifier）

OnepossiblesetofCREATETABLEstatementstodefinethedatabaseisgivenbelow.

CREATETABLESTUDENT（NameVARCHAR（30）NOTNULL,

StudentNumberINTEGERNOTNULL,

ClassCHARNOTNULL,

MajorCHAR（4）,

PRIMARYKEY（StudentNumber））;

CREATETABLECOURSE（CourseNameVARCHAR（30）NOTNULL,

CourseNumberCHAR（8）NOTNULL,

CreditHoursINTEGER,

DepartmentCHAR（4）,

PRIMARYKEY（CourseNumber）,

UNIQUE（CourseName））;

CREATETABLEPREREQUISITE（CourseNumberCHAR（8）NOTNULL,

PrerequisiteNumberCHAR（8）NOTNULL,

PRIMARYKEY（CourseNumber,PrerequisiteNumber）,

FOREIGNKEY（CourseNumber）REFERENCES

COURSE（CourseNumber）,

FOREIGNKEY（PrerequisiteNumber）REFERENCES

COURSE（CourseNumber））;

CREATETABLESECTION（SectionIdentifierINTEGERNOTNULL,

CourseNumberCHAR（8）NOTNULL,

SemesterVARCHAR（6）NOTNULL,

YearCHAR（4）NOTNULL,

InstructorVARCHAR（15）,

PRIMARYKEY（SectionIdentifier）,

FOREIGNKEY（CourseNumber）REFERENCES

COURSE（CourseNumber））;

CREATETABLEGRADE_REPORT（StudentNumberINTEGERNOTNULL,

SectionIdentifierINTEGERNOTNULL,

GradeCHAR,

PRIMARYKEY（StudentNumber,SectionIdentifier）,

FOREIGNKEY（StudentNumber）REFERENCES

STUDENT（StudentNumber）,

FOREIGNKEY（SectionIdentifier）REFERENCES

SECTION（SectionIdentifier））;

8.8RepeatExercise8.7,butusetheAIRLINEschemaofFigure5.8.

Answer:

Thefollowingreferentialintegrityconstraintsshouldhold:

FLIGHT_LEG.（FLIGHT_NUMBER）-->FLIGHT.（NUMBER）

FLIGHT_LEG.（DEPARTURE_AIRPORT_CODE）-->AIRPORT.（AIRPORT_CODE）

FLIGHT_LEG.（ARRIVAL_AIRPORT_CODE）-->AIRPORT.（AIRPORT_CODE）

LEG_INSTANCE.（FLIGHT_NUMBER,LEG_NUMBER）-->

FLIGHT_LEG.（FLIGHT_NUMBER,LEG_NUMBER）

LEG_INSTANCE.（AIRPLANE_ID）-->AIRPLANE.（AIRPLANE_ID）

LEG_INSTANCE.（DEPARTURE_AIRPORT_CODE）-->AIRPORT.（AIRPORT_CODE）

LEG_INSTANCE.（ARRIVAL_AIRPORT_CODE）-->AIRPORT.（AIRPORT_CODE）

FARES.（FLIGHT_NUMBER）-->FLIGHT.（NUMBER）

CAN_LAND.（AIRPLANE_TYPE_NAME）-->AIRPLANE_TYPE.（TYPE_NAME）

CAN_LAND.（AIRPORT_CODE）-->AIRPORT.（AIRPORT_CODE）

AIRPLANE.（AIRPLANE_TYPE）-->AIRPLANE_TYPE.（TYPE_NAME）

SEAT_RESERVATION.（FLIGHT_NUMBER,LEG_NUMBER,DATE）-->

LEG_INSTANCE.（FLIGHT_NUMBER,LEG_NUMBER,DATE）

OnepossiblesetofCREATETABLEstatementstodefinethedatabaseisgivenbelow.

CREATETABLEAIRPORT（AIRPORT_CODECHAR（3）NOTNULL,

NAMEVARCHAR（30）NOTNULL,

CITYVARCHAR（30）NOTNULL,

STATEVARCHAR（30）,

PRIMARYKEY（AIRPORT_CODE））;

CREATETABLEFLIGHT（NUMBERVARCHAR（6）NOTNULL,

AIRLINEVARCHAR（20）NOTNULL,

WEEKDAYSVARCHAR（10）NOTNULL,

PRIMARYKEY（NUMBER））;

CREATETABLEFLIGHT_LEG（FLIGHT_NUMBERVARCHAR（6）NOTNULL,

LEG_NUMBERINTEGERNOTNULL,

DEPARTURE_AIRPORT_CODECHAR（3）NOTNULL,

SCHEDULED_DEPARTURE_TIMETIMESTAMPWITHTIMEZONE,

ARRIVAL_AIRPORT_CODECHAR（3）NOTNULL,

SCHEDULED_ARRIVAL_TIMETIMESTAMPWITHTIMEZONE,

PRIMARYKEY（FLIGHT_NUMBER,LEG_NUMBER）,

FOREIGNKEY（FLIGHT_NUMBER）REFERENCESFLIGHT（NUMBER）,

FOREIGNKEY（DEPARTURE_AIRPORT_CODE）REFERENCES

AIRPORT（AIRPORT_CODE）,

FOREIGNKEY（ARRIVAL_AIRPORT_CODE）REFERENCES

AIRPORT（AIRPORT_CODE））;

CREATETABLELEG_INSTANCE（FLIGHT_NUMBERVARCHAR（6）NOTNULL,

LEG_NUMBERINTEGERNOTNULL,

LEG_DATEDATENOTNULL,

NO_OF_AVAILABLE_SEATSINTEGER,

AIRPLANE_IDINTEGER,

DEPARTURE_AIRPORT_CODECHAR（3）,

DEPARTURE_TIMETIMESTAMPWITHTIMEZONE,

ARRIVAL_AIRPORT_CODECHAR（3）,

ARRIVAL_TIMETIMESTAMPWITHTIMEZONE,

PRIMARYKEY（FLIGHT_NUMBER,LEG_NUMBER,LEG_DATE）,

FOREIGNKEY（FLIGHT_NUMBER,LEG_NUMBER）REFERENCES

FLIGHT_LEG（FLIGHT_NUMBER,LEG_NUMBER）,

FOREIGNKEY（AIRPLANE_ID）REFERENCES

AIRPLANE（AIRPLANE_ID）,

FOREIGNKEY（DEPARTURE_AIRPORT_CODE）REFERENCES

AIRPORT（AIRPORT_CODE）,

FOREIGNKEY（ARRIVAL_AIRPORT_CODE）REFERENCES

AIRPORT（AIRPORT_CODE））;

CREATETABLEFARES（FLIGHT_NUMBERVARCHAR（6）NOTNULL,

FARE_CODEVARCHAR（10）NOTNULL,

AMOUNTDECIMAL（8,2）NOTNULL,

RESTRICTIONSVARCHAR（200）,

PRIMARYKEY（FLIGHT_NUMBER,FARE_CODE）,

FOREIGNKEY（FLIGHT_NUMBER）REFERENCESFLIGHT（NUMBER））;

CREATETABLEAIRPLANE_TYPE（TYPE_NAMEVARCHAR（20）NOTNULL,

MAX_SEATSINTEGERNOTNULL,

COMPANYVARCHAR（15）NOTNULL,

PRIMARYKEY（TYPE_NAME））;

CREATETABLECAN_LAND（AIRPLANE_TYPE_NAMEVARCHAR（20）NOTNULL,

AIRPORT_CODECHAR（3）NOTNULL,

PRIMARYKEY（AIRPLANE_TYPE_NAME,AIRPORT_CODE）,

FOREIGNKEY（AIRPLANE_TYPE_NAME）REFERENCES

AIRPLANE_TYPE（TYPE_NAME）,

FOREIGNKEY（AIRPORT_CODE）REFERENCES

AIRPORT（AIRPORT_CODE））;

CREATETABLEAIRPLANE（AIRPLANE_IDINTEGERNOTNULL,

TOTAL_NUMBER_OF_SEATSINTEGERNOTNULL,

AIRPLANE_TYPEVARCHAR（20）NOTNULL,

PRIMARYKEY（AIRPLANE_ID）,

FOREIGNKEY（AIRPLANE_TYPE）REFERENCESAIRPLANE_TYPE（TYPE_NAME））;

CREATETABLESEAT_RESERVATION（FLIGHT_NUMBERVARCHAR（6）NOTNULL,

LEG_NUMBERINTEGERNOTNULL,

LEG_DATEDATENOTNULL,

SEAT_NUMBERVARCHAR（4）,

CUSTOMER_NAMEVARCHAR（30）NOTNULL,

CUSTOMER_PHONECHAR（12）,

PRIMARYKEY（FLIGHT_NUMBER,LEG_NUMBER,LEG_DATE,SEAT_NUMBER）,

FOREIGNKEY（FLIGHT_NUMBER,LEG_NUMBER,LEG_DATE）REFERENCES

LEG_INSTANCE（FLIGHT_NUMBER,LEG_NUMBER,LEG_DATE））;

8.9ConsidertheLIBRARYrelationaldatabaseschemaofFigure6.14.Choose

theappropriateaction（reject,cascade,settonull,settodefault）foreach

referentialintegrityconstraint,bothforDELETEofareferencedtuple,andforUPDATEofaprimarykeyattributevalueinareferencedtuple.Justifyyourchoices.

Answer:

Belowarepossiblechoices.Ingeneral,ifitisnotclearwhichactiontochoose,REJECT

shouldbechosen,sinceitwillnotpermitautomaticchangestohappen（byupdate

propagation）thatmaybeunintended.

BOOK_AUTHORS.（BookId）-->BOOK.（BookId）

CASCADEonbothDELETEorUPDATE（sincethiscorrespondstoamulti-valuedattribute

ofBOOK（seethesolutiontoExercise6.27）;hence,ifaBOOKisdeleted,orthevalueof

itsBookIdisupdated（changed）,thedeletionorchangeisautomaticallypropagatedtothe

referencingBOOK_AUTHORStuples）

BOOK.（PublisherName）-->PUBLISHER.（Name）

REJECTonDELETE（weshouldnotdeleteaPUBLISHERtuplewhichhasexistingBOOK

tuplesthatreferencethePUBLISHER）

CASCADEonUPDATE（ifaPUBLISHER'sNameisupdated,thechangeshouldbepropagatedautomaticallytoallreferencingBOOKtuples）

BOOK_LOANS.（BookId）-->BOOK.（BookId）

CASCADEonbothDELETEorUPDATE（ifaBOOKisdeleted,orthevalueofitsBookIdis

updated（changed）,thedeletionorchangeisautomaticallypropagatedtothereferencing

BOOK_LOANStuples）（Note:

OnecouldalsochooseREJECTonDELETE）

BOOK_COPIES.（BookId）-->BOOK.（BookId）

CASCADEonbothDELETEorUPDATE（ifaBOOKisdeleted,orthevalueofitsBookIdis

updated（changed）,thedeletionorchangeisautomaticallypropagatedtothereferencing

BOOK_COPIEStuples）

BOOK_LOANS.（CardNo）-->BORROWER.（CardNo）

CASCADEonbothDELETEorUPDATE（ifaBORROWERtupleisdeleted,orthevalueofitsCardNoisupdated（changed）,thedeletionorchangeisautomaticallypropagatedtothe

referencingBOOK_LOANStuples）（Note:

OnecouldalsochooseREJECTonDELETE,with

theideathatifaBORROWERisdeleted,itisnecessaryfirsttomakeaprintoutofall

BOOK_LOANSoutstandingbeforedeletingtheBORROWER;inthiscase,thetuplesin

BOOK_LOANSthatreferencetheBORROWERbeingdeletedwouldfirstbeexplicitly

deletedaftermakingtheprintout,andbeforetheBORROWERisdeleted）

BOOK_COPIES.（BranchId）-->LIBRARY_BRANCH.（BranchId）

CASCADEonbothDELETEorUPDATE（ifaLIBRARY_BRANCHisdeleted,orthevalueof

itsBranchIdisupdated（changed）,thedeletionorchangeisautomaticallypropagatedto

thereferencingBOOK_COPIEStuples）（Note:

OnecouldalsochooseREJECTonDELETE）

BOOK_LOANS.（BranchId）-->LIBRARY_BRANCH.（BranchId）

CASCADEonbothDELETEorUPDATE（ifaLIBRARY_BRANCHisdeleted,orthevalueof

itsBranchIdisupdated（changed）,thedeletionorchangeisautomatically

propagatedtothereferencingBOOK_LOANStuples）（Note:

Onecouldalsochoose

REJECTonDELETE）

8.10WriteappropriateSQLDDLstatementsfordeclaringtheLIBRARYrelationaldatabase

schemaofFigure6.14.Specifyappropriatekeysandreferentialtriggeredactions.

Answer:

OnepossiblesetofCREATETABLEstatementsisgivenbelow:

CREATETABLEBOOK（BookIdCHAR（20）NOTNULL,

TitleVARCHAR（30）NOTNULL,

PublisherNameVARCHAR（20）,

PRIMARYKEY（BookId）,

FOREIGNKEY（PublisherName）REFERENCESPUBLISHER（Name）ONUPDATECASCADE）;

CREATETABLEBOOK_AUTHORS（BookIdCHAR（20）NOTNULL,

AuthorNameVARCHAR（30）NOTNULL,

PRIMARYKEY（BookId,AuthorName）,

FOREIGNKEY（BookId）REFERENCESBOOK（BookId）

ONDELETECASCADEONUPDATECASCADE）;

CREATETABLEPUBLISHER（NameVARCHAR（20）NOTNULL,

AddressVARCHAR（40）NOTNULL,

PhoneCHAR（12）,

PRIMARYKEY（Name））;

CREATETABLEBOOK_COPIES（BookIdCHAR（20）NOTNULL,

BranchIdINTEGERNOTNULL,

No_Of_CopiesINTEGERNOTNULL,

PRIMARYKEY（BookId,BranchId）,

FOREIGNKEY（BookId）REFERENCESBOOK（BookId）

ONDELETECASCADEONUPDATECASCADE,

FOREIGNKEY（BranchId）REFERENCESBRANCH（BranchId）

ONDELETECASCADEONUPDATECASC