n Table

– Avec contraintes d ’intégrité Exemple (SQL 86)

CREATE TABLE COMMANDE (

NC NUMBER UNIQUE NOT NULL,

NV NUMBER NOT NULL

QUANTITE NUMBER(6))

Exemple (SQL 89)

CREATE TABLE COMMANDE (

NC NUMBER PRIMARY KEY,

NV NUMBER NOT NULL REFERENCES VIN, QUANTITE NUMBER(6) CHECK(QUANTITE > 0))

--------------------------------------------------------------------------------------------------

NC NUMBER, PRIMARY KEY (NC),

SQL 92NV NUMBER NOT NULL,

FOREIGN KEY (NV) REFERENCES VIN,

n Table

–    Modification du schéma d ’une table

ALTER TABLE

ADD COLUMN , , …

•  Exemple

ALTER TABLE COMMANDE ADD COLUMN DATE_COM DATE

–    Suppression d ’une table

DROP TABLE

•  Exemple

DROP TABLE COMMANDE

n Clause SELECT

– Forme

SELECT [ ALL | DISTINCT ] { | *} n Clause FROM – Forme

FROM n Exemple

Employe (mat#, nom, adresse, dept, sup)

Projet (num#, designation, budget)

Departement (dept#, dir, appellation)

Role (nume#, nump#, role_emp)


n Exemple

–    Donner les noms et adresses des employés

SELECT nom, adresse

FROM Employe

–    Donner la liste des projets

SELECT * FROM Projet

–    Donner les noms et les départements des employés

SELECT nom, appellation

FROM Employe, Departement

n Clause WHERE

– Forme

WHERE

n Exemple

–  Donner les noms et adresses des employés qui travaillent au

DIRO

SELECT nom, adresse

FROM Employe, Departement

WHERE appellation = ‘DIRO’

–  Donner les noms des employés responsable de projets

SELECT nom

FROM Employe E, Role R

WHERE E.mat = R.nume

AND role_emp = ‘responsable’

n Fonctions de calcul

–  COUNT, SUM,  AVG, MAX, MIN n Exemple

–  Donner le nombre d ’employés qui travaillent au DIRO

SELECT COUNT (*)

FROM Employe, Departement

WHERE appellation = ‘DIRO’

–  Donner le budget total des projets sous la responsabilité de l’employé numéro 35677

SELECT SUM (budget)

FROM Employe E, Role R, Projet

WHERE E.mat = R.nume

AND role_emp = ‘responsable’

AND mat = 35677

n Forme de conditions

–  IN (NOT IN), Donner la liste des directeurs de départements

SELECT *

FROM Employe

WHERE mat IN

(SELECT dir

FROM Departement)

–  ANY ou ALL, Donner la liste des employés responsables de projets

SELECT *

FROM Employe

WHERE mat = ANY

(SELECT nume

FROM Role

WHERE role_emp = ‘responsable’)


Accès aux données

n Forme de conditions

–  IS (NOT) NULL, Donner la liste des employés sans superviseurs

SELECT *

FROM Employe

WHERE sup IS NULL

–  EXISTS, Donner les numéro de projets dans lesquels intervient au moins un employé autre que le responsable

SELECT DISTINCT nump

FROM Role

WHERE EXISTS

(SELECT nump

FROM Role

WHERE role_emp != ‘responsable’)

n Clause GROUP BY

– Forme

GROUP BY

n Exemple

– Donner les noms des employés regroupés par projet

SELECT nom

FROM Employe E, Role R

WHERE E.mat = R.nume

GROUP BY R.nump

n Clause HAVING

–    Forme

HAVING

–    Exemple

Donner les numéros de projets dans lesquels interviennent au moins dix employés autres que le responsable

SELECT nump

FROM Role

WHERE role_emp != ‘responsable’

GROUP BY nump

HAVING COUNT(nume) > 9

n Unions

–    Forme

UNION 

–    Exemple, liste des cadres

SELECT *

FROM Employe

WHERE mat IN ( SELECT dir

FROM Departement)

UNION

SELECT *

FROM Employe

WHERE mat = ANY (            SELECT nume

FROM Role

WHERE role_emp = ‘responsable’)


n Insertion

–    Forme

INSERT INTO

[ ()]

{VALUES () | }

–    Exemple 1

INSERT INTO Employe (mat, nom, adresse, dept, sup)

VALUES (34098, ‘Gilles’, ‘3456, Gaspe, Mtl’, 456, 68735)

–    Exemple 2

INSERT INTO Directeur

SELECT *

FROM Employe

WHERE mat IN ( SELECT dir

FROM Departement)

n Mise à jour

–    Forme

UPDATE

SET { = }+

WHERE { | CURRENT OF }

–    Exemple 1

UPDATE Employe

SET adresse = ‘3456, Gaspe, Mtl’

WHERE mat = 34098

–    Exemple 2

UPDATE Projet

SET budget = budget + 1000

WHERE num IN (SELECT nump FROM Role

WHERE nume = 34098

AND role_emp = ‘ responsable ’)

n Suppression

–    Forme

DELETE FROM

[WHERE { | CURRENT OF }]

–    Exemple 1

DELETE FROM Employe

WHERE mat = 34098

–    Exemple 2

DELETE FROM Projet

WHERE num IN (SELECT nump

FROM Role

WHERE nume = 68754)


SQL intégré (JDBC)

n Introduction

–   SQL peut être intégré dans un langage hôte (C, COBOL, PL/1, PASCAL, JAVA, etc.)

–   Conflit (opération ensembliste vs. opération séquentielle)

• traitement de plusieurs n-uplets

–   Étude du cas du langage Java (JDBC)

Base de données

Chapitre 4

Conception de bases de données Plan du cours

n Introduction n Modèle relationnel n SQL

n Conception


Introduction

n Problème de la conception

– Associer une structure logique aux données. En d'autres termes, trouver les relations et leurs attributs

n Techniques

–    Dépendances fonctionnelles

–    Les formes normales

–    Le modèle Entité-Association

Modélisation sémantique

n Permet de modéliser les donnés n En utilisant différents formalismes – exemples : E/A, UML, … n Une conception de bases de données

qui commence par la modélisation sémantique est dite conception descendante (conception allant du plus  haut niveau vers le plus bas niveau)

Modèle Entité/Association

n Généralités

–   Appelé également Entité/Relation

–   Proposé par Chen en 1976

–   Englobe à l'origine un nombre restreint de concept (entité, association, propriété)

–   Diverses extensions ont été proposé par la suite (contraintes, généralisation agrégation, …)


Concepts

n Entité

–   "Une chose qui peut être identifiée distinctement"

–   Par abus de langage, on confond entité et type d'entité – Exemples :

•  Concrètes : voiture, employé,

•  Abstraites : projet, cours

–   Mais, mariage ???

Concepts

n Entité (suite)

–   On distingue souvent

•  Entité régulière

•  Entité faible dont l'existence dépend d'une autre entité

•  Exemple

–   Employé …. Entité régulière

–   Personne_a_charge …. Entité faible dont l'existence dépend de celle de l'entité Employé

Concepts

n Entité (suite)

– Représentation graphique

Nom de l'entité

Employe

ou

Nom de l'entité

Projet

Concepts

n Propriété

–   Les entités possèdent des propriétés qui les décrivent

–   Appelées également Attributs – Exemples :

•  Pour Employé, matricule, nom, adresse,

•  mais pas le numéro de département

Concepts

n Propriété

–   Elle peut être

• Simple ou composée

–   âge (simple)

–   adresse (composée)

•  Un identificateur (ou clé)

–   code_permanent

•  mono ou multi-valuée

–   nom (mono-valuée)

–   prénom (multi-valuée)

Concepts

n Propriété

–   Elle peut être

• Manquante

–   valeur inconnue

–   non applicable (date_mariage pour un célibataire)

• De base ou dérivée

–   nom (base)

–   ancienneté (dérivée de la date d'embauche et de la date d'aujourd'hui)

–   Quantité totale (dérivé de la somme des quantités)

Concepts

n Propriété

– Représentation graphique

propriété

Parfois, on utilise cette notation pour les propriétés multi-valuées propriété

Parfois, on utilise cette notation pour les identificateurs

Concepts

n Association

–   Est une relation entre entités

–   Même abus de langage entre association et type d'associations

–   Exemple

•  Participe_dans entre Employé et projet

•  Récolte entre Producteur et Vin

–   Comme une entité, elle peut posséder des propriété

Concepts

n Association

–   Elle est dite binaire si elle relie uniquement deux entité, ternaire si elle en relie trois, etc.

–   Chaque participant (entité) possède un rôle dans l'association

–   Elle peut être réflexive

• Exemple : est_mariée_à entre personne et personne

Concepts n Association

–   Cardinalités

•  Indiquent le nombre maximal et minimal d'occurrences d'une association pour une occurrence d'un participant.

•  Par exemple pour l'association contrôle entre département et projet

–   un département contrôle 0 ou plusieurs projet

–   un projet est contrôlé par un et un seul département

• Les différentes possibilités sont

– 0,1 ; 1,1 ; 0,N ; 1,N

Concepts n Association

– Représentation graphique

association

Parfois, on utilise cette notation pour une association impliquant une entité faible association