辅导DIT5302留学生、讲解SQL程序、辅导Database Systems、SQL语言讲解
- 首页 >> DatabaseTechnological and Higher Education Institute
of Hong Kong
Bachelor Science (Honours) of
Information and Communications Technology
Module Name: Advanced Database Systems
Module Number: DIT5302
Overall Assessment: Continuous Assessment 50%
Final Examination 50%
Assignment Number: One
Hand in: on or before 12:00pm. November 15, 2018 Thursday
Submit your work during class
This Assignment: 40% of Continuous Assessment
Aims and Objectives of the Assignment
This assignment is to allow you to familiarize yourself with the concept of Relational
Database System to practice Subquery, Stored Procedures, Functions, Packages and
Transactions in a Relational Database System. After doing this assignment, you should be
able to understand the components in Advanced Database Systems and have experienced the
practical skills for developing a commercial database system.
Assessment
- This is an individual assignment. Each student must submit his/her own coursework.
Plagiarism is strictly prohibited.
- Late submission without valid reason will be counted ZERO mark. Well-presented report
and complete testing of the given answers contribute to higher marks. If the given
answers are not clear, demo of the answers will be required.
- Coursework should be put in an envelope (with student name) containing your work
for submission as mentioned in Section D.
Software and Programming Skills Required:
Oracle Database System
PL/SQL Programming
Stored Procedures, Functions, and Packages, Triggers
Advanced Database
Systems (DIT5302)
Assignment One Page: 2
A. Scenario of the Assignment:
The database is designed for a sales order processing system. To simplify the case, only
addition of order is considered in this assignment. It is assumed that only one place to
store the items and all the prices are in Hong Kong Dollars (Please note, in real situation,
orders can be changed or deleted, Usually, more than one warehouses will be used to the
items and the price will be in different currencies such as HKD or RMB.)
In this system, the customer can place orders and the goods ordered can be shipped to
more than one places. But each order can only be shipped to one place.
i. When the order is placed for an item or a number of items, the system will create
a record in the order table.
ii. For each ordered item, the system checks whether the quantity available
(quantity on hand – quantity reserved) of that item is enough to fulfill the order
quantity.
iii. If the quantity available is enough for the ordered quantity, the quantity reserved
of that item will be increased by the ordered quantity.
iv. The system also retrieves the effective unit price for that item. The effective date
of the unit price should be equal and less than order date.
v. The order detail of that item is recorded in the orderDetail table.
vi. The transaction is committed with these two updates mentioned in (v) and (vii).
vii. A trigger is executed before the insertion of the orderDetail record to increase the
reserved quantity of the ordered item in the Item table. Another trigger is
executed to increase the orderTotal in the Order table after each orderDetail
record is updated.
viii. If the quantity available is not enough to cover the ordered quantity, the order
detail of that item will be recorded in a table called Backorder record to notify
the management to purchase more for that item.
ix. When the orderDetail table contains no rows for an order, the record in the order
table should be deleted.
B. You are given the following sampling data. However, additional testing data is
needed to ensure the system developed by you can handle various cases.
Customer
CustomerID CustomerName Contact Email Contact
PhoneNo
Contact Name
00001 Tai Hing taihing@gmail.com 2468-1111 Chan Tai Man
00002 Tea Wood teawood@yahoo.com 4567-8911 Lee Siu Yan
Advanced Database
Systems (DIT5302)
Assignment One Page: 3
CustomerAddress
CustomerID Address
ID
AddressType Address Tel_no
00001 01 Bill_to 3 Ashry Place, LA 2309-1222
00001 02 Ship_to 92 Selkirk Street, NY 2743-0213
00002 01 Bill_to 200 Nathan Road, HK 2300-9011
00002 02 Ship_to 655 Brown Drive, AL 2623-9002
00002 03 Ship_to 123 Richmond Ave. 2333-1234
Item
ItemNo ItemName QtyOnHand QtyReserved
00001 Panasonic Induction cooker 100 20
00002 Rasonic air conditioner 10 2
00003 ABC washer 12 4
00004 Loyala Rice cooker 100 50
00005 ABC dryer 10 10
ItemPrice
ItemNo Effective
Date
UnitPrice
00001 20180901 600
00001 20190101 650
00002 20180101 4250
00002 20181011 4300
00002 20190202 4310
00003 20180101 3500
00004 20180101 738
Order
OrderNo CustomerID OrderDate BillToAddressID ShipToAddressID OrderTotal
000001 00001 20180910 01 02 15750
000002 00002 20190902 01 02 34740
OrderDetail
OrderNo OrderLineNo ItemNo OrderQty OrderUnitPrice
00001 00001 00001 5 600
00001 00002 00002 3 4250
00002 00001 00002 4 4310
00002 00002 00003 5 3500
BackOrder
BackOrderNo RequestedDate CustomerID ItemNo RequestedQty
00001 20180926 00001 00005 5
00002 20181023 00002 00005 3
Advanced Database
Systems (DIT5302)
Assignment One Page: 4
C. Questions:
Design and Query
1. One of the best approaches to the application of semantic modelling is the Entity /
Relationship (E/R) approach. Based on the given scenarios, you are required to study
carefully and draw the Entity / Relationship Diagram (ERD) with data attributes for
the Sales Order Processing System. Define the relationships of the model by data
definition language statements (DDL) with primary keys, foreign keys and integrity
constraints. (25%)
2. Choose two tables, design and implement a Table Clustering in the Sales Order Database.
Give two good reasons, in details, of applying the concept of Clustering in the Sales
Order Database (4%)
3. Give SQL statements, using Subquery to carry out the following information using
the Relational Sales Order Database which contains all the given tables.
i) Find the year, the customer ID and the name of the customer and the total order
amount in each year whose total order amount is the highest of all customers in
that year. (8%)
ii) The manager wants to know how much sales amount is lost due to not enough
stock to fulfill the order in a given period of time. Therefore you need to print a
report which shows the itemNo, itemName, the requested quantity, total amount
of the requested item and the requested date within a chosen period. (8%)
PL/SQL - Stored Procedure, Function and Package (55% in Total)
4. Create two triggers for the OrderDetail table before and after insertion as
mentioned in A(vii) section of this assignment. (10%)
5. Create a Package called salesAdmin which consists of the following:
i) A function which create the order record with the orderNo as the return value as
mention in A(i). (5%)
ii) A function which checks the available quantity as mentioned in A(ii) (5%)
iii) A function to retrieve a unit price of the order item with the current order date as
mentioned in A(iv). (5%)
iv) A function to record the order detail for the order item, quantity and unit price
with the return value as the orderLineNo as mention in A(v) (5%)
v) A procedure to create a backorder record as mentioned in A(viii) (5%)
vi) A procedure to delete the order record if no orderDetail exists for that order as
mentioned in A(ix) (5%)
vii) A procedure to display the order and its corresponding order details with the
current order number as the input parameter. (5%)
6. Write a PL/SQL block to use the functions or procedures in the package salesAdmin
to add an order for a customer, your PL/SQL should be able to input more than one
orderDetail for each order. When the order is completed, the complete order
content and the order details should be displayed. (10%)
Advanced Database
Systems (DIT5302)
Assignment One Page: 5
D. YOU SHOULD SUBMIT THE FOLLOWING in an Envelop:
1. A Report containing
Answers to each questions
The spool file which shows
i) the contents of all tables before the test run of the queries in question
3 and PL/SQL block in question 6
ii) the screen dump or the spool file during the test run of question 3
iii) the screen dump during the test run for question 6 (your test run must
contain at least two conditions, (a) enough stock for an ordered item
and (b) not enough stock for an ordered item)
iv) the contents of all tables after the test run of the PL/SQL block in
question 6
2. One CD-ROM containing report and all code listings.
**** THE END ****