FINAL EXAM

QUESTION 1

A university uses a single entity called CourseSection to track everything related to class schedules:
section_id (PK), course_id, course_title, course_credits, instructor_id, instructor_name, instructor_email, section_day, section_time, classroom_id, building_name

This design suffers from several transitive and partial dependencies, making it inefficient and prone to data anomalies (insertion, update, and deletion anomalies).

1 ) Identify Functional Dependencies:
Identify the functional dependencies present among the attributes listed above.

2 ) Normalize to 3NF (Third Normal Form):
Decompose the single CourseSection entity to eliminate all partial and transitive dependencies. Ensure that every non-key attribute is functionally dependent only on the primary key of its respective table. Eliminate all data redundancy (e.g., ensuring that changing a building’s name only requires an update to a single record). Clearly state the primary keys (PK) and foreign keys (FK) for your new tables.

3 ) Design the Entity-Relationship (ER) Diagram:
Draw the final ER diagram normalized to 3NF. Precisely identify the relationships between the newly created tables, and clearly indicate the cardinality and optionality for each relationship.

start new page now !

QUESTION 2

Flight Network Database
You are managing a flight database for a global airline. You have two tables:
Airport: airport_id, airport_name, airport_code
Flight: flight_id, flight_number, airport_id_departure, airport_id_arrival

In this schema, both airport_id_departure and airport_id_arrival are foreign keys that point back to the same Airport table.

Write a SQL query that returns a list of all flights, showing the flight number alongside the actual names of the Departure Airport and the Arrival Airport side-by-side.

start new page now !

QUESTION 3

You are presented with a medical center scheduling database with the following tables:
Department (PK: department_id, department_name)
Doctor (PK: doctor_id, FK: department_id, doctor_full_name, doctor_specialty)
Patient (PK: patient_id, patient_full_name, patient_email)
Appointment (PK: appointment_id, FK: patient_id, FK: doctor_id, appointment_date)

Write a query to list every Audiology department’s (department_id “05”) patient’s full name alongside the full name of the doctor with whome they have an appointment and date of the apointment.

start new page now !

QUESTION 4

You are refactoring the database for a company’s Corporate Mentorship Program. Through this program some more experienced employees mentore some of the less experienced ones. By design of this program an employee may have at most one mentor, but may have multiple mentees. The current database was designed with 4 tables, but it contains redundancies and a transitive dependency that violates Third Normal Form (3NF).

Current 4-table schema:
Employee: employee_id (PK), employee_first_name, employee_last_name, employee_expertise_area, seniority_level (FK)
Mentor: mentor_id (PK), employee_id (FK)
MentorshipPairing: pairing_id (PK), mentee_employee_id (FK), mentor_id (FK)
Seniority: seniority_level (PK), seniority_salary_multiplier, seniority_vacation_days

1 ) Draw new ERD reducing the number of tables to the fewest possible number, while bringing the entire schema into 3NF and properly handling the self-referencing relationship. For the important purpose of data integrity, the employee_expertise_area
may be Science, Business, Technology, or Administration; and seniority_level may be 1, 2, 3, or 4.
2 ) Write an SQL query based on your newly refactored 3NF-compliant schema that will output a list containing all information for each mentor, sorted in decreasing order of the number of mentees they are currently advising (you must include that total count as a column in each row).

start new page now !

QUESTION 5

You have been hired to audit the inventory system database of an online retail company. You find that the previous developer followed a “One Table Per Concept” rule so zealously that they separated core product attributes into their own individual tables, even though those attributes have a strict one-to-one (1:1) relationship and share the exact same lifecycle with the primary product entity.

Their Current Schema for Tracking Products:
Product: product_id (PK), product_name, product_brand
SKU: sku_id (PK), product_id (FK), sku_code
Weight: weight_id (PK), product_id (FK), unit_weight_lbs
Dimensions: dimension_id (PK), product_id (FK), dimension_string
CustomerOrder: order_id (PK), product_id (FK), customer_id (FK), order_purchase_date
Customer: customer_id (PK), customer_full_name

This excessive number of tables creates aggressive “Vertical Partitioning” that complicates data integrity and violates the logic of Third Normal Form (3NF). Because of that extracting simple product info from this database requires an excessive number of expensive JOIN operations.

1 ) Baseline Query (Old Schema)
Write an SQL query based on the current problematic schema that returns the names and purchase dates of all products bought by the customer whose ID is “cust_9942”.

2 ) Schema Refactoring & ERD
Refactor this database structure so that it complies with 3NF, reducing the schema to the absolute fewest number of tables possible while preserving all existing data and relationships. Draw the new Entity-Relationship Diagram (ERD), ensuring you precisely mark the relationships, cardinalities, and optionalities.

3 ) Database Implementation
Write all the necessary CREATE TABLE SQL statements to precisely implement your newly refactored ERD. Ensure you explicitly define all Primary Keys (PK) and Foreign Keys (FK).

4 ) Optimized Query (New Schema)
Write an SQL query that returns the names and purchase dates of all products bought by the customer whose ID is “cust_9942” again — but this time, write it for your newly refactored database design to demonstrate how much simpler the data retrieval has become.

start new page now !

QUESTION 6

You are designing a database for a hospital. The hospital needs to store information about all of its staff members, but there are distinct categories of personnel that possess specialized attributes unique to their roles:

All Staff Members have a unique employee ID, a legal full name, and an email address.

Physicians additionally have a medical license number, a specific clinical specialty (General, Vision, Dental), and a board certification status (C, NC).

Nurses additionally have a nursing certification tier (N, RN, NP), a primary shift assignment (Day, Night, Weekend), and an assigned hospital ward.

Demonstrate how to implement inheritance in a relational database using the Table-Per-Class strategy (Class-Table Inheritance), where a base table handles shared attributes and specialized tables handle subclass-specific attributes.

1 ) Draw a 3NF-compliant ERD representing this inheritance structure. Ensure you accurately depict the base entity and the specialized entities, establishing the correct precise relationships, cardinalities, and optionalities that bind them.

2 ) Write the SQL CREATE TABLE statements for all entities defined in your ERD. Ensure you apply Primary Keys (PK) and Foreign Keys (FK) correctly to structurally link the specialized tables back to the base table, enforcing referential integrity, and ensuring the correctness of status, speciality, tier, and shift.

3 ) Write the following three SQL commands to demonstrate how data flows through this inherited structure:

• An SQL command to onboard a brand-new Nurse into the system.
• An SQL command to change this specific nurse’s primary shift assignment or assigned hospital ward.
• An SQL command (or sequence of commands) to completely remove this nurse from the hospital system entirely without leaving orphaned rows.