Section I: Normalization
Database Management Systems Practice Final Exam
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
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
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
3. List the location of all events where a cheese tray was served.
4. Rewrite the query in question 4 to include desserts which have not been served at any event.
Section III: PLSQL
1. 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, ,weight from item,shipmentline,shipment
where item.itemno and shipmentline.shipmentid shipment.shipmentid and shipment.warehouseid ;
cursor allwarehouses is
select warehouseid
from warehouse;
TB constant char1 : CHR9; begin
for wh in loop
dbmsoutput.putlineWarehouse ID: wh.warehouseid; dbmsoutput.newline;
dbmsoutput.putlineItem NumberTBDescriptionTBWeight; dbmsoutput.putline; for det in listitems loop
dbmsoutput.putlinedet.itemnoTBdet.descriptionTBdet.weight; end loop;
dbmsoutput.newline;
end loop; end;