Database Design Methodology: Practice Questions

A database table named StudentEnrollment is designed to store information about student course registrations. It has columns: EnrollmentID, StudentID, StudentName, CourseID, CourseTitle, and InstructorName. The primary key for this table is a composite key: (EnrollmentID, StudentID, CourseID).

However, it is observed that StudentName is repeated for every course a student enrolls in, and CourseTitle and InstructorName are repeated for every student enrolled in a specific course. Which normal form is immediately violated by the repetition of StudentName due to its dependency on only a part of the primary key (StudentID)?

A database table stores information about products with attributes (ProductID, ProductName, SKU, Barcode, Price). Both ProductID and SKU are unique identifiers for each product, and Barcode is also unique. The designer chooses ProductID as the primary key.

What is the significance of SKU and Barcode in this context, even though they are not chosen as the primary key?

What is the primary objective of normalizing a database?

Consider an unnormalized table EmployeeSkills with the following attributes: (EmployeeID, EmployeeName, Department, SkillName, CertificationDate). The primary key is EmployeeID. Assume an employee can have multiple skills, and each skill has a certification date.

If the company decides to update an employee's department, which type of anomaly is most likely to occur due to the current table design, and why?

Consider a scenario where a university database needs to store information about departments and the courses they offer. Each department offers multiple courses, but each course is offered by exactly one department. The ER diagram shows Department and Course as entities, with a one-to-many relationship \(1:M\) between them, where Department is on the 'one' side and Course is on the 'many' side.

Which of the following relational schemas correctly represents this relationship?

In a school database design, a many-to-many relationship exists between the Student entity and the Course entity. Describe the standard procedure used to resolve this relationship into a relational schema during the Database Design Methodology process.

A database designer decides to perform denormalisation on a system that is already in Third Normal Form (3NF). Explain the primary technical motivation for this decision and identify one specific risk it poses to data integrity during record updates.

Suppose a database table contains the fields ProjectID, ProjectManagerID, and ManagerPhone. If ManagerPhone depends on ProjectManagerID, which in turn depends on the primary key ProjectID, name this type of dependency and describe how the table should be modified to achieve Third Normal Form (3NF).

A high school is developing a relational database to manage its extracurricular activities. Each student is identified by a unique Student ID and has a name and grade. Each activity has a unique Activity Code, a name, and a staff supervisor. A student can participate in multiple activities, and each activity can have many students enrolled. The system currently stores all data in a single large spreadsheet, which has caused issues with data consistency when students change their names or when staff supervisors are replaced.

(a) State the specific type of relationship between the Student and Activity entities. Explain how this relationship is typically resolved in a relational database design. Provide the primary key(s) for the resulting table.

(b) The current spreadsheet design suffers from data redundancy. Describe one potential update anomaly that could occur in this spreadsheet if a staff member supervising three different activities changes their surname.

(c) Explain the requirements for a table to be in Third Normal Form (3NF). How does reaching 3NF specifically help reduce data redundancy and maintain data integrity in the context of the school's database?

(d) To protect student privacy, the school administrator wants to restrict the activities' staff supervisors from seeing the students' home addresses, even though that data exists in the database. Identify the mechanism used in a Database Management System (DBMS) to implement this and explain how it achieves the goal.