Cardiff School of Computer Science and Informatics Coursework Assessment Pro-forma
Module Code: Module Title: Lecturer: Assessment Title: Assessment Number: Date Set:
Submission Date and Time: Return Date:
CMT207
Information Modelling and Database Systems Irena Spasić
Relational data modelling
1
04 March 2019
12 April 2019 at 9:30AM
03 May 2019
This assignment is worth 50% of the total marks available for this module. The penalty for late or non-submission is an award of zero marks.
Your submission must include the official Coursework Submission Cover sheet, which can be found here:
https://docs.cs.cf.ac.uk/downloads/coursework/Coversheet.pdf
Submission Instructions
A submission page will be created on Learning Central in Week 11. You will need to submit two documents:
Any deviation from the submission instructions above (including the number and types of files submitted) may result in a mark of zero for the assessment or question part.
Assignment
The coursework is based on the following brief. Please read carefully.
A relational database is used to store information about students. For each student, the following information is stored: surname, first name, home address, date of birth, student number.
In addition, we know the student’s place of birth (by place name and country). There may be places where no student was born or currently resides.
Description
Type
Name
Cover sheet
Compulsory
One PDF (.pdf) file
[student_number].pdf
Coursework
Compulsory
One WORD file (.docx)
NOTE: Use the template provided on Learning Central as
described in the coursework description below.
[student_number].docx
Students complete a set of modules each academic year. Each module has got a title, a code and the number of credits. Some modules are mandatory while others are optional. Each module is assessed by a combination of an exam and coursework assignments each worth a certain percentage of the overall mark. For each student, their performance (i.e. mark out of 100) is recorded for each assessment. Each mark is accompanied by the date of assessment and the attempt number.
Each module is taught by lecturers. Some lecturers may not teach any modules in some academic years. They can also teach more than one module per academic year. Each module can be taught by more than one lecturer. For each module, we know how many lectures are given by each lecturer in each academic year. Optional modules may not be taught at all, e.g. if no students have opted for that module.
For each lecturer, the following information is stored: surname, first name, date of the employment start, annual salary, staff number.
In addition, we know where the lecturers live (by place name and country).
Please answer the questions in the Word submission template, which is provided as a separate document on Learning Central and distributed together with this pro-forma. The questions are divided into four parts.
Part 1: Normalisation [0–20 marks]
The following relation is defined to store information about assessment results:
ASSESSMENT_RESULT(STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE, ASSESSMENT, PERCENTAGE, MARK, ATTEMPT, ASSESSMENT_DATE)
1. Suggest the attributes that should become part of the primary key.
2. List all functional dependencies.
3. Provide all reasons why this relation is not in the first normal form.
4. Provide all reasons why this relation is not in the second normal form.
5. Normalise this relation so that is in Boyce–Codd normal form.
Part 2: ER modelling [0–20 marks]
Draw an entity-relationship (ER) diagram for the problem described in the brief above. The diagram
has to be neat and readable to get full marks.
Part 3: Data definition [0–30 marks]
Translate the ER diagram you created in Part 2 into tables using SQL CREATE TABLE definitions, which should include primary and foreign keys (if any). Describe the logic behind the choice of the keys and their role in managing the data stored in the database. Use examples to illustrate the latter point.
Part 4: Data manipulation [0–30 marks]
Using the database defined in Part 3, write SQL queries to perform the following actions:
1. Find all student pairs born in the same place. Note: Do not double count the students.
2. Find all student-lecturer pairs who live in the same place.
3. Find a total number of students per module in the academic year 2017/2018.
4. Find the average mark for each module in the academic year 2017/2018.
5. Find all students who failed a module in the academic year 2017/2018.
6. Find the best performing student(s) in the academic year 2017/2018 based on the number
of credits and the marks achieved.
7. Find the highest paid lecturer(s).
8. Find lecturer(s) who are not teaching any modules in the academic year 2017/2018.
Learning Outcomes Assessed
Understand how to create a conceptual model and map it to efficient representation in a database schema.
Understand relational database management systems. Criteria for assessment
The maximum mark for each task is given in brackets with a total maximum of 100 marks. Marks will be assigned on the basis of how well you achieved the task objective. In general, your work will be assessed according to the following factors:
Part 1:
Part 2: Part 3:
Distinction 70–100 Merit 60–69 Pass 50–59 Fail 0–49
Correctness and completeness of answers. Correctness of the resulting normalised relations together with the quality of explanation of the steps taken to normalise the relations, where normalisation is necessary.
Correctness/simplicity of the model. Following good practice in conceptual design described in the lecture slides presented in Week 3.
Correctness and simplicity of table definitions and how well they are aligned with the ER model from Part 2. Following good practice in logical database design described in the lecture slides presented in Week 3.
Correctness, simplicity and efficiency of queries. The overall mark can be interpreted as follows:
Part 4:
Feedback
Feedback on your coursework will address the above criteria. It will be entered using track changes and comments in the Word submission document. The marked document will be returned to you via Learning Central within three weeks of submission. This will be supplemented with group feedback, which will also be made available on Learning Central. Further individual feedback in person can be arranged upon request.