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