62679/CarServicingDB.accdb
date_of_service payment_received customer_Id Mechanic_Id booking_id
8/2/20 500 mit192384 101 B01
7/29/20 90 mit192385 103 B02
8/4/20 450 mit192384 102 B03
current_mileage engine_size License_Number customer_Id
356 large EM345 101
435 small EM567 102
23 large CG45 103
First_Name Last_Name Title Gender email_address phone_number address_line1 city State Customer_Id
sharad dhakal Mr. Male
[email protected] 546756767 ABC NSW NSW mit192384
Joy Smith Mr. Male
[email protected] 56576766 BCD Queensland Queensland mit192385
Sally Joseph Miss. Female
[email protected] 56767768 DEF NSW NSW mit192386
manufacturer_name manufacturer_Code
Suzuki 101
ABC 102
CDE 103
mechani_Name other_mechanic_details mechanic_Id
David 3 yrs experience 101
Roy 3 yrs experience 102
Sarah Joseph 3 yrs experience 103
manufacturer_Code model_name Model_Code
101 XYZ 123
102 HUJ 234
103 YZA 908
SELECT Car.[License_Number], Car.[current_mileage], Car.[engine_size], Car.[customer_Id]
FROM Car
ORDER BY Car.[current_mileage];
SELECT Booking.[booking_id], Booking.[date_of_service], Booking.[payment_received], Booking.[customer_Id], Booking.[Mechanic_Id]
FROM Booking
WHERE Booking.[payment_received] > 100;
SELECT count(Customer.[Customer_Id]) AS NumberOfCustomer
FROM Customer;
62679/Part B/car servicing report.docx
SELECT Booking.booking_id, Booking.customer_Id, Customer.First_Name, Customer.Last_Name
FROM Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]
WHERE (((Booking.payment_received)100));
SELECT Customer.First_Name, Customer.Last_Name, Booking.booking_id, Booking.payment_received, Booking.customer_Id, Customer.email_address
FROM Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]
WHERE (((Customer.First_Name)="L*") AND ((Customer.city)="City of Knox"));
SELECT DISTINCTROW Format$([Booking].[date_of_service],'Long Date') AS [date_of_service By Day], Sum(Booking.payment_received) AS [Sum Of payment_received]
FROM Booking
WHERE Format$([Booking].[date_of_service],'Long Date') = '27 September 2020'
GROUP BY Format$([Booking].[date_of_service],'Long Date');
SELECT DISTINCTROW Sum(Booking.payment_received) AS [Sum Of payment_received]
FROM Booking
GROUP BY Booking.customer_Id;
SELECT Booking.booking_id, Customer.Customer_Id AS Customer_Customer_Id, Customer.First_Name, Customer.Last_Name, Car.License_Number
FROM (Customer INNER JOIN Booking ON Customer.[Customer_Id] = Booking.[customer_Id]) INNER JOIN Car ON Customer.[Customer_Id] = Car.[customer_Id];
Functional Dependencies
Order (OrderNum, OrderDate, Customer_No, Customer_Name, Customer_contactNo, ItemNum, Description, NumOrdered, QuotedPrice)
OrderNum -> OrderDate
Customer_No -> Customer_Name, Customer_ContactNo
OrderNum, ItemNum -> NumOrdered, QuotedPrice
ItemNum -> Description
To make the table in third normal form, it is important to remove all the functional dependency from...