Suppose you are using the database composed of the following tables: a. Identify

Question

Suppose you are using the database composed of the following tables:

a.Identify the primary keys.

b.Identify the foreign keys.

c.Draw the relational schema to show the relationship between DIRECTOR and PLAY.

d.Create tables. Do not forget about primary and foreign keys.

e.Insert data.

f.Suppose you wanted quick lookup capability to get a listing of all plays directed by a given director. Which table would be the basis for the INDEX table, and what would be the index key? Create the index. Provide SQL code.

g.Create a view that lists all plays in alphabetical order and names of the directors.
Play                                                     Director
Cat On a Cold, Bare Roof                  Goofy
Hold the Mayo, Pass the Bread          Hollywoody
...                                                         ...

h.Find all directors who were born between January 1st, 1962 and December 31st, 1962. Provide SQL code and the result.

i.Delete director with number 101 from directors table. Provide SQL code and the result of select statement for both tables before deletion and after deletion.

j.Update table PLAY. Change the name of play #1006 to Starbuck in Biloxi.

k.Find all directors who directed less than two plays. Provide SQL code and the result.

Table name: DIRECTOR DIR NUM DIR LNAME OR DOB 00 Broadway 12-Jan-65 101 Hollywoody 18-Nov-53 102 Goofy 21-Jun-62 Table name: PLAY PLAY CODE PLAY NAME 1001 Cat On a Cold Bare Roof 1002 Hold the Mayo, Pass the Bread 1003 INever Promised You Coffee 1004 Sily Putty Goes To Washington 1005 See No Sound, Hear No sight 006 Starstruck in Biloxi 1007 Stranger in Perrot Ice DR NUM 102 101 102 100 101 102 101

Explanation / Answer

a. DIRECTOR - DIR_NUM

PLAY - PLAY_CODE

b. DIRECTOR - no foreign key

PLAY - DIR_NUM

d. create table DIRECTOR (DIR_NUM number,DIR_LNAME varchar2(20),DIR_DOB date,PRIMARY key(DIR_NUM))

create table PLAY (PLAY_CODE number,PLAY_NAME varchar2(20),DIR_NUM numberPRIMARY KEY(DIR_NUM),FOREIGN KEY(DIR_NUM) REFERENCES DIRECTOR(DIR_NUM))

e. insert into DIRECTOR values(102,'Goofy','21-JUN-62')

insert into PLAY value(1001,'Cat on a cold,Bare Roof',102)

k. select DIR_LNAME from Director where DIR_NUM in (select DIR_NUM from PLAY where count(DIR_NUM)>2 group by DIR_NUM)

j. update PLAY set PLAY_NAME='Starbuck in Biloxi' where PLAY_CODE=1006

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.