Database1

Question 2 Tables below are extracted from a manufacturing company database; Table 5: WAREHOUSE WarehouseID WarehouseCity WarehouseState Manager SgFeet 100 Atlanta GA Dave Jones 125,000 200 Chicago IL Lucille Smith 100.000 300 Bangor MA Bart Evans 150,000 400 Seattle WA Dale Rogers 130,000 Table 6: INVENTORY Warehouse ID SKU SKU. Description QuantityOn QuantityOn Hand Order 250 0 100 100100 Std. Scuba Tank, Yellow 200 100100 Std. Scuba Tank, Yellow 100 50 300 100100 Std. Scuba Tank, Yellow 100 0 400 100100 Std. Scuba Tank, Yellow 200 0 100 100200 30 Std. Scuba Tank, Magenta 200 Std. Scuba Tank, Magenta 75 200 100200 75 300 100200 Std. Scuba Tank, Magenta 100 100 400 100200 Std. Scuba Tank, Magenta 250 0 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by 'Lucille Smith'. Use a subquery. Te Based on Table 8, answer the following questions: (a) Identify the normal form for Employee Project relation. [2 marks (b) Transform the above table (Table 8) into third normal form (3NF). [8 marks) CISB214/CISB3213, Semester 1, 2020/2021 Table 7: WAREHOUSE STRUCTURE Column Name Type Key Required Remarks Primary Key YES Warshouse! Integer WarehouseCity VARCHAR2(30) Warehouse State VARCHAR2(2) YES YES IN ("GA', *MA', 'WA') only Manager VARCHAR2(35) SaFest Integer [8 marks] 1 - D