{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Specify foreign keys for this schema stating any

Info iconThis preview shows pages 4–6. Sign up to view the full content.

View Full Document Right Arrow Icon
order can be shipped from several warehouses. Specify foreign keys for this schema, stating any assumptions you make. Answer: Strictly speaking, a foreign key is a set of attributes, but when that set contains only one attribute, then that attribute itself is often informally called a foreign key. The schema of this question has the following five foreign keys: 1. the attribute Cust# of relation ORDER that references relation CUSTOMER , 2. the attribute Order# of relation ORDER_ITEM that references relation ORDER , 3. the attribute Item# of relation ORDER_ITEM that references relation ITEM , 4. the attribute Order# of relation SHIPMENT that references relation ORDER , and 5. the attribute Warehouse# of relation SHIPMENT that references relation WAREHOUSE . We now give the queries in relational algebra : The above query lists all orders for which no “timely” shipment was made, including
Background image of page 4

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

View Full Document Right Arrow Icon
orders for which no shipment was ever made. It is instructive to compare the above query with the one below that lists those orders for which at least one “late” shipment was made. 5.14 Consider the following relations for a database that keeps track of business trips of salespersons in a sales office: SALESPERSON (SSN, Name, Start_Year, Dept_No) TRIP (SSN, From_City, To_City, Departure_Date, Return_Date, Trip_ID ) EXPENSE (Trip_ID , Account# , Amount) Specify the foreign keys for this schema, stating any assumptions you make. Answer: The schema of this question has the following two foreign keys: 1. the attribute SSN of relation TRIP that references relation SALESPERSON , and 2. the attribute Trip_ID of relation EXPENSE that references relation TRIP . In addition, the attributes Dept_No of relation SALESPERSON and Account# of relation EXPENSE are probably also foreign keys referencing other relations of the database not mentioned in the question. We now give the queries in relational algebra: 5.15 Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT (SSN , Name, Major, Bdate) COURSE (Course# , Quarter , Grade) ENROLL (SSN, Course# , Quarter , Grade) BOOK_ADOPTION (Course# , Quarter , Book_ISBN) TEXT (Book_ISBN , Book_Title, Publisher, Author) Specify the foreign keys for this schema, stating any assumptions you make. Answer:
Background image of page 5
The schema of this question has the following four foreign keys: 3. the attribute SSN of relation ENROLL that references relation STUDENT , 4. the attribute Course# in relation ENROLL that references relation COURSE, 5. the attribute Course# in relation BOOK_ADOPTION that references relation COURSE, and 6. the attribute Book_ISBN of relation BOOK_ADOPTION that references relation TEXT. We now give the queries in relational algebra:
Background image of page 6
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}