COMP$3278$B Introduction to Database Management Systems

Assignment $1.$ ER$-$Modeling

Due Date: $2024$ Feb $19($Monday$)17$:$30$ pm

The ABC company provides rental service of portable

power banks to its registered members. You are a database

designer of the ABC company, and you are designing the

database to support a system with the following functions:

Membership System

Each member is required to provide their

name, email, and contact number in the

registration form. The system will

automatically generate a unique member ID

upon registration.

Power Bank Management System

There are charging stations, each equipped with a unique station ID and

contains a certain number of charging slots.

Each charging slot is assigned a unique slot ID$,$ exclusive to that particular

charging station.

The company possesses numerous power banks, each distinguished by a

unique pbID.

Any power bank can be plugged into any available charging slot within a

charging station.

Power Bank Rental System

A member can initiate multiple rental transactions, each

involving the rental of one power bank.

Each rental transaction is assigned a unique transaction

ID$,$ capturing information such as which member

renting which power bank.

The transaction can record which charging slot the user

retrieved from and the retrieval datetime, as well as

which charging slot the user returned to$,$ and the return

datetime.

Additionally, the transaction includes details of the

payment amount.

Coupon System

ABC Company issues coupons to its members, each with a

unique coupon ID and an expiration date. There are two types

of coupons: Free Rental Coupons and Discount Coupons.

Free Rental Tickets: Each ticket can be used to redeem one

free rental transaction.

Merchants Discounts:

i$.$ These coupons are not tied to the rental of power

banks but offer discounts accepted by one or more

partner merchants.

ii$.$ ABC Company collaborates with various partner

merchants, each having a unique merchant ID$.$ Some

of these merchants also maintain charging stations.

Reservation System

Members can reserve a power bank at any available charging station with a

reservation interface that operates as follows:

Members can search for a service area using a hierarchical structure,

eventually selecting a location $-$ this is the lowest$-$level service area without

any sub$-$service area.

Each location may host zero to many charging stations.

Members choose a charging station with an available power bank and

proceed to make a reservation.

Each reservation is assigned a unique reservation ID and records the

datetime of the reservation.

Ass$$gnment tasks

$[50\%]$ Task $1.$ Please draw an E$-$R diagram to capture all the requirements above.

If there is any information about the data model that is not listed in the

specification, please make a reasonable assumption and list your assumption in

your design.

Marks will be deducted if unnecessary components are added to the tables.

Please follow the problem specification when you decide on the name of the

components.

You are encouraged to draw the E$-$R diagram using any kind of editor, this can

help us to read and understand your data model. If you choose to draw the E$-$R

diagram by pen$/$pencil$,$ please scan your work and submit a PDF file. We may

ask you to re$-$submit a clearer version in case we cannot read the handwriting.

$[50\%]$ Task $2.$ Please translate your E$-$R diagram into relational table schemas.

For each relation, underline the primary key and specify all the foreign keys if

any.

You only need to give the table schema in text form. You do not need to draw the

schema and do not need to provide the data type$/$SQL to create the tables.

Enjoy this assignment as a practice related to Chapter $2($