CHC5049 Databases Coursework 2-Resit Semester: 1, 2024-2025
CHC5049 Database Coursework 2-Resit.
Learning Outcomes 
The two courseworks of this module address the following learning outcomes:
- Use SQL and XML to define data applications appropriate to a specified problem.
- Use a conceptual modeling language to specify and analyze data requirements 
and apply database design principles to map system requirements to an efficient 
(e.g., normalized) database.
- Explain and design transaction-based processing in database systems.
- Exploit techniques for storing and querying XML data.
OBJECTIVES 
The objectives of this assignment are:
- To develop skills in the design of data-driven applications.
- To develop skills in applying SQL to implement the relational design.
- To develop skills in using SQL to define data applications.
- To present an opportunity to practice problem-solving and communication skills.
IMPORTANT INFORMATION 
- Due date: (25th, April 2025. 11:59 AM).
- Submit a PDF file via the online portal with all necessary screenshots, figures, 
schemes, code, and test runs. State any assumptions made.
- The file must be named 'coursework2_StudentID.pdf'.
- This coursework is to be carried out individually.
- You have been given three CSV files (staff, stores, and products).
TASKS [30 marks 
total] 
For the coursework submission, you should perform the following:
1) Examine the files and determine an appropriate relationship model between 
them. Create and document a physical data model detailing the field types and 
relationships. [5 marks]
CHC5049 Databases Coursework 2-Resit Semester: 1, 2024-2025
2) Load all three tables into a new database schema. [3 marks]
3) Recommend and design one additional table that would be appropriate for more 
efficient reporting of relevant data. [2 marks]
The three tables show data redundancies. The reporting system needs to 
accommodate at least ten years of data. It is unlikely that the current data model will 
support performance with the anticipated volume.
4) Redesign the data model to become more efficient for querying by detailing a 
logical data model showing elements of first and second normal forms where 
appropriate. [3 marks]
5) Create and document a new physical data model detailing the new structure, and 
create that structure within your database schema. [3 marks]
6) Ensure that the data is clean, and describe the steps taken. If you need to remove 
some of it, explain why that is the case. [2 marks]
7) Identify appropriate columns and create necessary indexes to optimize query 
performance. [2 marks]
8) Answer the following questions using database queries. Include your SQL 
statements and a screenshot of the output. [10 marks]
1. How many different products does the company sell? 
2. How many products are available in each store?
3. How many staff members are active in each city?
4. What is the number of staff members managed by each manager in the stores?
5. Which store has the highest number of staff members?
6. Which store has the highest total value of products in stock?
7. Which Staff member works at the store with the highest total sales?
8. Which product category has the highest average list price?
9. Which product has the highest total quantity across all stores?
10. Find the total number of products sold by each store for the brand "Ritchey".

请加QQ：99515681  邮箱：99515681@qq.com   WX：codinghelp