Functional Dependency
Functional dependency is a constrain that determines the relations between two attributes or sets of attributes in a relation from a database where the value of one attribute determines the value of another attribute (Chavan & Shaikh, 2022). In the given example (Table 1), there are three functional dependencies corresponding to the following attributes relations:
- CustID → Name, StAddr, City, DOB — Customer ID uniquely determines the customer’s name, street address, city, and date of birth;
- CarVin# → Year, Manufacturer, Sales Price — CarVin# uniquely determines the car’s year, manufacturer, and sales price;
- CustID, CarVin# → lname, fname — A combination of CustID and CarVin# uniquely determines the initial name and first name of the customer who purchased the car.
The Primary and Alternatives Keys for the Table
Table 1. Records of Cars Sold.
In the given example, the primary key uniquely identifies each record in the database The main requirement for a potential primary key is to be unique and minimal. In the given case, the combination of the attributes CarVin# and CustID can be used to uniquely identify each separate purchase record within the database in Table 1. Moreover, there can be no repetition in the combination of CarVin# and CustID attributes, satisfying the uniqueness requirement for a composite primary key.
At the same time, there are no potential candidates for an alternative key, given that most of the attributes apart from the combination of CarVin# and CustID have at least one repetition in the present table. The attributes with present repetitions include car manufacturer names, customers’ IDs, customers’ names and surnames, cities, addresses, and dates of birth. The other attributes, including sales price and year of manufacture, can potentially have repetition if new purchases are added to the database in the future.
The Third Normal Form of the Table
To determine whether the table is represented in the third normal form (3NF), the following analysis is employed. First, Table 1 has atomic values in each cell, meaning that there are no cells with multiple values or arrays. Therefore, the table satisfies the first normal form (1NF) requirement (Chavan & Shaikh, 2022).
Moreover, all non-key attributes in Table 1 are fully functionally dependent on the single primary key, thus satisfying the requirement for the second normal form (2NF) (Chavan & Shaikh, 2022). However, Table 1 is not in the third normal form (3NF) due to the existence of a transitive dependency. Specifically, the Sales Price attribute depends on the CarVin#, which is a part of the composite primary key, and not directly on CustID.
The following SQLite code can be used to create a proper table:
CREATE TABLE Customers (
CustID INT PRIMARY KEY,
Name TEXT,
StAddr TEXT,
City TEXT,
DOB DATE
);
CREATE TABLE Cars (
CarVin# INT PRIMARY KEY,
Year INT,
Manufacturer TEXT,
SalesPrice INT
);
CREATE TABLE Purchases (
CustID INT,
CarVin# INT,
Iname TEXT,
fname TEXT,
FOREIGN KEY (CustID) REFERENCES Customers(CustID),
FOREIGN KEY (CarVin#) REFERENCES Cars(CarVin#)
);
In this case, the Customers table contains all relevant information about customers, the Cars table contains the required data for cars, and the Purchases table captures the relationship between customers and cars. This includes the initial and first names of the customers who made the purchase. This design ensures the tables’ 3NF format by removing the transitive dependency of Sales Price on CarVin# and ensuring all attributes are fully functionally dependent on the primary keys.
Reference
Chavan, D. H., Shaikh, P. S. (2022). Introduction to DBMS: Designing and implementing databases from scratch for absolute beginners (English edition). BPB Publications.
Loading...
