SET08104 Resit Coursework 2009-2010

Official Frontpage of this assessment

You must complete all parts of this coursework. Any files in your DCS account must be read-protected from other students (e.g. chmod 600 filename.sql).

Part 1: SQL

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 public relations department. (The jobs database was created during the mysql exercises).

Each department has one staff member who is the public relations contact of that department. The staff member in the Accounts department is Robert Smith. The staff member in the Administration department is James Gibson. The staff member in the Software Design department is Liza Brunell. The staff member in the Communications department is Audrey Cowan. The staff member in the Engineering department is Luke Kennedy. Each staff member receives a bonus of £200.00, but it is expected that these values might change in the near future.

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 public relations table from Part 2 above. You can find an ER Diagram of the original jobs database 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:

Retail( rID, rlocation, (staffID, staffName, staffPhNumber), (productID, productName, amount));

These are the functional dependencies:

staffID -> staffName
staffID -> staffPhNumber
rID -> rlocation
productID -> productName

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 2010, 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 DCS 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.