Database Modelling / SQL(Oracle)
Construct an Entity-Relationship diagram to model the specification described below.
The following stages should be undertaken:
A. Identify the relevant Entities and label them appropriately
B. Link these with the relevant Relationships, these should be
Labelled appropriately
Define the type of relationship (1:1, 1:M or M:M)
Resolve any many-to-many relationships
Identify attributes for each entity and identify Primary and Foreign Keysm
Your report needs to have ER-Diagram as shown the example below.
Specification
Blastonbury Pop Festival
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.
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 much-travelled Vegas crew who are recognised as one of the biggest rock acts in the world”. Members only belong to one band. Members will wear a security badge containing a photo, which can be scanned. 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 well-known bands may perform on different stages on different nights). The festival runs over 4 days and there are 10 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 4-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 4 days.
As an example you could look at
Please conform to the following layout in constructing your answer. This E-R diagram reflects the tables in the department store test set used in the SQL tutorial sheets.
Figure: ER diagram for department Store test set used in SQL tutorial sheets
Q 1.2 Design Tables
Login to UH oracle through Oracle SQL Developer and create appropriate tables with all constraints which reflects the ER diagram you have designed in Q1.
You report needs to have all SQL script to create tables, add constraints on them.
Q 1.3 Insert Data
Let us assume that there are two Agents- Agent1 and Agent2. The first Agent manages 3 bands and second agent manages 2 bands. There are in total 20 musicians distributed between the 5 bands, at least 5 different job types.
Now write an appropriate SQL statement (INSERT) to populate the tables with records under the above specification. You are free to consider any number of other objects do not provide in above specification.
Your report should have all SQL script to populate your table in ORACLE DB.
Q 1.4 SQL VIEWS
A. Create a view named ‘AgentJobs’ which display the number of different jobs carried out by each Agents.
B. Ensure that the view is READ-ONLY
C. Grant user -dp15aad access to the view.
Your report should have script of the view your have created, screen shot of Select * from AgentJobs and script to grant access.
Q 1.5 SQL Tigger
Create a new table called tblCountDelete – which have only one attribute countDelete. The tblCountDelete keeps record to number of the time data is deleted from Agent table. Now write appropriate tigger which increase the countDelete in the tblCountDelete each time an entity is deleted from Agent table.
You report should have SQL for you tigger and evidence that your tigger is working well i.e. screen shot of the Select * from tblCountDelete, before and after a record is deleted from Agent table. You can add a dummy data in your Agent table.
Part -2 MYSQL – PHP
Q 2.1 Moving Dataset to MySQL
Please install WAMP in your computer and run all services under WAMP. Export the dataset from ORACLE to MySQL server. Make sure all tables, constraints, records, views and tiggers are moved to MySQL.
Your report should have screen shot of WAMP server running in your computer, screen of the all tables and records, screen shot of script of view and script of tigger in MySQL.
Q 2.2 FETCHING DATA IN PHP
Write an appropriate PHP script to display results from AgentJobs view in your localserver.
Your report should have PHP script that you used to connect MySQL database and PHP script to display all result from AgentJobs view in webpage. Your report should have screen shot of your webpage displaying the data from AgentJobs view.
Your report should have
PHP script that you used to connect MySQL dataset
PHP script that you used to display the results.
Screen shot of the webpage displaying the result.
Part -3 MYSQL – JAVA
Q 3.1 Using JDBC
Using JDBC engine in JAVA, display all results from Agent table in the screen.
Note- you can populate data in command prompt. [No need to use java swing].
Your report should have
Java code to connect to MySQL server using JDBC
Java code to display results
Screen shot of the populated results
If you need any database modeling help using different programming languages like Java, Python, PHP or others.
Here you get all database related help with an affordable price. For more details you can contact us at:
realcode4you@gmail.com