Blastonbury Pop Festival
Database Concepts 5COM1077
Physical Design and Queries
Blastonbury Pop Festival
Blastonbury pop festival is a three days festival of modern performance of arts especially music.
Blastonbury pop festival has a security system. It provides members of a band and their crew (sound, lighting, etc) with security clearance to stage areas (venues) where the band plays.
A band will have one or many musicians. Associated with the band will be members who have different job types. For security reasons the band is considered to be made up of all the technical staff and musicians performing in the band. https://charteredessay.com/5com1077-physical-design-and-queries-blastonbury-pop-festival/
Each band name is unique, and a short description of the band will be available for inclusion on various literature, posters and programmes. For example, The Killers could be described as “The muchtravelled Vegas crew who are recognised as one of the biggest rock acts in the world”. Members only belong to one band. Each band/team will have one Agent who manages the publicity for the band. A band can only have one Agent, but an Agent can manage many bands.
A band can perform at more than one stage during the festival (for example some of the less wellknown bands may perform on different stages on different nights). The festival runs over 3 days and there are 7 venues which are called Stage1, Stage2, etc. Each stage also has a name as well as a number, for example the “John Peel Stage”. Each stage also has a capacity (the maximum number of festivalgoers allowed into the stage area for safety reasons). A stage will have many bands performing over the 3-day period. Each performance is given a performance id. As well as security clearance, we also need to be able to produce a line-up showing which band is performing at which venue and at what time over the 3 days.
After a design process the following tables have been put forward:
NOTE: There have been modifications to the original specification of relevant attributes
Agent (Agent_no, Given_name, Family_name)
Band (Name, Description, Agent_No*)
Stage (Stage_no, Stage_Name, Capacity)
Member (Member_ID, Given_name, Family_name, DOB, job_type, Band_Name*)
Performance (PerformanceID, Stage_No*, Band_Name*, Date_Time)
Section One 25% http://exquisiteessaywritings.com/blog/
Marks above refer to the tables created with correct keys and foreign keys and suitable datatypes and constraints
Create the above tables in the Oracle database (University Instance MORK122).
Ensure that you enforce Entity Integrity by declaring the Primary Keys for every table and ensure that you enforce Referential Integrity by creating the appropriate foreign keys. Please note these tables are for testing and only include the main attributes necessary to generate the information required.
Section Two 10%
Marks awarded for tables populated with relevant and complete data. Enough data is considered as much as needed to test the queries below and provide a sensible dataset.
You must include yourself in the test data as a member. Your member_ID is the same as your student number.
You must also include your Oracle Account userid (aa11aaa) on the work perhaps at the beginning (otherwise it is difficult access your tables). Failure to do so will result in reduced marks.
Section 3 50%
Marks awarded for answering queries and providing valid test output.
5 marks awarded per query. For all errors, marks are deducted from the initial 5 marks.
Legible screen shots are required otherwise marks will be deducted.
The queries are to be devised and run using standard SQL.
When you submit the work, provide a single file (any standard format MS Word or .pdf etc.)
It will contain:
SQL Developer screenshots of each queries, cut from the SQL Developer application SQL Developer screenshots of output produced from running each query in SQL Developer.
Set of Table Descriptions using the DESCRIBE table_name command.
DO NOT type queries and output into a Microsoft Word file longhand, these will be graded at 0
The work MUST have been completed on the Oracle RDBMS installation at the University (MORK122) using your user id, if it is not, it will not be marked.
Queries
1. Report the Names of the members of the band 'Castille'.
2. List the stage number and name of those stages that have a capacity of 8000 or more.
3. List all band names and number of members in each band, for which the total members of the band exceeds 4.
4. List each stage by name, and the total number of members requiring access to each stage over the festival.
5. List all the members of bands appearing on the stage called 'Spartan'. Join the family name and given name into a single column in the output.
6. List the stage no, stage name and capacity and start date and time of all performances by the band ‘Blue Pears.’
7. Using a nested select list other member details of members who work in the same band as member number ‘100004’.
8. List the Name and Year of Birth of all members in alphabetic order by family name and given name, that have Claire Lascayne as their agent
9. Report the name and age of the band members in Rorokoro
10. Create a playlist ordered by stage name then date, then time for all performances. List them by stage name, band name, DAY (such as Friday, Saturday etc.) and Time of performance.
With all queries, 3 marks are for the query (SQL syntax) and 2 marks for the submitted results based on your data. Please remember, we check your tables and table content on-line. Do NOT hand-in the contents of your tables.
Section 4 5%
Grant access on your tables to the users: rj14aab 3 marks
Table descriptions using: DESCRIBE table_name. 2 marks
Section 5 10%
Write a 500 words short report on how database has been used in your workplace to handle
operational data. 10 marks
You could consider explaining what database application you are using and its functionalities to carry out Create, Read/Retrieve, Update and Delete operation in your workplace.