CSE2/4DBF 2020 – Sample Exam (with Answers) Total questions in actual exam: 9 Total marks in the actual exam: 180 Questions 1-3 in the actual exam will be single-answer, multi-choice questions related to ER/EER modelling. Each will be worth 10 marks. One example is given below: Question 1. - ER Diagram (10 Marks) A client of an insurance company can register as either a private or a corporate type of client. Each client is assigned a unique client-id and other details of the client including name, address and date of birth are stored in the system. For corporate type of clients the company registration number is recorded, and for private clients a special type of insurance rate depending on the client’s age and profession is applied. Each client will be registered to one particular insurance plan. However, each client may have different insurance plans on different period of time (some clients decided to change their plans). Each insurance plan may be made up of any number of common policies, each of which is given a unique policy-name. A policy may be part of a number of insurance plans. A date-time attribute is stored for each policy that has been registered within an insurance plan. Which of the following EER diagrams is the best representation of the above problem statement? (Note: (i) for simplicity, participation has been omitted from the diagrams, (ii) PK attributes are underlined). (a) PRIVATE_ INSURANCE CORPORATEPRIVATE CLIENT has d clientID name address dob date N specialRate companyRegistration POLICY includes consistsOf M assign CORPORATE_ INSURANCE_PLAN N M date PIPlanID designer date M N DateTime dateTimeM N CIPlanID designer date policyName policyDesc (b) INSURANCE_ PLAN CORPORATEPRIVATE CLIENT assign d clientID name address dob date N specialRate companyRegistration M IPlanID designer date M dateTime N policyName policyDesc POLICY consistsOfi t f (c) INSURANCE_ PLAN CORPORATEPRIVATE CLIENT assign d clientID name address dob date N specialRate companyRegistration M IPlanID designer date M dateTime N policyName policyDesc POLICY consistsOf (d) PRIVATE_ INSURANCE CORPORATEPRIVATE CLIENT has d clientID name address dob date N specialRate companyRegistration includes M has CORPORATE_ INSURANCE_PLAN N M date PIPlanID designer date M N DateTime dateTime M N CIPlanID designer date policyName policyDesc POLICY consistsOfi t f Correct answer: Questions 4-5 in the actual exam will be single-answer, multi-choice questions related to Relational Algebra. Each will be worth 10 marks. Question 4 – Relational Algebra You are given a set of relations/tables below with some sample data. AUTHOR_T AuthorID FName LName 1 Paul Coelho 2 Isabel Allende 3 Stephen King 4 Tracy Chevalier 5 Mario Puzo BOOK_T BOOK_AUTHOR_T BookID Title Price BookID AuthorID B1 The Godfather 30 B1 5 B2 The Alchemist 15 B2 1 B3 The Last Don 35 B3 5 B4 Falling Angels 25 B4 4 B5 Portrait in Sepia 35 B5 2 ORDER_T OrderID BookID Quantity TotalPrice O101 B1 10 300 O101 B3 20 700 O101 B5 10 350 O102 B4 20 500 O102 B4 30 750 O103 B1 15 450 From the above relations, identify the correct final results from the following query: TempList1 BookIDd, Title, Price, Quantity (Book_T) BookID = BookID (Order_T) TempList2 AuthorID, Quantity (Book_Author_T) BookID = BookID (TempList1) RESULT FName, LName (Author_T) AuthorID = AuthorID (TempList2) (a) (b) FName LName FName LName Isabel Allende Paul Coelho Tracy Chevalier Isabel Allende Mario Puzo Tracy Chevalier Tracy Chevalier Mario Puzo Mario Puzo Mario Puzo (c) (d) FName LName FName LName Paul Coelho Paul Coelho Isabel Allende Isabel Allende Tracy Chevalier Stephen King Mario Puzo Tracy Chevalier Mario Puzo Correct answer: Question 5 – Relational Algebra (10 Marks) Consider the following tables for a HOTEL database: HOTEL (HotelNo, Name, Address) ROOM (RoomNo, HotelNo, Type, Price) BOOKING (HotelNo, CustomerNo, DateFrom, DateTo, RoomNo) CUSTOMER (CustomerNo, Name, Address) With reference to the above HOTEL database, the query in relational algebra to produce a list of rooms that are currently unoccupied at the HILTON MELBOURNE hotel is: (a) Temp1 roomNo (name = ‘Hilton Melbourne’ (Hotel) hotelNo = hotelNo (Room)) Temp2 roomNo ((name = ‘Hilton Melbourne’ (Hotel)) hotelNo = hotelNo (DateFrom <= SYSDATE AND DateTo >= SYSDATE (Booking)) Results Temp1 Temp2 (b) Temp1 roomNo (name = ‘Hilton Melbourne’ (Hotel) hotelNo = hotelNo (Room)) Temp2 roomNo ((name = ‘Hilton Melbourne’ (Hotel)) hotelNo = hotelNo (DateFrom <= SYSDATE AND DateTo >= SYSDATE (Booking)) Results Temp1 − Temp2 (c) Temp1 roomNo (name = ‘Hilton Melbourne’ (Hotel) hotelNo = hotelNo (Room)) Temp2 roomNo (DateFrom <= SYSDATE AND DateTo >= SYSDATE (Booking)) Results Temp1 Temp2 (d) Temp1 roomNo (name = ‘Hilton Melbourne’ (Hotel) hotelNo = hotelNo (Room)) Temp2 roomNo (DateFrom <= SYSDATE AND DateTo >= SYSDATE (Booking)) Results Temp1 − Temp2 Question 6. [25 marks] Consider the following EER diagram for a library database. BOOK CHAPTER WRITER AGENT EDITOR PRINTED E-BOOK PLATFORM writes represents edits viewedon O contains mentors BookID Title NumberPages ReleaseDate Genre EditorID EditorName EditorContact ForeignLang? WriterID WriterName WriterAddress CountryofOrigin AgentID AgentName AgentAddress AgentContact PlatformName PlatformDesc ChapterNo Excerpt NumberofCopies PrintedPrice EBookPrice N (1,N) N (1,N) N (1,1) 1 (1,N) N (1,N) N (1,N) N (1,1) 1 (1,N) 1 (0,N) N (0,1) a) Perform the transformation of the EER model into the relational tables. [20 marks] b) What are the implications of applying 8B or 8C for BOOK specialization? [5 marks] Solution: STEP 1: BOOK (BookID, Title, NumberPages, ReleaseDate) WRITER (WriterID, WriterName, WriterAddress, CountryofOrigin) AGENT (AgentID, AgentName, AgentAddress, AgentContact) EDITOR (EditorID, EditorName, EditorContact, ForeignLang?) PLATFORM (PlatformName, PlatformDesc) STEP 2: BOOKCHAPTER (BookID, ChapterNo, Excerpt) STEP 3: No 1-1 Relationship STEP 4: BOOK (BookID, Title, NumberPages, ReleaseDate, EditorID) WRITER (WriterID, WriterName, WriterAddress, CountryofOrigin, AgentID) EDITOR (EditorID, EditorName, EditorContact, ForeignLang?, MentorEditor) STEP 5: WRITES (WriterID, BookID) STEP 6: BOOKGENRE (BookID, Genre) STEP 7: No ternary relationship. STEP 8: 8A: PRINTED (BookID, NumberofCopies, PrintedPrice) E-BOOK (BookID, EBookPrice) Or, 8D BOOK (BookID, Title, NumberPages, ReleaseDate, EditorID, PrintedFlag, NumberofCopies, PrintePrice, EBookFlag, EBookPrice) REPEAT STEP 2-7 STEP 5: VIEWEDON (BookID, PlatformName) FINAL TABLES (Using 8A): AGENT (AgentID, AgentName, AgentAddress, AgentContact) PLATFORM (PlatformName, PlatformDesc) BOOKCHAPTER (BookID, ChapterNo, Excerpt) BOOK (BookID, Title, NumberPages, ReleaseDate, EditorID) WRITER (WriterID, WriterName, WriterAddress, CountryofOrigin, AgentID) EDITOR (EditorID, EditorName, EditorContact, ForeignLang?, MentorEditor) WRITES (WriterID, BookID) BOOKGENRE (BookID, Genre) PRINTED (BookID, NumberofCopies, PrintedPrice) E-BOOK (BookID, EBookPrice) VIEWEDON (BookID, PlatformName) Question 7. [35 marks] a) Consider the following Bill of Materials form: BILL OF MATERIALS PRODUCT NUMBER: M128 PRODUCT DESCRIPTION: BOOKCASE PART NUMBER PART DESCRIPTION QUANTITY USED LOCATION CODE 139409 328179 421835 218367 98210 SIDE SHELF BACK PANEL SCREW, 2 IN. 2 3 1 1 24 A A B A C 1 1 1 1 2 1. PART NUMBER: a number that uniquely identifies a part. 2. PART DESCRIPTION: a description of the given part number. 3. QUANTITY USED: the quantity of the given part used in this product. 4. LOCATION: the warehouse location of the specified part 5. CODE: a single-digit number that identifies the source of the part. The following code values are presently used: 1 = manufactured component 2 = purchased part (i) Normalize the Bill of Materials form to arrive at a suitable database design which satisfies the third normal form, clearly show the stages UNF, 1NF, 2NF, 3NF, BCNF. [15 marks] b) Consider the following form in a car dealership. When a customer is first interested in purchasing a car from the dealership, they complete the form that contains their personal details along with their desired car features. BUNDOORA LUXURY MOTORS Bundoora 3086 Tel: 9999-9999 CUSTOMER INFORMATION Name: M/F Address:………………………………………………..City:………………………………….. State: Postcode.:…………….. D.O.B:……………………………. Contact Number: ( ) Email: …………………………………………………… Archie wishes to match your desired automobile features with the cars in our collection. Please tick the features below that you feel are most essential for your needs. Comfort and Convenience: Air Cond. Climate Control Cruise Control Cup Holders 1st/2nd Row GPS Keyless Start Power Seats Sunroof Safety and Security: ABS Brakes Blind Spot Sensor Brake Assist Camera – Rear Vision Engine Immobilizer Parking Assistance Engine: 4 cyl. 6 cyl. 8 cyl. Diesel AV/Communication: Audio USB Input/iPod Bluetooth System CD Player DVD Player 1st/2nd Row Multifunction Control Screen Premium Sound System Voice Recognition Transmission: Automatic Manual Other: ________________________ ________________________ ________________________ [20 marks] Q7 (a) Solution: UNF: Bill(ProdNo, ProdDesc, (PartNo, PartDesc, QuantityUsed, Location, Code)) 1NF: Product(ProdNo, ProdDesc) Bill(ProdNo, PartNo, PartDesc, QuantityUsed, Location, Code) 2NF: Part(PartNo, PartDesc, Location, Code) Bill(ProdNo, PartNo, QuantityUsed) 3NF, BCNF: Already in 3NF and BCNF FINAL Product(ProdNo, ProdDesc) Part(PartNo, PartDesc, Location, Code) Bill(ProdNo, PartNo, QuantityUsed) Q7 (b) Solution: UNF: Customer (customerNo, customerName, gender, address, city, state, postcode, dob, contactNumber, email, (featurecategory, (feature))) 1NF: Customer (customerNo, customerName, gender, address, city, state, postcode, dob, contactNumber, email) FeatureCategory (featureCategoryID, featureCategory) DesiredFeatures(featureID, feature, featureCategory, customerNo) 2NF: DesiredFeatures (customerNo, featureID) FeatureCategory (featureCategoryID, featureCategory) Features ( featureID, feature, featureCategoryID) 3NF, BCNF: Already in 3NF and BCNF FINAL Customer (customerNo, customerName, gender, address, city, state, postcode, dob, contactNumber, email) DesiredFeatures (customerNo, featureID) FeatureCategory (featureCategoryID, featureCategory) Features ( featureID, feature, featureCategoryID) Question 8. [35 marks] The following tables show the schema of an Online Bid Auction database system. The MEMBER table stores the list of users who wants to interact in the auction. The ITEM table lists the information of the items put in the auctions. Each item can be categorized, and the information of categories is located in the CATEGORY table. The AUCTION table stores information about the auction. The PAYMENT, CREDITCARDPAYMENT and PAYPALPAYMENT tables store information about payment id and the details on the method of payment. The BID table stores information of bidding activities in an auction. MEMBER (username, lastName, firstName, title, address, city, postcode, country, phoneBH, phoneAH, faxNumber, email, registrationDate, password, isBuyer, isSeller) CATEGORY (categoryID, categoryName, categoryDescription) ITEM (itemNumber, itemName, itemDescription, itemValue, itemLocation, categoryID, sellerUsername) PAYMENT (paymentID) AUCTION (auctionNumber, currency, startDateTime, endDateTime, shippingTerms, startBidAmount, reserveAmount, bidIncrementAmount, noOfItems, itemSold, itemNumber, paymentDate, paymentid, ratingGiven) BID (bidderUsername, auctionNumber, bidDateTime, bidAmount) CREDITCARDPAYMENT( creditCardNumber, creditCardOwnerName, ExpiryDate, paymentID) PAYPALPAYMENT (payPalUserID, useWinnerAddress, email, paymentID) NOTE: Primary Key (PK) is printed bold and underlined, Foreign Key (FK) is printed italic, PK that is also FK is printed bold and underlined with italic. Attribute itemSold in AUCTION identifies whether an item is sold or not. If the value of that attribute is ‘Y’, it means the item in the particular auction is sold. Attribute ratingGiven in AUCTION identifies the rating given to the seller by the winner of the auction. Provide the SQL statements for questions (a) to (e) (a) Display the number of members of each country starting from the highest to the lowest number. [8 marks] (b) Find which category within the Online Bid Auction database has the largest number of items. [8 marks] (c) Display the names and contact details of members who have never listed any item for auction. [9 marks] (d) Display a list of items currently in auction within “Pottery” category. You only need to display the item if the current bidding price of the item is higher than its reserve amount. (Note: “Pottery” is a value of attribute categoryName) [10 marks] Solutions: a) SELECT Country, COUNT (*) FROM MEMBER GROUP BY Country ORDER BY count(*) DESC; b) SELECT c.categoryId, c.categoryName, c.categoryDescription FROM category c, item i WHERE c.categoryId = i.categoryId GROUP BY c.categoryId, c.categoryName, c.categoryDescription HAVING COUNT(c.categoryId) = (SELECT MAX(COUNT(categoryId)) FROM item GROUP BY categoryId); c) SELECT m.username, m.firstName, m.lastName, m.address, m.city, m.country, m.postcode, m.phoneAH, m.phoneBH, m.email FROM MEMBER M WHERE m.username NOT IN (SELECT i.sellerUsername FROM ITEM i, AUCTION a WHERE i.itemNumber = a.itemNumber); d) SELECT I.itemNumber, I.itemName, B.bidAmount FROM AUCTION A, BID B, CATEGORY C, ITEM I WHERE I.itemNumber = A.itemNumber AND A.auctionNumber = B.auctionNumber AND I.categoryid = C.categoryid AND B.bidAmount > A.reserveamount AND A.itemSold = ‘N’ AND C.categoryname = ‘Pottery’ ORDER BY I.itemNumber; Question 9. [35 marks] CUSTOMERS Cust_no Cust_name Street Town Post_code Cr_limit Curr_bal 1066 Bundy Shoe Kings St Bundoora 3083 500 450 13144 Target High St Clayton 3800 3000 3000 1776 Harry Store Elphin Rd Hawthorn 3122 500 500 2001 Kmart Burke Rd Kew 3101 500 0 2002 Qshoe Glen Rd Kew 3101 1000 1000 PRODUCTS Prod_cod Description Prod_group Prod_ price Qty_on_ hand Remake _level Remake_ qty MO9 Male Runner Male Shoes 150 6 3 5 LO9 Lady Runner Female Shoes 250 1 1 3 ST1 Girls Runner Female Shoes 300 10 5 15 LT1 Tennis Shoe General 450 1 2 2 LT9 Tennis Shoe General 100 20 15 20 GN9 Beach Sandal General 10 100 150 200 ST1 Slipper General 50 50 35 20 GA2 Soccer Shoe Male Shoes 500 10 15 40 ORDERS Order_no Order_date Cust_no 0001 01/07/09 13144 0002 02/07/09 13144 0003 02/07/09 1066 ORDER_DETAILS Order_no Prod_cod Order_qty 0001 ST1 10 0001 GA2 2 0002 ST1 5 0003 GN9 10 Where: cr_limit: The maximum that a customer is allowed to owe curr_bal: The amount currently owed by the customer list_price: The advertised price for a single unit of a particular product remake_level: The level to which the quantity on hand is compared; if qty_on_hand falls below this level, then the shoe supplier company will usually make another batch (to the manufacturer) to avoid stockout. remake_qty: The quantity usually made in any new batch. order_price: The unit price charged on this order for this product order_date: The date on which the order was taken. Based on the above tables: a. Write a stored procedure which receives a customer number (Cust_no) as input and displays the list of orders made by the customer together with the total price of each of the orders. [20 marks] b. Identify the trigger that correctly updates the QOH in the PRODUCTS table whenever a new order detail in ORDER_DETAILS table is made. [15 marks] Question 9 solutions: a) CREATE OR REPLACE PROCEDURE CustomerOrders (CustomerNum NUMBER) AS CURSOR orderCursor IS SELECT O.Order_no, O.Order_date, SUM (P.Prod_price * OD.Order_qty) AS totalPrice FROM Orders O, Order_Details OD, Products P WHERE CustomerNum = O.CustNo AND O.Order_no = OD.Order_no AND OD.Prod_cod = P.Prod_cod GROUP BY O.Order_no, O.Order_date; BEGIN FOR orderPointer IN orderCursor LOOP DBMS_OUTPUT.PUT_LINE (orderPointer.Order_no || ‘ ‘ || orderPointer.Order_date || ‘ ‘ || orderPointer.totalPrice); END LOOP; END CustomerOrders; / b) CREATE OR REPLACE TRTIGGER New_Order BEFORE INSERT ON ORDERS_DETAILS FOR EACH ROW BEGIN UPDATE PRODUCTS SET Qty_on_hand = Qty_on_hand - :new.order_qty WHERE Prod_cod = :new.Prod_cod; END New_Order; /
欢迎咨询51作业君
