scrip dreamhome poblamiento

/*==============================================================*/
/*================= llenar datos ============*/
/*==============================================================*/
/*==============================================================*/
/* datos: oficina */
/*==============================================================*/

insert into oficina values('B005','16 Holhead','Aberdeem','AB7 5SU');
insert into oficina values('B007','6 Argvill St.','London','NW2');
insert into oficina values('B003','163 Main street','Glasgow','G119Qx');
insert into oficina values('B004','2 Manor Rd','Glasgow','G114Qx');
insert into oficina values('B001','10 Dale Rd','bristol','G12');
insert into oficina values('B002','16 Holhead','Aberdeem','AB7 5SU');
insert into oficina values('B008','7 Argvill St.','London','NW21');
insert into oficina values('B006','163 Main street','Glasgow','G11');
insert into oficina values('B010','2 Manor Rd','Glasgow','G114x');
insert into oficina values('B011','14 Dale Rd','bristol','G2');
insert into oficina values('B017','6 Argvill St.','London','W2');
insert into oficina values('B013','164 Main street','Glasgow','9Qx');
insert into oficina values('B014','3 Manor Rd','Glasgow','Qx');
insert into oficina values('B012','11 Dale Rd','bristol','GH2');
insert into oficina values('B015','9 Argvill St.','London','W2');

/*==============================================================*/
/* datos: cliente */
/*==============================================================*/

insert into cliente values('CR76','Jhon','Kay','56 High ST,Londonn,SW1
4EH','0207774563','Departamento',450);
insert into cliente values('CR56','Aline','Stewart','64 Fern Dr,. Glasgow
G42 OBL','0141324182','Departamento',350);
insert into cliente values('CR74','Mike','Ritchie','63 Well St, Glasgow,
G42','0141943742','Casa',750);
insert into cliente values('CR62','Mary','Tregear','12 Park PI, Glasgow, G4
0QR','0141225742','Departamento',600);
insert into cliente values('CR78','Jhonx','Kays','55 High ST,Londonn,SW1
4EH','0207774564','Departamento',450);
insert into cliente values('CR57','Alinex','Stewarts','63 Fern Dr,. Glasgow
G42 OBL','0141324183','Departamento',350);
insert into cliente values('CR72','Mikex','Ritchies','62 Well St, Glasgow,
G42','0141943740','Casa',750);
insert into cliente values('CR63','Maryx','Tregears','13 Park PI, Glasgow,
G4 0QR','0141225741','Departamento',600);

/*==============================================================*/
/* datos: empleado */
/*==============================================================*/

insert into empleado
values('SL21','Jhon','White','Gerente','M','01/10/45',300000,'B005');
insert into empleado
values('SG37','Peter','Denver','Asistente','M','10/11/60',120000,'B003');
insert into empleado
values('SG14','David','Ford','Supervisor','M','09/09/58',180000,'B003');
insert into empleado
values('SA9','Mary','Lee','Asistente','F','17/09/59',90000,'B007');
insert into empleado
values('SG5','Susan','Sarandon','Gerente','F','21/03/60',240000,'B003');
insert into empleado
values('SL41','Julie','Roberts','Asistente','F','13/06/63',90000,'B005');
insert into empleado
values('SL22','Jhonx','Whites','Gerente','M','01/10/44',300000,'B005');
insert into empleado
values('SG36','Peterx','Denvers','Asistente','M','10/11/61',120000,'B003');
insert into empleado
values('SG13','Davidx','Fords','Supervisor','M','09/09/58',180000,'B003');
insert into empleado
values('SA8','Maryx','Lees','Asistente','F','17/09/59',90000,'B007');
insert into empleado
values('SG4','Susanx','Sarandons','Gerente','F','21/03/60',240000,'B003');
insert into empleado
values('SL40','Juliex','Robertss','Asistente','F','13/06/63',90000,'B005');

/*==============================================================*/
/* datos: propietario */
/*==============================================================*/

insert into propietario values('C046','Joe','Keogh','2 Fergus Dr, Aberdeen
AB 7SX','0122486121');
insert into propietario values('C087','Carol','Farrel','6 Achray St.
Glasgow, G32 9DX','0141357741');
insert into propietario values('C040','Tina','Murphy','63 Well St, Glasgow,
G42','0141943742');
insert into propietario values('C093','Tony','Shaw','12 Park PI, Glasgow, G4
0QR','0141225742');
insert into propietario values('C047','Joex','Keoghs','1 Fergus Dr, Aberdeen
AB 7SX','0122486125');
insert into propietario values('C088','Carolx','Farrels','5 Achray St.
Glasgow, G32 9DX','0141357741');
insert into propietario values('C041','Tinax','Murphys','43 Well St,
Glasgow, G42','0141943752');
insert into propietario values('C094','Tonyx','Shaws','13 Park PI, Glasgow,
G4 0QR','0141225542');

/*==============================================================*/
/* datos: PROPIEDAD */
/*==============================================================*/

insert into PROPIEDAD values('PA14','16 Holhead','Aberdeem','AB7 5SU','Casa','6','650','C046','SL21');
insert into PROPIEDAD values('PL94','6 Argvill St.','London','NW2','Departamento','4','400','C087','SL21');
insert into PROPIEDAD values('PG4' ,'6 Lawrence St','Glasgow','G11

9QX','Departamento','3','350','C040','SA9');
insert into PROPIEDAD values('PG36','2 Manor

Rd','Glasgow','G114QX','Departamento','3','375','C093','SA9');
insert into PROPIEDAD values('PG21','10 Dale Rd','Glasgow','G12','Casa','5','600','C087','SG5' );
insert into PROPIEDAD values('PG16','5 Novar Dr','Glasgow','G12

9AX','Departamento','4','450','C093','SL21');

/*==============================================================*/
/* datos: VISITA */
/*==============================================================*/
insert into visita values('CR56','PA14','24-11-1999','muy pequeño');
insert into visita values('CR56','PG36','28-10-1999',' ');
insert into visita values('CR56','PG4','26-11-1999',' ');
insert into visita values('CR62','PA14','14-11-1999','no tiene salón');
insert into visita values('CR76','PG4','20-10-1999','muy lejos');
/*==============================================================*/
/* datos: ARRIENDO*/
/*==============================================================*/
insert into arriendo values('10024','PA14','CR62','650','Visa','1300','Y','01-06-2005','31-05-2006');
insert into arriendo values('10075','PL94','CR76','400','Contado','800','N','01-08-2005','31-01-2006');
insert into arriendo values('10012','PG21','CR74','600','Cheque','1200','Y','01-07-2005','30-06-2006');
/*==============================================================*/

scrip dreamhome

/*==============================================================*/
/* BORRADO DE TABLAS */
/*==============================================================*/

drop table ARRIENDO cascade constraints;

drop table CLIENTE cascade constraints;

drop table EMPLEADO cascade constraints;

drop table OFICINA cascade constraints;

drop table PROPIEDAD cascade constraints;

drop table PROPIETARIO cascade constraints;

drop table VISITA cascade constraints;
/*==============================================================*/
/* Tabla: ARRIENDO */
/*==============================================================*/

create table ARRIENDO (
NUMARRIENDO INTEGER not null,
NUMPROPIEDAD CHAR(4),
NUMCLIENTE CHAR(4),
RENTA FLOAT,
FORMAPAGO CHAR(10),
DEPOSITO FLOAT,
PAGADO CHAR(1),
INICIORENTA DATE,
FINRENTA DATE,
constraint PK_ARRIENDO primary key (NUMARRIENDO)
);

/*==============================================================*/
/* Tabla: CLIENTE */
/*==============================================================*/


create table CLIENTE (
NUMCLIENTE CHAR(4) not null,

NOMBRE CHAR(30),
APELLIDO CHAR(30),
DIRECCION CHAR(35),
TELEFONO CHAR(10),
TIPOPREF CHAR(25),
MAXRENT FLOAT,
constraint PK_CLIENTE primary key (NUMCLIENTE)
);

/*==============================================================*/
/* Tabla: EMPLEADO */
/*==============================================================*/


create table EMPLEADO (
NUMEMPLEADO CHAR(4) not null,
NOMBRE CHAR(30),
APELLIDO CHAR(30),
CARGO CHAR(35),
SEXO CHAR(1),
FECHNAC DATE,
SALARIO FLOAT,
NUMOFICINA CHAR(4),
constraint PK_EMPLEADO primary key (NUMEMPLEADO)
);

/*==============================================================*/
/* Tabla: OFICINA */
/*==============================================================*/


create table OFICINA (
NUMOFICINA CHAR(4) not null,
CALLE CHAR(30),
CIUDAD CHAR(25),
CODIGOPOSTAL CHAR(10),
constraint PK_OFICINA primary key (NUMOFICINA)
);

/*==============================================================*/
/* Tabla: PROPIEDAD */
/*==============================================================*/


create table PROPIEDAD (
NUMPROPIEDAD CHAR(4) not null,
CALLE CHAR(30),
CIUDAD CHAR(25),
CODIGOPOSTAL CHAR(10),
TIPO CHAR(25),
HAB INTEGER,
RENTA FLOAT,
NUMPROPIETARIO CHAR(4),
NUMEMPLEADO CHAR(4),
constraint PK_PROPIEDAD primary key (NUMPROPIEDAD)
);

/*==============================================================*/
/* Tabla: PROPIETARIO */
/*==============================================================*/

create table PROPIETARIO (
NUMPROPIETARIO CHAR(4) not null,

NOMBRE CHAR(30),
APELLIDO char(30),
DIRECCION CHAR(30),
TELEFONO CHAR(10),
constraint PK_PROPIETARIO primary key (NUMPROPIETARIO)
);

/*==============================================================*/
/* Tabla: VISITA */
/*==============================================================*/


create table VISITA (
NUMCLIENTE CHAR(4) not null,
NUMPROPIEDAD CHAR(4) not null,
FECHA DATE not null,
COMENTARIO VARCHAR2(30),
constraint PK_VISITA primary key (NUMCLIENTE, NUMPROPIEDAD, FECHA)
);


alter table EMPLEADO
add constraint FK_EMPLEADO_REFERENCE_OFICINA foreign key (NUMOFICINA)
references OFICINA (NUMOFICINA);


alter table PROPIEDAD
add constraint FK_PROPIEDA_REFERENCE_EMPLEADO foreign key (NUMEMPLEADO)
references EMPLEADO (NUMEMPLEADO);

alter table VISITA
add constraint FK_VISITA_REFERENCE_CLIENTE foreign key (NUMCLIENTE)
references CLIENTE (NUMCLIENTE);

alter table VISITA
add constraint FK_VISITA_REFERENCE_PROPIEDA foreign key (NUMPROPIEDAD)
references PROPIEDAD (NUMPROPIEDAD);


/*==============================================================*/