Cardiff School of Computer Science and Informatics
Coursework Assessment Pro-forma
Module Code: CMT207
Module Title: Information Modelling and Database Systems
Assessment Title: Relational data modelling
Assessment Number: 1
Date Set: 04 March 2019
Submission Date and Time: 12 April 2019 at 9:30AM
Return Date: 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/down…
Submission Instructions
A submission page will be created on Learning Central in Week 11. You will need to submit two
documents:
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
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.
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)
- Suggest the attributes that should become part of the primary key.
- List all functional dependencies.
- Provide all reasons why this relation is not in the first normal form.
- Provide all reasons why this relation is not in the second normal form.
- 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: - Find all student pairs born in the same place. Note: Do not double count the students.
- Find all student-lecturer pairs who live in the same place.
- Find a total number of students per module in the academic year 2017/2018.
- Find the average mark for each module in the academic year 2017/2018.
- Find all students who failed a module in the academic year 2017/2018.
- Find the best performing student(s) in the academic year 2017/2018 based on the number
of credits and the marks achieved. - Find the highest paid lecturer(s).
- 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: 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.
Part 2: Correctness/simplicity of the model. Following good practice in conceptual design
described in the lecture slides presented in Week 3.
Part 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.
Part 4: Correctness, simplicity and efficiency of queries.
The overall mark can be interpreted as follows:
Distinction 70–100
Merit 60–69
Pass 50–59
Fail 0–49
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.
WX：codehelp