M2 - Bases de dades / Exercicis UF2: Exercici Resum

Exercici Resum

0. Creeu un usuari nou i doneu-li permisos per crear objectes.

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

MEMBER

Column_Name	MEMBER_ID	LAST_NAME	FIRST_NAME	ADDRESS	CITY	PHONE	JOIN_DATE
Key Type	PK
Null / Unique	NN,U	NN					NN
Default Value							System Date
Data Type	Number	Varchar2	Varchar2	Varchar2	Varchar2	Varchar2	Date
Length	10	25	25	100	30	15

b. Nom de la taula: TITLE

TITLE

Column_Name	TITLE_ID	TITLE	DESCRIPTION	RATING	CATEGORY	RELEASE_DATE
Key Type	PK
Null / Unique	NN,U	NN	NN
Check				G, PG, R, NC17, NR	DRAMA, COMEDY, ACTION, CHILD, SCIFI, DOCUMENTARY
Data Type	Number	Varchar2	Varchar2	Varchar2	Varchar2	Date
Length	10	60	400	4	20

C. Nom de la taula: TITLE_COPY

TITLE_COPY

Column_Name	COPY_ID	TITLE_ID	STATUS
Key Type	PK	PK,FK
Null / Unique	NN,U	NN,U	NN
Check			AVAILABLE, DESTROYED, RENTED, RESERVED
FK Ref Table		title
FK Ref Column		title_id
Data Type	Number	Number	Varchar2
Length	10	10	15

D. Nom de la taula: RENTAL

RENTAL

Column_Name	BOOK_DATE	MEMBER_ID	COPY_ID	ACT_RET_DATE	EXP_RET_DATE	TITLE_ID
Key Type	PK	PK,FK	PK,FK			PK,FK
Default Value	System Date				+ 2 days
FK Ref Table		member	title_copy			title_copy
FK Ref Column		member_id	copy_id			title_id
Data Type	Date	Number	Number	Date	Date	Number
Length		10	10			10

E. Nom de la taula: RESERVATION

RESERVATION

Column_Name	RES_DATE	MEMBER_ID	TITLE_ID
Key Type	PK	PK,FK	PK,FK
Null / Unique	NN,U	NN,U	NN,U
FK Ref Table		MEMBER	TITLE
FK Ref Column		member_id	title_id
Data Type	Date	Number	Number
Length		10	10

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

          TABLE_NAME
          ----------
          MEMBER
          RENTAL
          RESERVATION
          TITLE
          TITLE_COPY

          CONSTRAINT_NAME                  C  TABLE_NAME
          ------------------------------- --- --------
          MEMBER_LAST_NAME_NN              C  MEMBER
          MEMBER_JOIN_DATE_NN              C  MEMBER
          MEMBER_MEMBER_ID_PK              P  MEMBER
          RENTAL_BOOK_DATE_COPY_TITLE_PK   P  RENTAL
          RENTAL_MEMBER_ID_PK              R  RENTAL
          RENTAL_COPY_ID_TITLE_ID_FK       R  RENTAL
          RESERVATION_RESDATE_MEM_TIT_PK   P  RESERVATION
          RESERVATION_MEMBER_ID            R  RESERVATION
          RESERVATION_TITLE_ID             R  RESERVATION
          ...
          18 rows selected

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: member_id_seq.

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

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

          SEQUENCE_NAME  INCREMENT_BY  LAST_NUMBER
          -------------  ------------  -----------
          TITLE_ID_SEQ              1           92
          MEMBER_ID_SEQ             1          101

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.

Title	Description	Rating	Category	Release_date
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
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
The Glob	A meteor crashes near a small American town and unleashed carnivorous goo in this classic	NR	SCIFI	12-AGO-95
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
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
Soda Gang	After discovering a cache of drugs, a young couple find themselves pitted against a vicious gang.	NR	ACTION	01-JUN-95

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.

First Name	Last Name	Address	State	Phone	Join Date
Carmen	Velasquez	283 King Street	Seatle	206-899-6666	08-MAR-90
LaDoris	Ngao	5 Modrany	Bratislava	586-355-8882	08-MAR-90
Midori	Nagayama	68 Via Centrale	Sao Paulo	254-852-5764	17-JUN-91
Mark	Lewis	6921 King Way	Lagos	63-559-7777	07-ABR-90
Audry	Ropeburn	86 Chu Street	Hong Kong	41-559-87	18-ENE-91
Molly	Urguhart	3035 Laurier	Quebec	418-542-9988	18-ENE-91

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

Title	Copy Number	Status
Willie and Christmas Too	1	AVAILABLE
Alien	1	AVAILABLE
	2	RENTED
The Glob	1	AVAILABLE
My Day Off	1	AVAILABLE
	2	AVAILABLE
	3	RENTED
Miracles on Ice	1	AVAILABLE
Soda Gang	1	AVAILABLE

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

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

Title	Copy_number	Customer	Book_Date	Date_return_expected	Date_returned
92	1	101	3 days ago	1 day ago	2 days ago
93	2	101	1 day ago	1 day form now
95	3	102	2 days ago	Today
97	1	106	4 days ago	2 days ago	2 days ago

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.

          TITLE                       COPY_ID  STATUS     EXP_RET_D
          --------------------------  -------  ---------- -----------
          Alien Again                       1  AVAILABLE
          Alien Again                       2  RENTED     05-NOV-97
          Miracles on Ice                   1  AVAILABLE
          My Day Off                        1  AVAILABLE
          My Day Off                        2  AVAILABLE
          My Day Off                        3  RENTED     06-NOV-97
          Soda Gang                         1  AVAILABLE  04-NOV-97
          The Glob                          1  AVAILABLE
          Willie and Cristmas Too           1  AVAILABLE  05-NOV-97
          
          9 rows selected

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

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.

          TITLE                       COPY_ID  STATUS     EXP_RET_D
          --------------------------  -------  ---------- -----------
          Alien Again                       1  AVAILABLE
          Alien Again                       2  RENTED     05-NOV-97
          Interstellar Wars                 1  RENTED     08-NOV-97
          Interstellar Wars                 2  AVAILABLE
          Miracles on Ice                   1  AVAILABLE
          My Day Off                        1  AVAILABLE
          My Day Off                        2  AVAILABLE
          My Day Off                        3  RENTED     06-NOV-97
          Soda Gang                         1  AVAILABLE  04-NOV-97
          The Glob                          1  AVAILABLE
          Willie and Cristmas Too           1  AVAILABLE  05-NOV-97
          
          11 rows selected

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.

                           
          Nombre                         Null?     Tipo 
          ------------------------------ --------  ------------
          TITLE_ID                       NOT NULL  NUMBER(10) 
          TITLE                          NOT NULL  VARCHAR2(60)
          DESCRIPTION                    NOT NULL  VARCHAR2(400)
          RATING                                   VARCHAR2(4)
          CATEGORY                                 VARCHAR2(20)
          RELEASE_DATE                             DATE
          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:

          TITLE                       PRICE
          --------------------------  -----
          Willie and Cristmas Too        25  
          Alien Again                    35  
          The Glob                       35
          My Day Off                     35   
          Miracles on Ice                98
          Soda Gang                      35
          Interstellar Wars              29   

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

          CONSTRAINT_NAME    C   SEARCH_CONDITIONS
          ------------------ --- ------------------
          TITLE_PRICE_NN     C   PRICE IS 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.

          MEMBER              TITLE                      BOOK_DATE  DURATION
          ------------------  -------------------------  ---------  --------
          LaDoris Ngao        The Glob                   04-NOV-97
          Molly Urguhart      Miracles on Ice            02-NOV-97         2
          Carmen Velasquez    Willie and Cristmas Too    03-NOV-97         1
                              Willie and Cristmas Too    03-NOV-97         1
                              Alien Again                05-NOV-97