关键词 > INFS2200/7903
INFS2200/7903 PROJECT ASSIGNMENT 1 Semester Two 2022
发布时间:2022-08-30
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
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.
PURCHASE
PurchaseNo ReceiptNo ServiceType PaymentType GST Amount ServedBy ClientNo |
DEPT
DeptNo DName |
Figure 1 Database schema
The Script File: Please go to Blackboard and download the supplementary script file for this project assignment “SalesDB.sql” .
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 |
1 1 |
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.