ejercicio bases de datos utilizando CONSTRAINT CHECK Y UNIQUE #2

se tienen 2 tablas una propietarios y otra propiedades en propietarios esta los campos 

propietarios(id,nombre,apellido,sexo,ciudad,edad); donde id es primary key

y la tabla propiedades tiene los siguientes campos :

propiedades(id,descripcion,valor,mtzs); donde id es primary key y mtzs es metro 2 tierra

se quiere que al agregar a la base  de datos  valide lo siguiente:

1. las identificaciones de los propietarios tienen que ser mayores que 10 y < 90 

2. no se permite propietarios de sexo fenenino(que machistas wey)

3.no se permite propietarios que vivan en las ciudades  medellin ,bogota ó cali

4.las propiedades deben tener un valor > a 5000000

5.el id de la propiedad debe ser > que el id  de propietario

6.no se permite propiedades con la misma descripcion

7.no se permite propiedades con 2 metros cuadrados con valores null

8.no se permite propietarios cuyo nombre sea santiago

9.no se permite propietarios cuya edad < 18 y > 90

10.apellidos propietarios se deben almacenar en mayuscula.

bueno primero vamos hallar la cardinalidad entre estas 2 tablas como sabemos esta propietarios y propiedades entonces decimos una propiedad puede ser dueña de un propietario , pero un propietario pueder ser dueño de n propiedades entonces se nos da una relacion:

1: n

como vemos propietario manda

propietario                                             propiedad

1                                                            n

1                                                            1

entonces propietario propaga la primary key a propiedad

entonces seguiremos solucionando con las validaciones de la 1 hasta 8 y al final el script completo

en tabla propietarios y propiedades

0. id           number(2) constraint    pr_id_pk primary key, ponemos primary key al id (propietarios)

0.1.id             number(8) constraint    pro_id_pk primary key,(propiedades)

1.constraint pr_id_c CHECK(id > 10 and id < 90),

2.constraint pr_se_c CHECK(upper(sexo)='M'), tambien se puede poner upper(sexo)<>'f'

3.constraint pr_ci_c CHECK (UPPER(ciudad)NOT IN('BOGOTA','CALI','MEDELLIN')), in entra en un arreglo y verifica devuelve falso o verdadero

8.constraint pr_no_c  CHECK(UPPER(nombre)<>'santiago'), que no sea santiago

9. constraint pr_ed_c  CHECK(edad >=18 and edad<=90),

10.constraint pr_ap_c  CHECK(apellido=upper(apellido))

7.mtzs           number (6,2) not null, no nulls

5.constraint pr_id_c1 CHECK(id >idp), 

4.constraint pr_va_c1 CHECK(valor>5000000),

6.constraint pr_de_u UNIQUE(descripcion), se utiliza UNIQUE para no entre otra descripcion igual

por ultimo pasaremos la llave foranea a propiedades

constraint pr_id_f foreign key(idp) references propietarios(id)

y listo ahora les dejo el scrip para que lo ejecuten en linea de comandos:

drop table propiedades;
drop table propietarios;



create table propietarios(
id           number(2) constraint    pr_id_pk primary key,
nombre       char (35),
apellido     char (34),
sexo         char (1),
ciudad       char(35),
edad         number (2),
constraint pr_id_c CHECK(id > 10 and id < 90),
constraint pr_se_c CHECK(upper(sexo)='M'),
constraint pr_ci_c CHECK (UPPER(ciudad)NOT IN('BOGOTA','CALI','MEDELLIN')),
constraint pr_no_c  CHECK(UPPER(nombre)<>'santiago'),
constraint pr_ed_c  CHECK(edad >=18 and edad<=90),
constraint pr_ap_c  CHECK(apellido=upper(apellido))
);

create table propiedades(
id             number(8) constraint    pro_id_pk primary key,
descripcion    char (34),
valor          number (12),
mtzs           number (6,2) not null,
idp number(2),
constraint pr_id_c1 CHECK(id >idp),
constraint pr_va_c1 CHECK(valor>5000000),
constraint pr_de_u UNIQUE(descripcion),
constraint pr_id_f foreign key(idp) references propietarios(id)
);