Consider the following table for a parking ticket database
More Normalization
Consider the following table for a parking ticket database.
ParkingTicket(date, time, badgeNo, officerFName, officeLName, carLicenseNo, carMake, carColor, street, ownerFName, ownerLName, amount)
The primary key is chosen to be a combination of (date, time, badgeNo) based on the reasoning that on a given date at a given time an officer with a particular badge number can only be issuing one ticket.
1. Are there any candidate keys?
Date, time, carLicenseNo
2. Identify any functional dependencies.
A. carLicenseNo > ownerFName, OwnerLName, carMake, CarColor
B. badgeno > officerFName, officerLName
3. Are there any partial dependencies on the primary key?
B was partial
4. Are there any transitive dependencies on the primary key?
A primary key > carLicenseNo > ownerFName, OwnerLName, carMake, CarColor
5. Decompose (normalize) the table to 3NF.
Officer(badgeno, officerLName, officerFName)
Car(lisencseNo, ownerFName, OwnerLName, carMake, CarColor)
OfficerIssuesTIcketToCar(date, time, badgeno, carlicenseNO, street, amount)
More Northwind Queries:
1. Determine the average number of days between an order’s being placed and its being shipped.
SELECT Avg([Orders].[shippeddate]-[orders].[orderdate]) AS DaysToShip
FROM Orders;
2. Find the employee who was youngest when hired.
SELECT TOP 1 Employees.LastName, Employees.FirstName, [Employees].[HireDate]-[Employees].[BirthDate] AS AgeWhenHired
FROM Employees
ORDER BY [Employees].[HireDate]-[Employees].[BirthDate];
3. Add yourself to the Employees table. Then make a query that lists the number of orders handled by every employee (including yourself). Rank the employees by the number of orders they have handled.
SELECT Employees.LastName, Employees.FirstName, Count(Orders.OrderID) AS CountOfOrderID
FROM Employees LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName, Employees.FirstName
ORDER BY Count(Orders.OrderID) DESC;
4. Make a list of employees and the total number of items contained within the orders they have handled. (Extra credit for including yourself.)
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.