Chapitre II : Script d'implémentation de
la base de données
CREATE TABLE [dbo].[AGENCE] (
[Code_agence] [primary_key] [char] (10) COLLATE French_CI_AS NOT
NULL,
[Nom_agence] [char] (30) COLLATE French_CI_AS NULL ,
[Adresse_agence] [char] (60) COLLATE French_CI_AS NULL ,
[telephone_agence] [char] (10) COLLATE French_CI_AS NULL ,
[Fax_agence] [char] (10) COLLATE French_CI_AS NULL ,
[mail_agence] [char] (50) COLLATE French_CI_AS NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[AGENT] (
[Code_agent] [primary_key] [char] (10) COLLATE French_CI_AS NOT
NULL ,
[Nom_agent] [char] (20) COLLATE French_CI_AS NULL ,
[Prenom_agent] [char] (20) COLLATE French_CI_AS NULL ,
[Date_naissance_agent] [datetime] NULL ,
[Lieu_naissance_agent] [char] (30) COLLATE French_CI_AS NULL
,
[Fonction_agent] [char] (30) COLLATE French_CI_AS
NULL ,
[Contact_agent] [char] (20) COLLATE French_CI_AS NULL ,
[Adresse_agent] [char] (60) COLLATE French_CI_AS NULL ,
[Telephone_agent] [char] (10) COLLATE French_CI_AS NULL ,
[Mail_agent] [char] (50) COLLATE French_CI_AS NULL ,
[Code_agence] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[APPARTEMENT] (
[Numero_appartement] [primary_key] [char] (10) COLLATE
French_CI_AS NOT NULL ,
[Niveau_appartement] [char] (30) COLLATE French_CI_AS NULL ,
[Valeur_appartement] [money] NULL ,
[Jour_paiement_effectif] [int] NULL ,
[Code_propriete] [char] (10) COLLATE French_CI_AS NULL ,
[Type_appartement] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[BAIL] (
[Numero_bail] [primary_key] [char] (10) COLLATE French_CI_AS NOT
NULL ,
[Duree_bail] [char] (15) COLLATE French_CI_AS NULL ,
[Date_fin_bail] [datetime] NULL ,
[Numero_contrat] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CONSTAT] (
[Numero_constat] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Date_constat] [datetime] NULL ,
[Motif_costat] [text] COLLATE French_CI_AS NULL ,
[Code_huissier] [char] (10) COLLATE French_CI_AS NULL ,
[Numero_appartement] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[HUISSIER] (
[Code_huissier] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Nom_huissier] [char] (20) COLLATE French_CI_AS NULL ,
[Prenom_huissier] [char] (20) COLLATE French_CI_AS NULL ,
[Adresse_huissier] [char] (60) COLLATE French_CI_AS NULL ,
[Telephone_huissier] [char] (10) COLLATE French_CI_AS NULL ,
[Fax_huissier] [char] (10) COLLATE French_CI_AS NULL ,
[Mail_huissier] [char] (50) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[CONSTITUER] (
[Code_societe] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Code_propriete] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[LOCATAIRE] (
[Code_locataire] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Type_locataire] [char] (10) COLLATE French_CI_AS NULL ,
[Nom_locataire] [char] (20) COLLATE French_CI_AS NULL ,
[Prenom_locataire] [char] (20) COLLATE French_CI_AS NULL ,
[Date_naissance_locataire] [datetime] NULL ,
[Lieu_naissance_locataire] [char] (30) COLLATE French_CI_AS NULL
,
[Piece_identite] [char] (30) COLLATE French_CI_AS NULL ,
[Numero_identite] [int] NULL ,
[Adresse_locataire] [char] (60) COLLATE French_CI_AS NULL ,
[Contact1] [char] (20) COLLATE French_CI_AS NULL ,
[Contact2] [char] (20) COLLATE French_CI_AS NULL ,
[Nationalite_locataire] [char] (30) COLLATE French_CI_AS NULL
,
[Profession_locataire] [char] (20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[LOCATION] (
[Numero_contrat] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Date_location] [datetime] NULL ,
[Duree_contrat] [char] (15) COLLATE French_CI_AS NULL ,
[date_fin_contrat] [datetime] NULL ,
[nombre_mois_caution] [int] NULL ,
[montant_caution] [money] NULL ,
[Etat_contrat] [text] COLLATE French_CI_AS NULL ,
[Montant_location] [money] NULL ,
[Type_location] [char] (10) COLLATE French_CI_AS NULL ,
[Code_locataire] [char] (10) COLLATE French_CI_AS NULL ,
[Numero_appartement] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[PAIEMENT LOYER] (
[Numero_paiement] [char] (10) COLLATE French_CI_AS NOT NULL ,
[date_paiement] [primary_key] [datetime] NULL ,
[Etat_paiement] [text] COLLATE French_CI_AS NULL ,
[Montant_avance] [money] NULL ,
[mois_location] [char] (10) COLLATE French_CI_AS NULL ,
[Code_agence] [char] (10) COLLATE French_CI_AS NULL ,
[Numero_contrat] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[POSSEDER] (
[Code_proprietaire] [primary_key] [char] (10) COLLATE
French_CI_AS NOT NULL ,
[Code_propriete] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PROPRIETAIRE] (
[Code_proprietaire] [primary_key] [char] (10) COLLATE
French_CI_AS NOT NULL ,
[Nom_proprietaire] [char] (20) COLLATE French_CI_AS NULL ,
[Prenom_proprietaire] [char] (20) COLLATE French_CI_AS NULL ,
[Date_naissance_proprietaire] [datetime] NULL ,
[Lieu_naissance_proprietaire] [char] (30) COLLATE French_CI_AS
NULL ,
[Adresse_proprietaire] [char] (60) COLLATE French_CI_AS NULL
,
[Contact_proprietaire] [char] (40) COLLATE French_CI_AS NULL
,
[nationalite_proprietaire] [char] (20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[SOCIETE] (
[Code_société] [primary_key] [char] (10) COLLATE
French_CI_AS NOT NULL ,
[Raison_sociale_societe] [char] (50) COLLATE French_CI_AS NULL
,
[Adresse_societe] [char] (60) COLLATE French_CI_AS NULL ,
[Telephone_societe] [char] (10) COLLATE French_CI_AS NULL ,
[Mail_societe] [char] (50) COLLATE French_CI_AS NULL ,
[Code_proprietaire] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TYPE APPARTEMENT] (
[Type_appartement] [primary_key] [char] (10) COLLATE
French_CI_AS NOT NULL ,
[libelle_type_appartement] [char] (30) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TYPE LOCATAIRE] (
[Type_locataire] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Libelle_type_locataire] [char] (20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PROPRIETE] (
[Code_propriete] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Nom_propriete] [char] (30) COLLATE French_CI_AS NULL ,
[Nombre_appartement] [int] NULL ,
[Adresse_propriete] [char] (60) COLLATE French_CI_AS NULL ,
[Valeur_propriete] [money] NULL ,
[Type_propriete] [char] (10) COLLATE French_CI_AS NULL ,
[Code_localite] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[LOCALITE] (
[Code_localite] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Nom_localite] [char] (10) COLLATE French_CI_AS NULL ,
[ville_localite] [char] (10) COLLATE French_CI_AS NULL ,
[Departement] [char] (10) COLLATE French_CI_AS NULL ,
[Region] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TYPE PROPRIETE] (
[Type_propriete] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Libelle_type_propriete] [char] (30) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TYPE LOCATION] (
[Type_location] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Libelle_type_location] [char] (20) COLLATE French_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[VENTE] (
[Numero_vente] [primary_key] [char] (10) COLLATE French_CI_AS
NOT NULL ,
[Date_vente] [datetime] NULL ,
[Montant_vente] [money] NULL ,
[Etat_vente] [text] COLLATE French_CI_AS NULL ,
[Avance] [money] NULL ,
[Numero_appartement] [char] (10) COLLATE French_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|