CMSC 4003 - University of Central Oklahoma



CMSC 4003

Lab Assignment 7: Views

Name:

Due: See the due date in WebCT calendar.

1. Download lab7_schema.sql and run lab7_schema.sql in SQL*Plus (@lab7_schema). The schema is based on the EER diagram used in Lab Assignment 6. In this lab, records are inserted into tables directly from the file lab7_schema.sql.

2. Create a view named OKCStudent using the following statement:

create or replace view OKCStudent(id, name)

as select sid, sname

from student

where curaddress='OKC';

(a) Run the following SQL statement to check the view.

select * from OKCStudent;

(b) A view can be considered as a virtual table with no data in it. When Oracle runs an SQL statement on a view, it first transforms the SQL statement into an equivalent SQL statement on the base tables and then runs the transformed SQL statement to retrieve the result. So, the following SQL statement

select * from OKCStudent where name='Susan';

is transformed into:

select sid from student where sname='Susan' and curaddress='OKC';

Try both of the above statements to see the results.

(c) Run the following SQL statements:

insert into OKCStudent(id, name) values('A0001', 'Scott');

commit;

Run the following SQL statement:

select * from OKCStudent;

Do you see the tuple that you have just inserted? Why?

Answer:

Run the following SQL statement:

select * from Student;

Do you see the tuple that you have just inserted? Why?

Answer:

(d) You have to be careful about the semantic of the view when inserting tuples into a view. In many situations, you would rather not allow inserting into a view.

Change the view definition of OKCStudent to make it read only as follows:

create or replace view OKCStudent(id, name)

as select sid, sname from student where curaddress='OKC'

with read only;

(e) Run the following SQL statements to check the view.

select * from OKCStudent;

insert into OKCStudent(id, name) values('A1000', 'Tony');

Can you insert the tuple this time?

Answer:

3. Create another view as follows:

create or replace view GoodStudent (sid, degree, programname, avggrade)

as select s.sid, progtype, programname, avg(grade)

from degreeprogram dp, student s, taken t

where dp.progid=s.major and s.sid=t.sid

group by s.sid, progtype, programname

having avg(grade)>3.0;

(a) Run the following SQL statement to check the view.

select * from GoodStudent;

(b) For the following SQL statement:

select * from GoodStudent where degree='BS';

provide the corresponding transformed SQL statement on the base tables.

Answer:

Run your transformed SQL statement to compare with the SQL statement directly on the view GoodStudent.

(c) Run the following insertion statement:

insert into GoodStudent (sid) values ('A1001');

Why the tuple cannot be inserted?

Answer:

4. Create the follow view:

create or replace view studentcourse (sid, sname, cno, grade)

as select s.sid, sname, cno, grade

from student s, taken t, courseoffering co

where s.sid=t.sid and t.seqid=co.seqid;

(a) For the following SQL statement:

select cno, count(*), max(grade), min(grade), avg(grade)

from studentcourse

where cno='CS480'

group by cno;

provide the corresponding transformed SQL statement on the base tables.

Answer:

Run your transformed SQL statement to compare with the SQL statement directly on the view studentcourse.

5. Create the following view:

create or replace view facultydept (facssno, facname, deptname)

as select facssno, facname, deptname

from faculty f, department d

where f.worksfor=d.deptid;

(a) Run the following SQL statement to check the view:

select * from facultydept;

(b) Try the following two insertion statements, one into studentcourse and the other into facultydept

insert into studentcourse (sid, sname) values ('A1002', 'Mary');

insert into facultydept (facssno, facname) values ('000000000', 'Jim');

commit;

Indicate why the second insertion works but the first one does not.

Answer:

6. Now you design and create the following views.

(a) Define a view OKCGoodStudent on top of the two views OKCStudent and GoodStudent.

OKCGoodStudent (id, name, degree, programname, avggrade)

Answer:

(b) A view contains only those students in CS480 whose major is offered by the CS department.

CS480CSStudent (sid, sname, seqid, semester, year, instructor, grade)

Answer:

Run your CREATE VIEW statements to test their correctness.

Note: You can also use CREATE MATERIALIZED VIEW statement to create materialized views in Oracle. Use SQL references at otn. to check the syntax of the statement.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download