Description
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. Those employees work in different departments and the
information about these departments is stored in the DEPT table. Figure 1 presents the
database schema.
Page | 2
Figure 1 Database schema
The Script File: Please go to the following web page and download the supplementary
script file:
http://itee.uq.edu.au/~infs2200/pracs/prjScript.sql
Page | 3
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 ENamemust 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. Constraints
——–———
Page | 4
SECTION 2. ASSIGNMENT TASKS
Task 0 – Database
a. You need to execute the script file to create and populate your database before
working on the following tasks.
Task 1 –Constraints
a. 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.
b. Write the necessary SQL statements to create all the missing constraints.
Task 2 –Triggers
a. 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.
b. Write a SQL statement to create an Oracle trigger that applies a 15% discount to
any future purchases made by the top client found in Task 2a.
Hint: Your trigger should use the value obtained from Task 2a. In particular, it
should apply a 15% reduction to the purchase amount whenever a new purchase
made by that top client is inserted into the PURCHASE table.
c. The SALES – Sunshine department has unfortunately run into a technical issue
and are temporarily unable to process any Credit or Debit transactions. As a
result, they are only making Cash transactions. Additionally, the department is
offering a 30% discount on ‘Data Recovery’ at the moment. Write an SQL
statement to create an Oracle trigger that will set the PaymentType to always be
‘Cash’ for any purchases where the client was 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 Sunshine department.
Task 3–Views
a. Write a SQL statement to create a (regular) view called V_DEPT_AMOUNT that
lists the names and numbers of all the company departments together with the
Page | 5
maximum, minimum, average, and total purchase amount contributed by each of
those departments.
b. Write a SQL statement to create a materialized view called MV_DEPT_AMOUNT
that lists the same information as in Task 3a.
c. Execute the following two SQL statements and report their query execution time.
Is there any difference between the reported execution times of Q1 and Q2?
Please give the reasons for that.
Q1: SELECT * FROM V_DEPT_AMOUNT;
Q2: SELECT * FROM MV_DEPT_AMOUNT;
d. Write an SQL statements to create both a regular and materialized view called
V_DEPT_TOP_EMPS and MV_DEPT_TOP_EMPS. These views should list the
ten top ten employees in each department. For each of those employess, the
view should include: the total number of purchases, the average amount of
purchases, the highest amount of purchase, and the total amount of purchases.
The view should list the departments in order, and within each department the
employees should be ordered according to the total amount of purchases.
e. Execute the following two SQL statements and report their query execution time.
Is there any difference between the reported execution times of Q3 and Q4?
Please give the reasons for that.
Q3: SELECT * FROM V_DEPT_ TOP_EMPS;
Q4: SELECT * FROM MV_DEPT_ TOP_EMPS;
Task 4–Indexes I
a. 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 has 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.
b. In order to attempt to speed up the query in Task 4a, a function-based index is to
be created on the ReceiptNo field. Write a SQL statement to create an index that
best fits that task and justify your choice. Report the execution time of the query
statement you wrote in Task 4a before and after creating this index. Did the
index speed up the query (look at both the elapsed time and the costs in the
execution plan)? Explain your answer.
Page | 6
c. The manager of department 50 wants to see the total amount of sales for his
department for all purchases for services that do not contain the word ‘Software’
– eg not ‘Software Repair’, ‘Software Installation’ or any other ServiceType
containing the word ‘Software’. Write an SQL query to return this amount. To
make your query general enough, assume that Constraint #11 is not in place and
there could be many services that are offered by the department beyond the
ones listed in Constraint #11.
d. In order to speed up the query in Task 4c, a function-based index is to be created
on the ServiceType field. Write a SQL statement to create an index that best fits
that task and justify your choice. Report the execution time of the query
statement you wrote in Task 4c before and after creating this index. Did the
index speed up the query (look at both the elapsed time and the costs in the
execution plan)? Explain your answer.
Task 5 –Indexes II
a. Write a SQL statement to count the number of purchases for which there are at
least 1000 other purchases with the same ServiceType, PaymentType, and GST
values.
b. In order to speed up the query in Task 5a, 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?
Do not include any SQL to create these indexes (6b) in your script file.
Task 6 – Execution Plan
a. Write a SQL statement to list the information for purchase number 9989. Report
and explain the plan chosen by the Oracle optimizer for executing your query.
b. Now, drop the primary key constraint from the PURCHASE relation and reexecute the query you wrote in Task 6a. 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 6a?
Page | 7
Marking Scheme:
Tasks Marks
0 2
1a 4
1b 4
2a 6
2b 6
2c 8
3a 4
3b 4
3c 4
3d 8
3e 4
4a 6
4b 8
4c 4
4d 4
5a 4
5b 4
6a 2
6b 4
Presentation &
Readability
10
Total 100
Page | 8
——–———
SECTION 3. Deliverables
The project is due 11:59PM, 23rd May 2013. No late submission is allowed.
You are required to turn in two files (use studentID to name your files):
1. studentID.pdf:
A report answering all the questions in Section 2 including all the necessary SQL
statements and their outputs (maximum 8 pages).
2. studentID.sql:
A script file that includes all your SQL statements.
Your report file should include the following content:
o Answers to all the questions in Section 2.
o If you are asked to write SQL statements, you need to include those statements
in your report.
o When you execute a SQL statement, if SQL*Plus responses with any output (e.g.
query results, query execution time, query plan, etc), you need to include the
output as well. For example, in Task 2a, you need to include a SQL statement to
find the client who purchased the most and you also need to show the response
of SQL*Plus when you execute that statement. Your entire answer for Task 2a
should look similar to the text below (the actual result might be different).
SELECT … [your statement comes here] …
CLIENTNO CNAME TAMOUNT
———————————————————————–
21244 John Smith 126789
Your script file is in plain text format. You must make sure that your script file can be
executed on the ITEE lab machines. 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 in comparison to the report file, it is
intended to help the lecturer/tutors to quickly check the correctness of your SQL
statements before checking the details in your report file.
Enjoy your project!
