CSCU9B3 Relational Database Assignment

CSCU9B3 Relational Database Assignment 2024

Computing Science and Maths, University of Stirling

70% of module grade

Stirling Band Earnings and Tracking (Stirling BEaT), manages several local music bands that perform weekly gigs at various venues in the city. Each event provides a chance for musicians and their bands to earn revenue through royalties, ticket sales, and merchandise. Stirling BEaT takes care of business while the artists focus on attracting audiences and delivering an outstanding artistic experience.

Each gig`s revenue is calculated based on two primary income streams: audience ticket sales, and merchandise sales. A band`s revenue depends on their portion of ticket sales (determined by audience size) and merchandise sales. Each band receives a share of the ticket sales, and 80% of that share is further distributed among band members based on their individual royalty rates, reflecting their contribution to the performance.

Event organisers have been tracking all this data in a single spreadsheet file, which is poorly structured and highly redundant. Every time a gig occurs, new records are added with detailed information about each band, the musicians, and the venue. The lack of normalisation leads to repeated data entries, making it difficult to get a clear overview of earnings.

The data recorded for each musician includes:

• Unique ID
• Name (First name followed by Last name)
• Date of birth.
• Instrument(s) Played
• Experience Level (Professional or Amateur)
• Royalty Rate (per gig)

Each band’s details include:

• Band name
• Genre (e.g., Rock, Jazz, Pop)
• Formation Year
• Manager Contact Information (Phone number and email)
• Band’s Merchandise Revenue Share (%)

For each event, the following details are captured:

• Date of the event
• Venue name
• Theme of the event (e.g., Rock Night, Jazz Jamboree)
• Audience Size (used to calculate ticket revenue)
• Merchandise sales for the event
• Ticket revenue

Earning Calculations

• Ticket Revenue: Ticket revenue is listed as the total revenue per event. The bands that play receive 80% of the ticket revenue to split among them. The headlining act gets 60%, while the remaining 40% is split evenly between the other bands. Each band then further divides their portion according to the royalty rates of each band member.
• Merchandise Sales: Merchandise sales are similarly split, except the bands receive 100% of the merchandise revenue to split among themselves. The headlining act receives 60%, while the remaining 40% is split evenly between the other bands. Each band`s merchandise earnings are split evenly among its members.
• Royalties: Individual musician’s portion of the royalty earnings is based on their contributions to writing the set performed at each event and their recorded royalty rate per gig. These royalties are only used to calculate each member`s portion of the band`s portion of the ticket revenue.

Example of Revenue Calculations: Suppose there is a concert with a total ticket revenue of £100,000 and merchandise sales of £20,000. The headlining band and two other supporting bands performed at the event. The headlining band, `Rock Kings,` has four members: Alice (vocals, royalty rate 30%), Bob (guitar, royalty rate 25%), Charlie (bass, royalty rate 20%), and Dana (drums, royalty rate 25%). The first supporting band, `The Echoes,` has three members: Eva (vocals/guitar, royalty rate 40%), Frank (bass, royalty rate 30%), and Grace (drums, royalty rate 30%). The second supporting band, `Rhythm Riders,` has two members: Henry (vocals/guitar, royalty rate 60%) and Isla (drums, royalty rate 40%).

Ticket Revenue:

• Total revenue to be split among the bands is 80% of £100,000, which equals £80,000.
• The headlining band receives 60% of £80,000, which is £48,000.
• The remaining 40%, which is £32,000, is split evenly between the two supporting bands, giving each £16,000.
• Each band`s portion is then split among its members according to their royalty rates. For example, the headlining band `Rock Kings` receives £48,000 in ticket revenue. Based on the royalty rates, Alice (30%) receives £14,400, Bob (25%) receives £12,000, Charlie (20%) receives £9,600, and Dana (25%) receives £12,000. Similarly, for `The Echoes,` which received £16,000, Eva (40%) receives £6,400, Frank (30%) receives £4,800, and Grace (30%) receives £4,800. For `Rhythm Riders,` which received £16,000, Henry (60%) receives £9,600, and Isla (40%) receives £6,400.

Merchandise Sales:

• Total merchandise revenue to be split among the bands is £20,000.
• The headlining band receives 60% of £20,000, which is £12,000.
• The remaining 40%, which is £8,000, is split evenly between the two supporting bands, giving each £4,000.
• Each band`s merchandise earnings are split evenly among its members. For example, for the headlining band `Rock Kings,` which received £12,000, each member (Alice, Bob, Charlie, and Dana) receives £3,000. For `The Echoes,` which received £4,000, each member (Eva, Frank, and Grace) receives £1,333. For `Rhythm Riders,` which received £4,000, each member (Henry and Isla) receives £2,000.

Each member`s total earnings:

Rock Kings:

• Alice: Ticket Revenue (£14,400) + Merchandise Sales (£3,000) = £17,400
• Bob: Ticket Revenue (£12,000) + Merchandise Sales (£3,000) = £15,000
• Charlie: Ticket Revenue (£9,600) + Merchandise Sales (£3,000) = £12,600
• Dana: Ticket Revenue (£12,000) + Merchandise Sales (£3,000) = £15,000

The Echoes:

• Eva: Ticket Revenue (£6,400) + Merchandise Sales (£1,333) = £7,733
• Frank: Ticket Revenue (£4,800) + Merchandise Sales (£1,333) = £6,133
• Grace: Ticket Revenue (£4,800) + Merchandise Sales (£1,333) = £6,133

Rhythm Riders:

• Henry: Ticket Revenue (£9,600) + Merchandise Sales (£2,000) = £11,600
• Isla: Ticket Revenue (£6,400) + Merchandise Sales (£2,000) = £8,400

The Excel spreadsheet named “sample_data.xlsx” contains a formatted table with a sample of the data, please open that file to explore columns of the data.

As seen there, if a musician plays multiple instruments, belongs to multiple bands, or if multiple musicians belong to the same band, the data is redundantly recorded for each entry. Additionally, calculating the final earnings for each musician and band is tedious and error-prone in its current format.

It is your job to turn this data into a relational database.

The data is stored in the rawdata.csv file (and a version without header information, for loading into the database, in noheaddata.csv), which you can download from the module’s Canvas assignment page.

Your assignment is to complete the following steps and present your results in a written report. This assignment is worth 70% of your course grade and will be marked out of 100 points. Each task`s weight is indicated in brackets, with 5 points reserved for overall report quality and clarity.

1) Database Design [15]: Design a set of tables for a relational database to store the music event data. These should represent information about musicians, bands, events, ticket sales, merchandise, royalties, and any other relevant entities.

2) Entity-Relationship (ER) Diagram [10]:

In your report, provide an ER diagram that demonstrates the relationships between the tables you designed.

Include the following details:

• Put the table name at the top of each table.
• List the fields within each table.
• Highlight the primary key fields with the keyword “PK”.
• Highlight the foreign key fields with the keyword “FK”.
• Mark the cardinality of each relationship at both ends of the connecting line.
• Indicate optionality with a dashed line or circle symbol.

3) Justification for Database Design (word limit 200 words) [25]:

• Write a justification for your design in your report, addressing considerations that were discussed in the lectures.

4) SQL Table Creation in MySQL [10]:

• Using the phpMyAdmin interface, write and execute SQL statements to create each of the tables, including the definitions for all primary and foreign keys. Choose appropriate data types for each field, and provide the SQL code used in your report.

5) Import Data from CSV File [5]:

• Write and execute SQL to create a table that will hold the data from the nohead.csv file. Upload this data into your database using the phpMyAdmin “import” facility (no need to mention this step in the report).

6) Data Transfer SQL [10]:

• Use SQL statements to transfer the data from your imported table into the normalised tables you created in step 1. Include the SQL used in your report.

7) SQL Queries and Results [20]:

Write SQL statements to answer the following queries. Execute them, and include both the SQL and the resulting data in your report. Use your normalised tables from step 1 for all queries.

1. List each musician name and birthdate who was born between 1st January 1997 and 31st December 2012.
2. List the total number of performances for each band. Include the band name.
3. List the total revenue generated from ticket sales and the total merchandise sales for each band. Include the band name, genre, and formation year.
4. List all the venue names where all bands, except "The Waves" or "Groove Squad," performed during March 2023.
5. Write a query to produce the end-of-year summary for each venue, showing the Venue Name, number of events hosted, total ticket revenue generated, total merchandise sales, and the average audience size per event for each venue.
6. List musicians who are members of bands that were formed before their birthdate.
7. Choose a specific venue and list all the bands that performed there during any Jazz-themed event. If a band performed more than once, include only the first performance date.
8. Find the event (or events if there are many) that had the largest audience size. List the venues, the dates and what bands played.
9. Find the musician(s) with the highest royalty earnings during the month of March 2024. Include their name, all instruments they play, and total royalties earned.
10. Identify any solo artists who are also members of a band. Additionally, determine if these solo artists played multiple instruments during their performances, and list the dates when they performed with a band.

Submitting your work and assessment procedures

The assignment will be submitted as an electronic (docx) type-written report uploaded (via Turnitin) to Canvas by the above-mentioned deadline. DO NOT put your name in the report, only your student number.

In the report, include all the components listed in the assignment steps. The report should be professionally presented and easy to read. 5% of the marks will be given for the quality of the report.

Use of generative AI

2

AI-Assisted idea generation and structuring

AI can be used in the assessment for brainstorming, creating structures, and generating ideas to improving work. No AI content is allowed in the final submission

Late submission

If you cannot meet the assignment hand-in deadline and have good cause, please use the extension request link on the left-hand navigation to explain your situation and ask for an extension. Coursework will be accepted up to seven days after the hand-in deadline (or expiry of any agreed extension), but the mark will be lowered by three marks per day or part thereof. After seven days, the work will be deemed a non-submission.

Plagiarism

Work that is submitted for assessment must be your own work. Plagiarism means presenting the work of others as though it were your own. The University takes a very serious view of plagiarism, and the penalties can be severe (ranging from a reduced mark in the assessment, through a failure mark for the module, to expulsion from the University for more serious or repeated offences). We check submissions carefully for evidence of plagiarism and pursue those cases we find. Further details can be found here:

https://www.stir.ac.uk/media/stirling/services/academic-registry/documents/Policy-and-Procedure-Academic-Integrity_v4_FINAL.docx

Useful Information for Students Writing CSCU9B3 Relational Database Assignment

When writing your assignment for the CSCU9B3 Relational Database course, it is essential to have a clear understanding of the fundamental principles and practices associated with relational databases. A relational database is a collection of data organized into structured tables, where each table consists of rows (records) and columns (attributes). Understanding how data is structured and related is crucial. In a relational database, the primary key uniquely identifies each record in a table, while the foreign key creates relationships between tables by referring to primary keys in other tables. This ensures data is not isolated but interconnected across different entities within the system.

A key aspect of relational database design is the process of normalization. Normalization is a technique used to organize data to minimize redundancy and improve data integrity. The goal is to ensure that each piece of data is stored in only one place, which reduces the chance of anomalies such as update, insertion, or deletion anomalies. There are several normal forms, including the First, Second, and Third Normal Forms (1NF, 2NF, and 3NF), which progressively eliminate redundant data and ensure that the relationships between different entities are logical and efficient. Understanding normalization is essential in creating an optimal database design that minimizes unnecessary data duplication while ensuring data is structured appropriately.

Additionally, a solid understanding of SQL (Structured Query Language) is essential for manipulating and querying the data within a relational database. SQL allows you to create tables, insert data, and retrieve specific information through queries. For instance, you would use SQL commands like CREATE TABLE to define your tables, INSERT INTO to add data, and SELECT to retrieve specific records from a table. Furthermore, SQL also allows for more advanced querying techniques such as using JOIN clauses to combine data from multiple tables, subqueries to nest queries within each other, and aggregate functions like COUNT(), SUM(), and AVG() to perform calculations on the data. These advanced queries help in extracting meaningful insights from large datasets.

Data integrity and constraints are crucial to the functioning of a relational database. Constraints ensure that the data entered into the database adheres to certain rules and relationships. For example, the NOT NULL constraint ensures that certain fields cannot be left empty, while the UNIQUE constraint guarantees that values in a column are distinct. Another important constraint is the foreign key, which ensures referential integrity by enforcing valid relationships between tables. By implementing foreign keys, you ensure that any data in a table that references another table remains consistent and accurate, preventing orphaned records or invalid relationships.