Ejercicios propuestos : Restricciones al crear la tabla(Oracle)

Primer problema:

Un club de barrio tiene en su sistema 4 tablas:

- "socios": en la cual almacena documento, número, nombre y domicilio de cada socio;

- "deportes": que guarda un código, nombre del deporte, día de la semana que se dicta y documento del profesor instructor;

- "profesores": donde se guarda el documento, nombre y domicilio de los profesores e

- "inscriptos": que almacena el número de socio, el código de deporte y si la matricula está paga o no.

1- Elimine las tablas:

 drop table inscriptos;
 drop table socios;
 drop table deportes;
 drop table profesores;

2- Considere que:

- un socio puede inscribirse en varios deportes, pero no dos veces en el mismo.
- un socio tiene un documento único y un número de socio único.
- un deporte debe tener asignado un profesor que exista en "profesores" o "null" si aún no tiene un instructor definido.
- el campo "dia" de "deportes" puede ser: lunes, martes, miercoles, jueves, viernes o sabado.
- el campo "dia" de "deportes" por defecto debe almacenar 'sabado'.
- un profesor puede ser instructor de varios deportes o puede no dictar ningún deporte.
- un profesor no puede estar repetido en "profesores".
- un inscripto debe ser socio, un socio puede no estar inscripto en ningún deporte.
- una inscripción debe tener un valor en socio existente en "socios" y un deporte que exista en "deportes".
- el campo "matricula" de "inscriptos" debe aceptar solamente los caracteres 's' o'n'.
- si se elimina un profesor de "profesores", el "documentoprofesor" coincidente en "deportes" debe quedar seteado a null.
- no se puede eliminar un deporte de "deportes" si existen inscriptos para tal deporte en "inscriptos".
- si se elimina un socio de "socios", el registro con "numerosocio" coincidente en "inscriptos" debe eliminarse.

3- Cree las tablas con las restricciones necesarias:

 create table profesores(
  documento char(8) not null,
  nombre varchar2(30),
  domicilio varchar2(30),
  constraint PK_profesores_documento
   primary key (documento)
 );

 create table deportes(
  codigo number(2),
  nombre varchar2(20) not null,
  dia varchar2(9) default 'sabado',
  documentoprofesor char(8),
  constraint CK_deportes_dia_lista
   check (dia in ('lunes','martes','miercoles','jueves','viernes','sabado')),
  constraint PK_deportes_codigo
   primary key (codigo),
  constraint FK_deportes_profesor
   foreign key (documentoprofesor)
   references profesores(documento)
   on delete set null
 );

 create table socios(
  numero number(4),
  documento char(8),
  nombre varchar2(30),
  domicilio varchar2(30),
  constraint PK_socios_numero
   primary key (numero),
  constraint UQ_socios_documento
   unique (documento)
 );

 create table inscriptos(
  numerosocio number(4),
  codigodeporte number(2),
  matricula char(1),
  constraint PK_inscriptos_numerodeporte
   primary key (numerosocio,codigodeporte),
  constraint FK_inscriptos_deporte
   foreign key (codigodeporte)
   references deportes(codigo),
  constraint FK_inscriptos_socios
   foreign key (numerosocio)
   references socios(numero)
   on delete cascade,
  constraint CK_matricula_valores
   check (matricula in ('s','n'))
);

4- Ingrese registros en "profesores":

 insert into profesores values('21111111','Andres Acosta','Avellaneda 111');
 insert into profesores values('22222222','Betina Bustos','Bulnes 222');
 insert into profesores values('23333333','Carlos Caseros','Colon 333');

5- Ingrese registros en "deportes". Ingrese el mismo día para distintos deportes, un deporte sin día confirmado, un deporte sin profesor definido:

 insert into deportes values(1,'basquet','lunes',null);
 insert into deportes values(2,'futbol','lunes','23333333');
 insert into deportes values(3,'natacion',null,'22222222');
 insert into deportes values(4,'padle',default,'23333333');
 insert into deportes values(5,'tenis','jueves',null);

6- Ingrese registros en "socios":

 insert into socios values(100,'30111111','Martina Moreno','America 111');
 insert into socios values(200,'30222222','Natalia Norte','Bolivia 222');
 insert into socios values(300,'30333333','Oscar Oviedo','Caseros 333');
 insert into socios values(400,'30444444','Pedro Perez','Dinamarca 444');

7- Ingrese registros en "inscriptos". Inscriba a un socio en distintos deportes, inscriba varios socios en el mismo deporte.

 insert into inscriptos values(100,3,'s');
 insert into inscriptos values(100,5,'s');
 insert into inscriptos values(200,1,'s');
 insert into inscriptos values(400,1,'n');
 insert into inscriptos values(400,4,'s');

8- Realice un "join" (del tipo que sea necesario) para mostrar todos los datos del socio junto con el nombre de los deportes en los cuales está inscripto, el día que tiene que asistir y el nombre del profesor que lo instruirá (5 registros)

9- Realice la misma consulta anterior pero incluya los socios que no están inscriptos en ningún deporte (6 registros)

10- Muestre todos los datos de los profesores, incluido el deporte que dicta y el día, incluyendo los profesores que no tienen asignado ningún deporte, ordenados por documento (4 registros)

11- Muestre todos los deportes y la cantidad de inscriptos en cada uno de ellos, incluyendo aquellos deportes para los cuales no hay inscriptos, ordenados por nombre de deporte (5 registros)

12- Muestre las restricciones de "socios"

13- Muestre las restricciones de "deportes"

14- Obtenga información sobre la restricción "foreign key" de "deportes"

15- Muestre las restricciones de "profesores"

16- Muestre las restricciones de "inscriptos"

17- Consulte "user_cons_columns" y analice la información retornada sobre las restricciones de "inscriptos"

18- Elimine un profesor al cual haga referencia algún registro de "deportes"

19- Vea qué sucedió con los registros de "deportes" cuyo "documentoprofesor" existía en "profesores"
Fue seteado a null porque la restricción "foreign key" sobre "documentoprofesor" de "deportes" fue definida "on delete set null".

20- Elimine un socio que esté inscripto

21- Vea qué sucedió con los registros de "inscriptos" cuyo "numerosocio" existía en "socios"
Fue eliminado porque la restricción "foreign key" sobre "numerosocio" de "inscriptos" fue definida "on delete cascade".

22- Intente eliminar un deporte para el cual haya inscriptos
Mensaje de error porque la restricción "foreign key sobre "codigodeporte" de "inscriptos" fue establecida "no action".

23- Intente eliminar la tabla "socios"
No puede eliminarse, mensaje de error, una "foreign key" sobre "inscriptos" hace referencia a esta tabla.

24- Elimine la tabla "inscriptos"

25- Elimine la tabla "socios"

26- Intente eliminar la tabla "profesores"
No puede eliminarse, mensaje de error, una "foreign key" sobre "deportes" hace referencia a esta tabla.

27- Elimine la tabla "deportes"

28- Elimine la tabla "profesores"

Ver solución

 drop table inscriptos;
 drop table socios;
 drop table deportes;
 drop table profesores;

 create table profesores(
  documento char(8) not null,
  nombre varchar2(30),
  domicilio varchar2(30),
  constraint PK_profesores_documento
   primary key (documento)
 );

 create table deportes(
  codigo number(2),
  nombre varchar2(20) not null,
  dia varchar2(9) default 'sabado',
  documentoprofesor char(8),
  constraint CK_deportes_dia_lista
   check (dia in ('lunes','martes','miercoles','jueves','viernes','sabado')),
  constraint PK_deportes_codigo
   primary key (codigo),
  constraint FK_deportes_profesor
   foreign key (documentoprofesor)
   references profesores(documento)
   on delete set null
 );

 create table socios(
  numero number(4),
  documento char(8),
  nombre varchar2(30),
  domicilio varchar2(30),
  constraint PK_socios_numero
   primary key (numero),
  constraint UQ_socios_documento
   unique (documento)
 );

 create table inscriptos(
  numerosocio number(4),
  codigodeporte number(2),
  matricula char(1),
  constraint PK_inscriptos_numerodeporte
   primary key (numerosocio,codigodeporte),
  constraint FK_inscriptos_deporte
   foreign key (codigodeporte)
   references deportes(codigo),
  constraint FK_inscriptos_socios
   foreign key (numerosocio)
   references socios(numero)
   on delete cascade,
  constraint CK_matricula_valores
   check (matricula in ('s','n'))
);

 insert into profesores values('21111111','Andres Acosta','Avellaneda 111');
 insert into profesores values('22222222','Betina Bustos','Bulnes 222');
 insert into profesores values('23333333','Carlos Caseros','Colon 333');

 insert into deportes values(1,'basquet','lunes',null);
 insert into deportes values(2,'futbol','lunes','23333333');
 insert into deportes values(3,'natacion',null,'22222222');
 insert into deportes values(4,'padle',default,'23333333');
 insert into deportes values(5,'tenis','jueves',null);

 insert into socios values(100,'30111111','Martina Moreno','America 111');
 insert into socios values(200,'30222222','Natalia Norte','Bolivia 222');
 insert into socios values(300,'30333333','Oscar Oviedo','Caseros 333');
 insert into socios values(400,'30444444','Pedro Perez','Dinamarca 444');

 insert into inscriptos values(100,3,'s');
 insert into inscriptos values(100,5,'s');
 insert into inscriptos values(200,1,'s');
 insert into inscriptos values(400,1,'n');
 insert into inscriptos values(400,4,'s');

  select s.*,d.nombre as deporte,d.dia,p.nombre as profesor
  from socios s
  join inscriptos i
  on numero=i.numerosocio
  join deportes d
  on d.codigo=i.codigodeporte
  left join profesores p
  on d.documentoprofesor=p.documento;

  select s.*,d.nombre as deporte,d.dia,p.nombre as profesor
  from socios s
  full join inscriptos i
  on numero=i.numerosocio
  left join deportes d
  on d.codigo=i.codigodeporte
  left join profesores p
  on d.documentoprofesor=p.documento;

  select p.*,d.nombre as deporte,d.dia
  from profesores p
  left join deportes d
  on d.documentoprofesor=p.documento
  order by documento;

  select d.nombre,count(i.codigodeporte) as cantidad
  from deportes d
  left join inscriptos i
  on d.codigo=i.codigodeporte
  group by d.nombre;  

 select constraint_name, constraint_type, status, validated
  from user_constraints where table_name='SOCIOS';

 select constraint_name, constraint_type, status, validated, search_condition
  from user_constraints where table_name='DEPORTES';

 select *from user_cons_columns
  where constraint_name='FK_DEPORTES_PROFESOR';

 select constraint_name, constraint_type, status, validated, search_condition
from user_constraints where table_name='PROFESORES';

 select constraint_name, constraint_type, status, validated, search_condition
  from user_constraints
  where table_name='INSCRIPTOS';

 select *from user_cons_columns
  where table_name='INSCRIPTOS';

 delete from profesores where documento='22222222';

 select *from deportes;

 delete from socios where numero=200;

 select *from inscriptos;

 delete from deportes where codigo=4;

 drop table socios;

 drop table inscriptos;

 drop table socios;

 drop table profesores;

 drop table deportes;

 drop table profesores;

64 - Restricciones al crear la tabla

Retornar