7vCYap80

1
2CREATE TABLE IF NOT EXISTS tvshow (
3    id INT AUTO_INCREMENT PRIMARY KEY,
4    title TEXT NOT NULL,
5    description TEXT NOT NULL,
6    producer VARCHAR(64) NOT NULL,
7    network VARCHAR(64) NOT NULL
8);
9
10CREATE TABLE IF NOT EXISTS person (
11    id INT AUTO_INCREMENT PRIMARY KEY,
12    first_name VARCHAR(64) NOT NULL,
13    last_name VARCHAR(64) NOT NULL,
14    gender VARCHAR(32),
15    birth_date DATE NOT NULL,
16    website VARCHAR(128) NOT NULL
17);
18
19CREATE TABLE IF NOT EXISTS cast_person_tvshow (
20    id INT AUTO_INCREMENT PRIMARY KEY,
21    role VARCHAR(64) NOT NULL,
22    tvshow_id INT NOT NULL,
23    FOREIGN KEY(tvshow_id) REFERENCES tvshow(id),
24    person_id INT NOT NULL,
25    FOREIGN KEY(person_id) REFERENCES person(id)
26);
27
28INSERT INTO tvshow VALUES (
29    1,
30    'TVShowTitle1',
31    'TVShowDescription1',
32    'TVShowProducer1',
33    'Network1'
34);
35
36INSERT INTO tvshow VALUES(
37    2,
38    'TVShowTitle2',
39    'TVShowDescription2',
40    'TVShowProducer2',
41    'Network2'
42);
43
44INSERT INTO tvshow VALUES (
45    3,
46    'TVShowTitle3',
47    'TVShowDescription3',
48    'TVShowProducer3',
49    'Network3'
50);
51
52-- Actor X
53INSERT INTO person VALUES (
54    1,
55    'ActorXName',
56    'ActorXLastName',
57    'male',
58    STR_TO_DATE('15-01-1988', "%d-%m-%Y"),
59    'website.com'
60);
61
62-- other actors
63INSERT INTO person VALUES (
64    2,
65    'PersonFirstName2',
66    'PersonLastName2',
67    'female',
68    STR_TO_DATE('15-01-1988', "%d-%m-%Y"),
69    'website.com'
70);
71
72INSERT INTO person VALUES (
73    3,
74    'PersonFirstName3',
75    'PersonLastName3',
76    'female',
77    STR_TO_DATE('15-01-1988', "%d-%m-%Y"),
78    'website.com'
79);
80
81INSERT INTO person VALUES (
82    4,
83    'PersonFirstName4',
84    'PersonLastName4',
85    'female',
86    STR_TO_DATE('15-01-1988', "%d-%m-%Y"),
87    'website.com'
88);
89
90-- actor X acts in show 2 y 3
91INSERT INTO cast_person_tvshow VALUES (
92    NULL,
93    'lead',
94    2, -- show_id
95    1 -- person_id
96);
97
98INSERT INTO cast_person_tvshow VALUES (
99    NULL,
100    'lead',
101    3,
102    1
103);
104
105-- other actors act in tv show 2
106INSERT INTO cast_person_tvshow VALUES (
107    NULL,
108    'lead',
109    2,
110    2
111);
112
113INSERT INTO cast_person_tvshow VALUES (
114    NULL,
115    'lead',
116    2,
117    3
118);
119
120-- actor 3 acts in tvshow 3
121INSERT INTO cast_person_tvshow VALUES (
122    NULL,
123    'lead',
124    3,
125    3
126);
127
128-- actor 3 also directs tvshow 3
129INSERT INTO cast_person_tvshow VALUES (
130    NULL,
131    'director',
132    3,
133    3
134);