CHC5049 Databases Coursework 2 Semester: 1, 2024-2025
CHC5049 Database Coursework 2.
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: December 23, 2024 (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 (transactions, products, and customers)
along with a file description.
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 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 did the company sell in October?
3. How many customers have bought products from the company in October?
4. Which customer spent the most money with the company in October?
5. How many countries did the company ship products to in October?
6. Which state in America has the highest number of registered customers?
7. On which day were the most products sold?
8. Which development group had the most sales in October?
9. Which industry vertical had the lowest number of sales in October?
10. Which solution area has the lowest sales in the retail industry vertical?

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