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).
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.
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.
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|
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.