|
PRAGMA foreign_keys=OFF;
|
|
|
|
DROP TABLE IF EXISTS Membre;
|
|
DROP TABLE IF EXISTS Biblio;
|
|
DROP TABLE IF EXISTS Livre;
|
|
DROP TABLE IF EXISTS Emprunt;
|
|
|
|
CREATE TABLE Membre
|
|
(
|
|
IdMembre INTEGER NOT NULL PRIMARY KEY,
|
|
nomMembre CHAR(10) NOT NULL,
|
|
adrMembre CHAR(12),
|
|
cpMembre CHAR(5)
|
|
);
|
|
|
|
CREATE TABLE Biblio
|
|
(
|
|
IdBiblio INTEGER NOT NULL PRIMARY KEY,
|
|
nomBiblio CHAR(15) NOT NULL,
|
|
adrBiblio CHAR(12),
|
|
cpBiblio CHAR(5)
|
|
);
|
|
|
|
CREATE TABLE Livre
|
|
(
|
|
IdLivre CHAR(8) NOT NULL PRIMARY KEY,
|
|
titreLivre CHAR(40),
|
|
auteurLivre CHAR(30) NOT NULL,
|
|
categLivre SMALLINT DEFAULT 1 CHECK(categLivre<6),
|
|
IdBiblio INTEGER NOT NULL,
|
|
FOREIGN KEY (IdBiblio)
|
|
REFERENCES Biblio(IdBiblio)
|
|
);
|
|
|
|
CREATE TABLE Emprunt
|
|
(
|
|
IdLivre CHAR(8) NOT NULL,
|
|
IdMembre INTEGER NOT NULL,
|
|
dateEmprunt DATE,
|
|
dureeEmprunt INTEGER,
|
|
PRIMARY KEY (IdLivre, IdMembre)
|
|
);
|
|
|
|
|
|
INSERT INTO Membre VALUES (2, 'Durand', 'Marseille', '13008');
|
|
INSERT INTO Membre VALUES (7, 'Cassagne', 'Marseille', '13007');
|
|
INSERT INTO Membre VALUES (5, 'André', 'Gardanne', '13120');
|
|
INSERT INTO Membre VALUES (21, 'Petiot', 'Marseille', '13008');
|
|
INSERT INTO Membre VALUES (15, 'Monet', 'Aubagne', '13200');
|
|
INSERT INTO Membre VALUES (30,'Napo','Marseille','13012');
|
|
|
|
INSERT INTO Biblio VALUES (208, 'Prado', 'Marseille', '13008');
|
|
INSERT INTO Biblio VALUES (71, 'République', 'Marseille', '13002');
|
|
INSERT INTO Biblio VALUES (112, 'St Charles', 'Marseille', '13001');
|
|
INSERT INTO Biblio VALUES (202, 'Aubagne', 'Aubagne', '13200');
|
|
|
|
INSERT INTO Livre VALUES ('0228PRAZ','Les Misérables', 'Victor Hugo', 1, 208);
|
|
INSERT INTO Livre VALUES ('1206QIQA','Paroles','Jacques Prévert', 3, 202);
|
|
INSERT INTO Livre VALUES ('7658MNLK','Avicenne', 'Gilbert Sinoué', 1, 112);
|
|
INSERT INTO Livre VALUES ('4021QRPO', 'Les Contemplations','Victor Hugo', 1, 112);
|
|
INSERT INTO Livre VALUES ('7089PQIU', 'Le dernier pharaon','Gilbert Sinoué', 1, 202);
|
|
INSERT INTO Livre VALUES ('6154PANB','Ruy Blas', 'Victor Hugo', 5, 71);
|
|
INSERT INTO Livre VALUES ('1920RLTY','Contes pour les enfants pas sages', 'Jacques Prévert', 2, 208);
|
|
INSERT INTO Livre VALUES ('1956JJSS','Le crabe aux pinces d''or', 'Hergé', 4, 71);
|
|
INSERT INTO Livre VALUES ('4919FFEF','Silex and the city', 'Jul', 4, 202);
|
|
|
|
|
|
INSERT INTO Emprunt VALUES ('1920RLTY', 2, '2010-02-12', 6);
|
|
INSERT INTO Emprunt VALUES ('0228PRAZ',30, '2011-02-05',5);
|
|
INSERT INTO Emprunt VALUES ('6154PANB', 2, '2010-09-28', 9);
|
|
INSERT INTO Emprunt VALUES ('1920RLTY',30, '2011-01-10',10);
|
|
INSERT INTO Emprunt VALUES ('7658MNLK',30, '2011-01-28',11);
|
|
INSERT INTO Emprunt VALUES ('1920RLTY', 15, '2010-03-15', 6);
|
|
INSERT INTO Emprunt VALUES ('1956JJSS',30, '2011-02-19',2);
|
|
INSERT INTO Emprunt VALUES ('1206QIQA', 15, '2010-11-02', 10);
|
|
INSERT INTO Emprunt VALUES ('7658MNLK', 2, '2010-06-02', 5);
|
|
INSERT INTO Emprunt VALUES ('4919FFEF',30, '2011-04-19',7);
|
|
INSERT INTO Emprunt VALUES ('7658MNLK', 15, '2010-05-04', 5);
|
|
INSERT INTO Emprunt VALUES ('6154PANB', 5, '2010-05-04', 7);
|
|
INSERT INTO Emprunt VALUES ('4021QRPO', 5, '2010-05-04', 7);
|
|
INSERT INTO Emprunt VALUES ('0228PRAZ', 5, '2010-05-04', 7);
|
|
|
|
PRAGMA foreign_keys=ON;
|
|
|
|
|
|
|
|
|
|
|
