CREATE TABLE COUNTRY
(
countryId INT NOT NULL IDENTITY
, countryName VARCHAR(50) NOT NULL
, CONSTRAINT COUNTRY_PK PRIMARY KEY(countryId)
) ;
insert into COUNTRY values
('France'), ('Grande-Bretagne'), ('Belgique'), ('Etats-Unis')
;
select * from COUNTRY ;
CREATE TABLE CHANNEL
(
channelId INT NOT NULL IDENTITY
, channelName VARCHAR(50) NOT NULL
, CONSTRAINT CHANNEL_PK PRIMARY KEY(channelId)
, CONSTRAINT CHANNEL_AK UNIQUE(channelName)
) ;
insert into CHANNEL values
('BBC'), ('DVP'), ('M6')
select * from CHANNEL ;
CREATE TABLE SERIES
(
seriesId INT NOT NULL IDENTITY
, seriesTitle VARCHAR(50) NOT NULL
, releaseYear INT NOT NULL
, alsoKnownAs VARCHAR(50) NOT NULL
, channelId INT NOT NULL
, CONSTRAINT SERIES_PK PRIMARY KEY(seriesId)
, CONSTRAINT SERIES_CHANNEL_FK FOREIGN KEY(channelId) REFERENCES CHANNEL(channelId)
) ;
insert into SERIES values
('BUGS', 1995, '', 1)
, ('Vingt ans dans un mur', 1948, 'La vie d''une brique', 3)
, ('Vingt ans au plafond', 1950, 'La vie d''une poutre', 3)
, ('Vingt ans dans un toit', 1960, 'La vie d''une ardoise', 3)
, ('21 Jump Street', 1987, '', 1)
, ('Booker', 1989, '', 1)
;
select * from SERIES ;
CREATE TABLE FOREIGN_SERIES
(
seriesId INT NOT NULL
, frenchReleaseYear INT NOT NULL
, frenchTitle VARCHAR(50) NOT NULL
, frenchAka VARCHAR(50) NOT NULL
, channelId INT NOT NULL
, countryId INT NOT NULL
, CONSTRAINT FOREIGN_SERIES_PK PRIMARY KEY(seriesId)
, CONSTRAINT FOREIGN_SERIES_SERIES_FK FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId)
, CONSTRAINT FOREIGN_SERIES_CHANNEL_FK FOREIGN KEY(channelId) REFERENCES CHANNEL(channelId)
, CONSTRAINT FOREIGN_SERIES_COUNTRY_FK FOREIGN KEY(countryId) REFERENCES COUNTRY(countryId)
) ;
insert into FOREIGN_SERIES values
(1, 1996, 'Bugs', '', 1, 2)
;
select seriesTitle, frenchReleaseYear, frenchAka, countryName, channelName
from FOREIGN_SERIES as f
join SERIES as s on s.seriesId = f.seriesId
join COUNTRY as c on f.countryId = c.countryId
join CHANNEL as h on f.channelId = h.channelId
;
CREATE TABLE FRENCH_SERIES
(
seriesId INT NOT NULL
, CONSTRAINT FRENCH_SERIES_PK PRIMARY KEY(seriesId)
, CONSTRAINT FRENCH_SERIES_SERIES_FK FOREIGN KEY(seriesId) REFERENCES SERIES(seriesId)
) ;
insert into FRENCH_SERIES values
(2)
;
select s.seriesId, seriesTitle, releaseYear, alsoKnownAs, channelName
from SERIES as s
join FRENCH_SERIES as f on s.seriesId = f.seriesId
join CHANNEL as h on s.channelId = h.channelId
;
CREATE TABLE SPINOFF
(
seriesId_prequel INT NOT NULL
, seriesId_spinOff INT NOT NULL
, CONSTRAINT SPINOFF_PK PRIMARY KEY(seriesId_prequel)
, CONSTRAINT SPINOFF_SERIES_prequel_FK FOREIGN KEY(seriesId_prequel) REFERENCES SERIES(seriesId)
, CONSTRAINT SPINOFF_SERIES_spinOff_FK FOREIGN KEY(seriesId_spinOff) REFERENCES SERIES(seriesId)
);
insert into SPINOFF values
(3, 2)
, (4, 2)
, (6, 5)
;
/* Vue SPINOFF */
select q.seriesTitle as enfant, t.seriesTitle as parent
from SPINOFF as f
join SERIES as q on f.seriesId_prequel = q.seriesId
join SERIES as t on f.seriesId_spinOff = t.seriesId
;
Modélisation d'une BdD séries TV
Répondre avec citation
Envoyé par Neelix57
.png)
!
...)
spinoff.png)
isSpinOff.png)
Partager