Stockage et traitement des données

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;