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 (overtime). 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. 

請加QQ：99515681  郵箱：99515681@qq.com   WX：codinghelp