CS377 Syllabus & Progress

Logical Data Independence (Database "LogDataIndep" on home Linux system)

Information needs

Fact:

Illustration:

Example:

One group of users want to access the grade reports for students
Another group of users want to access the financial records for students
Yet a third group of users need health records for students
And so on...

The traditional (pre-Database) way to solve diverse information needs

Traditional solution to satisfy different user needs:
Example: suppose we have 3 different applications on students

We maintain one data file per application:

Grade Report Financial record (other files)
StuID Name Course Grd StuID Name Due +-----+----------+-------+----+ +-----+----------+------+ ..... | X12 | John Doe | CS171 | A | | X38 | Jane Ear | 2000 | +-----+----------+-------+----+ +-----+----------+------+ | X12 | John Doe | CS255 | B | | X12 | John Doe | 1500 | +-----+----------+-------+----+ +-----+----------+------+ | X38 | Jane Ear | CS170 | A | +-----+----------+-------+----+ | X38 | Jane Ear | CS171 | B | +-----+----------+-------+----+

Problems with this approach:

Some information may be stored multiple times

Example: The fact that X12 is the student ID of "John Doe" is stored 3 times

Grade Report Financial record (other files) StuID Name Course Grd StuID Name Due +-----+----------+-------+----+ +-----+----------+------+ ..... | X12 | John Doe | CS171 | A | | X38 | Jane Ear | 2000 | +-----+----------+-------+----+ +-----+----------+------+ | X12 | John Doe | CS255 | B | | X12 | John Doe | 1500 | +-----+----------+-------+----+ +-----+----------+------+ | X38 | Jane Ear | CS170 | A | +-----+----------+-------+----+ | X38 | Jane Ear | CS171 | B | +-----+----------+-------+----+

Duplicate information storage can cause data inconsistency if updates are not applied consistently

Example: before

Grade Report Financial record
StuID Name Course Grd StuID Name Due +-----+----------+-------+----+ +-----+----------+------+ ..... | X12 | John Doe | CS171 | A | | X38 | Jane Ear | 2000 | +-----+----------+-------+----+ +-----+----------+------+ | X12 | John Doe | CS255 | B | | X12 | John Doe | 1500 | +-----+----------+-------+----+ +-----+----------+------+ | X38 | Jane Ear | CS170 | A | +-----+----------+-------+----+ | X38 | Jane Ear | CS171 | B | +-----+----------+-------+----+

Jane Ear asks the Registrar to change her name:

Grade Report Financial record
StuID Name Course Grd StuID Name Due +-----+----------+-------+----+ +-----+----------+------+ ..... | X12 | John Doe | CS171 | A | | X38 | Jane Ear | 2000 | +-----+----------+-------+----+ +-----+----------+------+ | X12 | John Doe | CS255 | B | | X12 | John Doe | 1500 | +-----+----------+-------+----+ +-----+----------+------+ | X38 | Jane Air | CS170 | A | +-----+----------+-------+----+ | X38 | Jane Air | CS171 | B | +-----+----------+-------+----+

Result: inconsistent data !!!

How to satisfy the information need from diverse users....

Solution:

We store the information need from every group in the database
Result:
We then filter (extract) the right amount of information and present the information in the appropriate format to each group of users

Logical Data Independence

Logical Data Independence:

Logical Data Independence = the ability to present the stored information in different way to different users

Analogy:

Analogy:

One man said it's like a tree trunk (he felt the feet)
Another said it's like a snake (he felt the tail)
And so on...

Different users perceive the data stored differently !

Terminology

External schema:

An external schema = a way (= format) that some user(s) views (perceives) the data
There are many external schemas in a Database system - typically one external schema per user class

Conceptual schema:

The conceptual schema = the way (= format) in which the data is actually stored in the database

Logical Data Independence:

The way (= format) taht you view (perceive) the data is independent from the way (= format) you store the data.

How to achieve Logical Data Independence:

The Database system has the ability to define a mapping operation between:

The (one) conceptual schema (how to store the data) to any of the external schemas (how you view the data)

Graphically:

/------> External schema 1 / / Conceptual schema ---------> External schema 2 \ \ \------> External schema 3

NOTE:

nothing comes for free

To achieve Logical Data Independence, we need to "do something" - specifically: run a program to put the data into the desired format !

Illustrative Example: Logical Data Independence in action....

A typical grade report of students looks like the following:

Name	Course	Course Name	Semester	Grade
James Bond	CS170	Intro to CS	Fall 1997	B-
James Bond	CS255	Assembler programming	Fall 1998	B+
James Bond	CS355	Computer Architecture	Spring 1999	C+
John Doe	CS170	Intro to CS	Spring 2000	A-
John Doe	CS255	Assembler programming	Fall 2001	C+
John Doe	CS355	Computer Architecture	Spring 2002	D+

This is the way a user views the data
As you can see, the data is highly redundant:
- The fact that "CS170" is "Intro to CS" is store multiple times
- So is the fact that "CS255" is the course "Assembler Programming"
- And so on...

The data can be stored in 3 separate files to reduce redundancy:

Students

ID Name

007 James Bond

123 John Doe
Courses

CID Course Number Name

1542 CS170 Intro to CS

8726 CS255 Assembler Programming

8976 CS355 Computer Architecture

ID	Name
007	James Bond
123	John Doe

CID	Course Number	Name
1542	CS170	Intro to CS
8726	CS255	Assembler Programming
8976	CS355	Computer Architecture

Grade Report

ID CID Semester Grade

007 1542 Fall 1997 B-

007 8726 Fall 1998 B+

007 8976 Spring 1999 C+

123 1542 Spring 2000 A-

123 8726 Fall 2001 C+

123 8976 Spring 2002 D+

ID	CID	Semester	Grade
007	1542	Fall 1997	B-
007	8726	Fall 1998	B+
007	8976	Spring 1999	C+
123	1542	Spring 2000	A-
123	8726	Fall 2001	C+
123	8976	Spring 2002	D+

NOTE:

meta data

"Physical Data Independent"

The various files must now be merged together to produce the desired result for the user !!!
Schematically:
We will learn how to define the mapping operation after we have studied SQL
(When I discuss the mapping operation, I will remind you of this material.. - promise :-))

Demo: Logical Data Independence (the demo is in the LogDataIndep database)

Login:

Show the database:

(See: 377/Syllabus/1-files/Logical-Data-Depend) >> mysql -h 192.168.1.20 -u cheung -p (w04h) >> use LogDataIndep mysql> show tables; +-----------------------+ | Tables_in_cs377_teach | +-----------------------+ | course | | gradereport | | student | +-----------------------+ mysql> select * from student; +------+------------+ | id | name | +------+------------+ | 007 | James Bond | | 123 | John Doe | +------+------------+ mysql> select * from course; +------+---------+-----------------------+ | cid | cnumber | cname | +------+---------+-----------------------+ | 1542 | CS170 | Intro to CS | | 8762 | CS255 | Assembler Programming | | 8976 | CS355 | Computer Architecture | +------+---------+-----------------------+ mysql> select * from gradereport; +------+------+------------+-------+ | id | cid | semester | grade | +------+------+------------+-------+ | 007 | 1542 | Fall 1997 | B- | | 007 | 8762 | Fall 1998 | B+ | | 007 | 8976 | Spring 199 | C+ | | 123 | 1542 | Spring 200 | A- | | 123 | 8762 | Fall 2001 | C+ | | 123 | 8976 | Spring 200 | D+ | +------+------+------------+-------+

How to get the desired output format:

We now define a mapping operation to produce the desired presentation format:

create view my_report(stud_name, course_number, course_name, semester, grade) as (select name, cnumber, cname, semester, grade from student, course, gradereport where student.id = gradereport.id and course.cid = gradereport.cid); Query OK, 0 rows affected (0.03 sec)

We have a new table in the data base:

Now you can use the my_report logical view without worrying about how the data is actually stored:

Example 2

This command show student and # courses he has taken:

select student.id, name, count(cid) from student, gradereport where student.id = gradereport.id group by student.id, name;

This create a new view:

create view view2 as (select student.id, name, count(cid) from student, gradereport where student.id = gradereport.id group by student.id, name);

Summary....

The external schema describes the way (format) that the users WANTS to see the data

The conceptual schema describes the way (format) that the system STORES the actual data

How you want to see the data is your business

How I want to store the data is my business

Your business should ideally not interfere with my business !!!
We can achieve this goal with a transformation (mapping) operation (= a computer program in the SQL language !!!)

The mapping (transformation) operation is a computer algorithm (in SQL) that combines the data in the conceptual schema format and produces an output in the external schema format
Keep these concepts in mind when we learn about the Logical Data Independence

Logical Data Independence

Logical data independence:

Logical data independence = the ability to present the data stored in a conceptual schema format in any way specified by a user.
Because we can present the data stored in a conceptual schema format in any way we like, we can also:

Therefore, we can also define Logical Data Independence as:

Logical data independence = the ability to change the conceptual schema without having to change the external schemas (and the application programs that are written based on the external schemas)
(The application programs does not need to change because the format of their input data is unchanged !)

How to achive Logical Data Independence in Database Systems

The data in the external schema(s) is obtained from data stored in the conceptual schema (by running some program to merge the data files)

Question:

make a change

conceptual schema

Can we still obtain the same external schema ?

In other words: can we still present the data in the same format as before to the user ?

The answer is clearly Yes

external schema(s)

conceptual schema

external schema(s)

using a DIFFERENT merging program

So we need to update the mapping operation if we change the conceptual schema

In other words:

Database systems can provide support for Logical Data Independence by using the ability to redefine the mapping between external schemas and the conceptual schema

Example:

Previously, we used the following mapping operation to obtain the desired output format:

create view my_report(stud_name, course_number, course_name, semester, grade) as ( select name, cnum, cname, sem, grade from student, course, grade_report where id = s_id and cid = c_id );

When the conceptual scheme changes (i.e., the structure of one ore more of the tables student, course and grade_report changes), we must:

Explained with a figure:

To accommodate the change in the conceptual schema, all we need to do is to change the mapping operation between the external schema and the conceptual schema:

Why is "Logical Data Independence" such a Big Deal ???

Why

Logical Data Independence

big deal

Data to the end user is not just a file that he/she looks (stares ?) at !!!

The end user may have their own non-database applications that perform further processing

(One such application is Excel !!!)

The data analysis tools (programs) used by the end user requires a specific data format
(Or else, the end user cannot import the data into their tool ofr further analysis !!!)

The end user programs requires that the format of the data be unchanged in the event that the Conceptual Scheme is updated !!!!
(Or else, the end users will have to change the way they import the data !!!)
The Logical Data Independence technique can maintain the end user data file structure in the same format
It will make end users very happy customers !

This ability to keep presenting the user with the same format eventhough the way the actual data is stored has changed, is known as:
The term logical refers to thefact that: