COMP3278 Introduction to Database Management Systems (8%)
Application Development
Due Date: 2023 March 17 (Fri.) 17:30 pm
The database schema solution developed in Assignment 1 has been
adopted by the company with some simplification. The schemas
are shown below (The underlined attributes represent the primary
key of the relation):
• Member (member_ID, name, email, contact_number)
Foreign Key: None
• GroupOrder (order_ID, member_ID)
Foreign Key:
{member_ID} referencing Member.member_ID
• Package (package_ID, owner_ID, order_ID, weight)
Note: The weight column of the Package table stores the weight of the package in kg.
Foreign Key:
{owner_ID} referencing Member.member_ID
{order_ID} referencing GroupOrder.order_ID
• HomeDeliveryOrder (order_ID, address)
Foreign Key:
{order_ID} referencing GroupOrder.order_ID
• LockerPickupOrder (order_ID, locker_ID, cell_number, arrival_datetime, collect_datetime)
Foreign Key:
{order_ID} referencing GroupOrder.order_ID
{locker_ID} referencing Locker.locker_ID
• ServiceArea (service_area_ID, name, parent_area_ID)
Foreign Key:
{parent_area_ID} referencing ServiceArea.service_area_ID
• Locker (locker_ID, service_area_ID, name)
Foreign Key:
{service_area_ID} referencing ServiceArea.service_area_ID

Requirements
1) [20%] Build the database using MySQL
a. Using the schemas above, define tables with appropriate constraints.
b. Store the corresponding table definition commands in a SQL file called “tables.sql” (Please
include also the referential constraints in the .sql file, make sure that the files can correctly build
the necessary tables and constraints when import to another database).

2) [80%] Answer queries and display results
• Build from q1.php to q9.php, which solve the following nine queries, and display the result in a
web browser.
• Note that besides the nine php files, you have to include one more file q8_submit.php, we will
explain the use of q8_submit.php later.
• Browse the following page for the expected output of the php files with the given sample data:
https://i.cs.hku.hk/~zrxie/as2_2023/index.html
2
Q1. [Given sample] Display the member_ID, name, email and contact_number of the member(s)
whose name has “tom” in it.
• Case insensitive matching for the name of the member.
• Sort the records in descending order of member_ID.

Q2. [5%] Display the order_ID, address, member_ID, name of the HomeDeliveryOrder(s) that the
address has “Hong Kong Island” in it.
• member_ID and name are the member’s ID and the name of the member who made that
HomeDeliveryOrder.
• Case insensitive matching for the address of the HomeDeliveryOrder(s).
• Sort the records in ascending order of the order_ID.

Q3. [5%] Display the member_ID, name and service_count of the member(s) who used the GroupOrder
service for more than three times.
• service_count is the number of times the member has used the GroupOrder service.
• Sort the records in descending order of service_count, and then in ascending order of their
member_ID.

Q4. [5%] Display the member_ID, name and service_count of the member(s) who used the
LockerPickupOrder service for less than three times.
• service_count is the number of times the member has used the LockerPickupOrder service.
• The members who have not used any LockerPickupOrder service have to be included in the
result (with service_count as 0).
• Sort the records in descending order of the service_count, and then in ascending order of their
member_ID.

Q5. [5%] Display the order_ID, total_weight of the GroupOrder that is heavier than 10kg.
• total_weight is the sum of the weight of the packages of the GroupOrder.
• Sort the records in descending order of the total_weight, and then in ascending order of the
order_ID.

Q6. [10%] Display the order_ID, arrival_datetime, collect_datetime, and extra_hours of the
LockerPickupOrder(s) that is collected after 48 hours from its arrival_datetime.
• extra_hours is the number of extra hours that the LockerPickupOrder has not been collected
after it has arrived at the locker for 48 hours.
• You may use TIMESTAMPDIFF() to solve this question.
• Sort records in descending order of the extra_hours, and then in ascending order of order_ID.

Q7. [10%] Display the order_ID, locker_name, service_area_name of the LockerPickupOrder with the
Locker choose to be in the service area of “Hong Kong Island”.
• The orders that store in the Lockers of the sub-ServiceArea(s) of “Hong Kong Island” will be
included in the result.
• locker_name is the name of the Locker of the LockerPickupOrder.
• service_area_name is the name of the ServiceArea of the Locker.
• To simplify this question, you can assume that there is at most a 3-layer hierarchy structure of
the ServiceArea in the testing data.

3
Q8. [25%]

In q8_submit.php, display a drop-down menu that contains all the service_area_name and locker_count
for the serviceArea without sub-serviceArea.
• service_area_name is the name of the ServiceArea.
• locker_count is the total number of Lockers in the ServiceArea.
• Only list the record(s) with locker_count >= 1.
• The drop-down menu contains options in the format of “service_area_name: locker_count”, sort
the options in descending order of locker_count.
• Add a submit button, and after clicking the submit button, the page will be directed from
q8_submit.php to q8.php.
In q8.php, display locker_ID, locker_name, and uncollected_order_count of the Lockers in the
serviceArea selected from q8_submit.php
• uncollected_order_count is the total number of uncollected orders (with collect_datetime as
NULL) of the Locker.
• Sort the record in descending order of uncollected_order_count.

Q9. [15%]

Update q8.php by making the locker_name of the locker (say, with locker_ID as x) a hyperlink to
q9.php?locker_ID=x.
• In q9.php, display the locker_ID, cell_number, member_ID, member’s name and
contact_number of the LockerPickupOrder(s) in the locker x that is uncollected (with
collect_datetime as NULL). Also list the package_count of the LockerPickupOrder.
o package_count is the number of packages in the LockerPickupOrder.
o Sort the records in descending order of package_count.
Hand in
1. Please compress "tables.sql", and all the related PHP files into one zip file and use your student
number to name it (e.g. 303004954x.zip).
2. Please submit this zip file through our Moodle system before the deadline.
Important Notes
1. Let’s enjoy this assignment as an interesting SQL practice.
2. Table names and attribute names must be consistent with the names provided in relation schemas.
3. Tutorials 2 and 3 contain important information about how to install and use PHP and MySQL.
Please refer to these tutorials if necessary.
4. We provided sample tuples and sample query results for you to verify the correctness of your table
definitions. Please do not submit the files with the sample data.
5. The data that we use to grade your assignment may be different from the sample data.
4
Please feel free to post your questions on Moodle forum or contact us
(TA Tom zerong@connect.hku.hk) if you encounter any difficulty with this assignment. We are very
happy to help.
We wish you enjoy learning database technologies in this course!