Marks:
Due Date:
What to Submit: Where to Submit:
Semester Two 2019
100 marks 15
11:59PM 20October2019
SQL script file in addition to a short report Electronic submission via Blackboard
INFS22007903 PROJECT ASSIGNMENT
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.
You must work on this project individually. Academic integrity policies apply. Please refer to 3.60.04 Student Integrity and Misconduct of the University Policy for more information.
Roadmap: Section 1 describes the database schema for your project and it also provides instructions on downloading the script file needed to create and populate your database. Section 2 describes the tasks to be completed for this project. Finally, Section 3 provides you with all the necessary submission guidelines.
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 companys 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: Please go to Blackboard and download the supplementary script file for this project prjScript.sql.
EMP
EmpNo EName Position DeptNo
PURCHASE
PurchaseNo ReceiptNo ServiceType PaymentType GST
Amount ServedBy ClientNo
DEPT
DeptNo DName
CLIENT
ClientNo CName Phone
The Database Constraints: The following table lists all the constraints applied to the SALES database.
No
Constraint Name
Table.Column
Description
1
PKEMPNO
EMP.EmpNo
EmpNo is the primary key of EMP
2
PKDEPTNO
DEPT.DeptNo
DeptNo is the primary key of DEPT
3
PKPURCHASENO
PURCHASE.PurchaseNo
PurchaseNo is the primary key of PURCHASE
4
PKCLIENTNO
CLIENT.ClientNo
ClientNo is the primary key of CLIENT
5
UNDNAME
DEPT.DName
DName values are unique
6
CKAMOUNT
PURCHASE.Amount
Amount in dollars must not be empty not null
7
CKENAME
EMP.EName
EName must not be empty not null
8
CKDNAME
DEPT.DName
DName must not be empty not null
9
CKCNAME
CLIENT.CName
CName must not be empty not null
10
CKRECEIPTNO
PURCHASE.ReceiptNo
ReceiptNo must not be empty not null
11
CKSERVICETYPE
PURCHASE.ServiceType
Service type must be one of the following: Training, Data Recovery, Consultation, Software Installation, or Software Repair
12
CKPAYMENTTYPE
PURCHASE. PaymentType
Payment type must be one of the following: Debit, Cash, or Credit
13
CKGST
PURCHASE.GST
GST must be either Yes or No
14
FKDEPTNO
EMP.DeptNo and DEPT.DeptNo
EMP.DeptNo refers to DEPT.DeptNo
15
FKEMPNO
PURCHASE.ServedBy and EMP.EmpNo
PURCHASE.ServedBy refers to EMP.EmpNo
16
FKCLIENTNO
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
1. After running the script file, you will notice that only some of the constraints given in Table 1 were created. Write the necessary SQL statements to find out which constraints have been created on the tables EMP, DEPT, PURCHASE, and CLIENT.
2. WritethenecessarySQLstatementstocreateallthemissingconstraints.
Task 2 Triggers
1. WriteaSQLstatementtofindthecompanystopclient.Atopclientistheone who has purchased the most i.e., the one with the highest total purchase amount in dollars among all the companys clients. Your statement should display: client number, client name, and the total purchase amount by that client.
2. WriteaSQLstatementtocreateanOracletriggercalledTOPDISCOUNTthat 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. TheSALESSunshinedepartmenthasunfortunatelyrunintoatechnicalissue 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 SUNSHINEDEPT 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. WriteaSQLstatementtocreateavirtualviewcalledVDEPTAMOUNTthat 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. WriteaSQLstatementtocreateamaterializedviewMVDEPTAMOUNTthat lists the same information as in Task 3.1.
3. ExecutethefollowingtwoSQLstatementsandreporttheirqueryexecutiontime. Did the materialized view speed up query processing? Hint: Look at both the elapsed time and the cost in the execution plan? Explain your answer.
Q1: SELECT FROM VDEPTAMOUNT;
Q2: SELECT FROM MVDEPTAMOUNT;
4. WriteSQLstatementstocreateavirtualviewcalledVDEPTEMPAMOUNT and a materialized view called MVDEPTEMPAMOUNT 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.
5. ExecutethefollowingtwoSQLstatementsandreporttheirqueryexecutiontime. Did the materialized view speed up query processing? Hint: Look at both the elapsed time and the cost in the execution plan? Explain your answer.
Q3: SELECT FROM VDEPTEMPAMOUNT;
Q4: SELECT FROM MVDEPTEMPAMOUNT;
Task 4 Indexes
1. Eachreceiptisissuedfromareceiptbookwhosenumberisencodedinthefirst 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 ps ReceiptNo.
2. In order to speed up the query in Task 4.1, a functionbased index is to be created on the ReceiptNo field. Write a SQL statement to create an index called BOOKINDEX 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 functionbased index is to be created on the ServiceType field. Write a SQL statement to create an index called SERVICEINDEX 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. WriteaSQLstatementtocountthenumberofpurchasesforwhichthereareat 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. WriteaSQLstatementtolisttheinformationforpurchasenumber1234.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 reexecute 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?
Marking Scheme:
Tasks
Marks
1.1
4
1.2
6
2.1
6
2.2
6
2.3
10
3.1
4
3.2
4
3.3
4
3.4
8
3.5
4
4.1
6
4.2
8
4.3
4
4.4
6
4.5
4
4.6
4
5.1
3
5.2
4
Presentation Readability
5
Total
100
SECTION 3. Deliverables
The project is due 11:59PM, 20 October 2019. Late submissions will not be accepted. You are required to turn in two files use studentID to name your files:
1. studentID.pdf:renamestudentIDSubmitonBlackboardviatheTurnitinlink Submission Report Only
A report answering all the questions in Section 2 including all the necessary SQL statements and screenshots of their outputs.
2. studentID.sql: rename studentID Submit on Blackboard via the standard upload link Submission SQL Script Only
A plaintext script file that includes all your SQL statements.
Your report file should include the following content:
Answers to all the questions in Section 2.
If you are asked to write SQL statements, you need to include those statements in your report.
When you execute a SQL statement, if Oracle produces any output e.g. query results, query execution time, query plan, etc, you need to include a screenshot of the output as well. For example, in Task 2.1, you need to include a SQL statement to find the client who purchased the most and you also need to show a screenshot of the response of SQLPlus when you execute that statement. Your entire answer for Task 2.1 should look similar as below the actual result might be different.
SELECT … your statement comes here
Note: Please be sensible when including query output. Any output close to the size of one page can be shown by just including the first 10 lines and the last 10 lines. Reports including pages of a query output will lose presentation marks. You may find some helpful instructions for formatting query output in Practical 2 or the following Oracle documentation:
https:docs.oracle.comcdA5767301DOCserverdocSP33ch4.htm
Your script file is in plain text format. You must make sure that your script file can be executed on the ITEE lab machines by the command. The same SQL statements in your script file should also be copied and pasted into your report file as explained above. Even though the script file does not introduce any new information compared to the report, it is intended to help the lecturertutors to quickly check the correctness of your SQL statements before checking the details in your report file.