Normalize the
following table
A company provides cars for several leasing organizations in districts of London, in the UK such as Enfield or Lewisham. Agnts buy cars from a number of showrooms. The company buys a car from an agent before them prviding it for lease. The holding company needs to keep a record of each car, which agent bought each car, which showroom it came from. An example of a database for this company in unnormalized form. fields are Car Id, Make, Model, AgentId, Showroom, AgentName and AgentName. Convert this database to third normalized form by complaeting the field names for each table.
To convert the given unnormalized database to the Third Normal Form (3NF), we need to follow the normalization process step by step. Here's how we can do it:
Step 1: Identify the Fields
The unnormalized table has the following fields:
· CarId (Primary Key for the Car table)
· Make (Car manufacturer)
· Model (Car model)
· AgentId (Primary Key for the Agent table)
· Showroom (Name of the showroom)
· AgentName (Name of the agent)
Step 2: First Normal Form (1NF)
Ensure that each field contains atomic values and there are no repeating groups. The given table is already in 1NF.
Step 3: Second Normal Form (2NF)
Remove partial dependencies by ensuring that all non-key attributes are fully dependent on the primary key.
· Car Table:
o CarId (Primary Key)
o Make
o Model
o AgentId (Foreign Key to link to the Agent table)
· Agent Table:
o AgentId (Primary Key)
o AgentName
o Showroom
Step 4: Third Normal Form (3NF)
Remove transitive dependencies by ensuring that all non-key attributes are dependent only on the primary key and not on other non-key attributes.
· Car Table:
o CarId (Primary Key)
o Make
o Model
o AgentId (Foreign Key)
· Agent Table:
o AgentId (Primary Key)
o AgentName
o ShowroomId (Foreign Key to link to the Showroom table)
· Showroom Table:
o ShowroomId (Primary Key)
o ShowroomName
Final Tables in 3NF
1. Car Table
CarId (PK) | Make | Model | AgentId (FK) |
1 | Toyota | Camry | 101 |
2 | Honda | Civic | 102 |
2. Agent Table
AgentId (PK) | AgentName | ShowroomId (FK) |
101 | John Smith | 1 |
102 | Jane Doe | 2 |
3. Showroom Table
ShowroomId (PK) | ShowroomName |
1 | Enfield |
2 | Lewisham |
Explanation of Relationships
1. Car Table is linked to the Agent Table via the AgentId foreign key.
2. Agent Table is linked to the Showroom Table via the ShowroomId foreign key.
3. This structure ensures no redundancy, no partial dependencies, and no transitive dependencies, making it fully normalized to 3NF.
*********************************************************************************
Question 1: Library Management System
Scenario: A library manages
books, authors, and borrowers. Each book has a unique BookId, a Title, and is written by an
author with an AuthorId and AuthorName. Borrowers have a BorrowerId and BorrowerName. The library tracks which borrower has borrowed which book and
the BorrowDate.
Question 2: School Management System
Scenario: A school tracks
students, courses, and enrollments. Each student has a StudentId and StudentName. Each course has a CourseId and CourseName. The school records
which student is enrolled in which course and the EnrollmentDate.
Question 3: Hospital Management System
Scenario: A hospital manages
patients, doctors, and appointments. Each patient has a PatientId and PatientName. Each doctor has a DoctorId and DoctorName. The hospital records
which patient has an appointment with which doctor and the AppointmentDate.
Question 4: Online Shopping System
Scenario: An e-commerce
platform tracks customers, products, and orders. Each customer has a CustomerId and CustomerName. Each product has a ProductId and ProductName. The platform records
which customer placed an order for which product and the OrderDate.
Question 5: Employee Management System
Scenario: A company tracks
employees, departments, and salaries. Each employee has an EmployeeId, EmployeeName, and belongs to a
department with a DepartmentId and DepartmentName. The company also records the Salary of each employee.
Solution:
1.
Employee Table: EmployeeId (PK), EmployeeName, DepartmentId (FK)
2.
Department Table: DepartmentId (PK), DepartmentName
3.
Salary Table: EmployeeId (FK), Salary
Question 6: Movie Rental System
Scenario: A movie rental store
tracks movies, customers, and rentals. Each movie has a MovieId and MovieTitle. Each customer has a CustomerId and CustomerName. The store records
which customer rented which movie and the RentalDate.
Question 7: Airline Reservation System
Scenario: An airline tracks
flights, passengers, and bookings. Each flight has a FlightId and FlightName. Each passenger has a PassengerId and PassengerName. The airline records
which passenger booked which flight and the BookingDate.
Question 8: Restaurant Management System
Scenario: A restaurant tracks
dishes, customers, and orders. Each dish has a DishId and DishName. Each customer has a CustomerId and CustomerName. The restaurant
records which customer ordered which dish and the OrderDate.
Question 9: Gym Management System
Scenario: A gym tracks members,
trainers, and sessions. Each member has a MemberId and MemberName. Each trainer has a TrainerId and TrainerName. The gym records which
member attended a session with which trainer and the SessionDate.
Question 10: Hotel Booking System
Scenario: A hotel tracks rooms,
guests, and bookings. Each room has a RoomId and RoomType. Each guest has a GuestId and GuestName. The hotel records
which guest booked which room and the BookingDate.
Question 11: Car Rental System
Scenario: A car rental company
tracks cars, customers, and rentals. Each car has a CarId and CarModel. Each customer has a CustomerId and CustomerName. The company records
which customer rented which car and the RentalDate.
Question 12: University Course Registration
Scenario: A university tracks
students, courses, and registrations. Each student has a StudentId and StudentName. Each course has a CourseId and CourseName. The university
records which student registered for which course and the RegistrationDate.
Question 13: Online Banking System
Scenario: A bank tracks
customers, accounts, and transactions. Each customer has a CustomerId and CustomerName. Each account has an AccountId and AccountType. The bank records
which customer owns which account and the TransactionDate.
Question 14: Event Management System
Scenario: An event management
company tracks events, attendees, and registrations. Each event has an EventId and EventName. Each attendee has an AttendeeId and AttendeeName. The company records
which attendee registered for which event and the RegistrationDate.
Question 15: Inventory Management System
Scenario: A warehouse tracks
products, suppliers, and purchases. Each product has a ProductId and ProductName. Each supplier has a SupplierId and SupplierName. The warehouse records
which product was purchased from which supplier and the PurchaseDate.
No comments:
Post a Comment