mir2bSze

1CREATE DATABASE IF NOT EXISTS location;
2USE location;
3
4CREATE TABLE client IF EXISTS
5DROP TABLE IF EXISTS client,categorie,materiel,louer;
6(
7    code_client INT PRIMARY KEY NOT NULL,
8    nom VARCHAR(30),
9    prenom VARCHAR(30),
10    civilite VARCHAR(3),
11	code_postal VARCHAR(20)
12);
13
14CREATE TABLE materiel	
15( 
16    code_materiel VARCHAR(20)PRIMARY KEY NOT NULL,
17	nom_materiel VARCHAR(30),
18	descriptif TEXT,
19	prix_location DECIMAL(6,2),
20	code_categorie CHAR(5)
21);
22
23CREATE TABLE louer
24(
25	code_client INT,
26	code_materiel VARCHAR(20)
27);
28
29CREATE TABLE categorie
30(
31	code_categorie CHAR(5)PRIMARY KEY NOT NULL,
32    nom_categorie VARCHAR(30)
33);
34
35ALTER TABLE materiel ADD CONSTRAINT materiel_code_categorie_FK
36FOREIGN KEY(code_categorie) REFERENCES categorie(code_categorie);
37
38ALTER TABLE louer ADD CONSTRAINT louer_code_client_FK
39FOREIGN KEY(code_client) REFERENCES client(code_client);
40
41ALTER TABLE louer ADD CONSTRAINT louer_code_materiel_FK
42FOREIGN KEY(code_materiel) REFERENCES materiel(code_materiel);