Production Database for TV Station Program Schedule
INPUT
REM Dropping All Tables
REM DROP Statements
drop table Broadcasts;
drop table Plays;
drop table Airs;
drop table Ad_Timeslot;
drop table Viewership;
drop table Commercial;
drop table Show_Timeslot;
drop table TV_Show;
REM Creating All Tables
REM CREATE statements
CREATE TABLE TV_Show(
EpisodeNo Char(4) Constraint EpisodeNo_pk Primary Key,
EpisodeTitle VarChar2(50),
ShowName VarChar2(50),
Season Number(2)
);
CREATE TABLE Show_Timeslot(
Day Date Constraint Day_pk Primary Key,
NoOfMinutes Number
);
CREATE TABLE Commercial(
AdNo Char(5) Constraint AdNo_pk Primary Key,
ProductName VarChar2(50)
);
CREATE TABLE Viewership(
Age VarChar2(7),
Gender VarChar2(6),
Region VarChar2(5),
Ethnicity VarChar2(50),
EpisodeNo Char(4),
AdNo Char(5),
Ratings VarChar2(2)
Constraint Ratings_pk Primary Key,
Constraint EpisodeNo2_fk Foreign Key(EpisodeNo)
References TV_Show(EpisodeNo),
Constraint AdNo2_fk Foreign Key(AdNo)
References Commercial(AdNo)
);
CREATE TABLE Airs(
EpisodeNo Char(4),
Day Date,
Constraint Airs2_pk Primary Key(EpisodeNo, Day),
Constraint EpisodeNo1_fk Foreign Key(EpisodeNo)
References TV_Show(EpisodeNo),
Constraint Day1_fk Foreign Key(Day)
References Show_Timeslot(Day)
);
CREATE TABLE Plays(
AdNo Char(5),
Day Date,
Constraint Plays2_pk Primary Key(AdNo, Day),
Constraint AdNo1_fk Foreign Key(AdNo)
References Commercial(AdNo),
Constraint Day3_fk Foreign Key(Day)
References Show_Timeslot(Day)
);
CREATE TABLE Broadcasts(
EpisodeNo Char(4),
AdNo Char(5),
Constraint Broadcasts2_pk Primary Key(EpisodeNo, AdNo),
Constraint EpisodeNo3_fk Foreign Key(EpisodeNo)
References TV_Show(EpisodeNo),
Constraint AdNo3_fk Foreign Key(AdNo)
References Commercial(AdNo)
);
REM Loading All Tables
REM INSERT Statements
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0002', 'Diversity Day', 'The Office', 01);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0008', 'Robbed a Stoner Blind', 'My Name is Earl', 02);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0014', 'PTV', 'Family Guy', 04);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0012', 'Fez Gets the Girl', 'That Seventies Show', 03);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0016', 'The Finale', 'Everybody Loves Raymond', 09);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0015', 'Sword of Destiny', 'Arrested Development', 02);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0011', 'Mentalo Case', 'The King of Queens', 05);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0010', 'Fly', 'Breaking Bad', 03);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0001', 'Pilot', 'House', 01);
INSERT INTO TV_Show(EpisodeNo, EpisodeTitle, ShowName, Season)
VALUES('0013', 'The Pick', 'Seinfeld', 04);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('25-Sep-09', 30);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('26-Sep-09', 31);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('27-Sep-09', 32);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('28-Sep-09', 33);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('29-Sep-09', 34);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('30-Sep-09', 35);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('20-Sep-09', 36);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('21-Sep-09', 60);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('22-Sep-09', 61);
INSERT INTO Show_Timeslot(Day, NoOfMinutes)
VALUES('23-Sep-09', 37);
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19850', 'Dunder Mifflin and Scott Paper Company');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19870', 'The Crab Shack');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19840', 'The Drunken Clam');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19890', 'The Smokin Basement');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19860', 'Men of a Certain Age');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19900', 'Gobias Coffee Sold at Bluth Company');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19990', 'Mentalo');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19630', 'The Chemistry Behind Cooking');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19680', 'Vicodin');
INSERT INTO Commercial(AdNo, ProductName)
VALUES('19830', 'Superman Cereal by Kramerica');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('A+', 'Adult', 'Male', 'South', 'Asian', '0002', '19850');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('A', 'Adult', 'Male', 'South', 'Hispanic', '0008', '19870');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('C', 'Adult', 'Male', 'North', 'Caucasian', '0014', '19840');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('B+', 'Adult', 'Female', 'North', 'African-American', '0012', '19890');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('B', 'Child', 'Male', 'East', 'Asian', '0016', '19860');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('C+', 'Teen', 'Female', 'West', 'Hispanic', '0015', '19900');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('D+', 'Elderly', 'Female', 'North', 'Caucasian', '0011', '19990');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('D', 'Elderly', 'Female', 'South', 'African-American', '0010', '19630');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('S', 'Elderly', 'Male', 'East', 'Asian', '0001', '19680');
INSERT INTO Viewership(Ratings, Age, Gender, Region, Ethnicity, EpisodeNo, AdNo)
VALUES('S+', 'Elderly', 'Male', 'West', 'Hispanic', '0013', '19830');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0002', '25-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0008', '26-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0014', '27-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0012', '28-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0016', '29-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0015', '30-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0011', '20-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0010', '21-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0001', '22-Sep-09');
INSERT INTO Airs(EpisodeNo, Day)
VALUES('0013', '23-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19850', '25-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19870', '26-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19840', '27-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19890', '28-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19860', '29-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19900', '30-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19990', '20-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19630', '21-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19680', '22-Sep-09');
INSERT INTO Plays(AdNo, Day)
VALUES ('19830', '23-Sep-09');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0002', '19850');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0008', '19870');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0014', '19840');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0012', '19890');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0016', '19860');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0015', '19900');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0011', '19990');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0010', '19630');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0001', '19680');
INSERT INTO Broadcasts(EpisodeNo, AdNo)
VALUES ('0013', '19830');
REM Displaying All Tables
SELECT*FROM TV_Show;
SELECT*FROM Show_Timeslot;
SELECT*FROM Commercial;
SELECT*FROM Viewership;
SELECT*FROM Airs;
SELECT*FROM Plays;
SELECT*FROM Broadcasts;
REM Join Query
SELECT t.EpisodeNo, v. Ratings, v.Age, v.Gender, v.Region, v.Ethnicity
FROM TV_Show t, Viewership v
WHERE t.EpisodeNo = v.EpisodeNo;
REM Evan Carr
OUTPUT
SQL> start"E:\TV_Show_Database.txt"
SP2-0734: unknown command beginning "Production..." - rest of line ignored.
Table dropped.
Table dropped.
Table dropped.
drop table Ad_Timeslot
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0002 Diversity Day
The Office 1
0008 Robbed a Stoner Blind
My Name is Earl 2
0014 PTV
Family Guy 4
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0012 Fez Gets the Girl
That Seventies Show 3
0016 The Finale
Everybody Loves Raymond 9
0015 Sword of Destiny
Arrested Development 2
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0011 Mentalo Case
The King of Queens 5
0010 Fly
Breaking Bad 3
0001 Pilot
House 1
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0013 The Pick
Seinfeld 4
10 rows selected.
DAY NOOFMINUTES
--------- -----------
25-SEP-09 30
26-SEP-09 31
27-SEP-09 32
28-SEP-09 33
29-SEP-09 34
30-SEP-09 35
20-SEP-09 36
21-SEP-09 60
22-SEP-09 61
23-SEP-09 37
10 rows selected.
ADNO PRODUCTNAME
----- --------------------------------------------------
19850 Dunder Mifflin and Scott Paper Company
19870 The Crab Shack
19840 The Drunken Clam
19890 The Smokin Basement
19860 Men of a Certain Age
19900 Gobias Coffee Sold at Bluth Company
19990 Mentalo
19630 The Chemistry Behind Cooking
19680 Vicodin
19830 Superman Cereal by Kramerica
10 rows selected.
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Adult Male South Asian 0002
19850 A+
Adult Male South Hispanic 0008
19870 A
Adult Male North Caucasian 0014
19840 C
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Adult Female North African-American 0012
19890 B+
Child Male East Asian 0016
19860 B
Teen Female West Hispanic 0015
19900 C+
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Elderly Female North Caucasian 0011
19990 D+
Elderly Female South African-American 0010
19630 D
Elderly Male East Asian 0001
19680 S
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Elderly Male West Hispanic 0013
19830 S+
10 rows selected.
EPIS DAY
---- ---------
0002 25-SEP-09
0008 26-SEP-09
0014 27-SEP-09
0012 28-SEP-09
0016 29-SEP-09
0015 30-SEP-09
0011 20-SEP-09
0010 21-SEP-09
0001 22-SEP-09
0013 23-SEP-09
10 rows selected.
ADNO DAY
----- ---------
19850 25-SEP-09
19870 26-SEP-09
19840 27-SEP-09
19890 28-SEP-09
19860 29-SEP-09
19900 30-SEP-09
19990 20-SEP-09
19630 21-SEP-09
19680 22-SEP-09
19830 23-SEP-09
10 rows selected.
EPIS ADNO
---- -----
0002 19850
0008 19870
0014 19840
0012 19890
0016 19860
0015 19900
0011 19990
0010 19630
0001 19680
0013 19830
10 rows selected.
EPIS RA AGE GENDER REGIO ETHNICITY
---- -- ------- ------ ----- --------------------------------------------------
0002 A+ Adult Male South Asian
0008 A Adult Male South Hispanic
0014 C Adult Male North Caucasian
0012 B+ Adult Female North African-American
0016 B Child Male East Asian
0015 C+ Teen Female West Hispanic
0011 D+ Elderly Female North Caucasian
0010 D Elderly Female South African-American
0001 S Elderly Male East Asian
0013 S+ Elderly Male West Hispanic
10 rows selected.
SQL> spool off
THE TABLES
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0002 Diversity Day
The Office 1
0008 Robbed a Stoner Blind
My Name is Earl 2
0014 PTV
Family Guy 4
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0012 Fez Gets the Girl
That Seventies Show 3
0016 The Finale
Everybody Loves Raymond 9
0015 Sword of Destiny
Arrested Development 2
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0011 Mentalo Case
The King of Queens 5
0010 Fly
Breaking Bad 3
0001 Pilot
House 1
EPIS EPISODETITLE
---- --------------------------------------------------
SHOWNAME SEASON
-------------------------------------------------- ----------
0013 The Pick
Seinfeld 4
10 rows selected.
DAY NOOFMINUTES
--------- -----------
25-SEP-09 30
26-SEP-09 31
27-SEP-09 32
28-SEP-09 33
29-SEP-09 34
30-SEP-09 35
20-SEP-09 36
21-SEP-09 60
22-SEP-09 61
23-SEP-09 37
10 rows selected.
ADNO PRODUCTNAME
----- --------------------------------------------------
19850 Dunder Mifflin and Scott Paper Company
19870 The Crab Shack
19840 The Drunken Clam
19890 The Smokin Basement
19860 Men of a Certain Age
19900 Gobias Coffee Sold at Bluth Company
19990 Mentalo
19630 The Chemistry Behind Cooking
19680 Vicodin
19830 Superman Cereal by Kramerica
10 rows selected.
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Adult Male South Asian 0002
19850 A+
Adult Male South Hispanic 0008
19870 A
Adult Male North Caucasian 0014
19840 C
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Adult Female North African-American 0012
19890 B+
Child Male East Asian 0016
19860 B
Teen Female West Hispanic 0015
19900 C+
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Elderly Female North Caucasian 0011
19990 D+
Elderly Female South African-American 0010
19630 D
Elderly Male East Asian 0001
19680 S
AGE GENDER REGIO ETHNICITY EPIS
------- ------ ----- -------------------------------------------------- ----
ADNO RA
----- --
Elderly Male West Hispanic 0013
19830 S+
10 rows selected.
EPIS DAY
---- ---------
0002 25-SEP-09
0008 26-SEP-09
0014 27-SEP-09
0012 28-SEP-09
0016 29-SEP-09
0015 30-SEP-09
0011 20-SEP-09
0010 21-SEP-09
0001 22-SEP-09
0013 23-SEP-09
10 rows selected.
ADNO DAY
----- ---------
19850 25-SEP-09
19870 26-SEP-09
19840 27-SEP-09
19890 28-SEP-09
19860 29-SEP-09
19900 30-SEP-09
19990 20-SEP-09
19630 21-SEP-09
19680 22-SEP-09
19830 23-SEP-09
10 rows selected.
EPIS ADNO
---- -----
0002 19850
0008 19870
0014 19840
0012 19890
0016 19860
0015 19900
0011 19990
0010 19630
0001 19680
0013 19830
10 rows selected.
THE JOIN QUERY
EPIS RA AGE GENDER REGIO ETHNICITY
---- -- ------- ------ ----- --------------------------------------------------
0002 A+ Adult Male South Asian
0008 A Adult Male South Hispanic
0014 C Adult Male North Caucasian
0012 B+ Adult Female North African-American
0016 B Child Male East Asian
0015 C+ Teen Female West Hispanic
0011 D+ Elderly Female North Caucasian
0010 D Elderly Female South African-American
0001 S Elderly Male East Asian
0013 S+ Elderly Male West Hispanic
10 rows selected.
