Students are to create a database design specification (entity relationship diagram (ERD) and relational data model (RDM)) from the given business description below. Students are to present this in a report (as specified in the submission format) which also includes a short discussion of the approach for the solution. This assignment is to be carried out and reported individually.
The following defines the minimum information that should be reported:
1. A brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered
2. The Entity Relationship Diagram (ERD) (produced using a drawing tool such as those found in MS Word or PowerPoint, or a chosen CASE tool) for the system. Your ERD must use the ER notation that was taught in ICT701. Any assumptions made in creating the ERD must be explicitly stated in your report.
3. The Relational Data Model (RDM) which corresponds to the ERD. That is, the set of tables in 3rd normal form that could be used to represent the whole of this data model. Your RDM should identify primary, alternate and foreign keys.
Resolve the following relation to 3rd Normal Form. It is a requirement to show both the 1NF and 2NF that you found as part of the process in moving to 3NF.COMPACT_DISK(title number, album name, distributor id, distributor name,((track number, track name, track duration, ((artist id, artist name, date of birth, age, instrument_type, instrument description
Complete the following SQL tasks using MySQL. The marks available for each SQL task are indicated.
1) Write the SQL code that will create the table structure for a table named EMPLOYEE. The basic EMPLOYEE table structure is summarized in the table below.
3) Write the SQL code that will list all attributes for a job code of 502 in the EMPLOYEE Table.
4) Write the SQL code that will save the changes made to the EMPLOYEE table.
5) Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results, and then by using SQL reset the job code to its original value. All SQL code to complete this full task must be shown in your submission.
6) Write the SQL code to create a copy of EMPLOYEE, naming the copy EMP_2. Then write the SQL code that will add the attributes and associated constraintsEMP_PCT and PROJ_NUM to its structure. The new attribute characteristics are:
7) Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in the table below. .