Bonjour,

J'ai un projet de SGBD à rendre pour mi-mars avec création du modèle logique, modèle conceptuel et SQL. J'aimerai beaucoup avoir un avis sur ce que j'ai déjà fait jusqu'a maintenant. De plus, oracle ne voulant absolument pas fonctionner chez moi, je dois attendre la semaine prochaine pour tester mon code en cours donc si une âme généreuse pouvait au moins lancer le code et me dire si tout est correct. Merci

-- L'instruction suivante permet de changer le format des dates : 
ALTER SESSION SET nls_date_format='DD-MM-YYYY'; 

CREATE TABLE Commune 
  ( 
     idcommune  INTEGER, 
     codepostal INTEGER, 
     nomcommune VARCHAR2(50) NOT NULL, 
     distagence INTEGER NOT NULL, 
     PRIMARY KEY (idcommune) 
  ); 
 
INSERT INTO Commune VALUES (1,6000,'Charleroi',5);
  
CREATE TABLE Quartier 
  ( 
     idquartier   INTEGER, 
     libellequart VARCHAR2(50) NOT NULL, 
     idcom INTEGER,
     PRIMARY KEY (idquartier), 
     FOREIGN KEY (idcom) references Commune 
  ); 
  
INSERT INTO Quartier VALUES (1,'Sud',1);
INSERT INTO Quartier VALUES (2,'Nord',1);
INSERT INTO Quartier VALUES (3,'Centre',1);
  
CREATE TABLE Typelogement 
  ( 
     typelogement VARCHAR2(50), 
     charge  INTEGER NOT NULL, 
     PRIMARY KEY (typelogement) 
  ); 
 
INSERT INTO Typelogement VALUES ('T1',25);
INSERT INTO Typelogement VALUES ('T2',25);
INSERT INTO Typelogement VALUES ('T3',25);

CREATE TABLE Logement 
  ( 
     idlogement  INTEGER, 
     numero      INTEGER NOT NULL, 
     rue         VARCHAR2(50) NOT NULL, 
     superficie  INTEGER NOT NULL, 
     distcommune INTEGER NOT NULL,
	 typelog VARCHAR2(50),
	 idquart INTEGER,
     primary key (idlogement), 
     foreign key (typelog) references typelogement, 
     foreign key (idquart) references quartier 
  ); 
  
INSERT INTO Logement VALUES (1,5,'Rue de la Belotte',150,1,'T1',1);

CREATE TABLE Contrat 
  ( 
     numero     INTEGER, 
     datesign   DATE NOT NULL, 
     datedebut  DATE NOT NULL, 
     datefin    DATE NOT NULL, 
     idlog INTEGER NOT NULL, 
     primary key (numero), 
     foreign key (idlog) references logement, 
     CHECK (datedebut<datefin) 
  ); 
  
INSERT INTO Contrat VALUES (1,'16-02-2012','01-03-2012','28-02-2013',1);

CREATE TABLE Personne 
  ( 
     id        INTEGER, 
     nom       VARCHAR2 (30) NOT NULL, 
     prenom    VARCHAR2 (20) NOT NULL, 
     datenaiss DATE, 
     primary key (id), 
     UNIQUE (nom, prenom, datenaiss) 
  ); 

INSERT INTO Personne 
VALUES (1,'Di Toro','Marco','18-02-1980'); 


CREATE TABLE Signataire 
  ( 
     idpersonne INTEGER NOT NULL, 
     numcontrat INTEGER NOT NULL, 
     foreign key (idpersonne) references personne, 
     foreign key (numcontrat) references contrat 
  ); 
  
INSERT INTO Signataire VALUES (1,1);
  
SELECT * FROM Personne; 
SELECT * FROM Signataire; 
SELECT * FROM Contrat; 
SELECT * FROM Logement; 
SELECT * FROM Typelogement; 
SELECT * FROM Quartier; 
SELECT * FROM Commune; 

DROP TABLE Commune CASCADE CONSTRAINTS; 
DROP TABLE Quartier CASCADE CONSTRAINTS; 
DROP TABLE Typelogement CASCADE CONSTRAINTS; 
DROP TABLE Logement CASCADE CONSTRAINTS; 
DROP TABLE Contrat CASCADE CONSTRAINTS; 
DROP TABLE Personne CASCADE CONSTRAINTS; 
DROP TABLE Signataire;

pourquoi supprimer les contraintes a la fin ?
et pourquoi attribuer une id manuellement ?

(je ne suis pas un spécialiste oracle je m'interroge)

++

Je ne l'ai pas lancé mais cela me semble très propre.Quelques remarques de broutille:
1) Le varchar2(50) pour typelogement me semble un peu surdimensionné.
2) Je préfère quand les clefs primaires sont explicitement NOT NULL, plutôt que ce soit fait implicitement par la contrainte
3) Ce n'est pas courant, mais il y a des gens qui n'ont pas de prénom (j'en connais) - des gens d'origine asiatique naturalisés, en général.
4) Quand la clef primaire est un numéro arbitraire, il est sain d'avoir une contrainte d'unicité ailleurs comme tu l'as fait pour Personne. Tu devrais le faire partout, sinon tu risques des doublons qui techniquement (grâce au numéro différent) n'en sont pas.
5) La remarque de karuo à propos des id attribués manuellement n'est pas dépourvue de sens, même si je comprends bien que pour un test c'est plus facile de rentrer quelques valeurs à la main. Tu devrais avoir quelques séquences, mais SURTOUT PAS de trigger comme on voit souvent pour faire de l'auto-incrément façon SQL Server ou MySQL - seq.nextval et seq.currval dans les commandes INSERT.
6) Personnellement je préfère nommer les contraintes, sinon Oracle leur attribue un nom système imbuvable qui ressort parfois dans les messages d'erreur, à la grande frayeur de l'utilisateur final.

Encore une fois, c'est nettement mieux que ce que l'on voit trop souvent ...

Merci pour votre aide

@pythonneries :
1)en effet, je l'ai diminué.
2)ok, je prends note et je corrige !
3)il est demandé explicitement dans le projet que nom, prénom et date de naissance soient des champs obligatoires.
4)ok, je prends note et je corrige ! Par contre je ne vois pas commente faire pour la table logement car je ne peux pas mettre d'unicité entre, par exemple, l'idlogement et le numéro (deux logements peuvent avoir le même numéro) ou la rue (deux logements dans la même rue), etc...

EDIT : je suis bête... je n'ai qu'à faire un UNIQUE sur idlogement, numero et rue, ça m'est venu en relisant

Même chose pour la table contrat ou alors est-il possible de mettre un unique avec une clé étrangère ? (UNIQUE (numero, idlogement)) possible car un même logement n'aura pas, à priori, deux fois le même contrat.
5)on a pas encore vu ce que kaoru et toi expliquez là. Donc, pour ce projet il faut entrer les id manuellement.
6)en effet, c'est même indiqué sur le projet mais sur la fin (-à nommer de manière adéquate toutes les contraintes)du coup je l'ai zappé, je ferai ça après.

J'ai fait quelques màj sur le code, dont le rajout de code postal en clé primaire sur la table commune (il existe des commmune avec un nom différent mais un CP identique, par exemple, Chatelêt et Bouffioulx ont tout deux le cp 6200) et j'ai rajouté quelques clause CHECK par-ci par-là.

voici le code actuellement :

-- L'instruction suivante permet de changer le format des dates : 
ALTER SESSION SET nls_date_format='DD-MM-YYYY'; 

CREATE TABLE Commune 
  ( 
     idcommune INTEGER NOT NULL, 
     codepostal INTEGER NOT NULL, 
     nomcommune VARCHAR2(50) NOT NULL, 
     distagence INTEGER NOT NULL, 
     PRIMARY KEY (idcommune, codepostal),
	 CHECK (distagence>=0 AND distagence<=250)
  ); 
 
INSERT INTO Commune VALUES (1,6000,'Charleroi',5);
  
CREATE TABLE Quartier 
  ( 
     idquartier INTEGER NOT NULL, 
     libellequart VARCHAR2(50) NOT NULL, 
     idcom INTEGER,
     codepost INTEGER,
     PRIMARY KEY (idquartier), 
     FOREIGN KEY (idcom,codepost) references Commune,
     UNIQUE (idquartier,libellequart)
  ); 
  
INSERT INTO Quartier VALUES (1,'Sud',1,6000);
INSERT INTO Quartier VALUES (2,'Nord',1,6000);
INSERT INTO Quartier VALUES (3,'Centre',1,6000);
  
CREATE TABLE Typelogement 
  ( 
     typelogement VARCHAR2(20)
         CHECK (typelogement IN ('maison','studio','T1','T2','T3')), 
     charge INTEGER NOT NULL, 
     PRIMARY KEY (typelogement),
	 CHECK (charge>=0)
  ); 
 
INSERT INTO Typelogement VALUES ('studio',20); 
INSERT INTO Typelogement VALUES ('T1',25);
INSERT INTO Typelogement VALUES ('T2',50);
INSERT INTO Typelogement VALUES ('maison',65);
INSERT INTO Typelogement VALUES ('T3',75);

CREATE TABLE Logement 
  ( 
     idlogement INTEGER NOT NULL, 
     numero INTEGER NOT NULL, 
     rue VARCHAR2(50) NOT NULL, 
     superficie INTEGER, 
     distcommune INTEGER,
     typelog VARCHAR2(50),
     idquart INTEGER,
     primary key (idlogement), 
     foreign key (typelog) references typelogement, 
     foreign key (idquart) references quartier,
	 CHECK (superficie>=0)
  ); 
  
INSERT INTO Logement VALUES (1,5,'Rue de la Belotte',150,1,'T1',1);

CREATE TABLE Contrat 
  ( 
     numero INTEGER NOT NULL, 
     datesign DATE NOT NULL, 
     datedebut DATE NOT NULL, 
     datefin DATE NOT NULL,
     loyer INTEGER NOT NULL,
     idlog INTEGER NOT NULL, 
     primary key (numero), 
     foreign key (idlog) references logement, 
     CHECK (datedebut<datefin AND datesign<datedebut AND loyer>=0) 
  ); 
  
INSERT INTO Contrat VALUES (1,'16-02-2012','01-03-2012','28-02-2013',500,1);

CREATE TABLE Personne 
  ( 
     id INTEGER NOT NULL, 
     nom VARCHAR2 (30) NOT NULL, 
     prenom VARCHAR2 (20) NOT NULL, 
     datenaiss DATE, 
     primary key (id), 
     UNIQUE (nom, prenom, datenaiss) 
  ); 

INSERT INTO Personne 
VALUES (1,'Di Toro','Marco','18-02-1980'); 


CREATE TABLE Signataire 
  ( 
     idpersonne INTEGER NOT NULL, 
     numcontrat INTEGER NOT NULL, 
     foreign key (idpersonne) references personne, 
     foreign key (numcontrat) references contrat 
  ); 
  
INSERT INTO Signataire VALUES (1,1);
  
SELECT * FROM Personne; 
SELECT * FROM Signataire; 
SELECT * FROM Contrat; 
SELECT * FROM Logement; 
SELECT * FROM Typelogement; 
SELECT * FROM Quartier; 
SELECT * FROM Commune; 

DROP TABLE Commune CASCADE CONSTRAINTS; 
DROP TABLE Quartier CASCADE CONSTRAINTS; 
DROP TABLE Typelogement CASCADE CONSTRAINTS; 
DROP TABLE Logement CASCADE CONSTRAINTS; 
DROP TABLE Contrat CASCADE CONSTRAINTS; 
DROP TABLE Personne CASCADE CONSTRAINTS; 
DROP TABLE Signataire;

Par contre je bloque complètement sur un des point du projet : on demande d'interdire que la superficie d'un studio soit supérieure à 20m². Je pense qu'il faut mettre une clause check dans la table logement mais aucune idée du comment vérifier que pour le cas ou l'on encode un studio qui lui est choisis dans la table typelogement)

Prochain cours de GBD mardi, je pourrai lancé mon code sous oracle en espérant ne pas avoir trop de mauvaise surprises

Encore merci pour l'aide apportée !

Bonjour,

J'ai trouvé pour la superficie du studio !

Par contre je bloque complètement depuis plusiseurs jours sur un trigger et une requête j'en devient dingue !!!

Je dois faire un trigger qui met à jour le nombre de logement par commune chaque fois que l'on ajoute ou supprime un logement (j'ai donc ajouté un champs nbrlogement INTEGER dans la table Commune)

Et pour la requête je dois afficher le nom, prénom, le quartier et la commune de tout les locataires dont le nom commence par DE.