資料庫設計與正規化技巧：練習題

Which of the following is the primary reason for normalising a database to Third Normal Form (3NF)?

A relation is in Third Normal Form (3NF) if and only if it is in 2NF and which other condition is met?

In a database for a hospital, a relation exists: Prescription(PrescriptionID, PatientID, DoctorID, DoctorPager).
\(PrescriptionID\) is the primary key.
\(DoctorID \rightarrow DoctorPager\).
What steps are required to move this from its current form to 3NF?

A database relation is in First Normal Form (1NF) if it meets which of the following criteria?

A relation \(R(A, B, C, D)\) has a composite primary key \((A, B)\). The following functional dependencies exist:
\(A, B \rightarrow C, D\)
\(A \rightarrow D\)
Which normal form does this relation currently satisfy?

What is the primary reason for applying normalization to a database design?

Describe the characteristics a relation must possess to be in First Normal Form (1NF).

Explain why a relation with a single-attribute primary key that is already in 1NF is automatically in 2NF.

A school database uses the following table to store exam results:
ExamResults(StudentID, SubjectName, TeacherName, Score)

The primary key is (StudentID, SubjectName). The following functional dependency exists:
SubjectName \(\rightarrow\) TeacherName

a. Explain why this table is not in Second Normal Form (2NF). (2 points)
b. Redesign the table(s) so that they satisfy 2NF. (2 points)

Consider the following unnormalised table for a library system:
Library(BookID, Title, AuthorID, AuthorName, Genre, PublisherName, PublisherAddress)

The primary key is BookID. Assume an author has only one name and a publisher has only one address.

Describe the steps required to transform this relation into Third Normal Form (3NF), identifying the specific dependencies that must be removed at each stage.