SET08104 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). Your total will be divided by 10 (e.g. if you achieve 25% this will become 2.5 points of the coursework).

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 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:

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 5 November 2010.

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.

Coursework demonstrations

In addition to the printout, you also need to demonstrate Part 1 and 2 of your coursework in Week 10 or 11. If you cannot attend any practicals in Week 10 or 11 for whatever reason, you need to email the lecturer in advance. The purpose of the demonstration is to make sure that the code in your printout actually exists in your account.

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

You need a minimum of 16 points to pass the coursework.

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.