What to Submit: SQL script file
The goal of this project is to gain practical experience in applying several database management concepts and techniques using the Oracle DBMS.
Your main task is to first populate your database with appropriate data, then design, implement, and test the appropriate queries to perform the tasks explained in the next sections.
SECTION 1. THE SALES DATABASE
The Database: The SALES database (Figure 1) captures the sales information in a company that provides IT services. The database includes four tables: CLIENT, PURCHASE, EMP, and DEPT. CLIENT stores information about all the company’s clients. PURCHASE keeps track of the service purchases made by the clients. EMP stores information about the employees who work directly with the clients and serve their purchase requests. Employees work in different departments and the information about these departments is stored in the DEPT table. Figure 1 presents the database schema.
Figure 1 Database schema
The Script File: the supplementary script file for this project “prjScript.sql”.
The Database Constraints: The following table lists all the constraints applied to the SALES database.
No Constraint Name Table.Column Description
1 PK_EMPNO EMP.EmpNo EmpNo is the primary key of
EMP
2 PK_DEPTNO DEPT.DeptNo DeptNo is the primary key of
DEPT
3 PK_PURCHASENO PURCHASE.PurchaseNo PurchaseNo is the primary key of PURCHASE
4 PK_CLIENTNO CLIENT.ClientNo ClientNo is the primary key of CLIENT
5 UN_DNAME DEPT.DName DName values are unique
6 CK_AMOUNT PURCHASE.Amount Amount (in dollars) must not be empty (not null)
7 CK_ENAME EMP.EName EName must not be empty
(not null)
8 CK_DNAME DEPT.DName DName must not be empty
(not null)
9 CK_CNAME CLIENT.CName CName must not be empty
(not null)
10 CK_RECEIPTNO PURCHASE.ReceiptNo ReceiptNo must not be empty (not null)
11 CK_SERVICETYPE PURCHASE.ServiceType Service type must be one of the following: 'Training', 'Data Recovery',
'Consultation', 'Software
Installation', or 'Software
Repair'
12 CK_PAYMENTTYPE PURCHASE.
PaymentType Payment type must be one of the following: 'Debit', 'Cash', or 'Credit'
13 CK_GST PURCHASE.GST GST must be either 'Yes' or 'No'
14 FK_DEPTNO EMP.DeptNo and DEPT.DeptNo EMP.DeptNo refers to DEPT.DeptNo
15 FK_EMPNO PURCHASE.ServedBy and EMP.EmpNo PURCHASE.ServedBy refers to EMP.EmpNo
16 FK_CLIENTNO PURCHASE.ClientNo and CLIENT.ClientNo PURCHASE.ClientNo refers to CLIENT.ClientNo
Table 1. Database constraints
-----------------
SECTION 2. ASSIGNMENT TASKS
Create and Populate Database: You need to execute the script file “prjScript.sql” to create and populate your database before working on the following tasks. Wait till you see the message “Commit complete.” It should only take several seconds. The script will also drop related tables.
Task 1 – Constraints
Task 2 – Triggers
1.Write a SQL statement to find the company’s top client. A top client is the one who has purchased the most (i.e., the one with the highest total purchase amount (in dollars) among all the company’s clients). Your statement should display: client number, client name, and the total purchase amount by that client.
2.Write a SQL statement to create an Oracle trigger called TOP_DISCOUNT that applies a 15% discount to any future purchases made by the top client found in Task 2.1.
Hint: Your trigger should use the value obtained from Task 2.1. In particular, it should apply 15% reduction to the purchase amount whenever a new purchase made by that top client is inserted into the PURCHASE table.
3.The ‘SALES - Sunshine’ department has unfortunately run into a technical issue and is temporarily unable to process any ‘Credit’ or ‘Debit’ transactions. As a result, it only accepts ‘Cash’ transactions. Besides, the department is offering a 30% discount on ‘Data Recovery’ at the moment. Write a SQL statement to create an Oracle trigger SUNSHINE_DEPT that will set the PaymentType to always be ‘Cash’ for any purchases where the client is served by an employee of this department, and if the ServiceType is ‘Data Recovery’, give the customer a 30% discount. Note that this discount is exclusive to the ‘SALES - Sunshine’ department.
Task 3 – Views
1.Write a SQL statement to create a (virtual) view called V_DEPT_AMOUNT that lists the DeptNo and DName of all the company departments together with the maximum, minimum, average, and total purchase amount contributed by each of those departments.
2.Write a SQL statement to create a materialized view MV_DEPT_AMOUNT that lists the same information as in Task 3.1.
3.Write SQL statements to create a virtual view called V_DEPT_EMP_AMOUNT and a materialized view called MV_DEPT_EMP_AMOUNT respectively. These views should report the contribution of each employee in each department. In particular, for each employee, the view should list: EmpNo, the total number of purchases, the average amount of purchases, the largest amount of purchase, and the total amount of purchases served by that employee. The view should list the departments in increasing order of DeptNo, and within each department the employees should be listed in decreasing order of the total amount of purchases.
Task 4 – Indexes
1.Each receipt is issued from a receipt book whose number is encoded in the first three digits of the ReceiptNo field in the PURCHASE table. For example, the receipt numbered 454333 was issued from receipt book number 454. Write a SQL statement to count the number of purchases for which there have been at least 10 other purchases issued from the same receipt book.
Hint: For each purchase p in the PURCHASE table, you will need to go over all the other purchases and find the ones with a ReceiptNo that starts with the same 3 digits as in p’s ReceiptNo.
2.In order to speed up the query in Task 4.1, a function-based index is to be created on the ReceiptNo field. Write a SQL statement to create an index called BOOK_INDEX that best fits the task and justify your choice. Report execution time of the query statement you wrote in Task 4.1 before and after creating this index. Did the index speed up the query (Hint: Look at both the elapsed time and the cost in the execution plan)? Explain your answer.
3.The manager of department 50 wants to see the total amount of sales for his department for all purchases of services that do not contain the word ‘Software’
(e.g., not ‘Software Repair’, ‘Software Installation’ or any other ServiceType containing the word ‘Software’). Write a SQL query to return this amount.
Note: To make your query general enough, assume that Constraint #11 is not in place and there could be many services offered by the department that are beyond the ones listed in Constraint #11. Besides, you should avoid using LIKE and instead use string manipulation functions such as INSTR, SUBSTR, etc.
4.In order to speed up the query in Task 4.3, a function-based index is to be created on the ServiceType field. Write a SQL statement to create an index called SERVICE_INDEX that best fits the task and justify your choice. Report the execution time of the query statement you wrote in Task 4.3 before and after creating this index. Did the index speed up the query (Hint: Look at both the elapsed time and the cost in the execution plan)? Explain your answer.
5.Write a SQL statement to count the number of purchases for which there are at least 1,000 other purchases with the same ServiceType, PaymentType, and GST values.
6.In order to speed up the query in Task 4.5, indexes should be created on the ServiceType, PaymentType, and GST columns. In your opinion, what is the most suitable index type to create on those columns, and why? (Note: Do not include any SQL to create these indexes in your script file; just provide your answer in the report.)
Task 5 – Execution Plan
1.Write a SQL statement to list the information for purchase number 1234. Report and explain the plan chosen by the Oracle optimizer for executing your query.
2.Drop the primary key constraint from the PURCHASE relation and re-execute the query you wrote in Task 5.1. Report and explain the plan chosen by the Oracle optimizer for executing your query. In your opinion, what are the main differences between this plan and the one obtained in Task 5.1?