首页 > > 详细

INFS2200/7903 PROJECT ASSIGNMENT

 INFS2200/7903 PROJECT ASSIGNMENT

Semester Two 2019
Marks: 100 marks (15%)
Due Date: 11:59PM 20-October-2019
What to Submit: SQL script file in addition to a short report
Where to Submit: Electronic submission via Blackboard
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 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: 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
CLIENT
ClientNo
CName
Phone
DEPT
DeptNo
DName
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
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. Write the necessary SQL statements to create all the missing 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. Execute the following two SQL statements and report their query execution time. 
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 V_DEPT_AMOUNT; 
Q2: SELECT * FROM MV_DEPT_AMOUNT;
4. 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.
5. Execute the following two SQL statements and report their query execution time. 
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 V_DEPT_EMP_AMOUNT; 
Q4: SELECT * FROM MV_DEPT_EMP_AMOUNT;
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?
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: (rename studentID) – Submit on Blackboard via the Turnitin link 
“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 SQL*Plus 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.com/cd/A57673_01/DOC/server/doc/SP33/ch4.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 lecturer/tutors to quickly check the correctness 
of your SQL statements before checking the details in your report file.
联系我们
  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codehelp
热点文章
程序代写更多图片

联系我们 - QQ: 99515681 微信:codinghelp
© 2014 www.7daixie.com
程序代写网!