ICT701
Relational Database Systems
Task 2
Assessment and Submission Details
Marks: 30% of the Total Assessment for the Course
Due Date: as per Course outline
Submit your assignment to the link under Assessment- Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided.
The assignment will be marked out of a total of 100 marks and forms 30% of the total assessment for the course. ALL assignments will be checked for plagiarism by SafeAssign system provided by Blackboard automatically.
Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines.
Assignment submission extensions will only be made using the official Faculty of Arts & Business Guidelines.
Requests for an extension to an assignment MUST be made to the course coordinator PRIOR to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances.
Specific Instructions
You must use MySQL to develop the database required in Parts B & C. MS Access is not appropriate for any section of this assignment and is not to be used.
You must use the ER notation that was taught in ICT701in Part A. Penalties will apply to incorrect notations.
Any assumptions made in creating the ERD in Part A must be explicitly stated in your report.
Submission Format
For Part A you are to include a word document or PDF that contains: 1. Discussion of your approach to the modelling exercise
2. The ER Diagram
3. The Relational Schema (including primary & foreign keys)
4. Supplementary design requirements (e.g. any information on length of identifiers, postcodes, names, what data attributes are compulsory, structure and or format of any columns etc.)
5. All assumptions that explain important design choices you made
For Part B you are to submit a report name studentNumber PartB (either a PDF or word document, showing your progression from the given Unnormalised relationship form to 3rd Normal Form.
For Part C a single plain text file, name studentNumber PartC.sql. In this file you are to include all the SQL for all parts of Part C. Separate each of the 16 tasks of Part C with a comment line. Failure to include all statements required to answer a task in Part C will results in the loss of some or all marks for that task.
Submission
The completed assignment is to be submitted to Blackboard by the due date.
The assignment will be assessed according to the marking sheet. Late submission will be penalised according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late.

Part A (50 marks) Requirements
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 (maximum of ½ page).
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.
Business Description
Jo Bloggs Auto Traders sells motor vehicles. They sell new and used vehicles. For all vehicles, they store the unique vehicle id and the chassis number. For new vehicles, they also store information about the manufacturer name, colour, price, discount (there are 44 types of discount, each of a fixed percentage), engine capacity and details about extras, which would include car radios, air conditioning, etc. For used cars, they only store vehicle id, price and description. Each new vehicle has only one manufacturer (but a manufacturer may supply many different vehicles), and they store details about the manufacturer’s name, address, telephone and industry rank. A manufacturer may have many addresses, each of a particular type (e.g. postal) and one telephone number only per address. For new cars, each car may have many extras, for example ‘RA’ would signify car radio. Jo Bloggs employs many salespeople who sell to clients. For salesperson and client, they store name and address. For each sale, they store the client details, the vehicle details, the price and the commission to the salesperson. An interesting point: only one client may buy a particular vehicle but several salespeople may share the sale.
Part B (10 marks)
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 )) )) )
NOTE: The brackets in the above unnormalised relation data indicate non-atomic attributes (or repeating groups)
Part C (Total 40 Marks – 16 parts)
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.
ATTRIBUTE (FIELD) NAME DATA DECLARATION
EMP_NUM CHAR(3)
EMP_LNAME VARCHAR(15)
EMP_FNAME VARCHAR(15)
EMP_INITIAL CHAR(1)
EMP_HIREDATE DATE
JOB_CODE CHAR(3)
(1 mark)
2) Having created the table structure in Part C1 (1) above, write the SQL code to enter all the data in the table below into your EMPLOYEE table.
(3 marks)
3) Write the SQL code that will list all attributes for a job code of 502 in the EMPLOYEE Table. (2 marks)
4) Write the SQL code that will save the changes made to the EMPLOYEE table. (1 mark)
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. (3 marks)
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 constraints EMP_PCT and PROJ_NUM to its structure. The new attribute characteristics are:
EMP_PCT NUMBER(4,2)
PROJ_NUM CHAR(3)
(3 marks)
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. . All SQL code to complete this full task must be shown in your submission.
(4 marks)
8) Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. (2 marks)
9) Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification
(JOB_CODE) is 502 or higher. (2 marks)
10) Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994 and whose job code is at least 501. (4 marks)
11) Write the two SQL command sequences required to:
a. Create a temporary table named TEMP_1 whose structure is composed of the EMPLOYEE attributes including EMP_NUM and EMP_PCT attributes.
b. Copy the matching EMPLOYEE table values into the TEMP_1 table. (4 marks)
12) Write the SQL command that will delete the newly created TEMP_1 table from the database. (1 mark)
13) Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Assume case sensitivity. (4 marks)
14) Write the SQL code to find the average bonus percentage in the EMPLOYEE table you created in Problem 8. (2 marks)
15) Write the SQL code that will produce a listing for the data in the EMPLOYEE table in ascending order by the bonus percentage. (3 marks)
16) Write the SQL code that will list only the distinct project numbers found in the EMPLOYEE table. (1 marks)
Appendix A
Marking Sheet for ICT701 Task 2
Student name:
Student ID:
Items Maximum Marks Marks Obtained
PART A: DESIGN (50 marks made up of)
- ER Diagram (20 marks)
Completeness (participation & cardinality constraints & all relevant data represented)
- Accuracy
- Relational Schema & normalisation (20 marks)
- Assumptions/Additional Information (10 marks) 50
PART B: DATA NORMALISATION (10 marks)
10
PART C: SQL STATEMENTS: Marks as per assignment
documentation for Part C. Marks will be allocated on correctness and completeness of SQL statements. 40
Total = 100
/30%