Dec 21, 20224 min read

Oracle Database Design and Implementation Help | Realcode4you

Entity-Relationship diagram

ERD Details

There are 6 entities in this ER Diagram: -

Team
Team_Agent
Staff
Team_Performance
Vanue_Stage
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)

Cardinality

Team 1:M Staff
Team 1:M Team_Performance
Team_Agent 1:M Team
Vanue_Stage 1:M Team_Performance
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%');

Oracle Output Screenshots

You can create other tables like that using Oracle SQL Live

Creating SQL VIEWS

Creating view with read only option

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

RealCode4You

Oracle Database Design and Implementation Help | Realcode4you

Entity-Relationship diagram

Cardinality

Oracle Output Screenshots

Creating view with read only option

Recent Posts

Comentários