M2 - Bases de dades / Exercicis UF2: Solucio Exercici Resum

1. Crea les taules segons els quadres presentats successivament. Escull els tipus de dades apropiats i assegura't d'afegir les restriccions (constraints) d'integritat.

a. Nom de la taula: MEMBER

CREATE TABLE member (
member_id  NUMBER(10) 
              CONSTRAINT member_member_id_pk PRIMARY KEY, 
last_name  VARCHAR2(25)
              CONSTRAINT member_last_name_nn NOT NULL,
first_name VARCHAR2(25),
address    VARCHAR2(100),
city       VARCHAR2(30),
phone      VARCHAR2(15),
join_date  DATE DEFAULT SYSDATE
              CONSTRAINT member_join_date_nn NOT NULL);

b. Nom de la taula: TITLE

CREATE TABLE title (
title_id     NUMBER(10) 
                CONSTRAINT title_title_id_pk PRIMARY KEY, 
title        VARCHAR2(60)
                CONSTRAINT title_title_nn NOT NULL,
description  VARCHAR2(400),
rating       VARCHAR2(4)
                CONSTRAINT title_rating_ck CHECK (rating IN ('G', 'PG', 'R', 'NC17', 'NR'),
category     VARCHAR2(20),
                CONSTRAINT title_category_ck CHECK (caegory IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')),
relase_date    DATE);

c. Nom de la taula: TITLE_COPY

CREATE TABLE title_copy (
copy_id      NUMBER(10),
title_id     NUMBER(10) 
                CONSTRAINT title_copy_title_id_fk REFERENCES title(title_id), 
status       VARCHAR2(15)
                CONSTRAINT title_copy_status_nn NOT NULL,
                CONSTRAINT title_copy_status_ck CHECK (status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')),
   CONSTRAINT title_copy_copy_id_title_id_pk PRIMARY KEY (copy_id, title_id));

d. Nom de la taula: RENTAL

CREATE TABLE rental (
book_date    DATE DEFAULT SYSDATE,
member_id    NUMBER(10) 
                CONSTRAINT rental_member_id_fk REFERENCES memeber (member_id),
copy_id      NUMBER(10),
act_ret_date DATE,
exp_ret_date DATE DEFAULT SYSDATE + 2,
title_id     NUMBER(10),
   CONSTRAINT rental_book_date_copy_title_pk PRIMARY KEY (book_date, member_id, copy_id, title_id),
   CONSTRAINT rental_copy_id_title_id_fk FOREIGN KEY (copy_id, title_id) REFERENCES title_copy (copy_id, title_id));

e. Nom de la taula: RESERVATION

CREATE TABLE reservation (
res_date     DATE,
member_id    NUMBER(10) CONSTRAINT revservation_member_id_fk REFERENCES MEMBER (member_id),
title_id     NUMBER(10) CONSTRAINT revservation_title_id_fk REFERENCES TITLE (title_id),
CONSTRAINT revservation_res_date_member_id_title_id_pk (res_date, member_id title_id));

2. Comprova que les taules i les seves restriccions s'han creat correctament. Consulta el diccionari de dades.

SELECT table_name
FROM user_table;

SELECT constraint_name, c, table_name
FROM user_constraints;

3. Crea seqüències per identificar de forma única dada registre en les taules MEMBER i TITLE.

a. Número de membre, taula MEMBER: inici 101; sense caché. Nom de la seqüència: title_id_seq.

         CREATE SEQUENCE title_id_seq
                         START WHIT 101
                         NOCACHE;

b. Número de títol, taula TITLE: inici 92; no permetre caché de valors. Nom de la seqüència: member_id_seq.

         CREATE SEQUENCE member_id_seq
                         START WHIT 92
                         NOCACHE;

c. Comprova l'existència de les seqüències en el diccionari de dades.

4. Afegeix dades a les taules. Crea un script per a cada conjunt de dades a afegir.

   a. Afegeix títols de pel·lícules a la taula TITLE. Escriu un script, anomenat p15q4.sql, per introduir la informació de les pel·lícules. Utilitza la seqüència per identificar cada títol. Comprova que les pel·lícules s'han afegit correctament. 

   b. Afegeix dades a la taula MEMBER. Escriu un script i anomena'l p15q4.sql, per demanar als usuaris la informació. Executa el script. Assegura't d'utilitzar la seqüència per afegir números als membres. 

   c. Afegeix les següents còpies de pel·lícules, a la taula TITLE_COPY: 

   d. Afegeix els següents lloguers de pel·lícules, a la taula RENTAL. 

Nota: El títul pot variar segons la seqüència.

5. Crea una vista amb el nom TITLE_AVAIL per mostrar les pel·lícules i la seva disponibiliat, així com la data de recuperació, si està llogada. Consulta la taula complerta.

6. Fes els següents canvis sobre les dades de les taules:

   a. Afegeix un nou títol. La pel·lícula és "Interstellar Wars" amb el valor PG per a "rated" i classificada com Sci-fi. La "release date" és 07-JUL-77- La descripció és "Futuristic insterstellar action movie. Can the rebels save the humans from de evil Empire?" 

Assegura't d'afegir un registre a la taula títols, per disposar de dues còpies.

   b. Afegeix dues reserves. Una reserva és per a Carmen Velasquez, que vol llogar "Insterstellar Wars". L'altra és per Mark Lewis, que vol llogar "Soda Gang". 

   c. La client Carmen Velasquez lloga "Interstellar Wars", còpia 1. Esborra la reserva per a la pel·lícula. Registra la informació sobre el lloguer. Per a la data de recuperació del lloguer, assigna el valor per defecte. Verifica que el lloguer va ser registrat mitjançant la vista que vas crear prèviament. 

7. Fes les següents modificacions sobre les taules:

   a. Afegeix una columna PRICE a la taula TITLE per registrar el preu del vídeo. La columna tindrà una longitud total de vuit dígits i dos decimals. Comprova les modificacions. 

   b. Crea un scrip anomenat p15q7.sql per modificar cada vídeo amb un preu, d'acord a la següent llista: 

   c. Assegura't de que en el futur, tots els títol tindran un preu. Comprova la restricció. 

8. Fes un script, anomena't p15q8.sql, per crear un informe anomena't "Històric de Clients". Aquest informe reflectirà la història dels llogues de vídeos per client. Assegura't de incloure el nom del client, pel·lícula llogada, data i durada del lloguer. Mostra el total de lloguers per client, per un període especificat en l'informe.