INFS2200/7903 PROJECT ASSIGNMENT 1
Semester Two 2022


Total Marks: 30 marks
Due Date: 4:00PM 9-September-2022
What to Submit: SQL script file
Where to Submit: Electronic submission via Blackboard


The goal of the project assignments is to gain practical experience in applying several
database management concepts and techniques using the Oracle DBMS. In particular,
this assignment mainly focuses on ensuing database semantics using various integrity
constraints.

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 the assignment and provides
instructions on downloading the script file needed to create and populate the database.
Section 2 describes the tasks to be completed for this assignment. Finally, Section 3
explains the submission guidelines and marking scheme.

Enjoy the project!


-------- ---------

SECTION 1. THE SALES DATABASE


The Database: The SALES database (Figure 1) captures the sales information in a
company that provides various 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 assignment “SalesDB.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 that should be
created on 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_ENAME EMP.EName EName must not be empty (not null)
7 CK_DNAME DEPT.DName DName must not be empty (not null)
8 CK_CNAME CLIENT.CName CName must not be empty (not null)
9 CK_RECEIPTNO PURCHASE.ReceiptNo ReceiptNo must not be empty (not
null)
10 CK_AMOUNT PURCHASE.Amount Amount must be a positive value
11 CK_POSITION EMP.Position Position must be one of the
following: 'Group Manager', 'Group
Assistant', 'Group Member', 'Team
Leader', or 'Branch Manager'
12 CK_SERVICETYPE PURCHASE.ServiceType Service type must be one of the
following: 'Software Installation',
'Software Repair', 'Training',
'Consultation' or 'Data Recovery'
13 CK_PAYMENTTYPE PURCHASE.PaymentType Payment type must be one of the
following: 'Debit', 'Cash', or 'Credit'
14 CK_GST PURCHASE.GST GST must be either 'Yes' or 'No'
15 FK_DEPTNO EMP.DeptNo EMP.DeptNo refers to DEPT
16 FK_EMPNO PURCHASE.ServedBy PURCHASE.ServedBy refers to
EMP
17 FK_CLIENTNO PURCHASE.ClientNo PURCHASE.ClientNo refers to
CLIENT

Table 1. Database constraints

-------- ---------

SECTION 2. ASSIGNMENT TASKS


Create and Populate Database: You need to execute the script file “SalesDB.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 listed
in Table 1 were created. Write a SQL statement to find out which constraints have
been created on the four tables EMP, DEPT, PURCHASE, and CLIENT.

2. Write the SQL statements to create all the missing constraints.

Task 2 – Triggers

1. Assume that PurchaseNo should be automatically populated when a new purchase
is made by clients. Write a SQL statement to create a sequence object to generate
values for this column. The sequence, named PNO_SEQ, should start from 10,000
and increment by 1.

2. Write a SQL statement to create an Oracle trigger called BI_PNO that binds the
sequence object PNO_SEQ to the PurchaseNo column, i.e., the trigger populates
values of PNO_SEQ to the PurchaseNo column when a new purchase is made.

3. The company’s top client is the one who has purchased the most, i.e., the one with
the highest total purchase amount among all the company’s clients. Write a SQL
statement to create an Oracle trigger called TOP_DISCOUNT that applies a 15%
discount (i.e., 15% reduction to the purchase amount) to any new purchases made
by the top client. (Note: Your trigger should not hardcode the top client since the
top client could change when more purchases are made by other clients)

4. 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 now offering a 30%
discount on ‘Data Recovery’ service. Write a SQL statement to create an Oracle
trigger SUNSHINE_DEPT that will (1) set the PaymentType to ‘Cash’ for any new
purchases where the client is served by an employee of this department; (2) if the
ServiceType is ‘Data Recovery’, give the customer a 30% discount. Note that this
discount is exclusive to the ‘SALES - Sunshine’ department. (Note: Your trigger
should not hardcode the DeptNo or EmpNo)

-------- ---------
SECTION 3. Deliverables & Marking Scheme


The project is due by 4:00PM, 9 September 2022. Late submissions will be penalized
unless you are approved for an extension (refer to Section 5.3 of the ECP).

You are required to turn in a script file studentID.sql (rename studentID) that includes
all your SQL statements. Submit your script file on Blackboard via the upload link “SQL
Script Submission”. Your script file should be in plain text format. You must make sure
that your script file can be executed on the ITEE lab computers by the “@” command.


Marking Scheme:

Tasks Marks Marking Criteria
1.1 2 • Write only one SQL
• Find all the created constraints on the four tables (the result
should exclude the constraints on other tables)
1.2 7 • Write only one SQL for creating each constraint
• The constraints are created with the correct name and
semantics (the correctness of the constraints will be tested
using several INSERT statements)
2.1 3 • Sequence is created with the correct name and semantics
2.2 4 • Trigger is created without compilation error
• The correctness of the trigger will be tested using several
INSERT & SELECT statements
2.3 8 • Trigger is created without compilation error
• The correctness of the trigger will be tested using several
INSERT & SELECT statements
• No hardcode is used for the top client
2.4 6 • Trigger is created without compilation error
• The correctness of the trigger will be tested using several
INSERT & SELECT statements
• No hardcode is used for the DeptNo or EmpNo