1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
| CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom INT IDENTITY NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PE_dtnai DATE NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE AB_abonnne(
PE_ident INT,
AB_numero SMALLINT NOT NULL,
AB_dtadh VARCHAR(50) NOT NULL,
PRIMARY KEY(PE_ident),
UNIQUE(AB_numero),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE GE_gestionnaire(
PE_ident INT,
PRIMARY KEY(PE_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE YT_type_tel(
YT_ident INT IDENTITY,
YT_code CHAR(4) NOT NULL,
YT_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(YT_ident),
UNIQUE(YT_code)
);
CREATE TABLE AU_auteur(
PE_ident INT,
AU_pseudo VARCHAR(50) NOT NULL,
PRIMARY KEY(PE_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE MN_manuscrit(
MN_ident INT IDENTITY,
MN_dtecr DATE NOT NULL,
MN_titre VARCHAR(255) NOT NULL,
MN_resume VARCHAR(255),
PRIMARY KEY(MN_ident)
);
CREATE TABLE ED_editeur(
ED_ident INT IDENTITY,
ED_code CHAR(10) NOT NULL,
ED_nom VARCHAR(128) NOT NULL,
PRIMARY KEY(ED_ident),
UNIQUE(ED_code)
);
CREATE TABLE TL_telephone(
TL_ident INT IDENTITY,
TL_numero CHAR(25) NOT NULL,
YT_ident INT NOT NULL,
PE_ident INT NOT NULL,
PRIMARY KEY(TL_ident),
FOREIGN KEY(YT_ident) REFERENCES YT_type_tel(YT_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE LI_livre(
MN_ident INT,
LI_ident SMALLINT,
LI_ISBN CHAR(13) NOT NULL,
ED_ident INT,
PRIMARY KEY(MN_ident, LI_ident),
UNIQUE(LI_ISBN),
FOREIGN KEY(MN_ident) REFERENCES MN_manuscrit(MN_ident),
FOREIGN KEY(ED_ident) REFERENCES ED_editeur(ED_ident)
);
CREATE TABLE EX_exemplaire(
MN_ident INT,
LI_ident SMALLINT,
EX_ident SMALLINT,
EX_dtimp DATE NOT NULL,
EX_dtach DATE NOT NULL,
EX_codbar SMALLINT NOT NULL,
PE_ident INT NOT NULL,
PRIMARY KEY(MN_ident, LI_ident, EX_ident),
UNIQUE(EX_codbar),
FOREIGN KEY(MN_ident, LI_ident) REFERENCES LI_livre(MN_ident, LI_ident),
FOREIGN KEY(PE_ident) REFERENCES GE_gestionnaire(PE_ident)
);
CREATE TABLE AX_emprunter(
MN_ident INT,
LI_ident SMALLINT,
EX_ident SMALLINT,
CA_date DATE,
PE_ident INT NOT NULL,
PRIMARY KEY(MN_ident, LI_ident, EX_ident, CA_date),
FOREIGN KEY(MN_ident, LI_ident, EX_ident) REFERENCES EX_exemplaire(MN_ident, LI_ident, EX_ident),
FOREIGN KEY(PE_ident) REFERENCES AB_abonnne(PE_ident)
);
CREATE TABLE EC_ecrire(
PE_ident INT,
MN_ident INT,
PRIMARY KEY(PE_ident, MN_ident),
FOREIGN KEY(PE_ident) REFERENCES AU_auteur(PE_ident),
FOREIGN KEY(MN_ident) REFERENCES MN_manuscrit(MN_ident)
); |
Partager