Q1
Could you clarify the Q5 in Part 1: Normalisation that ‘normalise this relation so that is in Boyce-Codd normal form’? Couldn’t the answer for Q2 already be in Boyce-Codd normal form? Thank you.
A
You need to check Lecture_03_Logical database design (normalisation), slide 10. The slides show the relationship between normal forms. BCNF has to be in 2NF. Please read Q4.
Q2
Part 4 question 1 states: ‘Find all student pairs born in the same place. I’m confused what we would link on in this query, is it just finding at least 2 students who were born in the same place and how often this occurs?
A
No, as the questions says: ALL student pairs born in the same place. And nothing else.
Q3
What do you mean with: “For each module, we know how many lectures are given by each lecturer in each academic year” Does it mean that I have to say that lecturer A gives 50 hours of lecture for module X in the academic year 201X/201X?
A
Say that I share a module of total 60 hours with two other lecturers. Then it is known how much each of us will teach, e.g. 40 + 10 + 10. Do note that these numbers are just examples. You do not actually need to supply any of these. Do differentiate between a database instance and a schema. Instance refers to the actual content and that is not the question here. You should develop a schema, i.e. a database structure that will be capable of storing such information.
Q4
Part 1, what is meant with the attribute “Assessment”? Is that an assessment-ID or an assessment-name to identify a specific assessment?
A
You can make that choice. Just make sure you explain all your assumptions. In any case you are right here “to identify a specific assessment”. I would just add “within a given module”.
Q5
Part 2: The ER-modelling is related to the whole assignment or just related to part 1?
A
Part 2 says explicitly “ER modelling”. Part 3 makes reference to Part 2. Therefore, I am not sure I understand your question.
Q6
Part 3 question 5: When has a student failed a module? When one of his exams/ coursework¡¯s has a grade less 50 points or when on average the student has less than 50 marks?
A
Again, specific values do not matter at all as long as you explain your assumptions. However,
do try to mimic the real world. For example, you can use your student handbook to do some research and then reference it to back up your assumption. In reality, you fail when your overall mark is <50 or 40 for undergraduates.
Q7
Part 3 question 6: Would a student with an average grade of 90 points and 120 credits be better than a student with 95 point but just solved 90 credits? Or is the goal in that question to get the student with the best average grade based on the credits of his assessments?
A
You should make your own assumption based on common practice. Again, try to do a bit of research and explain your assumptions. You can check this web page for example:
https://gradecalculator.mes.fm/weighted-average-calculator
Q8
"Some modules are mandatory while others are optional" Is that a very important part of this assignment, because I honestly don't know how to process that information in my model.
A
That is very simple binary information that can be sorted by a single attribute. However, you may wish to think about constraints to enforce consistency of your data, e.g. a student has to
enrol on a mandatory module.
Q9
I have a question regarding the coursework. I understand that you can't give too much away in regards to your answer, but I'm struggling to understand how in Part 1: Question 3, the table is not in 1NF? I don't see anything to suggest that there are any non-atomic values in the table and I just wanted to make sure that I'm understanding correctly. I can see how it's not in the other normal forms but I am finding it difficult with 1NF.
A
It is definitely not in 1NF, but you are right. I cannot point to the reasons why.
Q10
"Find the best performing student(s) in the academic year 2017/2018 based on the number of credits and the marks achieved." How can I understand the 'number of credits'?
A
You may use the number of credits to break a tie between students who have the same average but different number of credits.
Q11
I met a problem when doing question 1. Each module has got a title, but could the title name be the same as other modules? For example, I found Cardiff University has 'Informatics' both in undergraduate and taught postgraduate core modules. I am sending this email to ask you, should we write both two cases in the answer fields? Or we just analyse one situation (repeated/non-repeated module title name).
A
Please check Lecture_03_Relational data model, slide 18. This is where we extensively discussed that we can never ever rely on an assumption that names/titles would be unique.
Q12
A
Q13
A