QHE5701 – Database Systems

QHE5701 – Database Systems 2024 Lab – 51

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 tocreate a database for the data accumulated through the last years. Consider that the datayou 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, sugarcontent, protein, and salt.For the same sales, we know the buyer if they have scanned their loyalty app. Abuyer has some data saved like first name, last name, gender, age and occupation.Employees can have diTerent contracts (full-time, part-time) with additional totalworking hours (overtime). We also account for the years of experience of every onof them.The branch is a location of our company that has an address and a size. Size is anindex that describes how big is the branch.List of ingredients used for every product (name, country of origin, unitspurchased, 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:

1. i) Create an ER model for this problem
2. ii) Perform the mapping to the logical modelQHE5701 – Database Systems 202Lab – 5iv) 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-3submission will be provided with Lab-6 description.

HE5701 – Database Systems 2024

Lab – 61Lab 6: Data Warehouse Design and SQL Query Development for Business Insights In this lab you’ll use MySQL to create queries for the database provided in sql script.

Database Specification:

In Lab – 5, you created a company that manages a big chain of doughnut-selling shops.The client needed a database for the data accumulated through the last few years.The client has now provided additional data for your reference. 代写 QHE5701 – Database Systems For your ease, this data isin the form of a database. SQL script for the database is provided, use Lab6.sql However, the data provided is much more than you will need so you will have to considerwhat to include. The company wants a data warehouse which allows them to answer thequestions (or at least as many as possible for these) for which you need to write SQLqueries.

Task Description:

Task – BASIC:

As a requirement from the client, you need to provided solution for these basic questionsas part of the data analysis. You are required to create queries for all BASIC questions.

1. i) List the branches in Asia.
2. ii) Create a list of full-time Employeeiii) What is the average number of sales in each month?

1. iv) What is the total revenue of the company (sum of prices associated with eachale)?

1. v) What is the percentage of registered sales, which originate from police oTicers?
2. vi) What is the distribution of gender among registered Customers?

Task – MEDIUM: Client wants you to create solution for the following questions to help the client in

analysis oftheir sales and revenue. From the followingMEDIUMquestions, you can select

any 3 of the following questions to create SQL queries.QHE5701 – Database Systems 202Lab – 62

1. i) Which is the branch that generates the most revenue (sum of price based onsales)?

1. ii) Which ingredients are generating the most salesiii) What size of the branch performs the best (in terms of revenue generated)?

1. iv) Find the worst 5 part-time employees according to sales.
2. v) Which continent generates the least sales?

Task – ADVANCED: Create SQL queries that to answer the following business insight queries to help thclient in their business expansion. From the following ADVANCED questions, you canselect any 1 of the following questions to create SQL query.

1. i) Find the country, that if it would be sanctioned, would mean the biggest disruptioto the supply chain (consider both most products and sales aTected separately)ii) What is the most profitable ingredient? Remember to consider the price of thatingredient.ii) Find the best menu. Consider: Volume of sales of specific menu * (the price of a

given pastry - the cost of production of it)NOTE: Use the provided SQL script (Lab6.sql) to generate an example Data Warehousefor this exercise.

Lab Submission Guidelines:

• Include a title page with your full name, module name, QMUL ID Number, BUPT IDNumber, Class Number, and lab number.

• Use a clear and organized layout.
• Clearly label each task (e.g., Lab 5 – Task (i), Lab 5 – Task (ii), Lab 6 – Task Basic (i),Lab 6 – Medium (i), etc.)QHE5701 – Database Systems 2024Lab – 

• Provide SQL code, outputs (screenshots), and any explanation (includingassumptions) where necessary especially in case of designing ER Model ormapping ER to logical model (Lab – 5).

• Create a folder and place all your database files (check where you saved yourdatabase in your laptop) along with the solution document (pdf) within the folder.

• Compress this folder and rename it with your QMUL ID number.
• Upload the compressed file on the QMPlus module page in Assessment Section.You are allowed to resubmit before the deadline. In case you face any issues, email me(a.masood@qmul.ac.uk). NOTE: Submit both labs (Lab 5 and Lab 6) in a single report (Week - 3).