top of page
Search

Oracle Database Design and Implementation Help | Realcode4you

realcode4you
Dec 21, 20224 min read

ERD Details

There are 6 entities in this ER Diagram: -

  1. Team

  2. Team_Agent

  3. Staff

  4. Team_Performance

  5. Vanue_Stage

  6. Festival


Team–Team_Id contains unique id of team, Team_Agent_Id contains unique team agent id , Team_Name shows name of particular team and last one is Team_Info which shows the information of team.


Item_Agent– It is having details of agent.


Staff – Staff related details will be showing in this entity.


Team performance – Performance related details will be showing in this entity,


Vanue_Stage –Vanue stage_Id which shows the vanue stagee id which is unique, Festival id which shows the unique festival id , Vanue_name which shows the name of vanue , capacity which shows the whole capacity , address which shows the address and Remark which shows the remark if any.


Festival –Festival id which shows the unique festival id , festival name which shows the name of festival , duration shows the duration , Year shows the Particular year , Start_Date declares the date of starting and end date declares the ending date , Description shows the full description about that.


Relationship Schema

  • Team_Agent ( Team_Agent_ID, Agent_Name, Phone, Email_Address, Agent_Role )

  • Team (Team_ID, Team_Agent_ID*, Team_Name, Team_Info)

  • Festival (Festival_ID, Festival_Name, Duration, Year, Start_date, Ene_date, Description)

  • Staff ( Staff_ID, Team_ID*, Badge_Number, Full_Name, Job_Type, Staff_Role)

  • Vanue_Stage ( Vanue_Stage_ID, Festival_ID*, Vanue_Name, Capacity, Address, Remark)

  • Team_Performance (Performance_ID, Team_ID*, Vanue_stage_ID*, Performance_Name, Start_date, End_date, Timing, Security_check, Performance_Status)


  1. Team 1:M Staff

  2. Team 1:M Team_Performance

  3. Team_Agent 1:M Team

  4. Vanue_Stage 1:M Team_Performance

  5. 5.Festival 1:M Vanue_Stage


Data Dictionary


...

...



Design Tables

SQL to create database in Oracle

Create Table Team_Agent(
Team_Agent_ID number(10),
Agent_Name Varchar2(50),
Phone Varchar2(50),
Email_Address Varchar2(50),
Agent_Role Varchar2(50),
Primary Key (Team_Agent_ID)
);


Create Table Team(
Team_ID number(10),
Team_Agent_ID number(10) Not Null,
Team_Name Varchar2(50),
Team_Info Varchar2(50),
Primary Key (Team_ID),
CONSTRAINT FK_Team_Team_Agent_Team_Agent_ID FOREIGN KEY (Team_Agent_ID) REFERENCES Team_Agent (Team_Agent_ID)
);


Create Table Staff(
Staff_ID number(10),
Team_ID number(10) Not Null,
Badge_Number Varchar2(50),
Full_Name Varchar2(50),
Job_Type Varchar2(50),
Staff_Role Varchar2(50),
Primary Key (Staff_ID),
CONSTRAINT FK_Staff_Team_Team_ID FOREIGN KEY (Team_ID) REFERENCES Team (Team_ID)
);


Create Table Festival(
Festival_ID number(10),
Festival_Name Varchar2(50),
Duration Varchar2(50),
Year Varchar2(50),
Start_Date Date,
End_Date Date,
Description Varchar2(50),
Primary Key(Festival_ID)
);

Create Table Venue_Stage(
Venue_Stage_ID number(10),
Festival_ID number(10) Not Null,
Venue_Name Varchar2(50),
Capacity Varchar2(50),
Address Varchar2(50),
Remark Varchar2(50),
Primary Key(Venue_Stage_ID),
CONSTRAINT FK_Venue_Stage_Festival_Festival_ID FOREIGN KEY (Festival_ID) REFERENCES Festival (Festival_ID)
);

Create Table Team_Performance(
Performance_ID number(10),
Team_ID number(10) Not Null,
Venue_Stage_ID number(10) Not Null,
Performance_Name Varchar2(50),
Start_Date Date,
End_Date Date,
Timing Timestamp,
Security_Check Varchar2(50),
Performance_Status Varchar2(50),
Primary Key (Performance_ID),
CONSTRAINT FK_Team_Performance_Team_Team_ID FOREIGN KEY (Team_ID) REFERENCES Team (Team_ID),
CONSTRAINT FK_Team_Performance_Venue_Stage_Venue_Stage_ID FOREIGN KEY (Venue_Stage_ID) REFERENCES Venue_Stage (Venue_Stage_ID)
);

Insert Data

/* Inserting data into Table Team_Agent*/

Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role)   Values (1, 'Liam', '4153564592','liam@outloook.com','Support');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (2, 'Noah', '9725267513','noah@gmail.com','In-game Leader');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (3, 'Oliver', '3014859487','oliver@gmail.com','Entry Fragger');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (4, 'Elijah', '2211542549','elijah@msn.com','Lurker');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (5, 'James', '8737582154','james@yahoo.com','Recon');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (6, 'Kylian', '3918545898','kylian@hotmail.com','Crowd Control');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (7, 'Jiraiya', '6328478469','jiraiya@msn.com','Crowd Control');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (8, 'Elio', '5907849216','elio@gmail.com','Entry Fragger');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (9, 'Evander', '6258058402','evander@yahoo.com','Support');
Insert INTO Team_Agent(Team_Agent_ID,Agent_Name,Phone,Email_Address,Agent_Role) Values (10, 'Eliam', '0925412548','eliam@rediffmail.com','Support');


/* Inserting data into Table Team */

Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (1,5, 'Aston Villa', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (2,9, 'Brentford', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (3,2, 'Fulham', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (4,8, 'West Ham United', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (5,10, 'Liverpool', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (6,3, 'Everton FC', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (7,1, 'Southampton', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (8,4, 'Tottenham Hotspur', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (9,9, 'Chelsea', '');
Insert INTO Team(Team_ID,Team_Agent_ID,Team_Name,Team_Info) Values (10,7, 'Newcastle United', '');

/* Inserting data into Table Staff */

Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (1,3,'WS13','William Smith','Supervision','Supervisor');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (2,5,'BJ25','Benjamin Jones','Training','Assistant Trainer');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (3,1,'LW31','Lucas Williams','Physical Fitness','Physiotherapist');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (4,8,'HT48','Henry Taylor','Supervision','Supervisor');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (5,9,'TB59','Theodore Brown','Training','Trainer');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (6,2,'CD62','Cillian Davies','Health','Masseur');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (7,6,'BE76','Bridger Evans','Media','Media manager');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (8,4,'OT84','Onyx Thomas','Analysis','Video Analyst');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (9,7,'ZS97','Zyair Smith','Public Meetings','Public Relations');
Insert INTO Staff(Staff_ID,Team_ID,Badge_Number,Full_Name,Job_Type,Staff_Role) Values (10,5,'KT105','Koen Taylor','Health','Nutritionist');


/* Inserting data into Table Festival */

Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (1, 'Maslenitsa', '10 Days','2019',To_Date('2019-07-21', 'YYYY/MM/DD'), To_Date('2019-07-21', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (2, 'Carnava','15 Days','2019', To_Date('2022-01-23', 'YYYY/MM/DD'),To_Date('2019-02-06', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (3, 'San Fermin','20 Days','2019', To_Date('2021-10-12', 'YYYY/MM/DD'),To_Date('2019-11-01', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (4, 'Water Jousting','7 Days','2019', To_Date('2018-07-19', 'YYYY/MM/DD'),To_Date('2018-07-26', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (5, 'Lavender Mob','5 Days','2019', To_Date('2022-11-01', 'YYYY/MM/DD'),To_Date('2022-11-5', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (6, 'Noble Grapes','5 Days','2019', To_Date('2014-01-10', 'YYYY/MM/DD'),To_Date('2014-01-14', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (7, 'Sziget','7 Days','2019', To_Date('2007-05-12', 'YYYY/MM/DD'),To_Date('2007-05-18', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (8, 'Colours Of Ostrava','10 Days','2019', To_Date('2009-12-30', 'YYYY/MM/DD'),To_Date('2010-01-09', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) 
Values (9, 'Up Helly Aa','15 Days','2019', To_Date('2016-10-05', 'YYYY/MM/DD'),To_Date('2016-10-19', 'YYYY/MM/DD'),'');
Insert INTO Festival(Festival_ID,Festival_Name,Duration,Year,Start_Date,End_Date,Description) Values (10, 'Creamfields','10 Days','2019', To_Date('2022-11-27', 'YYYY/MM/DD'),To_Date('2022-12-06', 'YYYY/MM/DD'),'');


/* Inserting data into Table Venue_Stage */



Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (1,2,'Camp Nou','10000','Pudding Mill Lane, London','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (2,3,'Manchester','50000','409-412 Strand, Ukraine','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (3,1,'Tauron','20000','Silk Street, Nethrelands','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (4,7,'Accor','25000','45 Queen Caroline Street, France','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (5,9,'Altice','10000','35-37 Shaftesbury Avenue, Poland','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (6,6,'Lanxess','20000','Nouvelle-Aquitaine, France','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (7,10,'Pala Alpitour','30000','21 New Globe Walk, London','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (8,5,'WiZink Center','15000','Place de la Bastille, Paris, France','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (9,4,'Gliwice','20000','Kensington Gore, London','');
Insert INTO Venue_Stage(Venue_Stage_ID ,Festival_ID,Venue_Name,Capacity,Address,Remark) Values (10,8,'Westfalenhallen','12000','Mitseon 25, Athens, Greece','');


/* Inserting data into Table Team_Performance */

Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (1,4,10,'Pleasant Era',To_Date('2019-07-21', 'YYYY/MM/DD'), To_Date('2019-07-30', 'YYYY/MM/DD'),TO_TIMESTAMP('2019-07-21 17:16:05.321000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','50%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (2,1,7,'Positive Tracks',To_Date('2022-12-15', 'YYYY/MM/DD'), To_Date('2022-12-21', 'YYYY/MM/DD'),TO_TIMESTAMP('2022-12-15 11:30:25.156000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','80%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (3,7,5,'Longer July',To_Date('2018-11-01', 'YYYY/MM/DD'), To_Date('2018-11-14', 'YYYY/MM/DD'),TO_TIMESTAMP('2018-11-01 05:46:12.40200000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','60%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (4,9,8,'Positive Life',To_Date('2022-01-12', 'YYYY/MM/DD'), To_Date('2022-01-18', 'YYYY/MM/DD'),TO_TIMESTAMP('2022-01-12 01:15:30.380000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'No','50%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (5,10,3,'Morning Lift',To_Date('2016-05-17', 'YYYY/MM/DD'), To_Date('2016-05-30', 'YYYY/MM/DD'),TO_TIMESTAMP('2016-05-17 14:00:18.297000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'No','70%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (6,6,9,'Piece For Keeps',To_Date('2021-03-08', 'YYYY/MM/DD'), To_Date('2021-04-01', 'YYYY/MM/DD'),TO_TIMESTAMP('2021-03-08 10:12:23.324000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','60%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (7,2,2,'Thoughtful Happiness',To_Date('2017-12-03', 'YYYY/MM/DD'), To_Date('2017-12-17', 'YYYY/MM/DD'),TO_TIMESTAMP('2017-12-03 12:20:19.315000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'No','80%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (8,5,1,'Now Or Never With Soul',To_Date('2019-04-11', 'YYYY/MM/DD'), To_Date('2019-04-25', 'YYYY/MM/DD'),TO_TIMESTAMP('2019-04-11 16:46:00.98500000', 'YYYY-MM-DD HH24:MI:SS.FF'),'No','50%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (9,8,6,'00s Indie Reflections',To_Date('2021-09-15', 'YYYY/MM/DD'), To_Date('2021-09-29', 'YYYY/MM/DD'),TO_TIMESTAMP('2021-09-15 01:15:06.721000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','60%');
Insert INTO Team_Performance(Performance_ID ,Team_ID,Venue_Stage_ID,Performance_Name,Start_Date,End_Date,Timing,Security_Check,Performance_Status) Values (10,3,4,'… Once More With Feeling!',To_Date('2015-03-30', 'YYYY/MM/DD'), To_Date('2015-04-13', 'YYYY/MM/DD'),TO_TIMESTAMP('2015-03-30 14:00:02.503000000', 'YYYY-MM-DD HH24:MI:SS.FF'),'Yes','80%');



You can create other tables like that using Oracle SQL Live



Creating SQL VIEWS

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost 
SQL SECURITY DEFINER VIEW VIEWAGENTWISEPERFORMANCE_COUNT AS 
SELECT Team_Agent.Agent_Name, COUNT(Team_Performance.Performance_ID) AS Performance_Count
FROM       Team_Agent 

INNER JOIN

Team ON Team_Agent.Team_Agent_ID = Team.Team_Agent_ID 
INNER JOIN
Team_Performance ON Team.Team_ID = Team_Performance.Team_ID
GROUP BY Team_Agent.Agent_Name ORDER BY Performance_Count DESC, Team_Agent.Agent_Name;

Screenshot of select clause of the View created.

Output:




For more details or get help in Database Design and Implementation email us:


realcode4you@gmail.com
 
 
 

Comments

REALCODE4YOU

Realcode4you is the one of the best website where you can get all computer science and mathematics related help, we are offering python project help, java project help, Machine learning project help, and other programming language help i.e., C, C++, Data Structure, PHP, ReactJs, NodeJs, React Native and also providing all databases related help.

​

Hire Us to get Instant help from realcode4you expert with an affordable price.

USEFUL LINKS

Discount

ADDRESS

Noida, Sector 63, India 201301

Follows Us!

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn

OUR CLIENTS BELONGS TO

  • india
  • australia
  • canada
  • hong-kong
  • ireland
  • jordan
  • malaysia
  • new-zealand
  • oman
  • qatar
  • saudi-arabia
  • singapore
  • south-africa
  • uae
  • uk
  • usa

© 2023 IT Services provided by Realcode4you.com

bottom of page