· 8 years ago · Jan 05, 2018, 01:52 PM
1
2drop database if exists biblio;
3create database biblio;
4use biblio;
5-- 3 attributs :
6CREATE TABLE oeuvres(
7NO integer primary key auto_increment,
8titre varchar(150),
9auteur varchar(100)
10) ENGINE InnoDB;
11-- 5 attributs :
12CREATE TABLE adherents (
13NA INT PRIMARY KEY AUTO_INCREMENT,
14nom VARCHAR(30),
15prenom VARCHAR(30),
16adr VARCHAR(100),
17tel CHAR(10)
18) ENGINE InnoDB;
19-- 3 attributs :
20CREATE TABLE livres (
21NL integer primary key auto_increment,
22editeur varchar(50),
23NO integer not null, foreign key(NO) references
24oeuvres(NO)
25) ENGINE InnoDB;
26-- 5 attributs :
27CREATE TABLE emprunter (
28NL integer not null, foreign key(NL) references
29livres(NL),
30datEmp date not null,
31dureeMax int not null,
32dateRet date,
33NA integer not null, foreign key(NA) references
34adherents(NA),
35primary key (NL, datEmp)
36) ENGINE InnoDB;
37-- 16 insertions :
38INSERT INTO oeuvres VALUES
39(1,'Narcisse et Goldmund','Hermann HESSE'),
40(2,'Bérénice','Jean RACINE'),
41(3,'Prolégomènes à toute métaphysique future','Emmanuel KANT'),
42(4,'Mon coeur mis à nu','Charles BAUDELAIRE'),
43
44(5,'Voyage au bout de la nuit','Louis-Ferdinand CELINE'),
45(6,'Les possédés','Fedor DOSTOIEVSKI'),
46(7,'Le Rouge et le Noir','STENDHAL'),
47(8,'Alcibiade','Jacqueline de ROMILLY'),
48(9,'Monsieur Teste','Paul VALERY'),
49(10,'Lettres de Gourgounel','Kenneth WHITE'),
50(11,'Lettres à un jeune poète','Rainer Maria RILKE'),
51(12,'Logique sans peine','Lewis CAROLL'),
52(13,'L''éthique','Baruch SPINOZA'),
53(14,'Sur le rêve','Sigmund FREUD'),
54(15,'Sens et dénotation','Gottlob FREGE'),
55(16,'Au coeur des ténèbres','Joseph CONRAD');
56-- 30 insertions :
57INSERT INTO adherents VALUES (1,'Lecoeur','Jeanette','16 rue de
58la République, 75010 Paris','0145279274');
59INSERT INTO adherents VALUES (2,'Lecoeur','Philippe','16 rue de
60la République, 75010 Paris','0145279274');
61INSERT INTO adherents VALUES (3,'Dupont','Yvan','73 rue Lamarck,
6275018 Paris','0163538294');
63INSERT INTO adherents VALUES (4,'Mercier','Claude','155 avenue
64Parmentier, 75011 Paris','0136482736');
65INSERT INTO adherents VALUES (5,'Léger','Marc','90 avenue du
66Maine, 75014 Paris','0164832947');
67INSERT INTO adherents VALUES (6,'Martin','Laure','51 boulevard
68Diderot, 75012 Paris','0174693277');
69INSERT INTO adherents VALUES (7,'Crozier','Martine','88 rue des
70Portes Blanches, 75018 Paris','0146829384');
71INSERT INTO adherents VALUES (8,'Lebon','Clément','196 boulevard
72de Sebastopol, 75001 Paris','0132884739');
73INSERT INTO adherents VALUES (9,'Dufour','Jacques','32 rue des
74Alouettes, 75003 Paris','0155382940');
75INSERT INTO adherents VALUES (10,'Dufour','Antoine','32 rue des
76Alouettes, 75003 Paris','0155382940');
77INSERT INTO adherents VALUES (11,'Dufour','Stéphanie','32 rue
78des Alouettes, 75003 Paris','0155382940');
79INSERT INTO adherents VALUES (12,'Raymond','Carole','35 rue
80Oberkampf, 75011 Paris','0153829402');
81INSERT INTO adherents VALUES (13,'Durand','Albert','4 rue
82Mandar, 75002 Paris','0642374021');
83INSERT INTO adherents VALUES (14,'Wilson','Paul','12 rue Paul
84Vaillant Couturier, 92400 Montrouge','0642327407');
85INSERT INTO adherents VALUES (15,'Grecault','Philippe','15 rue
86de la Roquette, 75012 Paris','0132762983');
87INSERT INTO adherents VALUES (16,'Carre','Stéphane','51
88boulevard Diderot, 75012 Paris','0174693277');
89INSERT INTO adherents VALUES (17,'Johnson','Astrid','3 rue Léon
90Blum, 75002 Paris','0143762947');
91INSERT INTO adherents VALUES (18,'Mirou','Caroline','2 square
92Mirabeau, 75011 Paris','0163827399');
93INSERT INTO adherents VALUES (19,'Espelette','Jean-Jacques','141
94avenue de France, 75019 Paris','0134887264');
95INSERT INTO adherents VALUES (20,'Despentes','Anthony','56
96boulevard de la Villette, 75019 Paris','0133889463');
97INSERT INTO adherents VALUES (21,'Terlu','Véronique','45 rue des
98Batignolles, 75020 Paris','0165998372');
99INSERT INTO adherents VALUES (22,'Rihour','Cécile','7 rue
100Montorgueil, 75002 Paris','0166894754');
101
102INSERT INTO adherents VALUES (23,'Franchet','Pierre','160 rue
103Montmartre, 75009 Paris','0633628549');
104INSERT INTO adherents VALUES (24,'Trochet','Ernest','34 rue de
105l''Esperance, 75008 Paris','0638295563');
106INSERT INTO adherents VALUES (25,'Gainard','Simon','55 rue
107Desnouettes, 75015 Paris','0174928934');
108INSERT INTO adherents VALUES (26,'Touche','Johanna','14 rue du
109Bac, 75006 Paris','0619384065');
110INSERT INTO adherents VALUES (27,'Cornu','Sylvain','22 rue
111Mouffetard, 75005 Paris','0184927489');
112INSERT INTO adherents VALUES (28,'Frederic','Cyril','15 rue du
113Simplon, 75018 Paris','0173625492');
114INSERT INTO adherents VALUES (29,'Crestard','Cedric','5 rue
115Doudeauville, 75018 Paris','0629485700');
116INSERT INTO adherents VALUES (30,'Le Bihan','Karine','170 bis
117rue Ordener, 75018 Paris','0638995221');
118-- 20 insertions :
119INSERT INTO livres VALUES (1,'GF',2);
120INSERT INTO livres VALUES (2,'FOLIO',1);
121INSERT INTO livres VALUES (3,'HACHETTE',4);
122INSERT INTO livres VALUES (4,'GF',3);
123INSERT INTO livres VALUES (5,'FOLIO',6);
124INSERT INTO livres VALUES (6,'FOLIO',5);
125INSERT INTO livres VALUES (7,'GF',9);
126INSERT INTO livres VALUES (8,'FOLIO',8);
127INSERT INTO livres VALUES (9,'HACHETTE',7);
128INSERT INTO livres VALUES (10,'GF',15);
129INSERT INTO livres VALUES (11,'HACHETTE',14);
130INSERT INTO livres VALUES (12,'FOLIO',13);
131INSERT INTO livres VALUES (13,'GF',12);
132INSERT INTO livres VALUES (14,'FOLIO',11);
133INSERT INTO livres VALUES (15,'HACHETTE',10);
134INSERT INTO livres VALUES (16,'HACHETTE',16);
135INSERT INTO livres VALUES (17,'GF',1);
136INSERT INTO livres VALUES (18,'FOLIO',2);
137INSERT INTO livres VALUES (19,'HACHETTE',2);
138INSERT INTO livres VALUES (20,'FOLIO',4);
139-- 25 insertions :
140INSERT INTO emprunter VALUES (1,'2007/9/2',14,'2007/9/7',1);
141INSERT INTO emprunter VALUES (1,'2006/10/1',14,'2006/10/11',26);
142INSERT INTO emprunter VALUES (1,'2007/6/14',14,'2007/6/19',2);
143INSERT INTO emprunter VALUES (1,'2007/4/27',14,'2007/5/8',3);
144INSERT INTO emprunter VALUES (2,'2007/8/23',14,'2007/9/2',4);
145INSERT INTO emprunter VALUES (2,'2007/10/6',14, NULL,28);
146INSERT INTO emprunter VALUES (9,'2007/10/6',14, NULL,28);
147INSERT INTO emprunter VALUES (3,'2007/9/9',14,'2007/9/13',3);
148INSERT INTO emprunter VALUES (4,'2007/2/8',14,'2007/2/12',12);
149INSERT INTO emprunter VALUES (4,'2006/2/7',14,'2006/2/20',4);
150INSERT INTO emprunter VALUES (4,'2007/6/17',14,'2007/6/27',5);
151INSERT INTO emprunter VALUES (5,'2007/10/4',14, NULL,16);
152INSERT INTO emprunter VALUES (6,'2007/3/11',14,'2007/3/16',3);
153INSERT INTO emprunter VALUES (8,'2007/7/14',14,'2007/7/20',18);
154INSERT INTO emprunter VALUES (8,'2007/3/9',14,'2007/3/27',13);
155INSERT INTO emprunter VALUES (10,'2007/4/11',14,'2007/4/23',8);
156INSERT INTO emprunter VALUES (10,'2006/1/27',14,'2006/1/31',7);
157INSERT INTO emprunter VALUES (11,'2007/10/1',14, NULL,22);
158
159INSERT INTO emprunter VALUES (12,'2003/3/3',14,'2003/3/13',7);
160INSERT INTO emprunter VALUES (14,'2007/10/2',14, NULL,1);
161INSERT INTO emprunter VALUES (15,'2007/5/4',14,'2007/5/12',10);
162INSERT INTO emprunter VALUES (16,'2007/10/5',14, NULL,2);
163INSERT INTO emprunter VALUES (18,'2007/7/4',14,'2007/7/11',3);
164INSERT INTO emprunter VALUES (18,'2007/10/15',14, NULL, 27);
165INSERT INTO emprunter VALUES (20,'2007/10/15',14, NULL, 27);