SOUNDMISSION Case Study

SOUNDMISSION is a UK company that is specialised in hiring out audio equipment to recording artists to be used for the recording, mixing and mastering of music projects such as music tracks and albums. The company is based on a n umber of recording studios located all over the UK. SOUNDMISSION offers a really wide range of studio equipment to their customers such as analogue and digital recorders, mixing desks, audio interfaces, microphones, studio monitors, digital converters, controllers and other miscellaneous studio accessories.
Coursework Part B Specifications

You have been hired by SOUNDMISSION a database architect consultant to undertake a database project to support all the business processes carried out by the organisation. You are given a conceptual data model for SOUNDMISSION (figure 1) and your goal in this second part is to derive from it a high-quality LOGICAL RELATIONAL DATA MODEL to logically represent how the key business data needs to be organised as a set of interrelated relations. These relations need to be interconnected according to the strict rules of the relational model so that they can then be translated into a set of operational database tables. You also have to produce the SQL code to create some of the tables and retrieve information from them.

1) Very carefully explain how you can map the SOUNDMISSION conceptual ERD (next page) into a full logical relational data model.
In other words, refer to the Logical Data Modelling Methodology and explain what you need to do to resolve each relationship and derive the relevant relations (i.e. tables) with all their attributes, primary keys and foreign keys. Your written explanation should not take more than 800 words (± 10%). The use of a table or bullet points is required.
[33marks for explanations and for mapping of conceptual ERD to logical ERD]

2) Produce a complete Logical Entity-Relationship Diagram for SOUNDMISSION.
This needs to include all the relations, relationships, multiplicity constraints, attributes, primary keys and foreign keys that you have previously identified.
[Diagram with all previous explanations add up to 33 marks, additional 05 marks for readability]

3) Produce the SQL code (DDL and DML operations) to complete the following tasks.

a) Create a table to store the details of all the vans used by SOUNDMISSION.
[04 marks]

b) Create a table to store the details of all the drivers working at SOUNDMISSION.
[04 marks]

c) Retrieve the details of all the Mercedes Vans registered after 01/01/2010 along all the vans with a mileage of less than 10000 miles.
[04 marks]