Problem
Read the following scenario and answer ALL questions. Your explanations MUST be entirely based on the provided relation.
An organisation uses the below table to record information about its fleet management. The table stores data about Departments and Vehicles. A vehicle can be assigned to one or more department, and a department can be allocated one or more vehicle. Some departments may not be assigned any vehicle. The company stores data of the Dept_No (unique identifier of the department), Dept_Name (name of the department), Vehicle_Rego (unique identifier for vehicles), Vehicle_Name and Prices of Vehicle. Although the company records data about all their purchased vehicles, some vehicles may not be ready for use, thus cannot be assigned to any department.
Fleet_Management table (unpopulated with data):
|
Dept_No
|
Dept_Name
|
Vehicle_Rego
|
Vehicle_Name
|
Vehicle_Price
|
|
...
|
|
|
|
|
A. Explain what are the major concerns or problems associated with this database design. Provide examples of these problems, your explanation MUST be entirely based on the above relation.
B. How would you change the design above to address the problems you have identified? Make sure you provide the new design, define the primary keys (by underlying the attributes that are primary keys), define the foreign keys (by indicating them in bold).
C. Examine the below SQL query and explain what information will be retrieved when it's executed (1.5 marks). As the DBA in this company, explain what can be made to restrict access to this retrieved information for a specific group of users (*you should provide an SQL query to support your response)
SELECT A.Vehicle_Name AS Vehicle_ Name1, B. Vehicle_Name AS Vehicle_ Name2, A.Vehicle_Price
FROM Vehicle A, Vehicle B
WHERE A.Vehicle_Rego <> B.Vehicle_Rego
AND A.Vehicle_Price = B.Vehicle_Price
ORDER BY A.Vehicle_Price;