1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
| CREATE TABLE PE_personne(
PE_ident INT IDENTITY,
PE_nom VARCHAR(50) NOT NULL,
PE_prenom VARCHAR(50) NOT NULL,
PE_ddn DATE NOT NULL,
PRIMARY KEY(PE_ident)
);
CREATE TABLE MB_membre(
MB_ident INT IDENTITY,
MB_matricule CHAR(6) NOT NULL,
PE_ident INT NOT NULL,
PRIMARY KEY(MB_ident),
UNIQUE(PE_ident),
UNIQUE(MB_matricule),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE RP_responsable(
PE_ident INT,
PRIMARY KEY(PE_ident),
FOREIGN KEY(PE_ident) REFERENCES PE_personne(PE_ident)
);
CREATE TABLE VI_ville(
VI_ident INT IDENTITY,
VI_insee CHAR(5) NOT NULL,
VI_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(VI_ident),
UNIQUE(VI_insee)
);
CREATE TABLE PA_parking(
PA_ident INT IDENTITY,
PA_long DECIMAL(5,2) NOT NULL,
PA_larg DECIMAL(5,2) NOT NULL,
PA_haut DECIMAL(5,2) NOT NULL,
PA_geo GEOGRAPHY NOT NULL,
PA_adr1 VARCHAR(38) NOT NULL,
PA_adr2 VARCHAR(38),
VI_ident INT NOT NULL,
MB_ident INT NOT NULL,
PRIMARY KEY(PA_ident),
FOREIGN KEY(VI_ident) REFERENCES VI_ville(VI_ident),
FOREIGN KEY(MB_ident) REFERENCES MB_membre(MB_ident)
);
CREATE TABLE AN_annonce(
PA_ident INT,
AN_ident SMALLINT,
AN_date DATE NOT NULL,
AN_tarif DECIMAL(7,2) NOT NULL,
MB_ident INT NOT NULL,
PRIMARY KEY(PA_ident, AN_ident),
FOREIGN KEY(PA_ident) REFERENCES PA_parking(PA_ident),
FOREIGN KEY(MB_ident) REFERENCES MB_membre(MB_ident)
);
CREATE TABLE RS_reserver(
PA_ident INT,
CA_date DATE,
RS_dtfin DATE NOT NULL,
MB_ident INT NOT NULL,
PRIMARY KEY(PA_ident, CA_date),
FOREIGN KEY(PA_ident) REFERENCES PA_parking(PA_ident),
FOREIGN KEY(MB_ident) REFERENCES MB_membre(MB_ident)
);
CREATE TABLE MO_moderer(
PE_ident INT,
PA_ident INT,
AN_ident SMALLINT,
MO_dateheure DATETIME2 NOT NULL,
MO_motif VARCHAR(128),
PRIMARY KEY(PE_ident, PA_ident, AN_ident),
FOREIGN KEY(PE_ident) REFERENCES RP_responsable(PE_ident),
FOREIGN KEY(PA_ident, AN_ident) REFERENCES AN_annonce(PA_ident, AN_ident)
);
ALTER TABLE AN_annonce
ADD CONSTRAINT FK_publier_posseder
FOREIGN KEY (MB_ident, PA_ident)
REFERENCES PA_parking(MB_ident, PA_ident)
; |
Diagramme de Classes et conception Base de Donnees
Répondre avec citation
Envoyé par NarumiN
ansi que son adresse (la géolocalisation c'est bien, mais l'adresse c'est indispensable 
)
Partager