



Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
Material Type: Exam; Class: DB Sys Concepts& Design; Subject: Computer Science; University: Georgia Institute of Technology-Main Campus; Term: Spring 2005;
Typology: Exams
1 / 5
This page cannot be seen from the preview
Don't miss anything!
YOUR SS# _____________________________(No names please)
EXAM I
CS 6400 DATABASE SYSTEM CONCEPTS AND DESIGN (Navathe, Time : 90 minutes)
Note: Answer the questions as completely as possible. If you need to make any assumptions, please state them and proceed. Examples should clearly illustrate the concept under question. (TOTAL POINTS 80 - allow roughly a minute per point to pace yourself). Q1 and parts of Q4 are answered on the exam and the rest in blue-book.
Q1. Multiple Choice: (15 points – 1.5 per question).
Q4. Relational Model. (20 pts.)
a. Consider the following tables for an order-processing application:
CUSTOMER (Cust#, Cname, City))
ORDER( O#, Odate, C#, Order_Amt)
ORDER_ITEM ( O#, Item#, Qty)
ORDER_PAYMENT ( O#, Paydate, Pay_Amt, Check#)
OVERDUE_NOTICE (Order#, Notice_date, Penalty_amount)
ITEM (Item#, Item-descr, Unit_Price)
C# is same as customer number. Ord_Amt refers to total amount of the order. Odate is the date order was placed. Pay_Amt refers to payment for the order made on a specific date. (Note that payments may come in multiple installments).
Paydate is the date payment was received.
A. Draw a diagram with arrows showing the referential integrity constraints in the above database. – DRAW ARROWS DIRECTLY ON THE SHEET. (5 pts.)
B. Assume that we insert < 25110, I10, 30 > in ORDER_ITEM. What checks must the system perform to guarantee integrity of the above database? Can the quantity be left null in the above? Why or why not? (4 pts.)
C. Consider the following two tables:
TABLE T1 TABLE T
P Q R A B C
10 a 5 10 b 6 15 b 8 25 c 3 25 a 6 10 b 5
Show the results of the following operations: (11 pts.)
Q 5. (21 pts.) Perform the following queries in algebra against the database in Question 4 (Show step by step operation)
a. List Cust#, Order# and Order_amount for all orders placed by customers in Atlanta. (4 pts.)
b. Draw a query tree for the above query and state it in SQL. (5pts.)
c. List all items (Item#, Item-descr, qty) ordered by Customer #C50. [ Note: the same item may appear on multiple orders. Multiple listings of the same item in the output are OK.] (5pts.)
d. List Order#, Total_Pay_amt for those orders for which the total of all payments received is less than the order amount. (Hint: use the SUM function). (7pts.)