i. List all the referential integrity constraints that should hold on the database schema? (2.5 marks)

ii. Write appropriate SQL DDL statements to define the database with the integrity constraints and store in a text file called userid_salesrep_schema.sql. Attach this file or also show it in your script file of (v) using the unix command more file.sql before or after running sqlplus. Do the same for the files in (iii) and (iv). (2.5 marks)

iii. To insert the data in the database tables, also write appropriate SQL DML instructions in a text file called userid_salesrep_data.sql. (2.5 marks)

iv. To remove any inserted data and destroy all created tables in the salesrep database, write appropriate SQL DML and DDL statements in a text file called userid_salesrep_droptable.sql to first delete all data in the tables and then drop the tables. (2.5 marks)

v. Using Oracle Sqlplus, implement this database design by creating all the tables with the integrity constraints using the SQL DDL you defined in (ii) above. You can create all these SQL DDL for creating the 6 tables by running your .sql file at the SQL prompt with the command:

Rep RID 11111 22222 33333 44444 RNAME Mary Peters Steve Markel Melanie Good John Doe RAGE 42 25 61 36 SALARY 150000 221000 188000 85000 Area ARID WDT SW TN ANAME CITY Down Town Windsor South Windsor Windsor Tecumseh North Tecumseh BUDGET 500000.99 950000.00 420000.50 Product PRID 1 2 3 4 PNAME pots towels apple basket potato bag PDESC cookware bath supply fruit food PRICE 101.00 45.50 30.30 5.50 RepSellsArea Rid 11111 11111 33333 44444 Arid TN SW SW WDT hours 12 10 33 revenue 20000.00 1500.00 40000.00 37200.00 28 RepSells Product Rid 11111 11111 33333 44444 Prid 1 2 2 4 hours 6 10 20 12 revenue 2000.00 4500.00 9000.00 3300.00 AreaSells Product revenue ARid WDT WDT SW Prid 1 2 2 4 hours 18 12 20 12 2000.00 6600.00 9000.00 3300.00 TN Fig 1.2: Schema of the 'Sales rep sells products in sales areas' database of Figure 1.1 Rep (Rid, Rname, Rage, salary) Area (Arid, Aname, city, budget) Product (Prid, Pname, Pdesc, price) RepSellsArea (Rid, Arid, hours, revenue) RepSells Product (Rid, Prid, hours, revenue) AreaSells Product (Arid, Prid, hours, revenue)