USE temp ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'CD')
DROP TABLE CD ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'DVD')
DROP TABLE DVD ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA_E')
DROP TABLE MEDIA_E ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'VHS')
DROP TABLE VHS ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA_C')
DROP TABLE MEDIA_C ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'LIVRE')
DROP TABLE LIVRE ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'ARTICLE')
DROP TABLE ARTICLE ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA_D')
DROP TABLE MEDIA_D ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'REVUE')
DROP TABLE REVUE ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA_B')
DROP TABLE MEDIA_B ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA_A')
DROP TABLE MEDIA_A ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'FICHIER')
DROP TABLE FICHIER ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'MEDIA')
DROP TABLE MEDIA ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'EDITEUR')
DROP TABLE EDITEUR ;
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'AUTEUR')
DROP TABLE AUTEUR ;
-------------------------------------------------------------------------------------------------
CREATE TABLE AUTEUR
(
AuteurId INT NOT NULL,
AuteurNom VARCHAR(32) NOT NULL,
AuteurPrenom VARCHAR(32) NOT NULL,
CONSTRAINT AUTEUR_PK PRIMARY KEY (AuteurId)
) ;
CREATE TABLE EDITEUR
(
EditeurId INT NOT NULL,
EditeurNom VARCHAR(32) NOT NULL,
CONSTRAINT EDITEUR_PK PRIMARY KEY (EditeurId)
) ;
CREATE TABLE MEDIA
(
MediaId INT NOT NULL,
AuteurId INT NOT NULL,
NbExemplaires INT NOT NULL,
CONSTRAINT MEDIA_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_AUTEUR_FK FOREIGN KEY (AuteurId)
REFERENCES AUTEUR (AuteurId)
) ;
CREATE TABLE MEDIA_A
(
MediaId INT NOT NULL,
Titre VARCHAR(64) NOT NULL,
CONSTRAINT MEDIA_A_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_A_MEDIA_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE MEDIA_B
(
MediaId INT NOT NULL,
EditeurId INT NOT NULL,
Genre VARCHAR(64) NOT NULL,
CONSTRAINT MEDIA_B_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_B_MEDIA_A_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_A (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE REVUE
(
MediaId INT NOT NULL,
RevueId INT NOT NULL,
Volume INT NOT NULL,
Numero INT NOT NULL,
DateParution DATE NOT NULL,
CONSTRAINT REVUE_PK PRIMARY KEY (MediaId, RevueId),
CONSTRAINT REVUE_MEDIA_B_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_B (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE MEDIA_D
(
MediaId INT NOT NULL,
Resume VARCHAR(512) NOT NULL,
NbPages INT NOT NULL,
CONSTRAINT MEDIA_D_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_D_MEDIA_A_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_A (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE ARTICLE
(
MediaId INT NOT NULL,
RevueMediaId INT NOT NULL,
RevueId INT NOT NULL,
CONSTRAINT ARTICLE_PK PRIMARY KEY (MediaId),
CONSTRAINT ARTICLE_MEDIA_D_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_D (MediaId) ON DELETE CASCADE,
CONSTRAINT ARTICLE_REVUE_FK FOREIGN KEY (RevueMediaId, RevueId)
REFERENCES REVUE
) ;
CREATE TABLE MEDIA_C
(
MediaId INT NOT NULL,
DateCreation DATE NOT NULL,
Duree SMALLINT NOT NULL,
MaisonProduction VARCHAR(64) NOT NULL,
CONSTRAINT MEDIA_C_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_C_MEDIA_A_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_A (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE MEDIA_E
(
MediaId INT NOT NULL,
NbPistes SMALLINT NOT NULL,
CONSTRAINT MEDIA_E_PK PRIMARY KEY (MediaId),
CONSTRAINT MEDIA_E_MEDIA_C_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_C (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE CD
(
MediaId INT NOT NULL,
CONSTRAINT CD_PK PRIMARY KEY (MediaId),
CONSTRAINT CD_MEDIA_E_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_E (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE DVD
(
MediaId INT NOT NULL,
CONSTRAINT DVD_PK PRIMARY KEY (MediaId),
CONSTRAINT DVD_MEDIA_E_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_E (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE FICHIER
(
MediaId INT NOT NULL,
TypeFichier VARCHAR(8) NOT NULL,
Taille INT NOT NULL,
Url VARCHAR(64) NOT NULL,
UniteMesure VARCHAR(3) NOT NULL,
CONSTRAINT FICHIER_PK PRIMARY KEY (MediaId),
CONSTRAINT FICHIER_MEDIA_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA (MediaId) ON DELETE CASCADE
) ;
CREATE TABLE LIVRE
(
MediaId INT NOT NULL,
AnneeEcriture INT NOT NULL,
Collection VARCHAR(64) NOT NULL,
CONSTRAINT LIVRE_PK PRIMARY KEY (MediaId),
CONSTRAINT LIVRE_MEDIA_B_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_B (MediaId) ON DELETE CASCADE,
CONSTRAINT LIVRE_MEDIA_D_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_D (MediaId)
) ;
CREATE TABLE VHS
(
MediaId INT NOT NULL,
CONSTRAINT VHS_PK PRIMARY KEY (MediaId),
CONSTRAINT VHS_MEDIA_C_FK FOREIGN KEY (MediaId)
REFERENCES MEDIA_C (MediaId) ON DELETE CASCADE
) ;
INSERT INTO AUTEUR (AuteurId, AuteurNom, AuteurPrenom) VALUES
(1, 'Hugo', 'Victor')
, (2, 'Brassens', 'Georges')
, (3, 'Mancheron', 'Benoît')
, (4, 'Volfoni', 'Raoul')
, (5, 'Stodder', 'David')
, (6, 'Date', 'Chris')
, (7, 'Chamberlin', 'Donald')
, (8, 'Simsion', 'Graeme')
SELECT * FROM AUTEUR ;
INSERT INTO EDITEUR (EditeurId, EditeurNom) VALUES
(1, 'Le livre de poche')
, (2, 'Pocket')
, (3, 'J''ai lu')
, (4, 'Miller Freeman')
INSERT INTO MEDIA (MediaId, AuteurId, NbExemplaires) VALUES
(1, 1, 20)
, (2, 1, 15)
, (3, 1, 10)
, (4, 5, 9)
, (5, 6, 0)
, (6, 5, 0)
, (7, 7, 0)
, (8, 6, 0)
, (9, 8, 0)
INSERT INTO MEDIA_A (MediaId, Titre) VALUES
(1, 'Les Travailleurs de la mer, tome 1')
, (2, 'Les Misérables, vol 1')
, (3, 'La Légende des siècles')
, (4, 'Database Programming & Design')
, (5, 'Summarize Revisited')
, (6, 'A conversation with Jim Gray')
, (7, 'Recursion in SQL')
, (8, 'Once more around the nullberry bush')
, (9, 'Testing the foundations')
;
INSERT INTO MEDIA_B (MediaId, EditeurId, Genre) VALUES
(1, 1, 'épopée')
, (2, 2, 'roman')
, (3, 3, 'poésie')
, (4, 4, 'bases de données')
, (5, 4, 'modélisation')
INSERT INTO REVUE (MediaId, RevueId, Volume, Numero, DateParution) VALUES
(4, 1, 9, 5, '1996-05-01')
, (4, 2, 9, 2, '1996-02-01')
INSERT INTO MEDIA_D (MediaId, NbPages, Resume) VALUES
(1, 674, 'Mess Lethierry est propriétaire de La Durande, un steamer échoué sur un écueil par la machination criminelle de son capitaine, le sieur Clubin...')
, (2, 615, 'Je m’appelle Jean Valjean. Je suis un galérien. J’ai passé dix-neuf ans au bagne...')
, (3, 125, 'Caïn, échevelé, livide, fuyant Jéhovah implacable...')
, (5, 3, 'A proposal for revising the relational Summarize operator')
, (6, 10, 'When Jim Gray began his carreer...')
, (7, 6, 'Innovation in DB2’s SQL language now allow recursive queries, the hows and whys as explained')
, (8, 2, 'Darwen and Chris Date respond to jim Melton')
, (9, 5, 'Data modelling is nothing if not an enigma')
INSERT INTO ARTICLE (MediaId, RevueMediaId, RevueId) VALUES
(5, 4, 1)
, (6, 4, 1)
, (7, 4, 1)
, (8, 4, 2)
, (9, 4, 2)
INSERT INTO LIVRE (MediaId, AnneeEcriture, Collection) VALUES
(1, 1866, 'collection truc')
, (2, 1862, 'collection verte')
, (3, 1859, 'au fil du temps')
Médiathèque
Répondre avec citation
Envoyé par alias2015_29
Partager