Scribd
Upload
20 views3 pages

MySQL Database Structure Overview

The document creates databases and tables to store information about computer segments, rooms, workstations, software, and software installations. It defines the structure and relationships between the tables and inserts sample data.

Uploaded by

tornodi771
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views3 pages

MySQL Database Structure Overview

The document creates databases and tables to store information about computer segments, rooms, workstations, software, and software installations. It defines the structure and relationships between the tables and inserts sample data.

Uploaded by

tornodi771
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

create Database parc;

use parc;

create table segment(indIP varchar(11) primary key,nom_segment varchar(20),etage int);

desc segment;

create table salle(nsalle varchar(7) primary key,nom_salle varchar(20),nbposte int,indIP


varchar(11),foreign key(indIP) references segment (indIP));

DESC SALLE;

CREATE TABLE poste(nposte varchar(7) primary key,nom_poste varchar(20),indIP


varchar(11),foreign key(indIP) references segment(indIP),ad varchar(3),typeposte
varchar(9),nsalle varchar(7),foreign key(nsalle) references salle (nsalle));

desc poste;

create table logiciel(nlog varchar(5) primary key,nomlog varchar(20),dateach Date,version


varchar(7),typelog varchar(9),prix decimal(6,2));

desc logiciel;

create table installer(nposte varchar(7) ,foreign key(nposte) references poste(nposte),nlog


varchar(5),numIns int primary key,dateIns Date,delai int);

desc installer;

create table types(typelp varchar(9) primary key,nomtype varchar(20));

desc types;

insert into segment values('130.120.80','Brin',0);

insert into segment values('130.120.81','Brin',1);

insert into segment values('130.120.82','Brin',2);

select*from segment;

insert into salle values('s01','salle 1',3,'130.120.80');

insert into salle values('s02','salle 2',2,'130.120.80');

insert into salle values('s03','salle 3',2,'130.120.80');

insert into salle values('s11','salle 11',2,'130.120.81');


insert into salle values('s12','salle 12',1,'130.120.81');

insert into salle values('s21','salle 21',2,'130.120.82');

insert into poste values('p1','poste 1','130.120.80',01,'Tx', 's01');

insert into poste values('p2','poste 2','130.120.80',02,'UNIX', 's01');

insert into poste values('p3','poste 3','130.120.80',03,'Tx', 's01');

insert into poste values('p4','poste 4','130.120.80',04,'pcws', 's02');

insert into poste values('p5','poste 5','130.120.80',05,'pcws', 's02');

insert into poste values('p6','poste 6','130.120.80',06,'UNIX', 's03');

insert into poste values('p7','poste 7','130.120.80',07,'Tx', 's03');

insert into poste values('p8','poste 8','130.120.81',01,'UNIX', 's11');

insert into poste values('p9','poste 9','130.120.81',02,'Tx', 's11');

insert into poste values('p10','poste 10','130.120.81',03,'UNIX', 's12');

insert into poste values('p11','poste 11','130.120.82',01,'PCNT', 's21');

insert into poste values('p12','poste 12','130.120.82',02,'pcws', 's21');

select*from poste;

insert into logiciel values('log1','oracle 6','1995-05-13',6.2,'UNIX',3000);

insert into logiciel values('log2','oracle 8','1999-09-15',8,'UNIX',5600);

insert into logiciel values('log3','SQL server','1998-04-12',7,'PCNT',2700);

insert into logiciel values('log4','Front page','1997-06-03',5,'pcws',500);

insert into logiciel values('log5','windev','1997-05-12',5,'pcws',750);

insert into logiciel values('log7','I.I.S.','2002-04-12',2,'PCNT',810);

insert into logiciel values('log8','dreamweaver','2003-09-21',2.0,'Beos',1400);

select*from logiciel ;

insert into types values('Tx','Terminal X-window');


insert into types values('UNIX','Systeme Unix');

insert into types values('PCNT','PC Windows NT');

insert into types values('PCWS','PC Windows');

insert into types values('NC','Network computer');

alter table segment ADD(nbsalle TINYINT(2) DEFAULT 0,nbPoste TINYINT(2) DEFAULT 0);

desc segment;

ALTER TABLE logiciel ADD nbInstall TINYINT(2) DEFAULT 0;

Desc logiciel;

ALTER TABLE Poste ADD nblog TINYINT(2) DEFAULT 0;

Desc poste;

select nposte,typeposte from poste where nposte = 'p8';

select nomlog from logiciel where typelog = 'unix';

select nlog from installer where nposte ='p6';

select nposte from installer where nlog ='log1';

select nlog from logiciel where typelog not in (select typelp from types);

You might also like