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).
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.
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.
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.
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.
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: SQL||10 points|
|Part 2: Creating a table||10 points|
|Part 3: ER Diagram||5 points|
|Part 4: Normalisation||15 points|
You need a minimum of 16 points to pass the coursework.
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.