PYDxsF9n

1-- Copyright (c) 2016 Terencio Agozzino & Guillaume Quittet
2-- All rights reserved. This program and the accompanying materials
3-- are made available under the terms of the GNU Lesser Public License v2.1
4-- which accompanies this distribution, and is available at
5-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
6--
7-- Contributors:
8--    Terencio Agozzino - initial API and implementation
9--    Guillaume Quittet - initial API and implementation
10--
11-- Be careful to the orders of tables, because foreign keys
12-- take care of it.
13
14CREATE DATABASE IF NOT EXISTS Exercice CHARACTER SET 'utf8' COLLATE utf8_general_ci;
15    USE Exercice;
16
17CREATE TABLE IF NOT EXISTS Segment (
18	indIP VARCHAR(11),
19	nomSegment VARCHAR(15) NOT NULL,
20	etage TINYINT(1),
21	nbSalle TINYINT(2) DEFAULT 0,
22	nbPoste TINYINT(2) DEFAULT 0,
23	CONSTRAINT pk_indIP PRIMARY KEY(indIP)
24	) ENGINE=InnoDB;
25
26CREATE TABLE IF NOT EXISTS Salle (
27	nSalle VARCHAR(7),
28	nomSalle VARCHAR(30) NOT NULL,
29	nbPoste TINYINT(2),
30	indIP VARCHAR(11),
31	CONSTRAINT pk_nSalle PRIMARY KEY(nSalle)
32	) ENGINE=InnoDB;
33
34CREATE TABLE IF NOT EXISTS Types (
35	typeLP VARCHAR(9),
36	nomType VARCHAR(20),
37	CONSTRAINT pk_typeLP PRIMARY KEY(typeLP)
38	) ENGINE=InnoDB;
39
40CREATE TABLE IF NOT EXISTS Poste (
41	nPoste VARCHAR(7),
42	nomPoste VARCHAR(20) NOT NULL,
43	indIP VARCHAR(11),
44	ad VARCHAR(3),
45	typePoste VARCHAR(9),
46	nSalle VARCHAR(7),
47	nbLog TINYINT(2) DEFAULT 0,
48	CONSTRAINT chk_ad CHECK(ad >= 0 AND ad <= 255),
49	CONSTRAINT pk_nPoste PRIMARY KEY(nPoste),
50	CONSTRAINT fk_nSalle FOREIGN KEY(nSalle) REFERENCES Salle(nSalle),
51	CONSTRAINT fk_typePoste FOREIGN KEY(typePoste) REFERENCES Types(typeLP),
52	CONSTRAINT fk_indIP FOREIGN KEY(indIP) REFERENCES Segment(indIP)
53	) ENGINE=InnoDB;
54
55CREATE TABLE IF NOT EXISTS Logiciel (
56	nLog VARCHAR(5),
57	nomLog VARCHAR(20),
58	dateAch DATETIME,
59	version VARCHAR(7),
60	typeLog VARCHAR(9),
61	prix DECIMAL(6, 2),
62	nbInstall TINYINT(2) DEFAULT 0,
63	CONSTRAINT chk_prix CHECK(prix >= 0),
64	CONSTRAINT pk_nLog PRIMARY KEY(nLog),
65	CONSTRAINT uc_nomLog UNIQUE(nomLog),
66	CONSTRAINT fk_typeLog FOREIGN KEY(typeLog) REFERENCES Types(typeLP)
67	) ENGINE=InnoDB;
68
69CREATE TABLE IF NOT EXISTS Installer (
70	nPoste VARCHAR(7),
71	nLog VARCHAR(5),
72	numIns INT(5) NOT NULL AUTO_INCREMENT,
73	dateIns TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
74	delai SMALLINT,
75	nbLog TINYINT(2) DEFAULT 0,
76	CONSTRAINT pk_numIns PRIMARY KEY(numIns),
77	CONSTRAINT fk_nPoste FOREIGN KEY(nPoste) REFERENCES Poste(nPoste),
78	CONSTRAINT fk_nLog FOREIGN KEY(nLog) REFERENCES Logiciel(nLog)
79	) ENGINE=InnoDB;
80
81CREATE TABLE IF NOT EXISTS Softs(SELECT nomLog AS nomSoft, version, prix
82    FROM Logiciel);
83
84CREATE TABLE IF NOT EXISTS PCSeuls(SELECT nPoste AS nP,
85	nomPoste AS nomP, indIP AS seq, ad, typePoste AS typeP, nSalle AS salle
86    FROM Poste);