From the following base tables: (20 points) MovieStar (name, address, gender, bi

Question

From the following base tables: (20 points)

MovieStar (name, address, gender, birthdate)

MovieExec (name, address, cert#, netWorth)

Studio (name, address, presC#)

Construct the following views:

A)A view RichExec giving the name, address, certificate number and net worth of all executive with a net worth of at least $10,000,000.

B)A view StudioPres giving the name, address, and certificate number of all executive who are studio presidents.

2,For the same base tables in Question 5, declare indexes on the following attributes or combination of attributes

A)address of MovieExec

B)gender and birthdate

Explanation / Answer

Question 1)

view: It is a virtual table based on the result-set of an SQL statement.

A)

create view RichExec As                 //creates a view called RichExec

select name,address,cert#,netWorth                   // list of attributes that appear in result table

From MovieExec                                               //Choose the table where does this search go on

Where netWorth>=10000000       ;                      // It checks row by row networth is greater than or equal to 10000000

Below is basic tamplate to create view:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

B)

Create view StudioPres AS //creates a view called StudioPres

select name,address,cert#                 // list of attributes that appear in result table

from studio inner join MovieExec on studio.name=MovieExec.name;    // it checks whether executive is studio president

JOIN: It is used to combine rows from two or more tables, based on a related column between them.

Based on condition, selects records that have matching values in both tables.

Below is basic tamplate to create view:
SELECT column1, column2, ...
FROM table_name

INNER JOIN table2 on condition;

Question 2)

Indexes: These are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.

A)

create index first // first is the name given for index

on MovieExec(address);                    //index is created on address attribute of MovieExec

Below is basic tamplate to create index:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

B)

create index second              // second is the name given for index

on MovieStar(gender, birthdate) ;            // //index is created on gender, birthdate attributes of MovieStar

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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