CMPS 277 Final, Fall 2003

Time: 2 Hrs. CMPS 277 Final Examination f Fall 2003-2004 Family Name First Name Id. Number Section 3 (9:30 TR) 4 (12:30 TR) Write down your answers on the question sheet. You can use the last page as well as the reverse side of each page for scratch work. There are pages all together (the last page is blank) DO NOT CUT OFF ANY PAGE. Good Luck. Grade Out Of Page | / 13 rane abasic es fo [ LIBKARY OF RUIKUT Problem 1 (15 Points) a. How does the concept of an object in an object-oriented model differ from the concept of an entity in the entity-relationship model ? b. What is the main difference in approach between having a (persistent-programming- language-based) object-oriented database and an object-relational database ? c. What are the main objectives of RAID in a computer system? d. Why is a hash structure not the best choice for a search key on which range queries are likely ? Page 2 / 13 Problem 2 (15 Points) Consider the relational schema R = (A, B, C, D, E ), and assume that the following set F of functional dependencies hold on R: F={A>BC ,CD>5E,B>D ,E>3A4 } a. Suppose that the schema R is decomposed into R = (A, B, C ) and R2= (A,D, E ). Show that the decomposition is lossless-join.. b. Show that the decomposition in (a) above is not dependency-preserving. c. Compute B* d. Is CD is a candidate key for R. Why ? Page 3/13 ne f bre LIL. OF BEIRUT Problem 3. ( 30 Points) Zakariya has designed the following relational database schema for his “Zakkour’s Pizza House”. izza content ingredient —spname pram (>| - iname price weight order-line out-order in-order pum “Syumber Humber’ date customer table-num ttl-price address in-or-out ° The database is supposed to keep track of the different kinds of pizza that are available at Zakkour’s Pizza House. This is kept in the relation pizza, which is identified by pname (the name of the pizza like Plain, Hawaian, Margaritta, Vegetarian, Cecilian, etc....), and contains the price attribute. The ingredients that are used in the pizza’s are stored in the relation ingredient . This relation is identified by the name of the ingredient, iname, (like cheese, tomato sauce, mushrooms, pepperoni, onions, olives, anchovies, etc...) and stores the weight of each ingredient portion. The ingredient contents of every pizza type is stored in the relation content , which is identified by the pair of attributes (pname, iname). The database also maintains the orders placed by customers. Orders are of two types: Orders at the pizza house (In-Orders), and orders to delivered to an address (Out- Orders). With every order is kept the order number, number , which is a serial number that identifies an order, the date of the order, the total price (¢t/-price), and its type in-or-out. In addition, for In- Orders the database stores the table number for that order, and for Out-Orders the name of the customer, and the delivery address are maintained. Moreover, an order consists of one or more order-lines, which indicates a pizza kind that is being ordered and the quajntity of that kind of pizza being ordered, e.g. a tuple (1234, ‘Hawaian’, 3) in order-line means that in order 1234, the customer needs 3 Hawaian pizzas. a. Give an appropriate ER diagram for this database, indicating al! appropriate constraints. eee ES jaa ius va Page 4 / 13 faa meen : - Lik oy ] OF bent b. Suppose that it is a “business rule” that every kind of pizza must contain a portion of cheese and a portion of tomato sauce. Can this business rule be expressed in the ER diagram you have given? Why ? c. Zakariya decides to add a new variant to the pizza products he sells: to allow the pizza of any kind to be either thin or thick, and at the same price. Suggest minimal changes to the given relational database design to enable the storage of such orders. Give a brief explanation. d. Indicate how the changes in (c) would affect your ER diagram of (a). Page 5 / 13 Problem 4 (30 Points) Referring to the relational design given above for “Zakkour’s Pizza House” database, write the following in SQL: a. Give a definition for the tables (relations) order and order_line. (Include constraint) b. Build an index number_indx on order whose search key is number c. Add to the database a new kind of pizza Thuraya (Zakariya’s wife name!!) priced at 22000, and which contains the ingredients: Cheese, Tomato Sauce, and Shrimps. What should the system do if while executing the statements in your answer to (c), the ingredient Shrimps is not in the database ? Why ? b- vanas 3 re yoo abe owe Page 6/13 d. Find all the ingredients in the ‘Hawaian’ pizza e. Find the total sales for orders placed on 20-Jan-2004 at table 5. f. Find the ingredients and their total quantities consumed for all orders in January 2003. Page 7/13 Co armen MEKW es fo ee RSTT Lib. ew Y OF HKEIRET ! —_——————— g. Find the pizza that has in it all the ingredients. First give this query in relational algebra. Now give the query in SQL. h. Enforce the business rule (integrity constraint!) that every kind of pizza must contain cheese and tomato sauce. Page 8 / 13 bee ec Problem 5. ( 15 Points) During the design phase, Zakariya’s son, Shakib, had suggested the following scheme for the pizza part: pizza-content-scheme = ( pname, price, iname, weight). a. Would you support Shakib in his suggested scheme ? Why ? b. List the basic functional dependencies that hold on this scheme. c. Is this scheme in Boyce-Codd Normal Form ? Why ? d. If your answer in (c) is no, then give a BCNF decomposition, showing the decomposition steps. y he Page 9/13 Problem 6. ( 20 Points) Suppose that the data for 5 years contained in the relation order (number, date, ttl-price, in-or-out) of the relational design of Problem 3 above is to be stored ina sequential file ordered by number. The record here is a tuple of order. Suppose that there are at most 100 orders per day. Also assume the following storage requirements: 6-bytes for number, 8-bytes for date, 6-bytes for ttl-price, 1-byte for in-or-out. Assume that the block size is 2048 bytes. No spanning is allowed (i.e. one record must be stored in one block, and cannot cross over two blocks). Answer the following showing your computations: a. How many records/tuples would fit in a block ? b. What will be the total number of blocks needed to store the relation order ? c. How much of this space is wasted (due to non-spanning)? d. Is the context of this problem, would insertion (into the sequential file)of a new order record be a problem ? Why ? aR Ee is i brpaw ay } PISA RA 4 rT ba) Page 10/ 13