Section I: Normalization
Database Management Systems Practice Final Exam Solutions
Archies Golden Supply is a distributor of supplies to the fast food industry. The owner of the company, Archie McKing, has hired you to design a Webbased database management system to track inventory and sales. Presently, the data for inventory and sales tracking are stored in one file with the following format:
StoreName, SupplyDescription, Price, Cost, ManufacturerName, ShippingTimeFromManufacturer,QuantityonHand, DateofLastDelivery, QuantityofLastDelivery, DeliveryDate1, QuantityofDelivery1,DeliveryDate2, QuantityofDelivery2, DeliveryDate3, QuantityofDelivery3,DeliveryDate4, QuantityofDelivery4, ReorderLevel
Assume that we want to track all purchase and inventory data, such as what was delivered, how many were delivered, etc. The present file format allows only the tracking of the last purchase and delivery as well as four prior purchases and deliveries.
a. Given this file structure, draw its dependency diagram. Label all transitive andor partial functional dependencies.
b. Break up this dependency diagram to produce dependency diagrams that are in third normal form. You might need to create a few new attributes.
c. Draw the relational schema
StoreName
SupplyDesc
Price
Cost
ManufacturerName
ShippingTime
QuantityonHand
DateofLastDelivery
Quantity of LastP
DeliveryDate1
DeliveryDate2
DeliveryDate3
DeliveryDate4
Quantity1
Quantity2
Quantity3
Quantity4
Now, lets create relations in 3NF
Store Ive added a store id and address info
Supply Ive added SupplyID
Inventory
Delivery Ive added quantity and a surrogate key, DeliveryID
Supplier
StoreID
StoreName
Address
City
State
ZipCode
SupplyID
SupplyDesc
StoreID
SupplyID
ManufactID
ShippingTime
Cost
Price
QuantityonHand
DeliveryID
StoreID
SupplyID
DeliveryDate
Quantity
ManufactID
ManufactName
Section II SQL Queries
Using the Richards Catering Case Study, please write SQL queries for the following questions.
1. List the last name and first name of all staff supervised by Wilma Smith who did work on the 23Jan1999 event
select lname,fname
from staff,eventstaff,event
where staff.supervisor select ssn from staff where lname Smith
and fname Wilma
and event.eventid eventstaff.eventid and eventstaff.ssn staff.ssn and eventdate 23Jan1999
Lname Fname Williams Warren Roth Earl
Yolenda Meredith
2. Find all main courses which have never been served at an event that starts on or after 6 pm hint use start hour here 18 is 6 pm
select description
from maincourse
where maincourseid not in
select maincourseid from menu,event
where menu.menuid event.menuid and starthour 18
results:
Chicken Picatta with Rice Chicken Picatta with Potato Pasta Primavera
Baked Ziti
Omlet with Homefries French Toast
Eggs Benedict
3. List the location of all events where a cheese tray was served.
select location
from event,menu,courseitem,dish
where event.menuid menu.menuid
and menu.maincourseid courseitem.maincourseid and courseitem.dishid dish.dishid and dish.description Cheese Tray;
Results: Westboro Lodge
4. For each dessert, print the name of the dessert and the number of times that it has been served at an event.
select dessert.description,countevent.menuid from dessert,menu,event
where dessert.dessertid menu.dessertid and menu.menuid event.menuid
group by dessert.description
results:
Cheesecake w Strawberries 4 Chocolate Cake 3
5. Rewrite the query in question 4 to include desserts which have not been served at any event.
select dessert.description,countevent.menuid from dessert,menu,event
where dessert.dessertid menu.dessertid and menu.menuid event.menuid
group by dessert.description
Results:
Fruit Torte 0
Cannoli 0
Cheesecake w Strawberries 4 Chocolate Cake 3
Chocolate Mousse 0
Ice Cream 0
Section III: PLSQL
For the following stored procedure, please fill in the missing information in the underlined areas:
create or replace procedure getitemsshipped as
cursor listitemswid in warehouse.warehouseidtype is
select item.itemno,description,weight
from item,shipmentline,shipment
where item.itemno shipmentline.itemno
and shipmentline.shipmentid shipment.shipmentid and shipment.warehouseid wid;
cursor allwarehouses is
select warehouseid
from warehouse;
TB constant char1 : CHR9;
begin
for wh in allwarehouses loop
dbmsoutput.putlineWarehouse ID: wh.warehouseid; dbmsoutput.newline;
dbmsoutput.putlineItem NumberTBDescriptionTBWeight; dbmsoutput.putline; for det in listitemswh.warehouseid loop
dbmsoutput.putlinedet.itemnoTBdet.descriptionTBdet.weight; end loop;
dbmsoutput.newline;
end loop; end;