Download Past Exam Questions - Database Systems Concepts and Design | CS 6400 and more Exams Computer Science in PDF only on Docsity!
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).
- Which of the following is the best general definition of a database? (a) known facts that can be recorded (b) a collection of related data with some meaning (c) a collection of stored data with some encoding (d) a collection of programs that allow users to create and maintain a database
- Which schema level in the 3- schema (ANSI architecture) hides the details of physical storage structures and concentrates on describing entities, relationships and constraints of the whole database? (a) conceptual level (b) internal level (c) external level (d) global level
- Logical data independence can be best defined as (a) the capacity to change the internal schema without having to change the conceptual or external schema (b) the capacity to change the conceptual schema without having to change external schema or application programs (c) the capacity to change the DBMS software (d) all of the above
- Which of the following is (are) a capability (capabilities) that should be provided by a database management system? (a) maintain consistency and integrity of data (b) restrict unauthorized access (c) maintain application ( business) rules (d) all of the above (e) a and b (f) a and c
- Which is not a responsibility of the database administrator? (a) authorizing access to the database (b) deciding on the storage structures and access strategies (c) modifying the DBMS software to support additional features (d) monitoring the performance of the database system
- Which of the following is not a software component of a database management system (a) host language compiler (b) run-time database processor (c) query language interpreter / processor (d) all of the above
- A relation instance is (a) a set of attributes in a relation schema (b) a set of n-tuples with values drawn from the domains of the attributes in a relation schema (c) the name of a relation schema (d) the relation intension
- Information such as database users, description of database transactions, frequencies transactions is stored in (a) the database management system (b) the data dictionary or catalog (c) an entity-relationship diagram (d) an application program
- Given the two relations R(A,B,C) and S(A,B,C), which of the following is always true (a) R∩S yields the same result as S∩R (b) R−S yields the same result as S−R (c) Projection of S on A yields the same result as projection of R on A (d) none of the above
- A company may not use a DBMS when (a) no DBMS can deal with application and data complexity (b) no DBMS has a proper interface (c) all data in the company is stored in an application-specific software system (d) all of the above
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.)
- T1 Equijoin T2 where T1.P = T2.A (Show either P or A in the result, not both)
- T1 Equijoin T2 where T1.Q < T2.B (The inequality is based on character comparison; Show both Q or B in the result)
- T1 Leftouterjoin T2 where T1.P = T2.A.
- T1 Rightouterjoin T2 where T1.Q = T2.B.
- T1 Union T
- T1 Equijoin T2 where T1.P = T2.A and T1.R = T2.C.
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.)
