/************************************************** *********
Génération du DDL
Schéma MRD : "Projet 1.0"
Fichier MRD : "(1) gymnase.rdm"
Généré le : 2004-01-22 14:40:38
Par : l'Interface MRD-SQL-Server 2.7.2.0
************************************************** *********/
/************************************************** *********
ÉNONCÉS DROP
************************************************** *********/
/************************************************** *********
Contrainte de clé étrangère "FK_MEMBRE"
************************************************** *********/
ALTER TABLE CONTRAT
DROP CONSTRAINT FK_MEMBRE
GO
/************************************************** *********
Contrainte de clé étrangère "FK_TYPE_CONTRAT"
************************************************** *********/
ALTER TABLE CONTRAT
DROP CONSTRAINT FK_TYPE_CONTRAT
GO
/************************************************** *********
Contrainte de clé étrangère "FK_DUREE_CONTRAT"
************************************************** *********/
ALTER TABLE CONTRAT
DROP CONSTRAINT FK_DUREE_CONTRAT
GO
/************************************************** *********
Contrainte de clé étrangère "FK_APPROCHE_PROMOT"
************************************************** *********/
ALTER TABLE CONTRAT
DROP CONSTRAINT FK_APPROCHE_PROMOT
GO
/************************************************** *********
Contrainte de clé étrangère "MUNI_FK"
************************************************** *********/
ALTER TABLE MEMBRE
DROP CONSTRAINT MUNI_FK
GO
/************************************************** *********
Contrainte de clé étrangère "FK_CONTRAT"
************************************************** *********/
ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_CONTRAT
GO
/************************************************** *********
Contrainte de clé étrangère "FK_TYPE_PAIEMENT"
************************************************** *********/
ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_TYPE_PAIEMENT
GO
/************************************************** *********
Contrainte de clé étrangère "FK_MODE_PAIEMENT"
************************************************** *********/
ALTER TABLE VERSERMENT
DROP CONSTRAINT FK_MODE_PAIEMENT
GO
/************************************************** *********
Contrainte de clé primaire "PK_APPROCHE_PROMOT"
************************************************** *********/
ALTER TABLE APPROCHE_PROMOTION
DROP CONSTRAINT PK_APPROCHE_PROMOT
GO
/************************************************** *********
Table "APPROCHE_PROMOTION"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'APPROCHE_PROMOTION')
BEGIN
DROP TABLE APPROCHE_PROMOTION
END
GO
/************************************************** *********
Contrainte de clé primaire "MEMBER_PK"
************************************************** *********/
ALTER TABLE CONTRAT
DROP CONSTRAINT MEMBER_PK
GO
/************************************************** *********
Table "CONTRAT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'CONTRAT')
BEGIN
DROP TABLE CONTRAT
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_DUREE_CONTRAT_P"
************************************************** *********/
ALTER TABLE DUREE_CONTRAT
DROP CONSTRAINT PK_DUREE_CONTRAT_P
GO
/************************************************** *********
Table "DUREE_CONTRAT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'DUREE_CONTRAT')
BEGIN
DROP TABLE DUREE_CONTRAT
END
GO
/************************************************** *********
Contrainte de clé primaire "MEMBRE_PK"
************************************************** *********/
ALTER TABLE MEMBRE
DROP CONSTRAINT MEMBRE_PK
GO
/************************************************** *********
Table "MEMBRE"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MEMBRE')
BEGIN
DROP TABLE MEMBRE
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_MODE_PAIEMENT_P"
************************************************** *********/
ALTER TABLE MODE_PAIEMENT
DROP CONSTRAINT PK_MODE_PAIEMENT_P
GO
/************************************************** *********
Table "MODE_PAIEMENT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MODE_PAIEMENT')
BEGIN
DROP TABLE MODE_PAIEMENT
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_MUNICIPALITE"
************************************************** *********/
ALTER TABLE MUNICIPALITE
DROP CONSTRAINT PK_MUNICIPALITE
GO
/************************************************** *********
Table "MUNICIPALITE"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'MUNICIPALITE')
BEGIN
DROP TABLE MUNICIPALITE
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_TYPE_CONTRAT_PR"
************************************************** *********/
ALTER TABLE TYPE_CONTRAT
DROP CONSTRAINT PK_TYPE_CONTRAT_PR
GO
/************************************************** *********
Table "TYPE_CONTRAT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'TYPE_CONTRAT')
BEGIN
DROP TABLE TYPE_CONTRAT
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_TYPE_PAIEMENT_P"
************************************************** *********/
ALTER TABLE TYPE_PAIEMENT
DROP CONSTRAINT PK_TYPE_PAIEMENT_P
GO
/************************************************** *********
Table "TYPE_PAIEMENT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'TYPE_PAIEMENT')
BEGIN
DROP TABLE TYPE_PAIEMENT
END
GO
/************************************************** *********
Contrainte de clé primaire "PK_VERSERMENT_PRIM"
************************************************** *********/
ALTER TABLE VERSERMENT
DROP CONSTRAINT PK_VERSERMENT_PRIM
GO
/************************************************** *********
Table "VERSERMENT"
************************************************** *********/
IF EXISTS (SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.type = 'U'
AND sysobjects.name = 'VERSERMENT')
BEGIN
DROP TABLE VERSERMENT
END
GO
/************************************************** *********
ÉNONCÉS CREATE
************************************************** *********/
/************************************************** *********
Table "APPROCHE_PROMOTION"
************************************************** *********/
CREATE TABLE APPROCHE_PROMOTION
(
APPROCHE_PROMOTION varchar(50) NOT
NULL
)
GO
ALTER TABLE APPROCHE_PROMOTION
ADD CONSTRAINT PK_APPROCHE_PROMOT
PRIMARY KEY (APPROCHE_PROMOTION)
GO
/************************************************** *********
Table "CONTRAT"
************************************************** *********/
CREATE TABLE CONTRAT
(
NUMERO_CONTRAT numeric(4) NOT
NULL,
DATE_DEBUT datetime
NULL,
PAYE bit
NULL,
MONTANT numeric(10,2)
NULL,
DATE_PAIEMENT_FINA datetime
NULL,
NUMERO_MEMBRE numeric(4) NOT
NULL,
ID_TYPE_CONTRAT numeric(4) NOT
NULL,
APPROCHE_PROMOTION varchar(50) NOT
NULL,
DUREE_CONTRAT varchar(15) NOT
NULL
)
GO
ALTER TABLE CONTRAT
ADD CONSTRAINT MEMBER_PK
PRIMARY KEY (NUMERO_CONTRAT)
GO
/************************************************** *********
Table "DUREE_CONTRAT"
************************************************** *********/
CREATE TABLE DUREE_CONTRAT
(
DUREE_CONTRAT varchar(15) NOT
NULL
)
GO
ALTER TABLE DUREE_CONTRAT
ADD CONSTRAINT PK_DUREE_CONTRAT_P
PRIMARY KEY (DUREE_CONTRAT)
GO
/************************************************** *********
Table "MEMBRE"
************************************************** *********/
CREATE TABLE MEMBRE
(
NUMERO_MEMBRE numeric(4) NOT
NULL,
TITRE varchar(4)
NULL,
NOM varchar(45)
NULL,
ADRESSE varchar(30)
NULL,
MAUVAIS_P bit
NULL,
CODE_POSTAL char(7)
NULL,
TELEPHONE char(12)
NULL,
FAX char(12)
NULL,
COURRIER_E char(30)
NULL,
SOLDE numeric(10,2)
NULL,
PHOTO varchar(100)
NULL,
DATE_DE_NAISSANCE datetime
NULL,
TELEPHONE_TRAVAIL char(12)
NULL,
NOTES varchar(200)
NULL,
NAS varchar(11)
NULL,
PRENOM varchar(45)
NULL,
NO_MUNICIPALITE numeric(4) NOT
NULL
)
GO
ALTER TABLE MEMBRE
ADD CONSTRAINT MEMBRE_PK
PRIMARY KEY (NUMERO_MEMBRE)
GO
/************************************************** *********
Table "MODE_PAIEMENT"
************************************************** *********/
CREATE TABLE MODE_PAIEMENT
(
MODE_PAIEMENT varchar(25) NOT
NULL
)
GO
ALTER TABLE MODE_PAIEMENT
ADD CONSTRAINT PK_MODE_PAIEMENT_P
PRIMARY KEY (MODE_PAIEMENT)
GO
/************************************************** *********
Table "MUNICIPALITE"
************************************************** *********/
CREATE TABLE MUNICIPALITE
(
NO_MUNICIPALITE numeric(4) NOT
NULL,
MUNICIPALITE varchar(15)
NULL
)
GO
ALTER TABLE MUNICIPALITE
ADD CONSTRAINT PK_MUNICIPALITE
PRIMARY KEY (NO_MUNICIPALITE)
GO
/************************************************** *********
Table "TYPE_CONTRAT"
************************************************** *********/
CREATE TABLE TYPE_CONTRAT
(
ID_TYPE_CONTRAT numeric(4) NOT
NULL,
TYPE_CONTRAT varchar(45) NOT
NULL
)
GO
ALTER TABLE TYPE_CONTRAT
ADD CONSTRAINT PK_TYPE_CONTRAT_PR
PRIMARY KEY (ID_TYPE_CONTRAT)
GO
/************************************************** *********
Table "TYPE_PAIEMENT"
************************************************** *********/
CREATE TABLE TYPE_PAIEMENT
(
TYPE_PAIEMENT varchar(45) NOT
NULL
)
GO
ALTER TABLE TYPE_PAIEMENT
ADD CONSTRAINT PK_TYPE_PAIEMENT_P
PRIMARY KEY (TYPE_PAIEMENT)
GO
/************************************************** *********
Table "VERSERMENT"
************************************************** *********/
CREATE TABLE VERSERMENT
(
NO_VERSEMENT numeric(4) NOT
NULL,
DATE_VERSEMENT datetime
NULL,
MONTANT numeric(10,2)
NULL,
NUMERO_CONTRAT numeric(4) NOT
NULL,
TYPE_PAIEMENT varchar(45) NOT
NULL,
MODE_PAIEMENT varchar(25) NOT
NULL
)
GO
ALTER TABLE VERSERMENT
ADD CONSTRAINT PK_VERSERMENT_PRIM
PRIMARY KEY (NO_VERSEMENT)
GO
/************************************************** *********
Les clés étrangères de la table "CONTRAT"
************************************************** *********/
ALTER TABLE CONTRAT
ADD CONSTRAINT FK_MEMBRE
FOREIGN KEY (NUMERO_MEMBRE)
REFERENCES MEMBRE (NUMERO_MEMBRE)
GO
ALTER TABLE CONTRAT
ADD CONSTRAINT FK_TYPE_CONTRAT
FOREIGN KEY (ID_TYPE_CONTRAT)
REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT)
GO
ALTER TABLE CONTRAT
ADD CONSTRAINT FK_DUREE_CONTRAT
FOREIGN KEY (DUREE_CONTRAT)
REFERENCES DUREE_CONTRAT (DUREE_CONTRAT)
GO
ALTER TABLE CONTRAT
ADD CONSTRAINT FK_APPROCHE_PROMOT
FOREIGN KEY (APPROCHE_PROMOTION)
REFERENCES APPROCHE_PROMOTION (APPROCHE_PROMOTION)
GO
/************************************************** *********
Les clés étrangères de la table "MEMBRE"
************************************************** *********/
ALTER TABLE MEMBRE
ADD CONSTRAINT MUNI_FK
FOREIGN KEY (NO_MUNICIPALITE)
REFERENCES MUNICIPALITE (NO_MUNICIPALITE)
GO
/************************************************** *********
Les clés étrangères de la table "VERSERMENT"
************************************************** *********/
ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_CONTRAT
FOREIGN KEY (NUMERO_CONTRAT)
REFERENCES CONTRAT (NUMERO_CONTRAT)
GO
ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_TYPE_PAIEMENT
FOREIGN KEY (TYPE_PAIEMENT)
REFERENCES TYPE_PAIEMENT (TYPE_PAIEMENT)
GO
ALTER TABLE VERSERMENT
ADD CONSTRAINT FK_MODE_PAIEMENT
FOREIGN KEY (MODE_PAIEMENT)
REFERENCES MODE_PAIEMENT (MODE_PAIEMENT)
GO
/* Fin
*/
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!