Muhammad Waleed Tariq.
fa22-bse-185
Assignment # 02
First Normal Form (1NF):
Ensure each column holds singular values: To achieve atomicity, it's necessary to divide the "Description
of Services" section into distinct rows for each service provided.
CustomerID Name Address Phone RegistrationNo Model VIN
101 Ali Abc address 0300-123456 ABC123 X 1234567890
ServiceDescription Price InvoiceNo PaymentID Date Method
Tune up $50.00 TU-5 1 2024-03-29 Card
Second Normal Form (2NF):
Remove partial dependencies: All attributes not part of the primary key should rely on the entire primary
key. Data correlated with each other should be organized into different tables with unique primary keys.
For example, segregating customer information and vehicle details into their own tables would fulfill this
requirement.
CustomerID Name Address Phone
101 Ali Abc address 0300-123456
RegistrationNo Model VIN
ABC123 X 1234567890
ServiceID Description Cost
1 Tune up $50
InvoiceNo PaymentID Date Method
TU-5 1 2024-03-29 Card
Third Normal Form (3NF):
Address transitive dependencies: Non-primary key attributes should solely depend on the primary key. If
attributes rely on non-primary key attributes, it's advisable to further segment the tables. For instance,
since "Tax" and "Total Amount Due" are contingent solely upon the subtotal, they could be relocated to a
distinct table.
(DBS)
submitted to: Mr Tahir Muhammad.
Muhammad Waleed Tariq.
fa22-bse-185
Assignment # 02
Table 1: Customers
CustomerID Name Address Phone
101 Ali Abc address 0300-123456
Table 2: Vehicles
RegistrationNo CustomerID Model VIN
ABC123 101 X 1234567890
Table 3: Services
ServiceID Description Cost
1 Tune up $50
Table 4: Invoice
InvoiceNo CustomerID Date
TU-5 101 2024-03-29
Table 5: InvoiceItems
ItemID InvoiceNo ServiceID Quantity Subtotal
1 Tu-5 1 1 $50
Table 6: Payment
PaymentID InvoiceNo Method
1 Tu-5 Card
Final Tables in ERD:
Table 1: Customers
CustomerID (Primary Key)
Name
Address
Phone
Table 2: Vehicles
Registration Number (Primary Key)
CustomerID (Foreign Key)
Make/Model
VIN/Chassis Number
(DBS)
submitted to: Mr Tahir Muhammad.
Muhammad Waleed Tariq.
fa22-bse-185
Assignment # 02
Table 3: Services
ServiceID (Primary Key)
Description
Cost
Table 4: Invoice
InvoiceID (Primary Key)
CustomerID (Foreign Key)
Date
Invoice Number
Table 5: InvoiceItems
ItemID (Primary Key)
InvoiceID (Foreign Key)
ServiceID (Foreign Key)
Quantity
Subtotal
Table 6: Payment
PaymentID (Primary Key)
InvoiceID (Foreign Key)
Method
Relationships:
Invoice – One to Many – Payment
Customer – One to Many – Vehicles
Customer – One to Many – Invoice
Invoice – One to Many – InvoiceItems
(DBS)
submitted to: Mr Tahir Muhammad.
Muhammad Waleed Tariq.
fa22-bse-185
Assignment # 02
InvoiceItem – One to Many Services
ERD:
(DBS)
submitted to: Mr Tahir Muhammad.
