· 7 years ago · Oct 24, 2018, 01:20 PM
1# CREATE DATABASE IF NOT exists zerocool CHARACTER SET 'utf8' ;
2USE zerocool;
3
4# DROP TABLE MANIPULATION 2*/
5drop table if exists TYPES;
6drop table if exists INSTALLER;
7drop table if exists LOGICIEL;
8drop table if exists POSTE;
9drop table if exists SALLE;
10drop table if exists SEGMENT;
11drop table if exists Softs;
12drop table if exists PCSeuls;
13
14# CREATE ALL TABLE MANIPULATION 1 */
15Create TABLE IF NOT EXISTS SEGMENT (
16 indIP VARCHAR(11),
17 nomSegment VARCHAR(20) NOT NULL,
18 etage TINYINT(1),
19
20 PRIMARY KEY (indIP)
21);
22
23Create TABLE IF NOT EXISTS SALLE (
24 nSalle VARCHAR(7),
25 nomSalle VARCHAR(20) NOT NULL,
26 nbPoste TINYINT(2),
27 indIP VARCHAR(11),
28
29 PRIMARY KEY (nSalle),
30 CONSTRAINT FK_SALLE_indIP FOREIGN KEY (indIP) REFERENCES SEGMENT (indIP)
31);
32
33Create TABLE IF NOT EXISTS POSTE (
34 nPoste VARCHAR(7),
35 nomPoste VARCHAR(20),
36 typePoste VARCHAR(10), ad VARCHAR(3) CHECK (0> VALUE >255),
37 indIP VARCHAR(7),
38 nSalle VARCHAR(7),
39 PRIMARY KEY (nPoste),
40
41 CONSTRAINT FK_POSTE_nSalle FOREIGN KEY (nSalle) REFERENCES SALLE (nSalle)
42);
43
44 Create TABLE IF NOT EXISTS LOGICIEL (
45 nLog VARCHAR(5),
46 nomLog VARCHAR(20),
47 dateach DATETIME,
48 version VARCHAR(7),
49 typeLog VARCHAR(9),
50 prix DECIMAL(6,2) CHECK (VALUE > 0.0),
51
52 PRIMARY KEY (nLog)
53);
54
55Create TABLE IF NOT EXISTS INSTALLER (
56 nPoste VARCHAR(7),
57 nLog VARCHAR(5),
58 numIns INTEGER(5) auto_increment,
59 dateIns timestamp default current_timestamp,
60 delai SMALLINT,
61
62 PRIMARY KEY (numIns)
63);
64
65Create TABLE IF NOT EXISTS TYPES (
66 typeLP VARCHAR(9),
67 nomType VARCHAR(20),
68
69 PRIMARY KEY (typeLP)
70);
71
72/* MANIPULATION 3 */
73insert into SEGMENT (indIP, nomSegment, etage)
74 values ('130.120.80','Brin RDC', null ),
75 ('130.120.81','Brin 1er etage', null),
76 ('130.120.82','Brin 2e etage ', null);
77
78insert into SALLE (nSalle, nomSalle, nbPoste ,indIP)
79 values
80 ('s01','salle 1', '3', '130.120.80'),
81 ('s02','salle 2', '2', '130.120.80'),
82 ('s03','salle 3', '2', '130.120.80'),
83 ('s11','salle 11', '2', '130.120.81'),
84 ('s12','salle 12', '1', '130.120.81'),
85 ('s21','salle 21', '2', '130.120.82'),
86 ('s22','salle 22', '0', '130.120.82'),
87 ('s23','salle 23', '0', '130.120.82');
88
89insert into POSTE (nPoste, nomPoste, typePoste, ad ,indIP ,nSalle)
90 values
91 ('p1', 'poste1','130.120.80','01','TX','s01'),
92 ('p2', 'poste2','130.120.80','02','unix','s01'),
93 ('p3', 'poste3','130.120.80','03','TX','s01'),
94 ('p4', 'poste4','130.120.80','04','PCWS','s02'),
95 ('p5', 'poste5','130.120.80','05','PCWS','s02'),
96 ('p6', 'poste6','130.120.80','06','UNIX','s03'),
97 ('p7', 'poste7','130.120.80','07','TX','s03'),
98 ('p8', 'poste8','130.120.81','01','UNIX','s11'),
99 ('p9', 'poste9','130.120.81','02','TX','s11'),
100 ('p10', 'poste10','130.120.81','03','UNIX','s12'),
101 ('p11', 'poste11','130.120.82','01','PCNT','s21'),
102 ('p12', 'poste12','130.120.82','02','PCWS','s21');
103
104insert into LOGICIEL (nLog, nomLog, dateach, version, typeLog, prix)
105 values
106 ('log1','oracle6','1995-05-13','6.2','UNIX',3000),
107 ('log2','oracle8','1999-09-15','8i','UNIX',5600),
108 ('log3','SQL Server','1998-04-12','7','PCNT',2700),
109 ('log4','Front Page','1997-06-03','5','PCWS',500),
110 ('log5','WinDev','1997-05-12','5','PCWS',750),
111 ('log6','SQL*Net',null,'2.0','UNIX',500),
112 ('log7','I. I. S.','2002-04-12','2','PCNT',810),
113 ('log8','DreamWeaver','2003-09-21','2.0','BeOS',1400);
114
115insert into TYPES (typeLP, nomType)
116 values
117 ('TX','Terminal X-Window'),
118 ('UNIX','Système Unix'),
119 ('PCNT','PC Windows NT'),
120 ('PCWS','PC Windows'),
121 ('NC','Network Computer');
122
123insert into INSTALLER (nPoste, nLog, dateIns,delai)
124 values
125 ('p2','log1','2003-05-15',null),
126 ('p2','log2','2003-09-17',null),
127 ('p4','log5',null,null),
128 ('p6','log6','2003-05-20',null),
129 ('p6','log1','2003-05-20',null),
130 ('p8','log2','2003-05-19',null),
131 ('p8','log6','2003-04-20',null),
132 ('p11','log3','2003-04-20',null),
133 ('p12','log4','2003-04-20',null),
134 ('p11','log7','2003-04-20',null),
135 ('p7','log7','2003-04-01',null);
136
137
138/* MANIPULATION 4*/
139update SEGMENT
140 set etage = '0'
141 where indIP = '130.120.80' ;
142
143update SEGMENT
144 set etage = '1'
145 where indIP = '130.120.81';
146
147update SEGMENT
148 set etage = '2'
149 where indIP = '130.120.82';
150
151
152update LOGICIEL
153 set prix = prix - prix * 1/10
154 where typeLog= 'PCNT' ;
155
156
157
158/*------------------- < MODIFICATION DES TABLES -----------------------------*/
159
160/*----------- Exercice 1 & 2 ------------------*/
161
162alter table SEGMENT
163 add nbSalle tinyint(2) default 0,
164 add nbPoste tinyint(2) default 0,
165 modify nomSegment varchar(15);
166
167alter table LOGICIEL
168 add nbInstall tinyint(2) default 0;
169
170alter table POSTE
171 add nbLog tinyint(2) default 0;
172
173alter table SALLE modify nomSalle varchar(30);
174
175/*------------ Exercice 3 ---------------*/
176
177/* Traitenement des Erreur */
178SELECT * FROM `SALLE` WHERE `indIP` NOT IN (SELECT `indIP` FROM `SEGMENT`);
179
180/* Suppression des enregistrement */
181DELETE FROM `SALLE` WHERE `indIP` NOT IN (SELECT `indIP` FROM `SEGMENT`);
182ALTER TABLE `SALLE` ADD CONSTRAINT FOREIGN KEY(`indIP`) REFERENCES `SEGMENT` (`indIP`);
183
184INSERT INTO `TYPES` (`typeLP`,`nomType`) VALUES ('Beos', 'System BE');
185ALTER TABLE `LOGICIEL` ADD CONSTRAINT FOREIGN KEY(`typeLog`) REFERENCES `TYPES` (`typeLp`);