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)
                CONSTRAINT title_description_nn NOT NULL,
rating       VARCHAR2(4)
                CONSTRAINT title_rating_ck CHECK (rating IN ('G', 'PG', 'R', 'NC17', 'NR')),
category     VARCHAR2(20),
                CONSTRAINT title_category_ck CHECK (category 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 member (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 reser_date_member_title_pk PRIMARY KEY(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_tables;

SELECT constraint_name, constraint_type 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 member_id_seq
                START WITH 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 title_id_seq
                START WITH 92
                NOCACHE;

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

SELECT sequence_name, increment_by, last_number
FROM user_sequences;

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.

INSERT INTO title
VALUES (title_id_seq.nextval, 'Willie and Christmas Too', 'All of Willie''s friends made a Christmas list for Santa, but Willie has yet to add this own wish list','G','CHILD','05-OCT-95');
INSERT INTO title
VALUES (title_id_seq.nextval, 'Alien Again', 'Yet another installation of science fiction history. Can the heroine save the planet from the alien life form?','R','SCIFI','19-MAY-95 ');
INSERT INTO title
VALUES (title_id_seq.nextval, 'The Glob', 'A meteor crashes near a small American town and unleashed carnivorous goo in this classic','NR','SCIFI','12-AGO-95 ');
INSERT INTO title
VALUES (title_id_seq.nextval, 'My Day Off', 'With a little luck and a lot of ingenuity, a teenager skips school for a day in New York','PG','COMEDY','12-JUL-95');
INSERT INTO title
VALUES (title_id_seq.nextval, 'Miracles on Ice', 'A six-year-old has doubts about Santa Claus but she discovers that miracles really do exist','PG','DRAMA','12-SEP-95');
INSERT INTO title
VALUES (title_id_seq.nextval, 'Soda Gang', 'After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang','NR','ACTION','01-JUN-95');
COMMIT;

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.

INSERT INTO member
VALUES (member_id_seq.nextval,'&v_last_name', '&v_first_name', '&v_address', '&v_state', '&v_phone', '&v_date');
...
COMMIT;

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

INSERT INTO title_copy
VALUES (1, 92, 'AVAILABLE');
INSERT INTO title_copy
VALUES (1, 93, 'AVAILABLE');
INSERT INTO title_copy
VALUES (2, 93, 'RENTED');
INSERT INTO title_copy
VALUES (1, 94, 'AVAILABLE');
INSERT INTO title_copy
VALUES (1, 95, 'AVAILABLE');
INSERT INTO title_copy
VALUES (2, 95, 'AVAILABLE');
INSERT INTO title_copy
VALUES (3, 95, 'RENTED');
INSERT INTO title_copy
VALUES (1, 96, 'AVAILABLE');
INSERT INTO title_copy
VALUES (1, 97, 'AVAILABLE');
COMMIT;

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

INSERT INTO rental
VALUES (sysdate-3, 101, 1, sysdate-2, sysdate-1, 92); 
INSERT INTO rental
VALUES (sysdate-1, 101, 1, null, sysdate+1, 93); 
INSERT INTO rental
VALUES (sysdate-2, 102, 1, null, sysdate, 95); 
INSERT INTO rental
VALUES (sysdate-4, 106, 1, sysdate-2, sysdate-2, 97); 
COMMIT;

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.

CREATE VIEW title_avail
AS
SELECT t.title, c.copy_id, c.status, r.exp_ret_date
FROM title t JOIN title_copy c on (t.title_id = c.title_id) LEFT JOIN rental r ON (c.copy_id = r.copy_id AND c.title_id = r.title_id);

select * from title_avail
order by title;

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 Scifi. 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 dos registres a la taula title_copy, per disposar de dues còpies (status Avaliable).

INSERT INTO title
VALUES (title_id_seq.nextval, 'Interstellar Wars', 'Futuristic insterstellar action movie. Can the rebels save the humans from de evil Empire?','PG','SCIFI','07-JUL-77');

INSERT INTO title_copy
VALUES (1, title_id_seq.currval, 'AVAILABLE');
INSERT INTO title_copy
VALUES (2, title_id_seq.currval, 'AVAILABLE');

COMMIT;

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".

INSERT INTO reservation
VALUES (sysdate , 101, 98);
INSERT INTO reservation
VALUES (sysdate , 104, 97);

COMMIT;

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.

INSERT INTO rental (member_id, copy_id, title_id)
VALUES (101, 1, 98);

UPDATE title_copy
SET status ='RENTED'
WHERE title_id=98 AND copy_id=1;

DELETE reservation
WHERE member_id = 101 AND title_id = 98;

SELECT *
FROM title_avail
ORDER BY title;

COMMIT;

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.

ALTER TABLE title
ADD (PRICE NUMBER(8,2));

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

UPDATE title
SET price = &v_price
WHERE title = '&v_title';

COMMIT;

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

ALTER TABLE title
MODIFY (PRICE NUMBER(8,2) CONSTRAINT title_price_nn NOT NULL);

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.

SELECT m.first_name || ' ' || m.last_name member, t.title, r.book_date, r.act_ret_date-r.book_date DURATION
FROM member m join rental r on (m.member_id = r.member_id) join title t on (r.title_id = t.title_id)
ORDER BY member;