Elm04_08 - Chapter 8: SQL-99: Schema Definition, Basic...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Chapter 8: SQL-99: Schema Definition, Basic Constraints, and Queries CHAPTER 8: SQL-99: SCHEMA DEFINITION, BASIC CONSTRAINTS, AND QUERIES Answers to Selected Exercises 8. 7 Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1. What are the referential integrity constraints that should hold on the schema? Write appropriate SQL DDL statements to define the database. Answer: The following referential integrity constraints should hold (we use the notation: R.(A1, ..., An) --> S.(B1, ..., Bn) to represent a foreign key from the attributes A1, ..., An of R (the referencing relation) to S (the referenced relation)): PREREQUISITE.(CourseNumber) --> COURSE.(CourseNumber) PREREQUISITE.(PrerequisiteNumber) --> COURSE.(CourseNumber) SECTION.(CourseNumber) --> COURSE.(CourseNumber) GRADE_REPORT.(StudentNumber) --> STUDENT.(StudentNumber) GRADE_REPORT.(SectionIdentifier) --> SECTION.(SectionIdentifier) One possible set of CREATE TABLE statements to define the database is given below. CREATE TABLE STUDENT ( Name VARCHAR(30) NOT NULL, StudentNumber INTEGER NOT NULL, Class CHAR NOT NULL, Major CHAR(4), PRIMARY KEY (StudentNumber) ); CREATE TABLE COURSE ( CourseName VARCHAR(30) NOT NULL, CourseNumber CHAR(8) NOT NULL, CreditHours INTEGER, Department CHAR(4), PRIMARY KEY (CourseNumber), UNIQUE (CourseName) ); CREATE TABLE PREREQUISITE ( CourseNumber CHAR(8) NOT NULL, PrerequisiteNumber CHAR(8) NOT NULL, PRIMARY KEY (CourseNumber, PrerequisiteNumber), FOREIGN KEY (CourseNumber) REFERENCES COURSE (CourseNumber), FOREIGN KEY (PrerequisiteNumber) REFERENCES COURSE (CourseNumber) ); CREATE TABLE SECTION ( SectionIdentifier INTEGER NOT NULL, CourseNumber CHAR(8) NOT NULL, Semester VARCHAR(6) NOT NULL, Year CHAR(4) NOT NULL, Instructor VARCHAR(15), PRIMARY KEY (SectionIdentifier), FOREIGN KEY (CourseNumber) REFERENCES COURSE (CourseNumber) ); CREATE TABLE GRADE_REPORT ( StudentNumber INTEGER NOT NULL, SectionIdentifier INTEGER NOT NULL, Grade CHAR, PRIMARY KEY (StudentNumber, SectionIdentifier), FOREIGN KEY (StudentNumber) REFERENCES STUDENT (StudentNumber), FOREIGN KEY (SectionIdentifier) REFERENCES SECTION (SectionIdentifier) ); Pre-Publication Material: This is draft manuscript yet to be copy edited or paged. Copyright AWL2004 1 Chapter 8: SQL-99: Schema Definition, Basic Constraints, and Queries 8. 8 Repeat Exercise 8.7, but use the AIRLINE schema of Figure 5.8. Answer: The following referential integrity constraints should hold: 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....
View Full Document

Page1 / 12

Elm04_08 - Chapter 8: SQL-99: Schema Definition, Basic...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online