ADVANCE DATABASE MANAGEMENT SYSTEM

QUESTION 1 (COMPULSORY) Suppose a database system required to help track books being sold by different bookshops must contain the tables below. Data need to be kept on each bookshop, the books they sell, and the publishers. Also, data need to be kept on sales made by each bookshop. BOOKS ISBN Book Title 1356487256 Oracle Schema Objects 1545454545 Mastering SQL 4343439898 Programming Fundamentals 9893434399 IP Addressing Publisher P1 P2 P1 P3 Pub Id P1 P2 P3 PUBLISHER Pub Name McGraw-Hill Pearson Eddy Williams Shop Id BS1 BS2 BS3 BOOKSHOP ShopName Challenge EPP E&A Location Madina Circle Madina SALES Shop Id Book_ld SalesPrice QtySold SalesDate BS1 1356487256 10.50 50 5-11-2014 BS1 4343439898 20 40 5-11-2014 BS2 4343439898 20 80 8-11-2014 BS3 9893434399 9.50 45 20-10-2014 BS1 9893434399 9.50 100 21-10-2014 a. Write the SQL commands needed to create the tables above in bookSalesDB schema. Use the sample data and notations to deduce the data type and constraints appropriate for each attribute. (5 marks) b. Write an SQL command to change the structure of the SALES table by adding a primary key column called sales_id (1 mark) c. Write an SQL command to create a Sequence called AUTO_SALESID that will generate numbers starting from 100. (1 mark) d. Write an SQL command to insert one record into the new SALES table created in question b above. The value for the sales_id column should come from the AUTO_SALESID sequence created in question c. above. (1 mark) e. Write an SQL command to create an index on one of the foreign keys in the SALES table. Page 6 of 8