Aquí una descripción resumida del Modelo de datos de Dream Home
/*==============================================================*/
/* BORRADO DE TABLAS */
/*==============================================================*/
drop table TotPropEmpleado cascade constraints;
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)
);
/*==============================================================*/
/* Tabla TotPropEmpleado */
/* Se utiliza para insertar desde otra tabla
/*==============================================================*/
create table TotPropEmpleado (
NUMEMPLEADO CHAR(4) not null,
totProp INTEGER,
constraint PK_TotPropEmpleado primary key (NUMEMPLEADO)
);
alter table TotPropEmpleado
add constraint FK_TotProp_REFERENCE_EMPLEADO foreign key (NUMEMPLEADO)
references EMPLEADO (NUMEMPLEADO);
alter table EMPLEADO
add constraint FK_EMPLEADO_REFERENCE_OFICINA foreign key (NUMOFICINA)
references OFICINA (NUMOFICINA)
on delete Cascade;
alter table PROPIEDAD
add constraint FK_PROPIEDA_REFERENCE_EMPLEADO foreign key (NUMEMPLEADO)
references EMPLEADO (NUMEMPLEADO)
on delete set null;
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);
/*==============================================================*/
/*= P o b l a m i e n t o d e t a b l a s =*/
/*==============================================================*/
/*==============================================================*/
/* 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','164 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','17 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','166 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','Costanera 25','Valdivia','0324');
insert into oficina values('B115','Picarte 124','Valdivia','0324');
insert into oficina values('B215','El Morro 110','Arica','10300');
insert into oficina values('B315','El Vergel 1500','Arica','123123');
insert into oficina values('B415','Av. Walker Martinez 1360','Santiago','W101');
insert into oficina values('B515','Av. Antonio Varas 929','Santiago','W101');
/*==============================================================*/
/* datos: cliente */
/*==============================================================*/
insert into cliente values('CR76','Jhon','Kay','56 High ST,Londonn,SW14EH','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, G40QR','0141225742','Departamento',600);
insert into cliente values('CR78','Juan','Kayser','55 High ST,Londonn,SW14EH','0207774564','Departamento',450);
insert into cliente values('CR57','Alicia','Soto','63 Fern Dr,. GlasgowG42 OBL','0141324183','Departamento',350);
insert into cliente values('CR72','Miguel','Torres','62 Well St, Glasgow,G42','0141943740','Casa',750);
insert into cliente values('CR63','Maria','Perez','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,'B006');
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','Tina','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','Juan','Blanco','Gerente','M','01/10/44',300000,'B005');
insert into empleado values('SG36','Luis','Jara','Asistente','M','10/11/61',120000,'B003');
insert into empleado values('SG13','David','Gates','Supervisor','M','09/09/58',180000,'B003');
insert into empleado values('SA8','Maria','Bombal','Asistente','F','17/09/59',90000,'B007');
insert into empleado values('SG4','Susana','Sarandons','Gerente','F','21/03/60',240000,'B003');
insert into empleado values('SL40','Jose','Bond','Asistente','F','13/06/63',90000,'B005');
insert into empleado values('SL50','Juan','Perez','Vendedor','M','13/06/63',151000,'B015');
insert into empleado values('SL60','Jaime','Soto','Vendedor','M','14/06/83',350000,'B115');
insert into empleado values('SL70','Julia','Berne','Vendedor','F','23/01/53',200000,'B215');
insert into empleado values('SL55','Jorge','Fernandez','Vendedor','M','13/06/63',151000,'B015');
insert into empleado values('SL65','Jose','Isla','Vendedor','M','14/06/83',350000,'B115');
/*==============================================================*/
/* datos: Propietario */
/*==============================================================*/
insert into propietario values('C046','Joe','Keogh','2 Fergus Dr, AberdeenAB 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, G40QR','0141225742');
insert into propietario values('C047','Jose','Casanova','El Volvan 123, Santiago AB 7SX','0122486125');
insert into propietario values('C088','Carolina','Fernandez','Macul 1800. Santiago, G32 9DX','0141357741');
insert into propietario values('C041','Cristina','Mora','Av. Matta 1800, Santiago, G42','0141943752');
insert into propietario values('C094','Jorge','Figueroa','Av. Macul 120, Santiago, G40QR','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','G119QX','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','AV. Matta 150','Santiago','G12','Casa','5','600','C087','SG5' );
insert into PROPIEDAD values('PR01','Macul 120 ','Santaigo','G129AX','Departamento','4','450','C093','SA8');
insert into PROPIEDAD values('PR02','Macul 220','Santiago','G129AX','Departamento','5','550','C093','SG13');
insert into PROPIEDAD values('PR03','Macul 420','Santiago','G129AX','Departamento','6','650','C093','SG14');
insert into PROPIEDAD values('PR04','Macul 620','Santiago','G129AX','Departamento','3','350','C093','SG36');
insert into PROPIEDAD values('PR05','Loa 100','Santiago','G129AX','Departamento','2','250','C093','SG4');
insert into PROPIEDAD values('PG16','Arturo Prats 250','Santiago','G129AX','Departamento','4','450','C047','SL22');
insert into PROPIEDAD values('PR07','Gorbea 200','Santiago','G129AX', 'Departamento','6','650','C047','SL40');
insert into PROPIEDAD values('PR08','Gomez 230','Santiago','G129AX', 'Departamento','2','250','C041','SL41');
insert into PROPIEDAD values('PR09','Garibaldi 1500','Santiago','G129AX', 'Departamento','6','650','C041','SL50');
insert into PROPIEDAD values('PR10','Las Urbinas 210','Santiago','G129AX', 'Departamento','6','650','C094','SL55');
insert into PROPIEDAD values('PR11','Lastarria 1400','Santiago','G129AX', 'Departamento','3','350','C094','SL60');
insert into PROPIEDAD values('PR12','Las Giraldas 200','Santiago','G129AX','Departamento','4','450','C093','SL70');
/*==============================================================*/
/* datos: VISITA */
/*==============================================================*/
insert into visita values('CR56','PA14','24-11-1999','muy pequeño');
insert into visita values('CR62','PA14','14-11-1999','no tiene salón');
insert into visita values('CR76','PG4','20-10-1999','muy lejos');
insert into visita values('CR72','PG16','24-06-2007','Bakan');
insert into visita values('CR72','PG36','24-06-2007','Super');
insert into visita values('CR62','PG16','25-06-2007','Cool');
insert into visita values('CR62','PG4','25-06-2007', NULL);
insert into visita values('CR62','PG36','25-06-2007','No salva');
insert into visita (numCliente, numPropiedad, fecha) values ('CR72','PG4','25-06-2007');
insert into visita (numCliente, numPropiedad, fecha) values('CR56','PG36','28-10-1999');
insert into visita (numCliente, numPropiedad, fecha) values('CR56','PG4','26-11-1999');
/*==============================================================*/
/* 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');
/*==============================================================*/
miércoles, 18 de julio de 2007
martes, 3 de julio de 2007
miércoles, 20 de junio de 2007
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');
/*==============================================================*/
/*================= 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);
/*==============================================================*/
/* 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);
/*==============================================================*/
jueves, 31 de mayo de 2007
Otros Tips
Para mostrar las tablas creadas : SELECT table_name FROM user_tables;
all_tables y all_tab_columns
En all_tables hay dos campos, owner que es para que consultar las tablas creadas en un esquema especifico y table_name que seria los nombres de las tablas creadas dentro del esquema.
Y en all_tab_columns puede ver los campos de las tablas de acuerdo al esquema.
A estas dos tablas puedes hacer las consultas para ver si existe la tabla y si tiene campos asignados.
owner=usuario....
all_tables y all_tab_columns
En all_tables hay dos campos, owner que es para que consultar las tablas creadas en un esquema especifico y table_name que seria los nombres de las tablas creadas dentro del esquema.
Y en all_tab_columns puede ver los campos de las tablas de acuerdo al esquema.
A estas dos tablas puedes hacer las consultas para ver si existe la tabla y si tiene campos asignados.
owner=usuario....
Los primeros Tips
Estos Tips los envio el Alumno Rodrigo Gutierrez
; o / para ejecutar comando en sqlplus o isql....
show user; muestra usuario actual conectado
select * from global_name; muestra el sid o instancia donde se está conextado
desc XX ; muestra la estructura de tabla XX
rename XX to YY; renombra tabla
- Para cambiar la conexion a otro usuario dentro de una sesion sqlplus :
connect username/password si está en la misma instancia, si esta en otra se debe usar el string de conexion : connect username/password@string
- El archivo que guarda los string de conexion a otras instancias o BD's en $ORACLE_HOME/network/admin se llama tsnames.ora
- Para ver fecha de hoy se usa select sysdate from dual; si es necesario formatear usar :
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual; select to_char(sysdate,'dd-mon-yy hh:mi:ss') from dual;
Sysdate es reemplazable por cualquier columna de tipo date y dual se puede reemplazar por otra tabla., si se necesita comparar fechas es mejor aplicar función truncate ejemplo :
select 'X' from tabla where trunc(fecha) = trunc(sysdate); select 'X' from tabla where trunc(fecha) = trunc(sysdate-1); select 'X' from tabla where trunc(fecha) = trunc(sysdate+1);
- Select * from cat;
- Tablas del catalogo Oracle para consultas(se hace un select simple sobre ellas puedes ver el desc de c/u para ver estructuras) :
user_constraints
user_cons_columns
user_db_links
user_tables
user_indexes
user_ind_columns
user_col_comments
user_tab_comments
user_synonyms
user_tab_privs
user_rol_privs
user_objects (todos los objetos ver object_type select distinct object_type from user_objects;)
- Para cambiar password es en sqlplus alter user YY identified by "NewPass";
-Para ejecutar un script es con @nombre.sql si no comienz poner /
-Para matar una sesion se debe estar como sys o system y dar :
ALter system kill session 'SID, SERIAL#'; ver datos en v$session (vista
ver estructura con desc)
; o / para ejecutar comando en sqlplus o isql....
show user; muestra usuario actual conectado
select * from global_name; muestra el sid o instancia donde se está conextado
desc XX ; muestra la estructura de tabla XX
rename XX to YY; renombra tabla
- Para cambiar la conexion a otro usuario dentro de una sesion sqlplus :
connect username/password si está en la misma instancia, si esta en otra se debe usar el string de conexion : connect username/password@string
- El archivo que guarda los string de conexion a otras instancias o BD's en $ORACLE_HOME/network/admin se llama tsnames.ora
- Para ver fecha de hoy se usa select sysdate from dual; si es necesario formatear usar :
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual; select to_char(sysdate,'dd-mon-yy hh:mi:ss') from dual;
Sysdate es reemplazable por cualquier columna de tipo date y dual se puede reemplazar por otra tabla., si se necesita comparar fechas es mejor aplicar función truncate ejemplo :
select 'X' from tabla where trunc(fecha) = trunc(sysdate); select 'X' from tabla where trunc(fecha) = trunc(sysdate-1); select 'X' from tabla where trunc(fecha) = trunc(sysdate+1);
- Select * from cat;
- Tablas del catalogo Oracle para consultas(se hace un select simple sobre ellas puedes ver el desc de c/u para ver estructuras) :
user_constraints
user_cons_columns
user_db_links
user_tables
user_indexes
user_ind_columns
user_col_comments
user_tab_comments
user_synonyms
user_tab_privs
user_rol_privs
user_objects (todos los objetos ver object_type select distinct object_type from user_objects;)
- Para cambiar password es en sqlplus alter user YY identified by "NewPass";
-Para ejecutar un script es con @nombre.sql si no comienz poner /
-Para matar una sesion se debe estar como sys o system y dar :
ALter system kill session 'SID, SERIAL#'; ver datos en v$session (vista
ver estructura con desc)
Suscribirse a:
Entradas (Atom)