ERD:

Scenario:

The above ERD represents the details of a simple course database which records the course registration details of students. The course entity has a unique identifier CourseNum and the Registration entity is allocated a partial key. The mode represents the mode of registration: online or on-campus. A student may have more than one phone number.

Semantic Rules:

1. Each course may have many registrations.
2. Many registrations are recorded for each course.
2. Each student may register only once.
2. Each student can have multiple registrations.

Question: Which of the following represents a fully normalised set of relations?

Question

ERD:

Scenario:

The above ERD represents the details of a simple course database which records the course registration details of students. The course entity has a unique identifier CourseNum and the Registration entity is allocated a partial key. The mode represents the mode of registration: online or on-campus. A student may have more than one phone number.

Semantic Rules:

1. Each course may have many registrations.
2. Many registrations are recorded for each course.
2. Each student may register only once.
2. Each student can have multiple registrations.

Question: Which of the following represents a fully normalised set of relations?

BlackTom AI · Accepted Answer

Examining the given scenario and semantic rules helps identify what a fully normalized schema should look like. Here are the options analyzed in turn.
Option 1:
- Course is defined as (CourseNum, Name, Description) which is fine. Registration includes (RegistrationNum, CourseNum, RegistrationDate, Mode, StudentNum*), which correctly models the relationship to Course and Student. However, Student is defined as (StudentNum, StudentFName, StudentLName, Email, Phone) and there is a separate StudentContact(StudentNum, Phone). This puts Phone as both an attribute of Student and as a separate relation, which is inconsistent for normalization. A multivalued phone implies a separate relation is needed, but the presence of Phone in both relations indicates redundancy and potential anomalies. Overall, this option does not represent a properly normalized set of relations because Phone should be stored only in a separate multivalued relation (StudentContact).
Option 2:
- Course is fine with (CourseNum, CourseName, Description). Registration is (RegistrationNum, CourseNum, RegistrationDate, Mode) but lacks a direct link to the Student; there is no StudentNum in Registration, so the relationship between a student and their registrations is incomplete. The Student relation (StudentNum, StudentName, Email, RegistrationNum*) incorrectly ties RegistrationNum to Student as a multivalued attribute, which is not how 1-to-many relationships are modeled in normalization. This option fails to capture the intended connections and violates normalization by embedding a registration reference in Student.
Option 3:
- Course(CourseNum, CourseName, Description) is appropriate. Registration(RegistrationNum, CourseNum, RegistrationDate, Mode, StudentNum*) correctly includes a foreign key to Course and to Student, and uses RegistrationNum as a primary key. Student(StudentNum, StudentFName, StudentLName, Email) properly stores student details with a single key. StudentContact(StudentNum, Phone) cleanly handles the one-to-many phone numbers per student without duplicating Phone in Student. This aligns with the stated rules: a student can have multiple registrations and multiple phones, and a course can have many registrations. Overall, this option appears to be fully normalized.
Option 4:
- Course is fine. Registration(RegistrationNum, CourseNum, RegistrationDate, Mode) is okay but lacks a StudentNum in Registration to link a registration to a student, breaking the registration-to-student relationship. Student(StudentNum, StudentName, Email, Phone, RegistrationNum*) places RegistrationNum as a multivalued or optional attribute inside Student, which is not appropriate for a 1-to-many relationship and introduces redundancy. StudentContact(StudentNum, Phone) would still be present, but the core issue remains: Registration is not properly linked to the Student, violating normalization rules.
In summary, Option 3 provides the correct structure: Course with CourseNum, CourseName, Description; Registration with RegistrationNum, CourseNum, RegistrationDate, Mode, and StudentNum*; Student with StudentNum, StudentFName, StudentLName, Email; and StudentContact with (StudentNum, Phone). The other options either misplace Phone (breaking 4NF-like distinctions), fail to represent the student-registration linkage, or introduce redundant attributes.

View Explanation

Log in for full answers

Similar Questions

What would be the best normalised relational model for the Entity E? Entity: E (A, B, C, D) Functional Dependencies : A, B → C, D C → B

What is the highest Normal Form of the following table? Each Girl can grow more than one Flower and lives at one Address.

What is the highest Normal Form of the following table? Each Owner can own one or more PetTypes. Each PetType may have one or more Owners. Each Owner has one Address and one Phone Number

When Normalisation creates a foreign key, what type of dependency does it correct?

What is the highest Normal Form of the following table ? Each Owner has only one Pet with one PetColour and PetAge. Each Pet has only one Vet.

Using the Entity Relationship Diagram you have drawn on your piece of paper for the Final Subjects Results Table answer the following question. Which answer below shows the Normalised Relational Model of the Final Subjects Results Table.

Using the Entity Relationship Diagram you have drawn on your piece of paper for the Hamilton Island 'BoatRaceResults' Table, answer the following question. Which functional dependency shows a Boyce-Codd Dependency in the BoatRaceResults table?

More Practical Tools for Students Powered by AI Study Helper

Homework AI Solver

Stylized AI Paper Writer

Plagiarism Checker Assistant

Citation AI Academic Writing Tool

In-Class Translation Assistant

AI Note Generator

AI Quiz Answers

Past Exam Questions from University Test Bank

Smart Practice Assistant

Adaptive Practice

Making Your Study Simpler