Transcript S4

Welcome:
To the forth learning sequence
“ Possible representation (1) “
Recap
: In the previous learning sequence, we discussed the basic definitions of
database subject.
Present learning: We shall explore the following topics:
- Regular organization.
- Factoring out the CITY value.
- Indexing on city.
- Combination the previous representations.
1
Possible representation
- In this section we make a simple collection of
sample data and consider some of many ways it
could be represented in storage at the level of
stored record interface.
2
Possible representation


The sample data is consists of information about
five suppliers; for each supplier we wish to
record a:
Supplier number (S#), a supplier name (SNAME),
and location (CITY).
3
Possible representation
1- Note that the (Figure1) actually a very simple
conceptual model of the data.
S#
SNAME
CITY
S1
Smith
London
S2
JOHNS
Paris
S3
BLACKE
Paris
S4
CLARK
London
S5
ADAMS
Athens
Fig 1: sample data regular organization
4
Possible representation
The representation in fig 1 (file containing five
stored record occurrence) has the advantage of
simplicity but would probably be inadequate for
various reasons in a realistic situation.
Suppose, for example we have (10.000) suppliers
instead of just of five, but that they were located
in only (10) different cities.
5
Possible representation
2- If we assume that the amount of storage
required for a pointer is less than that required a
city name, the representation illustrated in fig2
will clearly save some storage space in such a
situation.
6
Possible representation
S#
SNAME
S1
Smith
S2
JOHNS
S3
BLACKE
S4
CLARK
S5
ADAMS
CITY
POINTER
CITY
Athens
London
Paris
City file
Supplier
file
Fig2: Factoring out the CITY value
7
Possible representation
S#
SNAME
S1
Smith
S2
JOHNS
S3
BLACKE
S4
CLARK
S5
ADAMS
CITY
POINTER
CITY
Athens
London
Paris
City file
Supplier
file
Fig2: Factoring out the CITY value
8
Possible representation



Here we have two stored files, a supplier file and
a city file, with pointers out of the former into the
later.
These pointers are Stored Record Addresses
(SRAs). The only advantage of this representation
(compared with the previous one) is the saving in
space.
A request to find all properties (attributes) of a
given supplier (entity) will require at least one
more access than before.
9
Possible representation
3- A request to find all suppliers (entities) in a
given CITY will involve several more access.
If such a case requested and it is an important
one.
The DBA may choose the alternative
representation shown in fig3.
10
Possible representation
CITY
SUPPLEIR
POINTER
Athens
London
Paris
S#
SNAME
S1
Smith
S2
JOHNS
S3
BLACKE
S4
CLARK
S5
ADAMS
Supplier file
City file
Fig3: Indexing on city
11
Possible representation
CITY
SUPPLEIR
POINTER
Athens
London
Paris
S#
SNAME
S1
Smith
S2
JOHNS
S3
BLACKE
S4
CLARK
S5
ADAMS
Supplier file
City file
Fig3: Indexing on city
12
Possible representation


Here again we have two stored file. A supplier file
and a city file but this time there are pointers out
of the later city into the firmer supplier.
Each city stored record occurrence contains
pointers to all corresponding supplier stored
record occurrence.
13
Possible representation


It should be obvious that the last representation
of data (factoring out the city) is better than the
previous figure (Indexing on city) for queries
asking for all supplier in a given city but worse
for queries asking for all attributer of given
supplier.
Here we conclude that representation of data is
strongly dependent on the queries (access)
asked by the system user.
14
Possible representation


Also it is worth to mention, that it is the
database Management System (DBMS), not the
Access Method that maintain the pointers; the
access method is only responsible for assigning
a unique stored record address (SRA) as it was
mentioned before.
The interesting thing about the previous
representation is that the CITY file is an index
to the SUPPLIER file (an index controlled by
the DBMS, not by the access method).
15
Possible representation
4- To obtain the advantages of each of the
previous two representation (at the cost of
repairing slightly more storage space and
probably more significant having more pointer
maintenance to do when changes occur); we
can have the following representation for data
(fig 4).
16
Possible representation
Athens .
S1
Smith
London . .
.
S2
Jones
S3
Black
Paris . .
S4
Clark
S5
Adams
Fig 4: Combination the previous Fig2@3
17
Possible representation
Athens .
London . .
Paris . .
.
S1
Smith
.
S2
Jones
S3
Black
S4
Clark
S5
Adams
.
Fig 4: Combination the previous Fig2@3
18
Possible representation
Athens .
London . .
Paris . .
.
.
.
S1
Smith
.
S2
Jones
S3
Black
S4
Clark
S5
Adams
.
Fig 4: Combination the previous Fig2@3
19
Possible representation
Athens .
London . .
Paris . .
.
.
.
S1
Smith
.
S2
Jones
S3
Black
S4
Clark
S5
Adams
.
Fig 4: Combination the previous Fig2@3
20
Summary: In this learning sequence, we discussed
the following topic:
- Four possible representations for some sample
data.
21
END
22