PPE 1-3 - Jordan D.

Création de la Base de données pour le
système réservation des salles
PPE 1-3
Base de données
Jordan Dutaillis
Steveen Hanta
Mathieu Collin
Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
1
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
Contenu
I.
Introduction : ................................................................................................................................... 3
II.
Le système de gestion de base de donnée (SGBD) ......................................................................... 3
A.
B.
III.
Comparatif des différents SGBD.................................................................................................. 3
1.
Apache Derby .......................................................................................................................... 3
2.
MySQL Entreprise .................................................................................................................... 3
3.
Oracle DataBase 11g Standard Edition One ............................................................................ 4
4.
Microsoft SQL Server 2012 Standard (sort le 1 avril 2013) ..................................................... 4
Le SGBD retenu............................................................................................................................ 5
Le Processus de création de la base de données ........................................................................ 5
A.
Le Modèle Conceptuel de Données ............................................................................................ 6
B.
Le Modèle Logique des Données................................................................................................. 7
C.
Le Modèle Physique de Données ................................................................................................ 8
IV.
Les Scripts SQL ............................................................................................................................. 9
A.
Le Script de création de la base de données ............................................................................... 9
B.
Le Script d’insertion des données dans la base de données ..................................................... 12
C.
Le jeu d’essai ............................................................................................................................. 16
D.
Conclusion ................................................................................................................................. 17
2
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
CRÉATION DE BASE DE DONNÉES
I.
Introduction :
La maison des ligues de Lorraine autrement appelée M2L est une entité pourvu de salles de réunion
muni d’ordinateurs. Ces salles sont réservables depuis leur site Internet. Pour cela, le client doit
fournir des informations essentiels, tels que son nom, son adresse, le type de réservation, la durée de
réservation ou encore la salle dans laquelle il souhaite réserver. Ces réservations sont reliées à
différents problèmes. En effet, quels types de système de gestion de base de données choisir ?
Comment le mettre en place ? Comment sécuriser ces données?
II.
Le système de gestion de base de donnée (SGBD)
A.
Comparatif des différents SGBD
De nos jours, il existe un grand nombre de SGBD au monde. L’équipe de professionnel de Kos
Informatique a sélectionné les 4 meilleurs SGBD et les ont comparé pour choisir celui qui était le
mieux adapté au attente du site M2L. Leurs choix ont été faits à partir de leur notoriété et des
différents avis posté sur le net par divers utilisateurs expérimentés. C’est pourquoi, ci-après se trouve
une comparaison des différents SGBD :
1.
Apache Derby
Apache Derby est une base donnée Open Source (Licence libre) mis en œuvre entièrement par
l’environnement de programmation script Java. Il est basé sur les environnements script Java, Java
DataBase Connectivity (JDBC) et SQL. Apache possède un faible encombrement : seulement 2.6 Mo.
Le système ne nécessite aucune installation, il peut directement être embarqué sur l’application, il
est d’ailleurs auto-administré et auto-optimisé, ce qui permet à l’utilisateur de se décharger de toute
administration. Le point faible de ce moteur de base de données est son temps de préparation de
requête mais des solutions ont été mis en place pour succomber à ce malus, une stratégie de
réutilisation de requête est intégré.
2.
MySQL Entreprise
MySQL Entreprise est une base de données distribuée sous une double licence (GPL). Cependant elle
prend 207 Mo de mémoire ce qui n’est pas négligeable. Son interface est simple d’utilisation et la
sécurité des données est au point grâce à un sous-logiciel : MySQL Entreprise Backup. De plus c’est le
logiciel le plus utilisé au monde, ce qui démontre ses performances. Il coute 11 844,00 € pour un
serveur et 3 ans d’accès.
Le tableau de bord du logiciel permet une supervision de chaque serveur SQL que dispose
l’utilisateur dans une seule console qui fournit un récapitulatif. L’utilisateur pourra donc être averti
de quelconque nouvelle requête effectué ou modifiée affectant le système. Il est aussi possible de
visualiser les l’état de la base de données sur les serveurs graphiquement.
3
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
De plus, le moteur de base données intègre des conseillers qui permettent à l’utilisateur d’être averti
des bogues spécifiques qui peuvent avoir un impact sur le système, les problèmes de paramétrages
et de configurations sont aussi reportés auprès de l’utilisateur. Les conseillers peuvent aussi
diagnostiquer les problèmes au niveau des performances matérielles. Ces conseillers peuvent aussi
prendre un rôle actif sur le système, il peuvent conseillers l’utilisateur mais ils peuvent aussi avoir un
rôle de supervision, l’utilisateur peut le programmer pour des tâches automatiques telles que la
collecte automatiques de données et les stocker. Ils peuvent aussi effectuer des tâches spécifiques
selon un planning.
3.
Oracle DataBase 11g Standard Edition One
Oracle DataBase est un des systèmes de gestion de base de données relationnel (SGBDR) les plus
utilisés au monde, fournit par Oracle Corporation. Il permet à plusieurs utilisateurs d’accéder
simultanément aux données tout en garantissant une disponibilité et des mécanismes de
récupération en cas d’incident. Celui-ci offre des améliorations en termes de performance, de
gestion, d’applications réseau et de routage. Contrairement à la version Standard, il n’y a pas de
limite de stockage.
Il est utilisable sur un serveur supportant un maximum de deux sockets, le prix de ce logiciel s’élève à
4 521,44 € HT pour 1 serveur et 5 licences d’accès (138.242 € par licence supplémentaire).
http://www.microatlantic.com/oracle/Oracle%20Database%2011g.pdf
4.
Microsoft SQL Server 2012 Standard (sort le 1 avril 2013)
Microsoft SQL Server 2012 Standard s’occupe des fonctions de bases de base, d'analyse et de
reporting basiques. Le système d’exploitation se vend sous une licence payante. Néanmoins, Pour
couvrir un serveur physique, une licence devra être acquise pour chacun des cœurs de l’ordinateur
hôte, avec un minimum de 4 « licences cœur » par processeur physique. L’application est assez
gourmande en espace disque puisque rien que son fichier d’installation prend 4.02 Go d’espace
disque.
Etant une solution Microsoft, Microsoft SQL Server 2012 offre de meilleures performances pour
certaines librairies du Framework.NET. Un des gros avantages de cette plateforme est qu’elle
possède des fonctions avancé d’audit et d’administration simple à utiliser et à configurer. La stratégie
de sécurité peut soit être définie par l’IT local ou soit par une configuration recommandé (selon de le
type d’organisation). Cette solution de ne possède pas de limite de stockage de données.
Cette solution est particulièrement orientée sur le Cloud, en effet Windows Azure qui est le service
de Cloud Computing de Microsoft est associé à SQL Server 2012.
Enfin, son prix est de 3 574,74 € HT pour 1 serveur et 10 licences d’accès.
4
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
B.
Groupe B1
Le SGBD retenu
Kos Informatique a du sélectionner le SGBD adapté aux besoins de M2L. De plus, la répartition du
budget précédent de 35 000€ nous laisse le choix entre un logiciel payant (3802.04€ maximum) ou
non. Kos Informatique a sélectionné les 4 meilleures bases de données du marché, payant ou non, et
les a comparé pour choisir la meilleure, selon les critères suivants :
-
-
Interface facile d’utilisation : Le professionnel manipulant la SGBD devra pouvoir facilement
manipuler les données grâce au plus simple mais efficace des SGBD.
Interface graphique ET ligne de commande OU uniquement ligne de commande : En effet,
l’utilisation d’une base de données requiert un personnel expérimenté qui connait déjà
toutes les facettes de la ligne de commande. Pour s’aider, une interface graphique pourra
être envisagée.
Environnement stable : Le logiciel choisis devra être stable, donc il ne devra jamais planter ou
avoir de « bug ».
Au prix de 3 574,74 €, Microsoft Server SQL 2012 Standard semble être le système d’exploitation
répondant le mieux aux besoins de M2L. En effet, son interface, d’après les nombreuses captures
d’écrans comparés prises sur le net, semble être assez simple d’utilisation faisant appel à une
interface graphique ainsi qu’à une interface en ligne de commande.
C’est pourquoi Kos Informatique a décidé de choisir Microsoft Server SQL 2012 Standard afin de
gérer entièrement le système de gestion de base de données de M2L.
III.
Le Processus de création de la base de données
La mise en place d’une base de données requiert 3 étapes. Nous devons donc tout d’abord effectué
le MCD (Modèle conceptuel de Données), le MLD (Modèle Logique de Données) et le MPD (Modèle
Physique de Données) qui est la structure finale de la base de données. Ces 3 modèles doivent être
faits dans cet ordre-là. Ces 3 différentes étapes vont nous permettre de réaliser la base de données
afin de répartir aux mieux les différentes informations que l’utilisateur va renseigner lors d’une
réservation de salles. Ci-après se trouve donc le premier modèle : le MCD
Le MCD ci-joint a pour but de conceptualiser l’application. Il y regroupe donc 8 tables : Clients, Type
Client, Service, Confirmation, Tarif, Réservation, Type Réservation, Salle, Type Salle. Toutes ces tables
sont reliées avec différentes cardinalités.
5
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
A.
Groupe B1
Le Modèle Conceptuel de Données
6
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
B.
Groupe B1
Le Modèle Logique des Données
Ensuite se trouve l’avant dernière étape du modèle de données : Le Modèle Logique de Données (MLD).
7
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
C.
Groupe B1
Le Modèle Physique de Données
8
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
IV.
Groupe B1
Les Scripts SQL
A.
Le Script de création de la base de données
Voici le script de création de la base de données de réservation des salles spécifique a Microsoft
Server 2012.
/*==============================================================*/
/* Nom de SGBD : Microsoft SQL Server 2008
*/
/* Date de création : 02/03/2013 15:21:06
*/
/*==============================================================*/
if exists (select 1
from sysobjects
where id = object_id('CLIENT')
and
type = 'U')
drop table CLIENT
GO
if exists (select 1
from sysobjects
where id = object_id('CONFIRMATION')
and
type = 'U')
drop table CONFIRMATION
GO
if exists (select 1
from sysobjects
where id = object_id('RESERVATION')
and
type = 'U')
drop table RESERVATION
GO
if exists (select 1
from sysobjects
where id = object_id('SALLE')
and
type = 'U')
drop table SALLE
GO
if exists (select 1
from sysobjects
where id = object_id('SERVICE')
and
type = 'U')
drop table SERVICE
GO
if exists (select 1
from sysobjects
where id = object_id('TARIF')
and
type = 'U')
drop table TARIF
GO
if exists (select 1
from sysobjects
9
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
where id = object_id('TYPE_CLIENT')
and
type = 'U')
drop table TYPE_CLIENT
GO
if exists (select 1
from sysobjects
where id = object_id('TYPE_RESERVATION')
and
type = 'U')
drop table TYPE_RESERVATION
GO
if exists (select 1
from sysobjects
where id = object_id('TYPE_SALLE')
and
type = 'U')
drop table TYPE_SALLE
GO
/*==============================================================*/
/* Table : CLIENT
*/
/*==============================================================*/
CREATE table CLIENT (
NO_CLI
NO_TYPECLI
NO_TARIF
NOM_CLI
PREN_CLI
NO_TEL
NO_FAX
EMAIL
NO_RUE
NOM_RUE
CODEPOSTAL
NOM_VILLE
CONSTRAINT PK_CLIENT
)
GO
numeric(5)
not null,
numeric(10)
not null,
numeric(5)
not null,
text
not null,
text
not null,
varchar(10)
not null,
varchar(10)
null,
varchar(30)
null,
numeric(5)
null,
varchar(30)
null,
numeric(5)
null,
varchar(30)
not null,
primary key nonclustered (NO_CLI)
/*==============================================================*/
/* Table : CONFIRMATION
*/
/*==============================================================*/
CREATE table CONFIRMATION (
NO_CONFIRM
numeric(1)
not null,
DATE_CONFIRM
datetime
not null,
CONSTRAINT PK_CONFIRMATION primary key nonclustered (NO_CONFIRM)
)
GO
/*==============================================================*/
/* Table : RESERVATION
*/
/*==============================================================*/
CREATE table RESERVATION (
NO_RESERVE
numeric(15)
NO_CLI
numeric(5)
NO_SALLE
numeric(5)
NO_TYPE
numeric(10)
NO_SERVICE
numeric(10)
NO_CONFIRM
numeric(1)
DATE_DEBUT
datetime
HEURE_DEBUT
datetime
DUREE_RESERVE
datetime
not null,
not null,
not null,
not null,
not null,
null,
not null,
not null,
not null,
10
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
PRIX_RESERVE
numeric(5,2)
not null,
DESCRIPTION_BREVE
varchar(50)
not null,
DESCRIPTION_COMPLETE varchar(500)
not null,
CONSTRAINT PK_RESERVATION primary key nonclustered (NO_RESERVE)
)
GO
/*==============================================================*/
/* Table : SALLE
*/
/*==============================================================*/
CREATE table SALLE (
NO_SALLE
numeric(5)
not null,
NO_TYPESALLE
numeric(5)
not null,
NOM_SALLE
varchar(15)
not null,
BATI_SALLE
varchar(15)
not null,
CAPAC_SALLE
numeric(5,0)
not null,
EQUIP_SALLE
varchar(35)
not null,
CONSTRAINT PK_SALLE primary key nonclustered (NO_SALLE)
)
GO
/*==============================================================*/
/* Table : SERVICE
*/
/*==============================================================*/
CREATE table SERVICE (
NO_SERVICE
numeric(10)
not null,
LIBELLE_SERVICE
varchar(15)
not null,
PRIX_SERVICE
numeric(5,2)
not null,
CONSTRAINT PK_SERVICE primary key nonclustered (NO_SERVICE)
)
GO
/*==============================================================*/
/* Table : TARIF
*/
/*==============================================================*/
CREATE table TARIF (
NO_TARIF
numeric(5)
not null,
NIVEAU_TARIF
numeric(1)
not null,
CONSTRAINT PK_TARIF primary key nonclustered (NO_TARIF)
)
GO
/*==============================================================*/
/* Table : TYPE_CLIENT
*/
/*==============================================================*/
CREATE table TYPE_CLIENT (
NO_TYPECLI
numeric(10)
not null,
TYPECLI
varchar(15)
not null,
CONSTRAINT PK_TYPE_CLIENT primary key nonclustered (NO_TYPECLI)
)
GO
/*==============================================================*/
/* Table : TYPE_RESERVATION
*/
/*==============================================================*/
CREATE table TYPE_RESERVATION (
NO_TYPE
numeric(10)
not null,
LIBELLE_TYPE
varchar(15)
not null,
DATE
datetime
not null,
CONSTRAINT PK_TYPE_RESERVATION primary key nonclustered (NO_TYPE)
)
GO
11
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
/*==============================================================*/
/* Table : TYPE_SALLE
*/
/*==============================================================*/
CREATE table TYPE_SALLE (
NO_TYPESALLE
numeric(5)
not null,
NOM_TYPESALLE
varchar(10)
not null,
TYPE_SALLE
varchar(10)
not null,
TARIF_SALLE
money
not null,
CONSTRAINT PK_TYPE_SALLE primary key nonclustered (NO_TYPESALLE)
)
GO
B.
Le Script d’insertion des données dans la base de données
INSERT INTO CLIENT (No_Cli, Nom_Cli, Pren_Cli, Tel_Cli, Fax_Cli,
Mail_Cli, No_Rue, Nom_Rue, CdPostale, Ville)
VALUES
(1,'Martin','Emma','01 32 54 00 00','01 32 54 01
01','[email protected]',11,'rue des Aires',34677,'Saint-Maurice')
(2,'Bernard','Enzo','01 32 54 02 01','01 32 54 00
05','[email protected]',22,'rue de Berdiansk',34400,'Giloque')
(3,'Dubois','Ines','01 32 54 01 02','01 32 54 00
06','[email protected]',33,'avenue Pierre-Fraysse',56700,'Grick')
(4,'Thomas','Leo','01 32 54 07 08','01 32 54 00
07','[email protected]',44,'rue de la République',48600,'Martinasur-Seine')
(5,'Robert','Tom','01 32 54 08 07','01 32 54 00
09','[email protected]',55,'ruelle des Amoureux',46778,'Val-duSigne')
(6,'Richard','Eva','01 32 54 80 07','01 32 54 00
10','[email protected]',66,'rue Lavoisier',50003,'Leclerc')
(7,'Petit','Paul','01 32 54 50 08','01 32 54 00
11','[email protected]',77,'rue Ampère',45000,'Saint-Denis')
(8,'Durand','Axel','01 32 54 02 03','01 32 54 00
12','[email protected]',88,'rue Louis-Antelme',34000,'Tampon')
(9,'Leroy','Lou','01 32 54 00 01','01 32 54 00
14','[email protected]',99,'rue Molière',32000,'Méro')
(10,'Moreau','Elsa','01 32 54 03 00','01 32 54 00
15','[email protected]',1010,'rue Emile-Combes',38000,'Cerly')
GO
12
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
INSERT INTO SERVICE (No_service, Libelle_Service, Prix_Service)
VALUES
(1,'Orangina',10)
(2,'Ordinateur particulier',25)
(3,'Logiciel particulier',15)
(4,'Tapis de souris',0)
(5,'Oasis',1)
(6,'Coca-Cola',1)
(7,'Café',1)
(8,'Eau',0)
(9,'Thé standard',0)
(10,'Thé Exotique',1)
GO
INSERT INTO TYPE_RESERVATION (No_Type, Libelle_Type, Périodicité)
VALUES
(1,'occasionnel','Ponctuelle')
(2,'régulier','quotidient')
(3,'régulier','mensuelle')
(4,'régulier','trimestrielle')
(5,'régulier','semestrielle')
(6,'régulier','annuelle')
(7,'régulier','hebdomadaire')
(8,'régulier','quotidien')
GO
13
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
INSERT INTO SALLE (No_Salle, Nom_Salle, Bati_Salle, Surface_Salle,
Capac_Salle, Equip_Salle)
VALUES
(1,'Majorelle','Batiment A',30,40,'Prise ethernet')
(2,'Grüber','Batiment A',25,12,'Vidéoprojecteur')
(3,'Lamour','Batiment C',,10,'Videoprojecteur')
(4,'Longway','Batiment B',30,15,'Prise ethernet')
(5,'Daum','Batiment D',27,15,'Videoprojecteur')
(6,'Gallé','Batiment B',22,10,'Prise ethernet')
(7,'Corbin','Batiment C',20,10,'Prise ethernet')
(8,'Baccarat','Batiment B',20,15,'Videoprojecteur')
GO
INSERT INTO RESERVATION (No_Reserve, Date_debut, Heure_debut,
Duree_Reserve, Description_reserve, No_Cli, No_Service, No_Salle,
No_Type)
VALUES
(1,'15/03/2013','09h00','01h00','REUNION',1,1,1,1)
(2,'22/03/2013','11h30','02h00','LOCATION',2,2,2,2)
(3,'27/03/2013','15h45','01h15','RENDEZ-VOUS VISITEUR',3,3,3,3)
(4,'01/04/2013','17h00','02h00','CONFERENCE',4,4,4,4)
(5,'10/04/2013','09h30','02h00','RENDEZ-VOUS VISITEUR',5,5,5,5)
(6,'24/04/2013','13h30','03h30','REUNION',6,6,6,6)
(7,'04/05/2013','08h30','02h00','CONFERENCE',7,7,7,7)
(8,'26/05/2013','15h00','01h30','REUNION',8,8,8,8)
(9,'05/06/2013','14h00','03h00','LOCATION',9,9,9,9)
(10,'21/06/2013','16h00','01h00','REUNION',10,10,10,10)
GO
14
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
INSERT INTO CONFIRMATION (No_confirm, Date_Confirm)
VALUES
(1,'01/01')
(2,'02/02')
(3,'03/03')
(4,'04/04')
(5,'05/05')
GO
INSERT INTO TYPE_CLIENT (No_TypeCli, TypeCli)
VALUES
(1,'association')
(2,'lycée')
(3,'collèges')
(4,'club sportif')
(5,'commité départementaux')
(6,'société privée')
GO
INSERT INTO TARIF (No_tarif, Niveau_Tarif)
VALUES
(1,'club sportif’,1)
(2,’Association’,2)
(3,'')
(4,'autres',4)
GO
15
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
INSERT INTO TYPE_SALLE (No_TypeSalle, Type_Salle, Tarif_Salle)
VALUES
(1,'réunion',50)
(2,'multimédia',60)
(3,'amphithéâtre',70)
(4,'convivialité',20)
GO
C.
Le jeu d’essai
Afin d’assurer l’étanchéité de la base de données conçu, l’équipe Kos Informatique a créé un jeu
d’essai, qui fait appel à plusieurs requêtes pour chaque tables. Une fois ces données ajoutées, elles
permettront d’être sûre que la base de donnée soit correct, puis supprimer pour laisser place aux
valeurs réelles.
/*Commande pour vérifier le nom et prénom des clients en les triant
dans l'ordre des noms: */
SELECT Nom_Cli, Pren_Cli
FROM CLIENT
ORDER BY Nom_Cli;
/*Commande pour vérifier l'intégralité de la table Réservation: */
SELECT *
FROM RESERVATION;
/* Commande pour vérifier les 3 premiers clients:*/
SELECT *
FROM CLIENT
LIMIT 3;
/* Commande pour vérifier le numéro et le prix des services 8 à 9 */
SELECT No_Service, Prix_Service
FROM
SERVICE
LIMIT 7,2;
16
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko
BTS SIO. PPE 1-3 KOS INF☼
Groupe B1
/* Commande pour compter le nombre total de confirmation */
SELECT COUNT(*)
AS nbconfirmation
FROM CONFIRMATION
/* Commande pour sélectionner les types de salles qui ont un coûts
supérieur à 50€ */
SELECT No_TypeSalle, Type_Salle, Tarif_Salle
FROM TYPESALLE
WHERE Tarif_Salle > 50
D.
Conclusion
La base de données conçue par l’équipe de Kos Informatique, s’appliquera donc dans le SGBD
Microsoft Server SQL 2012 Standard du site M2L, choisis méticuleusement. De plus, ces informations
seront remplies par le client, dans le processus de réservation en ligne du site Internet de la Maison
des Ligues De lorraines.
17
Jordan Dutaillis / Steveen Hanta / Mathieu Collin / Ismaël Sacko