- Important fact:
- The stored data format is
independent from
the presentation data format
In other words:
- The way you store the data is independent from the way you present it
- The stored data format is
independent from
the presentation data format
- Suppose the
information on
a grade report on students must
look like
this:
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 presentation format)
- Notice that
this format contain
redundancy in the
information:
- The fact that "CS170" is "Intro to CS" is stored multiple times
- So is the fact that "CS255" is the course "Assembler Programming"
- And so on...
(As you know, redundancy of information can cause inconsistent data if you missed some updates !!!)
- Suppose we
store the
data using
3 (separate) files:
- A file on
students:
ID Name 007 James Bond 123 John Doe - A file on
courses:
CID Course Number Name 1542 CS170 Intro to CS 8726 CS255 Assembler Programming 8976 CS355 Computer Architecture - A file about
grade report
(using strange looking numbers):
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+
- A file on
students:
- Important observation:
- The data in the presentation format can be obtained by combining the information in the 3 (stored) data files !!!
Schematically:
- We will learn
later in the course
on 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:
(Database: ~cheung/377/Syllabus/1-files/Logical-Data-Depend) >> cs377-sql (Select the LogDataIndep database) >> show tables +------------------------+ | Tables_in_LogDataIndep | +------------------------+ | course | | gradereport | | student | +------------------------+ >> select * from student +------+------------+ | id | name | +------+------------+ | 007 | James Bond | | 123 | John Doe | +------+------------+ >> select * from course +------+---------+-----------------------+ | cid | cnumber | cname | +------+---------+-----------------------+ | 1542 | CS170 | Intro to CS | | 8762 | CS255 | Assembler Programming | | 8976 | CS355 | Computer Architecture | +------+---------+-----------------------+ >> 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+ | +------+------+------------+-------+
>> select name, cnumber, cname, semester, grade from student, course, gradereport where student.id = gradereport.id and course.cid = gradereport.cid +------------+---------+-----------------------+------------+-------+ | name | cnumber | cname | 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 199 | C+ | | John Doe | CS170 | Intro to CS | Spring 200 | A- | | John Doe | CS255 | Assembler Programming | Fall 2001 | C+ | | John Doe | CS355 | Computer Architecture | Spring 200 | D+ | +------------+---------+-----------------------+------------+-------+Comment:
- We need to write a (computer) program
to perform the
combination
- The computer program that
we used is:
select name, cnumber, cname, semester, grade from student, course, gradereport where student.id = gradereport.id and course.cid = gradereport.cidwritten in the SQL programming language !!!
- We need to write a (computer) program
to perform the
combination
- In the presentation above,
I have used some
colloquial term to
make things easier to
understand, like:
- Presentation format
- Stored format
There are "official" terminologies in database literature for these terms
- External schema:
- The
external schema
describes the how the
data is
perceived (= seen) by
the user
- External schema = the way (format) that the users see the data (External schema = "presentation" format)
Note: there are multiple external schemas in use
- There are usually
many kinds of
users
- Each kind of user will view the data in a different way !!!
- Therefore: there can be multiple external schemas used in the database system !!!
- The
external schema
describes the how the
data is
perceived (= seen) by
the user
- Cenceptual schema:
- The
conceptual schema
describes the how the
data is
stored in
data files
- Conceptual schema = the way (format) that the data files store the data (Conceptual schema = "stored" data format)
Note: there is only one conceptual schema in use
- There is only one
copy of each data file
- Therefore: you can only have one conceptual schema used in the database system !!!
- The
conceptual schema
describes the how the
data is
stored in
data files
- Mapping the
(one) conceptual schema to
an external schema:
- The (one) conceptual schema can
be mapped to
different external schemas:
- For each mapping from the (one) conceptual schema to a (specific) external schema, we must write the mapping algorithm (in the SQL programming language)
- The (one) conceptual schema can
be mapped to
different external schemas:
- 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