1.

Prepare a database specification (either a RAL or an ESG showing database-related details only) for the database. This database

specification may be refined by introducing a sixth relational table, and adjusting three other tables to each have a foreign key that

references this additional table.

1a. Identify the additional table that is required, and clearly describe the adjustments that need to be made to three tables in order to had a

normalized database. [08]

1b. Propose an ESG or RAL that provides specifications for the six relational tables of the music database. [36]

2.

2a. Write appropriate SQL statements to create the tables specified in the figure (you may assign appropriate column types and lengths); also include important integrity constraints. Use your SQL statements to create the tables. [36]

2b. Insert sample data into your database tables (at least six records per table). Your data must illustrate the role of foreign keys and referential integrity. [18]

3.

Write SQL statements to realize the following:

3a. List all registered musicians from USA or JAM (where "USA" and "JAM" are abbreviations for United States and Jamaica). [03]

3b. Give the ENO of every ensemble that includes a SAXAPHONE or CLARINET player. [03]

3c. Give the ENO of every ensemble that includes a SAXAPHONE but not a CLARINET player. [04]

3d. List all compositions (CNO) by MOZART [04]

3e. List all performances (PNO CNO MNO & PCOUNTRY) of compositions in the country of origin. [06]

3f. Give the ENO of every ensemble that includes a SAXAPHONE or CLARINET player, but not both. [06]

3g. Find CNO for compositions which have been performed in USA. [03]

3h. List countries in which MOZART's compositions have been performed. [03]

3i. Give ENAME of ensembles whose manager is AMERICAN, JAMAICAN, or RUSSIAN. [03]

4. Define and create SQL views to realize the above requirements (of question 3). [36]

Music Database Relations Musicians (Mog. MName, MDOB MCountry} PK (Mnol Compositions (Co. CTitle CMno. CDate} PK [Cng]: FK [CMng references Musicians) Ensembles {Eno, EName ECountry, EMnoMor} PK [Eno]; FK [EMnoMar references Musicians) Performances (Png. PDate. Pone PCity. PCountry Peno_CKs (PDate. Penol. Prel; FK [PCno references Compositions); FK [PEno references Ensembles] Ensemble Members (EmEng. EmMno, Emlostrument) PK [EmEng. Emunol: FK [EmEng references Ensembles); FK EmMno references Musicians)