Objectives:
To analyse and comprehend a provided ER diagram and Database Schema
To implement a database based on the provided ER diagram and Database Schema
To write required SQL statements to query the database
Implementation of the Database
To implement the database system, you are required to
a) provide SQL commands to create the database, its tables, the relationships of the tables
b) Create a text file called YourStudentId-Create.sql (format xxxxxxx-Create.sql) for example 2225991-Create.sql that will provide SQL commands to:
i. create a database called BigM[YourStudentID] (eg BigM30011111)
ii. create all of the required tables for the database including their primary keys, foreign keys and the relationships of tables.
c) Create a text file called YourStudentId-Insert.sql (format xxxxxxx-Insert.sql) for example 2225991-Insert.sql that will provide SQL commands to:
i. insert sufficient data into each table you have created to test the queries (at least 5 to 10 records in each table; some table may require less or more).
ii. You are required to include your full name as one of the employee and/or customer; but you can provide fake details for your address and contact.
d) Create a text file called YourStudentId-Queries.sql (format xxxxxxx-Queries.sql - for example 2225991-Queries.sql) that contains all of the queries to display the following reports:
1. List of names and complete address of all employees sorted by their salary. Also indicate if the employee works fulltime or casual.
2. The date on which the most recent customer order has been made. The customer name and date of order will suffice.
3. List of all the store names and their addresses, sorted in dictionary order of the store name.
4. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.
5. A list containing the name of employees, which work as accountant.
6. A list containing the total quantity on hand for each product (product no and description) regardless of stores.
7. A list showing each product sold (picked) on or before May 20, 2015. Show product number, name and quantity sold, sorted by product number and then quantity sold.
8. A list of CDs (show CD title, release date and price) whose price is less than or equal to the average CD price.
9. Increase each fulltime employee's salary by 7.5% and show the updated salary of all fulltime employees (name and salary).
10. Show all the artist names and CDs in which they appear, sorted by artist first name and then by last name and finally by CD title.
Problem (note: edited from the above source. Please take it as an example, not an exact implementation for the assignment's supertype-subtype relationships):
I need to create the following database:
Vehicle can be semi-trucks and cars. For semi-trucks I don't need extra subtypes, while for Car I need to have only 3 subtypes (Van, Suv and Sedan) and also for Sedan I need the four subtypes (Gas, Diesel, Hybrid and Electric). For SELECTs I will use JOINs (normalized database) but I need to find an easy way to make correct INSERTs.
- Vehicle table stores common information
- Semi-truck stores specific information for semi-trucks
- Car tables has specific fields for cars and a car_type field which is linked to the three subtypes
- Van, Suv and Sedan (and other types if I would need them) should be in one table CAR_TYPE
- However, for Sedan type I need to have additional subtypes which maybe should be contained in another table. These subtypes are not needed for Suvs and Vans (in real life suv, vans can have the same subtypes as sedans but not in my case).
So far, my first approach is to have the following tables:
- Vehicle: veh_id, veh_type(Semi, car), ..., other_fields
- Vehicle_semis: veh_id, ..., other_semis_fields
- Vehicle_car: veh_id, car_type(Van, Suv, Sedan), other_car_specific_fields
- Car_type: car_type_id, type
- Sedan_type: sedan_type_id, type