1. Given a database state of the Windsor Music Records database shown in Figure 1.1, with schema shown in Figure 1.2 Total for que 1 is 15 marks) Fig 1.1: An Example Database State of Windsor Music Records Database Musician SSN MNAME Majid Peters Steven Markel MADDR 4747 Oakwood Dr PHONE 1113331112 222222222 333333333 Melanie Good 444444444 John Doe 1231 Berry Ave 423 Walker Rd1231235555 2221113332 5431 Hefty Lane 6751231231 Instrument INAME Flute Trumpet Guitar Drums MUSICKEY C-Flat E-Flat A-Minor B-Minor Album ATITLE Recovery Revival Welcome AID COPYRIGHTDATE FORMAT 2012-08-08 CD CD CD 2015-09-09 2014-04-03 Song STITLE Hello World Happy All Love Sosa Ball For Me AUTHOR 21 Savage 21 Savage Chief Keef Post Malone Play SSN INAME Drums Hlsie Guitar Trumpet Cauitar Contains STITLE Hello World Happy All Love Sosa Ball For Me AID Performs SSN STITLE Hello World Love Sosa Ball For Me Hello World Produces SSN AID Fig 1.2: Schema of the Windsor Music Records database of Figure 1.1 Musician SSN MName Maddress Phone Instrument Iname Musicke Album Aid atitle copyrightdate format Song stitleauthor Play SSN Iname List all the referential integrity constraints that should hold on the database schema? (2.5 marks) Write appropriate SQL DDL statements to define the database with the integrity constraints and store in a text file called userid_musicschema.sql. Attach this file or also show it in your script file of (v) using more file.sql command before or after running sqlplus. Do the same for the files in (ii) and (iv) To insert the data in the database tables, also write appropriate SQL DML instructions in a text file called userid musicdata.sql To remove any inserted data and destroy all created tables in the music database, write appropriate SQL DML and DDL statements in a text file called userid musicdroptable.sql to first delete all data in the tables and then drop the tables (2.5 marks) (2.5 marks) IV (2.5 marks) 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 8 tables by running your .sql file at the SQL prompt with the command @userid _musicschema.sql. After creating your tables successfully, you load your data with the .sql file you created in (ii) above by running @userid_musicdata.sql. If there are errors and you need to correct them, you might want to delete the tuples and drop the tables first using the .sql file you created in (iv) above as with @useridmusicdroptable.sql before re-creating the schema and re-loading the data.Then, using a script file, show the contents of all 8 tables in the database by selecting * from each of the tables and saving on script file called username assn3que1.txt. You can do this using the following sequence of Unix/Linux commands after you have created the database and inserted data. (5 marks) (Note: remember to create the entity tables with primary keys before the relationship tables that reference them through foreign key attributes. When inserting data, do the same. If you need to delete the data and tables at any time, go in the reverse order (that is, delete the tuples that reference a primary key attribute tuple in another table, before deleting the parent primary keyed tuple))
