Identify The Functional Dependencies That Exist Between The
Identify the functional dependencies that exist between the columns of the table in Figure 14.21 and identify the primary key and any alternate key(s) (if present) for the table
The core task involves examining the relationships among the columns in the provided FastCabs booking table to determine how data is functionally dependent on certain attributes. Functional dependency refers to a relationship where the value of one set of attributes determines the value of another set. In context, for the FastCabs table, certain fields can be uniquely determined by others, revealing the dependency structure.
From the sample data and typical understanding of a booking system, the primary attribute for identifying each record uniquely is likely the Booking ID or a similar unique identifier assigned to each booking. Given this, attributes such as Customer Name, Pickup Location, Dropoff Location, Date, and Time are dependent on the Booking ID. Conversely, attributes like Driver Name or Taxi ID might depend on the Driver assigned, which could be linked through Driver ID rather than the booking itself.
Specifically, the following functional dependencies are plausible:
- Booking ID → Customer Name, Pickup Location, Dropoff Location, Date, Time, Driver ID, Taxi ID
- Driver ID → Driver Name, Driver Contact
- Taxi ID → Taxi Details (such as Taxi Type, Plate Number)
In this case, the primary key appears to be the Booking ID, as each booking is unique and determines other fields. An alternate key could be the combination of other attributes that collectively guarantee uniqueness, such as a composite of Customer Name, Date, and Time, but typically Booking ID serves as the primary key.
Identify the primary key for the table in the above figure. Indicate whether there are any alternate keys (for this table). Explain each of the choices
The primary key for the table is the Booking ID. This identifier uniquely distinguishes each booking record, so it naturally functions as the primary key. It ensures that each row can be uniquely referenced and retrieved.
Potential alternate keys might include combinations of attributes that, together, uniquely identify a record. For example, a combination like Customer Name + Date + Time might serve as an alternate key if each customer makes only one booking at a specific date and time — although in practice, this is less robust given the possibility of multiple bookings by the same customer at the same time. Hence, unless explicitly specified, the primary key remains Booking ID, with no confirmed alternate keys present in the data.
Is the table in 3NF? If not, explain why – (provide specific rational, use field names and values in the table to demonstrate understanding). Explain what normal form the table provided is in.
The table is not in 3NF because it likely contains transitive dependencies. For example, Driver Name and Driver Contact depend on Driver ID, which in turn depends on Booking ID. Since Driver Name and Driver Contact are dependent on Driver ID, not directly on the primary key, this introduces a transitive dependency, violating 3NF conditions.
In the current form, it might be in 1NF if data is atomic, but it fails 2NF because non-prime attributes depend on parts of a composite key (if a composite key exists), and it's not in 3NF due to dependencies like Driver Name → Driver ID, which are not directly reliant on the primary key alone. Therefore, the table probably exists in 1NF or 2NF but not 3NF.
Normalization to 3NF involves decomposing the table into smaller tables where all non-key attributes depend only on the primary key, eliminating transitive dependencies.
Describe why storing the FastCabs data across the 3NF tables avoids the update anomalies described in Exercise 14.17 (B)
Storing data in 3NF tables prevents update anomalies because each piece of data is stored only once, in the appropriate related table. For instance, Driver details are stored separately from booking details. If a driver’s contact information changes, updating it in one place ensures all bookings linked to that driver reflect the change, preventing inconsistent data (update anomaly). Similarly, inserting or deleting records becomes safer: new drivers can be added without duplicating their details, and deletions of bookings do not delete driver or taxi data inadvertently. This separation ensures data integrity and consistency across the database.
Describe how the original table shown in Figure 14.21 can be re-created through relational joins between primary key and foreign keys columns of the tables in Figure 14.22
The original denormalized table can be reconstructed by performing SQL JOIN operations on the normalized tables using their primary and foreign keys. For example, a JOIN between the Bookings table (primary key: Booking ID) and the Drivers table (primary key: Driver ID, foreign key in Bookings) will assemble the driver details for each booking. Similarly, joining with the Taxis table via Taxi ID retrieves vehicle details. This process reconstructs the original dataset by combining related data across multiple tables, preserving referential integrity while avoiding data redundancy and anomalies. Each record in the combined result set corresponds to a single booking with all associated information fully linked.