SET08104 Resit Coursework 2010-2011

Official Frontpage of this assessment

You must complete all 4 parts of this coursework. For the second part, you need to create a file (with extension ".sql") in your Socweb account. All your SQL statements should be saved in that file. Any files in your Socweb account must be read-protected from other students (e.g. chmod 600 filename.sql).

Part 1: SQL

A printout of your current Assessment total (best closed assessments) for the ActiveSQL tutorial System (as shown on the Main Index Page).

Part 2: Creating a new table

For the jobs database create a table for the course budgets. (The jobs database was created during the mysql exercises).

Courses are managed by departments.
The Accounts department is responsible for courses 1 and 2.
The Administration department is responsible for courses 3 and 4.
The Software Design department is responsible for courses 5, 6, 7 and 8.
The Engineering department is responsible for courses 9 and 10.
The Communications department is not responsible for any courses.
Courses 1-5 each have a budget of £2000.00.
Courses 6-10 each have a budget of £3000.00.

Choose appropriate datatypes for each of the fields. Avoid redundancy. Create the table and insert the data. Decide which keys you might need (primary key, foreign key) and make sure that these are correctly implemented.

Part 3: ER Diagram

Create an ER Diagram for the jobs database including the course budget table from Part 2 above. You can find a link to the ER Diagram of the original jobs database on the Database Systems main page, at this location.
Use the Dia program to draw the ER Diagram (see the Week 7 exercises for instructions on using this program).

Part 4: Normalisation

Normalise the following relation to 3rd Normalform:

Cooking( rID, rname, (ingredientID, ingredientName, amount), cookID, cookName, cookPhNumber);

These are the functional dependencies: cookID -> cookName
cookID -> cookPhNumber
rID -> rname
rID -> cookID
ingredientID -> ingredientName

Your solution should show the relations, steps and justifications for 1NF, 2NF and 3NF.

How to submit the coursework

A printout of the Main Index Page of your ActiveSQL account (Part 1), plus the solutions to Parts 2, 3, and 4, must be submitted to the School Office by 3pm Friday, last week of July 2011, or as published by the School of Computing.

Please, use a 12pt font on A4 paper. Staple the printout in the upper left corner. Please, do NOT use plastic folders. Write your matric number (but not your name) on the first page.

Safeguarding your work and Plagiarism

It is your responsibility to ensure that nobody else copies your work. Any files on Socweb related to the coursework must be read-protected from others (chmod 600 filename). You should not leave any printouts of your coursework on Campus, not even in the rubbish bins.

You should make regular backup copies of your work. No extension will be given if you lose your work!

You are not allowed to discuss any part of this assessment with other students. If you have any questions about this assessment, please, ask the lecturers.

Marking Scheme

If you submit your coursework late (between 1 and 7 days after the deadline), the mark will be capped at 40%. After that your coursework will be marked as "fail".

You can reach a total of 40 points for the coursework. This is 40% of your final mark. The points are distributed as follows:

Part 1: SQL10 points
Part 2: Creating a table10 points
Part 3: ER Diagram5 points
Part 4: Normalisation15 points

Learning Outcomes

This assessment covers the following Learning Outcomes:

LO2: Implement a relational database schema from initial written specifications.
LO3: Utilise SQL and relational algebra for database manipulation.
LO4: Utilise the techniques required to implement the components of a DBMS.