INFS2200/7903代写、代写SQL设计程序

日期：2022-09-06 10:21

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.

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