CS457 Syllabus & Progress

SQL: Logical Data Independence

SQL has a "View Definition Language" component that can achieve Logical Data Independence

Flashback....

Recall there are 2 kinds of data "independences":

Physical data independence

Physical data independence = the ability to change the Physical storage format of the data files without having to change the program (so you don't need to re-compile the code !!!)

In other words:

You can change the structure of the physical data records (e.g., add a field) without having to change the programs to access the new record structure
All you need to do is:

Logical data independence

Logical data independence = the ability to present the stored data in any format to the user

In other words:

Although the database system stores the data in one format (called the conceptual database schema ), the database system can present the data in different ways to different users

(I.e., the view of the data can be adapted to the need of the user )

Here is the class notes on logical data independence: click here

Implementing the logical data independence:

I have demonstrated (long ago) how to implement the logical data independence feature of database systems:
Notable fact:

After studying SQL, you can now fully appreciate the Logical Data Independence concept....

External Schema or View (a.k.a.: Virtual table/relation)

A view is a virtual relation that is derived from:

Property of a virtual relation:

A virtual relation (= view) does not exist as in physical form
I.e.: the tuples in a virtual relation (= view) are not stored physically in the database
The tuples in a virtual relation (= view) are computed as a temporary relation when the virtual relation is used

In other words:

Example temporary relation.

Scenario:

The list contains the following information:

FName LName ProjectName #HoursWorked

You could obtain the data using this SQL query

SELECT fname, lname, pname, hours FROM employee, works_on, project WHERE ssn = essn AND pno = pnumber Output: FNAME LNAME PNAME HOURS ------ -------- --------------- ---------- John Smith ProductX 32.5 John Smith ProductY 7.5 Frank Wong ProductY 10 Frank Wong Reorganization 10 Frank Wong ProductZ 10 Frank Wong Computerization 10 Joyce English ProductX 20 Joyce English ProductY 20 John Doe ProductZ 40 James Borg Reorganization 0 Jack Wallace Reorganization 15 Jack Wallace Newbenefits 20 Jake Jones Computerization 35 Jake Jones Newbenefits 5 Alice Miller Computerization 10 Alice Miller Newbenefits 30

To find the activities of "John Smith", we can use the following temporary relation construct:

SELECT * FROM ( SELECT fname, lname, pname, hours FROM employee, works_on, project WHERE ssn = essn AND pno = pnumber ) EmpActivity WHERE fname = 'John' AND lname = 'Smith' Output: FNAME LNAME PNAME HOURS ------ -------- --------------- ---------- John Smith ProductX 32.5 John Smith ProductY 7.5

The temporal relation EmpActivity is created on the fly !!!!

Creating virtual relations

A view (= virtual relation) is defined using the CREATE VIEW command.

Example:

CREATE VIEW EmpActivity AS ( SELECT fname, lname, pname, hours FROM employee, works_on, project WHERE ssn = essn AND pno = pnumber )

Result:

The CREATE VIEW command defines an virtual table (relation) called EmpActivity
This virtual table/relation will be construct when we use the EmpActivity relation in a query !!!

Example: Find all activities of John Smith

SELECT * FROM EmpActivity WHERE fname = 'John' AND lname = 'Smith' FNAME LNAME PNAME HOURS ---------- ---------- --------------- ---------- John Smith ProductX 32.5 John Smith ProductY 7.5

External Schema (View) is computed on the fly.

Consider the following view definition :

CREATE VIEW Dept_Info(dname, no_emps, total_sal) AS ( SELECT dname, count(ssn), sum(salary) FROM department, employee WHERE dnumber = dno GROUP BY dname )

Example query on this view:

Find all department with >2 employees:

Solution:

SELECT * FROM Dept_Info WHERE no_emps > 2 DNAME NO_EMPS TOTAL_SAL --------------- ---------- ---------- Administration 3 93000 Research 4 133000

Change the salary of employees:

UPDATE employee SET salary = salary + 10000 SELECT * FROM Dept_Info WHERE no_emps > 2 DNAME NO_EMPS TOTAL_SAL --------------- ---------- ---------- Research 4 173000 Administration 3 123000

LOGICAL data independence
- Now you have all the information that you need to understand the full meaning of logical data independence.
- To re-iterate:
- Question:
  Answer:

Demo scetion

This section contains the commands I used in class to demonstrate the logical table concept:

Comment: use "cheung-sql" use companyDB
SELECT dname, count(ssn), sum(salary) FROM department, employee WHERE dnumber = dno GROUP BY dname;
SELECT * FROM ( SELECT dname, count(ssn), sum(salary) FROM department, employee WHERE dnumber = dno GROUP BY dname ) DeptInfo WHERE dname = 'Research';
CREATE VIEW DeptInfo(dname, no_emps, total_sal) AS ( SELECT dname, count(ssn), sum(salary) FROM department, employee WHERE dnumber = dno GROUP BY dname ) ;
select * from DeptInfo; /* Show data in virtual table */
UPDATE employee SET salary = salary + 44 where fname = 'John' and lname = 'Smith'; UPDATE employee SET salary = 30000 /* reset to original value */ where fname = 'John' and lname = 'Smith';
drop view DeptInfo;