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 161 162 163 164 165 166 167
|
/* CREATION DES TABLES + CLE PRIMAIRE/ETRANGERE */
DROP TABLE Proprietaire cascade constraints ;
DROP TABLE Appartement cascade constraints ;
DROP TABLE Locataire cascade constraints ;
DROP TABLE Tarif cascade constraints ;
DROP TABLE Contrat cascade constraints ;
DROP SEQUENCE nopropri ;
DROP SEQUENCE nolocat ;
DROP SEQUENCE noappt ;
DROP SEQUENCE nocontrat ;
DROP SEQUENCE notarif ;
prompt Creation des tables et des sequences en cours ...
/*
table Proprietaire
*/
create table Proprietaire
(
nopropri number(4) CONSTRAINT p1 not null,
nom varchar2(30) CONSTRAINT p2 not null,
adresse varchar2(100) CONSTRAINT p3 not null,
teleph varchar2(10) CONSTRAINT p4 not null,
mail varchar2(30) ,
montcum number(6) ,
CONSTRAINT pk_nopropri_Proprietaire primary key (nopropri)
);
commit;
insert into Proprietaire values(
211,'Mario', '38 rue des Cerises','0669696969', 'mario@rouge.fr',2000) ;
insert into Proprietaire values(
212, 'Luigi', '39 rue des Poires','0670707070', 'luigi@vert.fr',7000 ) ;
insert into Proprietaire values(
213, 'Rosalina', 'Place de l etoile','0671717171', 'harmonie@bleu.fr',6000) ;
insert into Proprietaire values(
214, 'Daisy', 'Bois de Boulogne','0672727272', 'daisy@orange.fr',5000) ;
insert into Proprietaire values(
215, 'Wario', 'Port Yoneuve','0673737373', 'wario@gros.fr',1000) ;
commit;
/*table Locataire*/
create table Locataire
(
nolocat number(4) CONSTRAINT Locataire_nolocat_nn not null,
nom varchar2(30) CONSTRAINT Locataire_nom_nn not null,
adresse varchar2(100) CONSTRAINT Locataire_adresse_nn not null,
teleph varchar2(10) CONSTRAINT Locataire_teleph_nn not null,
mail varchar2(30) ,
CONSTRAINT pk_nolocat_Locataire primary key (nolocat)
);
insert into Locataire values
(311, 'Link', 'Rue Kokiri','0689898989', 'link@nain.fr');
insert into Locataire values
(312, 'Zelda', 'Bois Vermeil','0690909090', 'zelda@chateau.fr');
insert into Locataire values
(313, 'Samus', 'Planete Metroid','0691919191', 'samus@bottes.fr');
insert into Locataire values
(314, 'Ganon', 'Chateau Bizarre','0692929292', 'ganon@moche.fr');
insert into Locataire values
(315, 'Hayrule', 'Place du Roi','0693939393', 'hayrule@serpilliere.fr');
commit;
/*table Appartement*/
create table Appartement
(
noappt number(3) not null,
adresse varchar2(100) not null,
categorie varchar2(2) not null,
nbpiece number(2) not null,
surface number(3) not null,
garage varchar2(1) not null,
description varchar2(100) ,
noproprio number(4) not null,
notarif number(3) not null,
CONSTRAINT pk_noappt_Appartement primary key (noappt)
) ;
alter table Appartement
add constraint fk_noproprio_Appartement foreign key (nopropri)
references Proprietaire (nopropri)
;
alter table Appartement
add constraint fk_notarif_Appartement foreign key (notarif)
references Tarif (notarif)
;
insert into Appartement values
(411, 'Foret Champignon', 2, 56, 192, 5, 'Un beau parc en jardin', 211, 753);
insert into Appartement values
(412, 'Jungle Koko', 3, 7, 12, 3, 'Bof', 212, 123);
insert into Appartement values
(413, 'Mont Donkey', 2, 36, 130, 2, 'Incroyable', 213, 456);
insert into Appartement values
(414, 'Route Arc en Ciel', 4, 75, 300, 7, 'Moyen', 214, 789);
insert into Appartement values
(415, 'Chateau Peach', 1, 49, 10, 1, 'Maison de Retraite', 215, 147);
commit;
/*table Tarif*/
create table Tarif
(
notarif number(3) not null,
minimum number(5) not null,
maximum number(5) not null,
CONSTRAINT pk_notarif_Tarif primary key (notarif)
);
insert into Tarif values
(753, 60000, 99999);
insert into Tarif values
(123, 10000, 88888);
insert into Tarif values
(465, 2000, 22222);
insert into Tarif values
(789, 30000, 70000);
insert into Tarif values
(147, 5000, 9999);
commit;
/*table Contrat*/
create table Contrat
(
nocontrat number(4) not null,
datsignat date ,
datedeb date not null,
datefin date ,
loyernego number(5) not null,
nolocat number(4) not null,
noappt number(3) not null,
CONSTRAINT pk_nocontrat_Contrat primary key (nocontrat)
);
alter table Contrat
add constraint fk_nolocat_Contrat foreign key (nolocat)
references Locataire (nolocat)
;
alter table Contrat
add constraint fk_noappt_Contrat foreign key (noappt)
references Appartement (noappt)
;
insert into Contrat values
(4856, 05/06/2014, 03/08/2015, 05/10/2025, 311, 411);
insert into Contrat values
(4751, 06/09/2014, 15/12/2014, 10/01/2018, 312, 412);
insert into Contrat values
(4686, 01/07/2014, 03/11/2014, 04/06/2016, 313, 413);
insert into Contrat values
(4996, 03/01/2014, 07/06/2015, 19/04/2028, 314, 414);
insert into Contrat values
(4126, 12/08/2014, 09/10/2014, 04/03/2017, 315, 415);
commit;
prompt Creation des tables et sequences terminee. |
Partager