This task requires students to apply their understanding and ability to use Relational Database Management Systems (RDBMS) as well as use SQL in the modelling of the physical world. Students will be provided with a set of business scenarios and are required to design a database and provide related SQL queries.
We provide you with an Oracle sample database which is based on a global fictitious company that sells computer hardware including storage, motherboard, RAM, video card, and CPU.
The company maintains the product information such as name, description standard cost, list price, and product line. It also tracks the inventory information for all products including warehouses where products are available. Because the company operates globally, it has warehouses in various locations around the world.
The company records all customer information including name, address, and website. Each customer has at least one contact person with detailed information including name, email, and phone. The company also places a credit limit on each customer to limit the amount that customer can owe.
Whenever a customer issues a purchase order, a sales order is created in the database with the pending status. When the company ships the order, the order status becomes shipped. In case the customer cancels an order, the order status becomes canceled.
In addition to the sales information, the employee data is recorded with some basic information such as name, email, phone, job title, manager, and hire date.
The following illustrates the sample database diagram: see image.
To do the following tasks, it needs to execute the schema.sql to build the database schema and run data.sql to insert the data into the created database if you are using Oracle database lab environment. There are several commands that may be useful. Select table_name from user_tables; It can be used to check the existence of the tables. Suppose you upload your sql file at your network drive H:. Then you can run by the command, @schema.sql.
Task 1.1:
Write the SQL query to list the region names and the number of countries within the regions in the above database.
Task 1.2:
Write the SQL query to find all customers who have made orders before 2017. List must include the customer ID, customer name, and ordered by their ID values in descending.
Task 1.3:
Write the SQL query to list all customers who have the sequential letters 'co' in the customer name. List must include the customers ID, names and ordered by their names in ascending.
Task 1.4:
Write the SQL query to list all products' ID, Name and price where the products havent been purchased by any customer in the database. The list must be ordered by the product price.
Task 1.5:
Write the SQL query to list all the warehouses and their total sold price. Here, given a product, the total sold price of the product is calculated by the sold quantity of the product and its unit price. The list must be ordered by the total sold price in the descending.
Task 1.6:
Write the SQL query to list the employees and the quantity of orders that they proceeded in the database. The output list must include employee ID, name, and the quantity of orders. The list must be sorted by the quantity of orders in the descending order.
schema.sql:
--------------------------------------------------------------------------------------
-- Name : OT (Oracle Tutorial) Sample Database
-- Link : http://www.oracletutorial.com/oracle-sample-database/
-- Version : 1.0
-- Last Updated: July-28-2017
-- Copyright : Copyright © 2017 by www.oracletutorial.com. All Rights Reserved.
-- Notice : Use this sample database for the educational purpose only.
-- Credit the site oracletutorial.com explitly in your materials that
-- use this sample database.
--------------------------------------------------------------------------------------
---------------------------------------------------------------------------
-- execute the following statements to create tables
---------------------------------------------------------------------------
-- regions
CREATE TABLE regions
(
region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
START WITH 5 PRIMARY KEY,
region_name VARCHAR2( 50 ) NOT NULL
);
-- countries table
CREATE TABLE countries
(
country_id CHAR( 2 ) PRIMARY KEY ,
country_name VARCHAR2( 40 ) NOT NULL,
region_id NUMBER , -- fk
CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
REFERENCES regions( region_id )
ON DELETE CASCADE
);
-- location
CREATE TABLE locations
(
location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24
PRIMARY KEY ,
address VARCHAR2( 255 ) NOT NULL,
postal_code VARCHAR2( 20 ) ,
city VARCHAR2( 50 ) ,
state VARCHAR2( 50 ) ,
country_id CHAR( 2 ) , -- fk
CONSTRAINT fk_locations_countries
FOREIGN KEY( country_id )
REFERENCES countries( country_id )
ON DELETE CASCADE
);
-- warehouses
CREATE TABLE warehouses
(
warehouse_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 10
PRIMARY KEY,
warehouse_name VARCHAR( 255 ) ,
location_id NUMBER( 12, 0 ), -- fk
CONSTRAINT fk_warehouses_locations
FOREIGN KEY( location_id )
REFERENCES locations( location_id )
ON DELETE CASCADE
);
-- employees
CREATE TABLE employees
(
employee_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 108
PRIMARY KEY,
first_name VARCHAR( 255 ) NOT NULL,
last_name VARCHAR( 255 ) NOT NULL,
email VARCHAR( 255 ) NOT NULL,
phone VARCHAR( 50 ) NOT NULL ,
hire_date DATE NOT NULL ,
manager_id NUMBER( 12, 0 ) , -- fk
job_title VARCHAR( 255 ) NOT NULL,
CONSTRAINT fk_employees_manager
FOREIGN KEY( manager_id )
REFERENCES employees( employee_id )
ON DELETE CASCADE
);
-- product category
CREATE TABLE product_categories
(
category_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 6
PRIMARY KEY,
category_name VARCHAR2( 255 ) NOT NULL
);
-- products table
CREATE TABLE products
(
product_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 289
PRIMARY KEY,
product_name VARCHAR2( 255 ) NOT NULL,
description VARCHAR2( 2000 ) ,
standard_cost NUMBER( 9, 2 ) ,
list_price NUMBER( 9, 2 ) ,
category_id NUMBER NOT NULL ,
CONSTRAINT fk_products_categories
FOREIGN KEY( category_id )
REFERENCES product_categories( category_id )
ON DELETE CASCADE
);
-- customers
CREATE TABLE customers
(
customer_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
name VARCHAR2( 255 ) NOT NULL,
address VARCHAR2( 255 ) ,
website VARCHAR2( 255 ) ,
credit_limit NUMBER( 8, 2 )
);
-- contacts
CREATE TABLE contacts
(
contact_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 320
PRIMARY KEY,
first_name VARCHAR2( 255 ) NOT NULL,
last_name VARCHAR2( 255 ) NOT NULL,
email VARCHAR2( 255 ) NOT NULL,
phone VARCHAR2( 20 ) ,
customer_id NUMBER ,
CONSTRAINT fk_contacts_customers
FOREIGN KEY( customer_id )
REFERENCES customers( customer_id )
ON DELETE CASCADE
);
-- orders table
CREATE TABLE orders
(
order_id NUMBER
GENERATED BY DEFAULT AS IDENTITY START WITH 106
PRIMARY KEY,
customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
status VARCHAR( 20 ) NOT NULL ,
salesman_id NUMBER( 6, 0 ) , -- fk
order_date DATE NOT NULL ,
CONSTRAINT fk_orders_customers
FOREIGN KEY( customer_id )
REFERENCES customers( customer_id )
ON DELETE CASCADE,
CONSTRAINT fk_orders_employees
FOREIGN KEY( salesman_id )
REFERENCES employees( employee_id )
ON DELETE SET NULL
);
-- order items
CREATE TABLE order_items
(
order_id NUMBER( 12, 0 ) , -- fk
item_id NUMBER( 12, 0 ) ,
product_id NUMBER( 12, 0 ) NOT NULL , -- fk
quantity NUMBER( 8, 2 ) NOT NULL ,
unit_price NUMBER( 8, 2 ) NOT NULL ,
CONSTRAINT pk_order_items
PRIMARY KEY( order_id, item_id ),
CONSTRAINT fk_order_items_products
FOREIGN KEY( product_id )
REFERENCES products( product_id )
ON DELETE CASCADE,
CONSTRAINT fk_order_items_orders
FOREIGN KEY( order_id )
REFERENCES orders( order_id )
ON DELETE CASCADE
);
-- inventories
CREATE TABLE inventories
(
product_id NUMBER( 12, 0 ) , -- fk
warehouse_id NUMBER( 12, 0 ) , -- fk
quantity NUMBER( 8, 0 ) NOT NULL,
CONSTRAINT pk_inventories
PRIMARY KEY( product_id, warehouse_id ),
CONSTRAINT fk_inventories_products
FOREIGN KEY( product_id )
REFERENCES products( product_id )
ON DELETE CASCADE,
CONSTRAINT fk_inventories_warehouses
FOREIGN KEY( warehouse_id )
REFERENCES warehouses( warehouse_id )
ON DELETE CASCADE
);
The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings can be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer.
Here is an example record for a customer with his history purchases. see image.
Task 2.1:
Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization. You have to present the results step by step from 1NF to 3NF.
Task 2.2:
Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 - schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade.
Task 2.3:
Write a set of SQL queries to add data into the database implemented in Task 2.2. You can follow the template in Question 1 - data.sql or dataXAMPP.sql. The database must include at least five customers, six artists, 10 paintings, and 15 transactions on purchasing.
Task 2.4:
Write a SQL query to list every customer and their purchased paintings. The list must be sorted by customer name first and painting title second.
Task 2.5:
Write a SQL query to list the TOP-Three customers whose expenditure are the top-3 most in the database.
Application Description: Suppose there is one bank company - Commonwealth that requires to develop a relational database. The daily duty is to serve their customers managing their saving bank accounts, e.g., withdraw, deposit, etc. Additionally, the company Commonwealth also makes business for the home loan marketing, e.g., a customer may have a home loan account. To set up a home loan account for a customer, there are several information to be collected and recorded: (1) the customer needs to specify a property address; (2) a bank staff needs to be assigned to process the home loan application; (3) the property value should be assessed by using the average of the sold price of the properties located in the same suburb. Note: suppose there are sold properties in every suburbs; (4) each customer has their own home loan limit that is calculated by his/her annual salary amount multiplied by 7. Note: In general, the bank company used 7 years' salary to estimate, e.g., if the customer Kevin has annual income $60,000, then his home loan limit cannot exceed $420,000. For the customers having multiple home loans, their total home load amount cannot exceed the customers home loan limit; (5) a customer may have more than one home loan because multiple properties can be bought by one customer. Last but not least, your design should reflect the practical requirement as much as possible, i.e., meeting the maximum business rules. For example, some customers may have home loan accounts, but some ones may only have saving accounts. For a customer who need to set up a home loan account, it must create a saving account first. The average sold price for a suburb should be calculated on demand, not saved directly in the database.
Task 3.1:
Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints.
Task 3.2:
Task 3.2: [3 Marks] Write a set of SQL queries to implement the database schema. You can follow the template in Question 1 - schema.sql or schemaXAMPP.sql. To create each table, it must declare the primary keys, foreign keys, constrains and ON Delete Cascade or ON Update Cascade.
Task 3.3:
Write a set of SQL queries to add data into the database implemented in Task 3.2. You can follow the template in Question 1 - data.sql or dataXAMPP.sql. The database must include at least six customers, five suburbs, 10 sold properties, and home loan records.