[SOLVED] ITS62904 DATABASE SYSTEMS ASSESSMENT 3

TAYLOR’S UWE DUAL AWARDS PROGRAMMES

SEPTEMBER 2024 SEMESTER

(ITS62904)

DATABASE SYSTEMS

ASSESSMENT 3 (GROUP ASSIGNMENT)

30%

DUE DATE: 9/12/2024, 8:00am

Learning Outcome Assessed:

Construct solutions, interact positively in a group of peers and foster stable and harmonious relationships in solving computational problems related to database modelling and SQL based reporting of a selected scenario.

TASK

NOTE: Before you begin modelling read the:

1. SRS

2. Read BPC version V1.1.1

3. SQL Report

As you many need to include additional attributes in some of the table to ensure you can generate the SQL reports accurately.

A. Database Design (20%)

1. Draw a complete Conceptual Enhanced Entity-Relationship Diagram (EERD)

a. List all attributes, ensure you identify the identifiers.

b. Map the entities with appropriate relationships.

2. Physical Model → Provide the necessary information for the Model (Relational Model – ensure data integrity).

a. Include explanation on Open suggestion or changes made to the model.

B. Deployment: create database, tables and populate the data (20%)

3. 20+ rows of valid records if it’s applicable). This may not be applicable for all table but ensure that the sample data reflects the client’s information and the types of memberships and the types of workshops or seminar they attend. Ensure data across tables is consistent and adheres to the defined rules. Clients and class tables should have at least 30 records.

C. SQL Report (20%)

4. List the workshop with the highest total revenue for Bella Italia Culinary Studio.

5. Get a list of clients who have registered for seminars, sort the report by client name and workshop date.

6. Produce a report of client information who have attended workshops or seminar for last 3 months (June, July & August 2024), you should include details like membership type, total loyalty points earned, and the total number of workshops or seminars attended.

7. Generate a report showing the total number of clients registered for each cooking workshop, categorized by the “Difficulty Level” and brand (“Gourmet Gurus,” “Master Chef Club,” or “All”).

8. List all of chefs who have conducted workshops in the past year, along with their culinary specialties, number of workshops conducted, and total attendance at these workshops.

9. Create a comprehensive report detailing all client activities, including workshops attended, equipment rented, and points earned, segmented by membership type.

D. Analysing database structure and designing useful reports (20%)

10. Develop five (5) different types of reports using SQL queries to demonstrate the ability to analyse data stored across various tables. These reports should provide in-depth insights relevant to the culinary studio’s operations. Marks for this section will be allocated based on the complexity of the queries and the students’ ability to effectively analyse the recorded data.

Note: ensure to write out the purpose of each query.

E. Unforeseen challenges (Research) (20%)

11. Considering the unforeseen challenges posed by climate change, what significant changes should be made to the design and development of BICS database system? Discuss the specific features, such as transaction management and concurrency control, and the information needs required to effectively address the problems presented by climate change.

Marks Tabulation process:

Students need to be aware that database design outcome effects the development process of the database (SQL outcome). If the design is not being properly considered, then the implementation and roll-out carriers the effect of the database design.

In the case of poor database design, a simple reduction of marks with design, will be carried to development as well. Incorrect and inaccurate design leads to incorrect and inaccurate development as well, even though the implementation is completed.