Info321 Assignment 6 Instructions A Company Called Fastca
Info321 Assignment 6 Instructions1417 A Company Calledfastcabsprovi
INFO321 Assignment 6 Instructions: 14.17 – A company called FastCabs provides a taxi service to clients. The table shown in Figure 14.21 displays some details of client bookings for taxis. Assume that a taxi driver is assigned to a single taxi, but a taxi can be assigned to one or more drivers. A) 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. B) Describe why the table in Figure 14.21 is not in 3NF. C) The table shown in Figure 14.21 is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table. (Figure 14.21 – Table displaying sample data for FastCabs.) 14.18 – Applying normalization to 3NF on the table shown in Figure 14.21 results in the formation of the three 3NF tables shown in Figure 14.22. A) Identify the functional dependencies that exist between the columns of each table in Figure 14.22 and identify the primary key and any alternate and foreign key(s) (if present) for each table. B) Describe why storing the FastCabs data across the 3NF tables avoids the update anomalies described in Exercise 14.17 (B). C) 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. Use the information in the figure below to answer the following questions. (The sample data may not represent all future field values; use common knowledge and consider the domain for each field).
We record only the last sale for each car. For each sale, a car can be sold to only one customer; however, each customer can buy (own) more than one car. Include the below numbers to organize the submission. DO NOT include the Questions ot other content from the instructions in your answer. 1) Describe functional dependency only; NOT full functional dependency, or partial dependency. While you may incorporate the formal definition, you must explain the concept in your own words, using field names and values from this exercise. 2) 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. 3) 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. APA guidelines, spelling & grammar
Paper For Above instruction
The assignment revolves around analyzing a database table for a taxi service company, FastCabs, with a focus on understanding functional dependencies, keys, normalization forms, and anomalies. The initial step involves identifying the functional dependencies among the columns in the provided table (Figure 14.21). Functional dependencies describe the relationship where one set of attributes uniquely determines another within the database. For instance, if each booking ID uniquely determines details like pickup location, drop-off location, driver details, and fare, then booking ID functionally determines these attributes.
In examining the primary key, it is essential to find the minimal set of attributes that can uniquely identify each record in the table. For example, if BookingID uniquely determines all other fields, then BookingID is the primary key. An alternate key could be any other attribute or combination thereof that also uniquely identifies records but is not the primary key. For example, perhaps a combination of driver ID and date might serve as an alternate key if it uniquely identifies a record.
Understanding why the table is not in Third Normal Form (3NF) involves analyzing the dependencies for transitive dependencies. Since 3NF requires that non-prime attributes depend only on the primary key and not on other non-prime attributes, the presence of attributes like driver name or vehicle details depending indirectly on other attributes indicates the table is in a lower normal form, possibly 1NF or 2NF. For example, if driver name depends on driver ID, which in turn depends on booking ID, then this introduces a transitive dependency, meaning the table is not in 3NF.
Update anomalies occur when insertions, deletions, or updates lead to inconsistencies or redundancy. An insertion anomaly might happen if a new driver is added to the system without a current booking; because driver details are stored directly in the table, this insertion could be problematic if driver information is incomplete. Deletion anomalies may occur if deleting a booking also inadvertently removes driver or vehicle information necessary for other records. Modification anomalies happen if updating a driver's name in one record doesn't reflect in others, leading to inconsistency.
Normalization to 3NF, as shown in Figure 14.22, involves decomposing the original table into multiple related tables. Each of these tables has well-defined primary keys, and foreign keys are established to maintain relationships across tables. For example, a drivers table would contain driver details with driverID as primary key; a bookings table would contain booking details with bookingID as primary key and driverID as a foreign key, linking it to the drivers table. Functional dependencies within each normalized table become clearer; non-prime attributes depend solely on the primary key, eliminating transitive dependencies.
Storing data across these 3NF tables prevents update anomalies because each piece of information is stored only once, and dependencies are explicitly managed through foreign keys. This minimizes redundancy and ensures data consistency when updates occur. For instance, updating a driver's contact information only requires changing one record in the drivers table, rather than multiple records spread across a denormalized table.
Recreating the original denormalized table involves performing joins across the normalized tables using primary and foreign keys. For example, joining the bookings table with the drivers table on driverID and with the vehicles table on vehicleID reconstructs the full dataset similar to the original table in Figure 14.21. This approach ensures data integrity and reduces redundancy, enabling more flexible and reliable data management.