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.

查看解析

登录即可查看完整答案

类似问题

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?

更多留学生实用工具

考试浏览器助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单