代写QHE5701、SQL编程语言代做

日期：2024-12-02 09:21

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.