Aide script SQL

Version imprimable

1 pièce(s) jointe(s)

Aide script SQL

Bonsoir à tous,

Après avoir reçu l'aide de l'excellent JPhi33 sur le forum Modélisation pour mon MCD, je me permets de poste dans cette catégorie afin de vous poser quelques questions quant au code SQL que AMCDesigner a généré...

(lien vers le post en question: ICI)

Je précise que je travaille sous mysql 5.1.30, avec phpmyadmin 3.1.1.
Egalement, mon MCD est en pièce-jointe.

Après une recherche Google, il semble que Phpmyadmin ne permette pas d'utiliser les contraintes d'intégrité... il m'est donc impossible de tester si celles que j'ai ajoutées à mon script sont correctes... (j'obtiens une erreur #1064 à chaque fois)
C'est pour cela que je viens demander un peu d'aide ici. N'y a-t-il pas un moyen pour que PhpMyAdmin accepte ces contraintes d'intégrité ? Sinon, pouvez-vous vérifier si ma syntaxe est correcte ?

Je copie mon script ci-dessous... c'est assez long, j'espère ne pas enfreindre une loi du forum :roll:

Merci beaucoup :king: !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
create table REGION
(
    reg_nom                    CHAR(30) UNIQUE       not null,
    reg_altitude integer       CONSTRAINT Raltitude CHECK (reg_altitude between 0 and 10000),
    primary key (reg_nom)
)ENGINE=innoDB;
 
create table CLIENTS
(
    C_num integer              UNIQUE               not null,
    C_nom                      CHAR(30)               not null,
    C_CP integer                                         not null,
    C_ville                    CHAR(30)                 not null,
    C_rue                      CHAR(30)                not null,
    C_tel integer,
    primary key (C_num)
)ENGINE=innoDB;
 
create table SAISON
(
    S_nom                      CHAR(10)    not null   CONSTRAINT Nomsaison CHECK (S_nom IN ('printemps', 'été', 'automne', 'hiver')),
    S_datedebut DATE,
    S_datefin   DATE,
    primary key (S_nom)
)ENGINE=innoDB;
 
create table TYPE_CHAMBRE
(
    type_typechambre           CHAR(10)    not null   CONSTRAINT Tchambre CHECK (type_typechambre IN ('Suite', 'Simple')), 
    primary key (type_typechambre)
)ENGINE=innoDB;
 
create table CATEGORIE_HOTEL
(
    CATH_etoile integer        not null   CONSTRAINT Cathotel CHECK (CATH_etoile between 1 and 5),
    primary key (CATH_etoile)
)ENGINE=innoDB;
 
create table Type_SDB
(
    type_typesdb        CHAR(10)           not null   CONSTRAINT Tsdb CHECK (type_typesdb IN ('Privée', 'Publique')),
    primary key (type_typesdb)
)ENGINE=innoDB;
 
create table STATION
(
    stat_numero integer    UNIQUE       not null     CONSTRAINT Snum CHECK (stat_numero>0),   
    reg_nom                CHAR(30)        not null,
    stat_nom               CHAR(30) UNIQUE       not null,
    primary key (stat_numero)
)ENGINE=innoDB;
 
create table HOTEL
(
    H_num integer             CHAR(10) UNIQUE       not null   CONSTRAINT Hnum CHECK (H_num>0),
    stat_numero integer                  not null,
    CATH_etoile integer                   not null,
    h_nom          CHAR(30)              not null,
    h_ville          CHAR(30)              not null,
    h_CP integer                            not null,
    h_rue           CHAR(30)              not null,
    primary key (H_num)
)ENGINE=innoDB;
 
create table CHAMBRE
(
    H_num integer            not null    CONSTRAINT Hnumber CHECK (H_num>0),
    ch_num integer          not null    CONSTRAINT Cnum1 CHECK (ch_num>0),
    type_typechambre    CHAR(10)                      not null    CONSTRAINT Tchambre2 CHECK (type_typechambre IN ('Suite', 'Simple')),
    type_typesdb        CHAR(10)                      not null    CONSTRAINT Tsdb2 CHECK (type_typesdb IN ('Privée', 'Publique')),
    ch_nblits           integer             not null    CONSTRAINT CHlit CHECK (ch_nblits>0),
    primary key (H_num, ch_num)
)ENGINE=innoDB;
 
create table RESERVATION
(
    Res_num integer    not null   CONSTRAINT Rnum CHECK (Res_num>0),
    C_num integer    not null   CONSTRAINT Cnum2 CHECK (C_num>0),  
    Res_Nosemaine   integer    not null   CONSTRAINT Cres CHECK (Res_Nosemaine between 1 and 52),
    primary key (Res_num)
)ENGINE=innoDB;
 
create table concerner
(
    H_num      integer               not null,
    ch_num     integer              not null,
    Res_num    integer              not null,
    primary key (H_num, ch_num, Res_num)
)ENGINE=innoDB;
 
create table Tarif
(
    S_nom      CHAR(10)          not null,
    H_num      integer             not null,
    ch_num     integer             not null,
    prix                CHAR(10)                      ,
    primary key (S_nom, H_num, ch_num)
)ENGINE=innoDB;
 
create table Abonnement
(
    stat_numero integer       not null    CONSTRAINT Stnum CHECK (stat_numero>0),  
    C_num integer            not null    CONSTRAINT Cnum3 CHECK (C_num>0),
    datedebut DATE,
    Datefin DATE,
    pourcentage_remise          CHAR(10)    CONSTRAINT Premise CHECK (pourcentage_remise between 0 and 1),
    primary key (stat_numero, C_num)
)ENGINE=innoDB;
 
alter table STATION
    add foreign key  (reg_nom)
       references REGION (reg_nom);
 
alter table HOTEL
    add foreign key  (stat_numero)
       references STATION (stat_numero);
 
alter table HOTEL
    add foreign key  (CATH_etoile)
       references CATEGORIE_HOTEL (CATH_etoile);
 
alter table CHAMBRE
    add foreign key  (H_num)
       references HOTEL (H_num);
 
alter table CHAMBRE
    add foreign key  (type_typechambre)
       references TYPE_CHAMBRE (type_typechambre);
 
alter table CHAMBRE
    add foreign key  (type_typesdb)
       references Type_SDB (type_typesdb);
 
alter table RESERVATION
    add foreign key  (C_num)
       references CLIENTS (C_num);
 
alter table concerner
    add foreign key  (H_num, ch_num)
       references CHAMBRE (H_num, ch_num);
 
alter table concerner
    add foreign key  (Res_num)
       references RESERVATION (Res_num);
 
alter table Tarif
    add foreign key  (S_nom)
       references SAISON (S_nom);
 
alter table Tarif
    add foreign key  (H_num, ch_num)
       references CHAMBRE (H_num, ch_num);
 
alter table Abonnement
    add foreign key  (stat_numero)
       references STATION (stat_numero);
 
alter table Abonnement
    add foreign key  (C_num)
       references CLIENTS (C_num);

27/04/2009, 08h04
SQLpro

Phpmyadmin étant un outil client et non pas un SGBDR il n'y peut rien le pauvre. C'est à MySQL de s'occuper de cela.
Nous savons tous que depuis des lustres MySQL est un SGBDR très limité et farcis de bugs. Il y a fort à parier que votre version de MySQL ne supporte pas la pose des contraintes de type FOREIGN KEY. Mieux vaudrait donc utiliser un vrai SGBDR libre comme PostGreSQL.
Rappelons que MySQL n'est pas gratuit, mais qu'il faut soit payer une licence soit payer en code sources. Enfin, on peut se poser la pérennité de MySQL avec le rachat de ce dernier par Oracle qui est réputé pour ses tarifs exorbitants !

A +

MySQL 5.1 et INNODB supportent les clés étrangères. Mais les contraintes CHECK ne sont pas prises en compte par INNODB .

Il y a des erreurs de syntaxe dans certains exemples: il faut supprimer 'integer' si la colonne est de type CHAR ! J'ai supprimé le mot clé CONSTRAINT et le nom de la contrainte pour les clauses CHECK:
Utiliser aussi ENGINE= au lieu de TYPE= pour le moteur de stockage.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
C:>mysql -v -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.34-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> source tfk.sql
Database changed
Show warnings enabled.
--------------
drop table station
--------------
 
Query OK, 0 rows affected (0.06 sec)
 
--------------
drop table region
--------------
 
Query OK, 0 rows affected (0.05 sec)
 
--------------
CREATE TABLE REGION
(
    reg_nom                    CHAR(30) UNIQUE       NOT NULL,
    reg_altitude               CHAR(10) CHECK (reg_altitude BETWEEN 0 AND 10000),
    PRIMARY KEY (reg_nom)
)ENGINE=innoDB
--------------
 
Query OK, 0 rows affected (0.11 sec)
 
--------------
CREATE TABLE STATION
(
    stat_numero            CHAR(10) UNIQUE       NOT NULL  CHECK (stat_numero>0),
    reg_nom                CHAR(30)              NOT NULL,
    stat_nom               CHAR(30) UNIQUE       NOT NULL,
    PRIMARY KEY (stat_numero)
)ENGINE=innoDB
--------------
 
Query OK, 0 rows affected (0.11 sec)
 
--------------
ALTER TABLE STATION
    ADD FOREIGN KEY  (reg_nom)
       REFERENCES REGION (reg_nom)
--------------
 
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

27/04/2009, 18h36
aurelius91

Bonsoir,

Merci pour vos réponses !!

Citation:

Phpmyadmin étant un outil client et non pas un SGBDR il n'y peut rien le pauvre. C'est à MySQL de s'occuper de cela.
Nous savons tous que depuis des lustres MySQL est un SGBDR très limité et farcis de bugs. Il y a fort à parier que votre version de MySQL ne supporte pas la pose des contraintes de type FOREIGN KEY. Mieux vaudrait donc utiliser un vrai SGBDR libre comme PostGreSQL.
Rappelons que MySQL n'est pas gratuit, mais qu'il faut soit payer une licence soit payer en code sources. Enfin, on peut se poser la pérennité de MySQL avec le rachat de ce dernier par Oracle qui est réputé pour ses tarifs exorbitants !

J'utilise Phpmyadmin et MySQL parce que ce sont les outils qu'on utilise en cours... du coup j'avais installé cette même solution chez moi avec easyPHP...

En fait mon prof va regarder le script SQL en lui-même, il ne compte pas accéder à la base que je vais créer pour vérifier... Même si je ne pourrai visiblement pas vérifier si mes contraintes marchent, j'aimerais que mon code soit juste :mouarf:

Citation:

il faut supprimer 'integer' si la colonne est de type CHAR !

J'ai supprimé dans le code de mon premier message les 'CHAR' quand je voulais une donnée numérique.

Citation:

J'ai supprimé le mot clé CONSTRAINT et le nom de la contrainte pour les clauses CHECK:

Pourquoi ? Notre prof met constamment CONSTRAINT pour nommer les nommer les contraintes :cry:

Merciii
27/04/2009, 22h07
pifor

Parce que c'est la syntaxe MySQL 5.1:
http://dev.mysql.com/doc/refman/5.1/...ate-table.html

Citation:

create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)

Ah d'accord, tout simplement :P, merci

Jusqu'ici, j'ai donc :
- supprimé les CONSTRAINT et les noms des contraintes avant les CHECK.
- supprimé les CHAR et laissé integer quand je veux une donnée numérique
- remplacé TYPE=innoDB par ENGINE=innodb.

Est-ce possible que je n'ai "que" ces erreurs si ? 8O

Voici mon code actuel :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
CREATE TABLE REGION
(
    reg_nom                  CHAR(30) UNIQUE       NOT NULL,
    reg_altitude integer    CHECK (reg_altitude BETWEEN 0 AND 10000),
    PRIMARY KEY (reg_nom)
)ENGINE=innoDB;
 
CREATE TABLE CLIENTS
(
    C_num integer     UNIQUE          NOT NULL,
    C_nom              CHAR(30)        NOT NULL,
    C_CP integer                       NOT NULL,
    C_ville            CHAR(30)        NOT NULL,
    C_rue              CHAR(30)        NOT NULL,
    C_tel integer,
    PRIMARY KEY (C_num)
)ENGINE=innoDB;
 
CREATE TABLE SAISON
(
    S_nom       CHAR(10)    NOT NULL  CHECK (S_nom IN ('printemps', 'été', 'automne', 'hiver')),
    S_datedebut DATE,
    S_datefin   DATE,
    PRIMARY KEY (S_nom)
)ENGINE=innoDB;
 
CREATE TABLE TYPE_CHAMBRE
(
    type_typechambre       CHAR(10)    NOT NULL   CHECK (type_typechambre IN ('Suite', 'Simple')), 
    PRIMARY KEY (type_typechambre)
)ENGINE=innoDB;
 
CREATE TABLE CATEGORIE_HOTEL
(
    CATH_etoile integer        NOT NULL   CHECK (CATH_etoile BETWEEN 1 AND 5),
    PRIMARY KEY (CATH_etoile)
)ENGINE=innoDB;
 
CREATE TABLE Type_SDB
(
    type_typesdb        CHAR(10)      NOT NULL   CHECK (type_typesdb IN ('Privée', 'Publique')),
    PRIMARY KEY (type_typesdb)
)ENGINE=innoDB;
 
CREATE TABLE STATION
(
    stat_numero integer    UNIQUE                NOT NULL     CHECK (stat_numero>0),   
    reg_nom                CHAR(30)               NOT NULL,
    stat_nom               CHAR(30) UNIQUE       NOT NULL,
    PRIMARY KEY (stat_numero)
)ENGINE=innoDB;
 
CREATE TABLE HOTEL
(
    H_num integer          CHAR(10) UNIQUE       NOT NULL   CHECK (H_num>0),
    stat_numero integer                           NOT NULL,
    CATH_etoile integer                           NOT NULL,
    h_nom          CHAR(30)                       NOT NULL,
    h_ville          CHAR(30)                      NOT NULL,
    h_CP integer                                   NOT NULL,
    h_rue           CHAR(30)                       NOT NULL,
    PRIMARY KEY (H_num)
)ENGINE=innoDB;
 
CREATE TABLE CHAMBRE
(
    H_num integer            NOT NULL    CHECK (H_num>0),
    ch_num integer          NOT NULL     CHECK (ch_num>0),
    type_typechambre    CHAR(10)            NOT NULL   CHECK (type_typechambre IN ('Suite', 'Simple')),
    type_typesdb        CHAR(10)            NOT NULL   CHECK (type_typesdb IN ('Privée', 'Publique')),
    ch_nblits           integer             NOT NULL   CHECK (ch_nblits>0),
    PRIMARY KEY (H_num, ch_num)
)ENGINE=innoDB;
 
CREATE TABLE RESERVATION
(
    Res_num integer    NOT NULL           CHECK (Res_num>0),
    C_num integer      NOT NULL           CHECK (C_num>0),  
    Res_Nosemaine   integer    NOT NULL   CHECK (Res_Nosemaine BETWEEN 1 AND 52),
    PRIMARY KEY (Res_num)
)ENGINE=innoDB;
 
CREATE TABLE concerner
(
    H_num      integer              NOT NULL,
    ch_num     integer              NOT NULL,
    Res_num    integer              NOT NULL,
    PRIMARY KEY (H_num, ch_num, Res_num)
)ENGINE=innoDB;
 
CREATE TABLE Tarif
(
    S_nom      CHAR(10)            NOT NULL,
    H_num      integer             NOT NULL,
    ch_num     integer             NOT NULL,
    prix                CHAR(10)                      ,
    PRIMARY KEY (S_nom, H_num, ch_num)
)ENGINE=innoDB;
 
CREATE TABLE Abonnement
(
    stat_numero integer      NOT NULL    CHECK (stat_numero>0),  
    C_num integer            NOT NULL    CHECK (C_num>0),
    datedebut DATE,
    Datefin DATE,
    pourcentage_remise          CHAR(10)  CHECK (pourcentage_remise BETWEEN 0 AND 1),
    PRIMARY KEY (stat_numero, C_num)
)ENGINE=innoDB;
 
ALTER TABLE STATION
    ADD FOREIGN KEY  (reg_nom)
       REFERENCES REGION (reg_nom);
 
ALTER TABLE HOTEL
    ADD FOREIGN KEY  (stat_numero)
       REFERENCES STATION (stat_numero);
 
ALTER TABLE HOTEL
    ADD FOREIGN KEY  (CATH_etoile)
       REFERENCES CATEGORIE_HOTEL (CATH_etoile);
 
ALTER TABLE CHAMBRE
    ADD FOREIGN KEY  (H_num)
       REFERENCES HOTEL (H_num);
 
ALTER TABLE CHAMBRE
    ADD FOREIGN KEY  (type_typechambre)
       REFERENCES TYPE_CHAMBRE (type_typechambre);
 
ALTER TABLE CHAMBRE
    ADD FOREIGN KEY  (type_typesdb)
       REFERENCES Type_SDB (type_typesdb);
 
ALTER TABLE RESERVATION
    ADD FOREIGN KEY  (C_num)
       REFERENCES CLIENTS (C_num);
 
ALTER TABLE concerner
    ADD FOREIGN KEY  (H_num, ch_num)
       REFERENCES CHAMBRE (H_num, ch_num);
 
ALTER TABLE concerner
    ADD FOREIGN KEY  (Res_num)
       REFERENCES RESERVATION (Res_num);
 
ALTER TABLE Tarif
    ADD FOREIGN KEY  (S_nom)
       REFERENCES SAISON (S_nom);
 
ALTER TABLE Tarif
    ADD FOREIGN KEY  (H_num, ch_num)
       REFERENCES CHAMBRE (H_num, ch_num);
 
ALTER TABLE Abonnement
    ADD FOREIGN KEY  (stat_numero)
       REFERENCES STATION (stat_numero);
 
ALTER TABLE Abonnement
    ADD FOREIGN KEY  (C_num)
       REFERENCES CLIENTS (C_num);

27/04/2009, 22h38
pifor

Il faut executer votre script avec la commande mysql et l'option -v: chaque commande SQL exécutée est affichée et en cas d'erreur le message d'erreur est affiché et par défaut l'exécution s'arrête à la première erreur ;)

Mettez au début du script la commande:

Code:

warnings

pour avoir aussi le texte des warnings

Les commandes DDL SQL MySQL 5.1 sont documentées ici.

Essayez aussi de respecter les règles de ce forum.

Merci pifor, cette option et le lien vers les commandes SQL m'ont pas mal aidé !!

Malgré tout, je bloque maintenant sur une requête... Je dois en fait créer une vue qui permette aux clients de mes stations de ski de pouvoir consulter la liste des hôtels par station, les chambres libres et prix associés...

J'ai vraiment beaucoup de mal avec cette requête, et donc je fais appel à votre aide...

Pour le moment, j'ai commencé par essayer de faire une requête qui m'afficherait la liste des chambres libres, avec leur numéro, leur prix, l'hôtel correspondant et le numéro de la semaine où elles sont libres... Mais je crois que les résultats que me retourne la requête ne sont pas bons...
Code:

1 2 3 4 5 6 7 8 SELECT DISTINCT T.ch_num, T.prix, H.h_nom, R.Res_Nosemaine FROM Tarif T, HOTEL H, RESERVATION R, concerner CO WHERE T.H_num = H.H_num AND CO.Res_num=R.Res_num AND CO.H_num=H.H_num AND(T.ch_num, H.h_nom) NOT IN (SELECT ch_num, H_num FROM concerner)
Le code SQL est dans mon premier post, ainsi que mon MCD.

Merci beaucoup de votre aide

06/05/2009, 15h47
Maljuna Kris
Saluton,
J'ai bien étudié ta requête et je peux déjà te faire deux remarques.
- tu aurais intérêt à utiliser la syntaxe normalisée pour les jointures ([INNER] | [OUTER RIGHT|LEFT] JOIN ... ON
- Comment veux-tu que le système trouve une information qui lui est extérieure ?
Pour cette deuxième question je me réfère aux semaines libres, si tu n'as pas de réservation pour une semaine donnée, où doit-on aller chercher son identifiant ?

Bonsoir, merci de me venir en aide Maljuna Kris ;-)

Citation:

tu aurais intérêt à utiliser la syntaxe normalisée pour les jointures ([INNER] | [OUTER RIGHT|LEFT] JOIN ... ON

Malheureusement, notre prof ne s'est pas encore mise à la syntaxe SQL2 avec les JOIN... elle veut donc qu'on utilise la syntaxe qu'elle utilise elle, qui est celle que j'ai utilisé au-dessus :(

Citation:

Comment veux-tu que le système trouve une information qui lui est extérieure ?

Pour cette deuxième question je me réfère aux semaines libres, si tu n'as pas de réservation pour une semaine donnée, où doit-on aller chercher son identifiant ?

Dans mon Entité RESERVATION, j'ai bien un numéro de réservation (Res_num) et une semaine de réservation associée (Res_Nosemaine)...

Voici le début du contenu actuel de ma base RESERVATION :
Code:

1 2 3 4 5 6 7 8 9 10 11 Res_num | C_num | Res_Nosemaine 1 | 1 | 32 2 | 2 | 23 3 | 3 | 23 4 | 4 | 21 5 | 5 | 22 6 | 6 | 20 7 | 7 | 20 8 | 8 | 24 9 | 9 | 23 ... | ... | ...

06/05/2009, 19h04
Maljuna Kris

Citation:

Envoyé par aurelius91

Malheureusement, notre prof ne s'est pas encore mise à la syntaxe SQL2 avec les JOIN... elle veut donc qu'on utilise la syntaxe qu'elle utilise elle, qui est celle que j'ai utilisée au-dessus :(

J'allais écrire «no comment», et puis je me ravise. Certaines opérations de l'algèbre relationnelle ne sont accessibles que par la syntaxe SQL2. Et je crois que ce que tu veux obtenir en requiert justement une.

Citation:

Envoyé par aurelius91

Dans mon Entité RESERVATION, j'ai bien un numéro de réservation (Res_num) et une semaine de réservation associée (Res_Nosemaine)...

La question est : comment déterminer une semaine qui n'existe dans aucune réservation pour dire qu'elle est manquante ?
Tu ne peux pas aller chercher ce Res_Nosemaine dans la table puisque, justement, il ne s'y trouvera pas. J'en veux pour preuve que les cardinalités autour de la table RESERVATION dans ton MCD sont 1,1 et 1,n et non 0,1 et 0,1. Donc, s'il n'y a pas de réservation du tout pour une semaine quelconque cette semaine n'est pas connue du système.
Tu comprends ?

Par ailleurs, semaine 24 n'identifie pas grand chose, dans l'absolu.
Semaine 24, certes, mais de quelle année ?
06/05/2009, 20h24
aurelius91

Citation:

J'allais écrire «no comment», et puis je me ravise. Certaines opérations de l'algèbre relationnelle ne sont accessibles que par la syntaxe SQL2. Et je crois que ce que tu veux obtenir en requiert justement une.

Il ne fallait pas vous raviser, visiblement elle mérite ce "no comment" si elle nous demande des choses qui nécessitent l'utilisation d'une syntaxe qu'on n'a pas étudié ;)

Citation:

La question est : comment déterminer une semaine qui n'existe dans aucune réservation pour dire qu'elle est manquante ?
Tu ne peux pas aller chercher ce Res_Nosemaine dans la table puisque, justement, il ne s'y trouvera pas. J'en veux pour preuve que les cardinalités autour de la table RESERVATION dans ton MCD sont 1,1 et 1,n et non 0,1 et 0,1. Donc, s'il n'y a pas de réservation du tout pour une semaine quelconque cette semaine n'est pas connue du système.
Tu comprends ?

Je dois dire que je travaille sur des requêtes et des vues sur la base depuis ce matin, et que je m'embrouille un peu.
Mais pas d'excuses, j'ai bien compris avec votre explication.

Citation:

Par ailleurs, semaine 24 n'identifie pas grand chose, dans l'absolu.
Semaine 24, certes, mais de quelle année ?

Le sujet indique qu'on veut gérer la base pour une année, donc c'est la semaine 24 de l'année en cours.

Créer cette vue est possible ou la conception de ma base fait que c'est impossible ?

Merci

Code:

select distinct Res_Nosemaine FROM RESERVATION ORDER BY Res_Nosemaine

Va nous fournir la liste des semaines pour lesquelles il existe au moins une réservation.
Pour récupérer les semaines manquantes il faut tester les Res_Nosemaine pour lesquels il n'existe pas de Res_Nosemaine+1, sauf si Res_Nosemaine = 52.
Ça donnerait qqe chose comme ça
Code:

1 2 3 4 5 6 SELECT s3.Res_Nosemaine+1 as manquante from(select distinct Res_Nosemaine FROM RESERVATION) as s1 left outer join(select distinct Res_Nosemaine FROM RESERVATION) as s2 ON s1.Res_Nosemaine=s2.Res_Nosemaine+1 WHERE s2.Res_Nosemaine IS NULL AND s1.Res_Nosemaine<>52) as s3 ORDER BY S3.Res_Nosemaine
Est-ce que, déjà, tu comprends cette requête ?
Elle ne correspond pas encore à la vue attendue, mais tu vois que, sans le LEFT OUTER JOIN, on n'y arrivera pas.
Moi pas, en tout cas.

06/05/2009, 21h16
Maljuna Kris

En poursuivant ma réflexion sur cette requête je m'aperçois que si l'on a la série de semaines de réservations {1 3 7 12 15 ....} la requête va me trouver les semaines manquantes 2 4 8 13, mais pas les semaines 5 et 6 ni la 14.
Donc, sans une table provisoire qui contiendrait juste les 52 numéros de semaine je ne vois pas comment déterminer toutes les semaines manquantes, sauf à passer par du SQL procédural, mais là, je n'ai pas assez de pratique dans ce domaine pour me risquer à te proposer quelque solution que ce soit.
06/05/2009, 21h42
Maljuna Kris

A lire à propos des requêtes calendaires exhaustives :
http://sqlpro.developpez.com/cours/gestiontemps/#L3.3.3
06/05/2009, 21h49
aurelius91

J'aurais donc dû faire une table DATE à la création de ma base ?

Citation:

En poursuivant ma réflexion sur cette requête je m'aperçois que si l'on a la série de semaines de réservations {1 3 7 12 15 ....} la requête va me trouver les semaines manquantes 2 4 8 13, mais pas les semaines 5 et 6 ni la 14.
Donc, sans une table provisoire qui contiendrait juste les 52 numéros de semaine je ne vois pas comment déterminer toutes les semaines manquantes, sauf à passer par du SQL procédural, mais là, je n'ai pas assez de pratique dans ce domaine pour me risquer à te proposer quelque solution que ce soit

Si j'ajoute une table NUMEROSEMAINE avec les entrées 1 2 3 4 5 6 .... 51 52, la requête devient possible ?
06/05/2009, 22h00
Maljuna Kris
Citation:

Envoyé par aurelius91

J'aurais donc dû faire une table DATE à la création de ma base ?

Disons que c'est une solution

Citation:

Envoyé par aurelius91

Si j'ajoute une table NUMEROSEMAINE avec les entrées 1 2 3 4 5 6 .... 51 52, la requête devient possible ?
Code:

1 2 3 4 5 SELECT ns.Nosemaine AS manquante FROM NUMEROSEMAINE AS ns LEFT OUTER JOIN RESERVATION AS R ON R.Res_Nosemaine= ns.Nosemaine where R.Res_Nosemaine is null
06/05/2009, 22h45
Maljuna Kris

En confrontant ta requête initiale avec ton MCD, je me demande comment tu arrives à cela

Code:

AND CO.H_num=H.H_num

puisque la table CONCERNER est censée représenter la relation entre les entités CHAMBRE et RESERVATION.
D'autre part je ne vois pas comment faire le lien entre la CHAMBRE et une RESERVATION{ Res_num integer , C_num integer , Res_Nosemaine} puisque la PK d'une chambre est une clé composite avec celle de l'hôtel.

La bonne nouvelle c'est que je pense qu'une fois ces points de détail éclaircis, on devrait assez facilement pouvoir écrire la requête.
06/05/2009, 22h57
aurelius91

Citation:

En confrontant ta requête initiale avec ton MCD, je me demande comment tu arrives à cela

Code:

AND CO.H_num=H.H_num

puisque la table CONCERNER est censée représenter la relation entre les entités CHAMBRE et RESERVATION.

En fait CHAMBRE est une entité faible par rapport à HOTEL... la clé primaire de l'entité CHAMBRE est donc (H.num, ch_num). Et la table concerner contient donc H_num, ch_num et Res_num avec comme clé (H_num, ch_num et Res_num).

Est-ce que cela répond à ton interrogation sur ce point ?

Citation:

La bonne nouvelle c'est que je pense qu'une fois ces points de détail éclaircis, on devrait assez facilement pouvoir écrire la requête.

Alors là ça serait extraordinaire :king:

Bon, alors avant d'aller faire dodo, je me lance

Code:

1
2
3
4
5
6
7
8
9
SELECT DISTINCT T.ch_num, T.prix, H.h_nom, NS.Nosemaine
FROM Tarif T,HOTEL AS H,NOSEMAINE NS
WHERE  H.H_num=T.H_num
AND NS.Nosemaine not in
(select R.RS_Nosemaine 
FROM RESERVATION as  R, CONCERNER as CO
WHERE CO.Res_num=R.Res_num
and CO.ch_num=T.ch_num 
and CO.H_num=T.H_num)

Bonan nokton.

Citation:
Bon, alors avant d'aller faire dodo, je me lance
Code:

1 2 3 4 5 6 7 8 9 SELECT DISTINCT T.ch_num, T.prix, H.h_nom, NS.Nosemaine FROM Tarif T,HOTEL AS H,NOSEMAINE NS WHERE H.H_num=T.H_num AND NS.Nosemaine NOT IN (SELECT R.Res_Nosemaine FROM RESERVATION AS R, concerner AS CO WHERE CO.Res_num=R.Res_num AND CO.ch_num=T.ch_num AND CO.H_num=T.H_num)
Alors là, la classe absolue je dirais... :king:
Il y avait seulement deux micro erreurs d'écriture (table concerner au lieu de CONCERNER ligne 6, et R.Res_Nosemaine plutôt que R.RS_Nosemaine) qui ont engendré des erreurs mais après correction cette requête me donne bien les chambres libres !! C'est magnifique !

J'ai rajouté le nom de la station, la vue me donnerait alors ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 CREATE VIEW VUECLIENT(NuméroSemaine, NomStation, NomHotel, NumeroChambre, Prix) AS SELECT DISTINCT NS.Nosemaine, S.stat_nom, H.h_nom, T.ch_num, T.prix FROM Tarif T,HOTEL AS H,NOSEMAINE NS, STATION S WHERE H.H_num=T.H_num AND H.stat_numero=S.stat_numero AND NS.Nosemaine NOT IN (SELECT R.Res_Nosemaine FROM RESERVATION AS R, concerner AS CO WHERE CO.Res_num=R.Res_num AND CO.ch_num=T.ch_num AND CO.H_num=T.H_num)
Avec à l'affichage le numéro de la semaine, le nom de la station, le nom de l'hotel, le numéro de chambre et le prix, et tout ceci seulement pour les chambres libres !

C'est bien cela ?

07/05/2009, 05h58
Maljuna Kris

Je ne sais pas à quoi servira cette vue par la suite, mais si c'est pour proposer ces chambres à la location, il faudrait y conserver, au moins, les colonnes identifiant la chambre pour pouvoir, ensuite, faire les requêtes INSERT dans les tables RESERVATION et concerner.

Bref, le plus gros est fait, en respectant la syntaxe obsolète imposée. C'est l'essentiel, non ?

Si c'est :ccool: , n'oublie pas de mettre le post en :resolu:

Ĝis.
07/05/2009, 09h13
aurelius91

Citation:

Je ne sais pas à quoi servira cette vue par la suite, mais si c'est pour proposer ces chambres à la location, il faudrait y conserver, au moins, les colonnes identifiant la chambre pour pouvoir, ensuite, faire les requêtes INSERT dans les tables RESERVATION et concerner.

Vous avez raison, je vais rajouter le numéro d'hotel.

Citation:

ef, le plus gros est fait, en respectant la syntaxe obsolète imposée. C'est l'essentiel, non ?

Tout à fait :king:.

Merci de votre aide et de votre patience.