QHE5701 SQL

Java Python QHE5701 – Database Systems 2024
Lab – 5 1
Lab 5: Designing Database and Generating sample data (using data generator tool)
In this lab you’ll use MySQL to design and implement a database from user requirement.

Database Specification Details:
In a company that manages a big chain of doughnut-selling shops, there is a need to
create a database for the data accumulated through the last years. Consider that the data
you have available is:
• List of menus which can be regular or seasonal (for example Christmas menu),
they have a name and start and end date in case of a seasonal menu. Additionally,
we also have a specific colour palette they must follow used by each menu.

• List of products available for each menu we know names, prices, calories, sugar
content, protein, and salt.

• For the same sales, we know the buyer if they have scanned their loyalty app. A
buyer has some data saved like first name, last name, gender, age and occupation.

• Employees can have diTerent contracts (full-time, part-time) with additional total
working hours (overti QHE5701、SQL me). We also account for the years of experience of every one
of them.

• The branch is a location of our company that has an address and a size. Size is an
index that describes how big is the branch.

• List of ingredients used for every product (name, country of origin, units
purchased, price, name of the supplier)

• Each sale must be stored with the exact time and date of sale and price.

NOTE: Assume that size is a parameter denoting roughly the cost of running that branch.

Task:
Design a database for this problem, which involves:
i) Create an ER model for this problem
ii) Perform the mapping to the logical model QHE5701 – Database Systems 2024
Lab – 5 2
iii) Produce the SQL code for the database
iv) Create sample data for your database (to make it easier use a generator
like: https://extendsclass.com/csv-generator.html )

Lab Submission Guidelines:
Complete the task in Lab-5 as this will also be used in next lab. Details on Week-3
submission will be provided with Lab-6 description