4-dbms
TRANSCRIPT
-
8/9/2019 4-dbms
1/54
Schemas, Subschema and Instances
Schema:-
The plan (or formulation ) of the databaseis known as schema.
Schema gives the names of the entitiesand attributes.
Schema speci es the relationship amongthem.
-
8/9/2019 4-dbms
2/54
It is a framework into which the values ofthe data items (or elds) are tted.
The plans or the format of schemaremains the same.
ut the values tted into this formatchanges from instance to instance.
The following e!ample speci es theconte!t of the schema of "#s $ %& amanufacturing compan'.
-
8/9/2019 4-dbms
3/54
Product
Prod-id Prod-desc Unit-cost CustomerCust-id
Cust-name
Cust-street
Cust-city
Cust-bal
Sales
Cust-id Prod-id Prod- city Prod-price
Schema diagram for
sales record
-
8/9/2019 4-dbms
4/54
The schema diagram displa's the structureof each record t'pe but not the actualinstances of records.
ach ob ect in the schema& for e!ample&*+, %T& % ST," +& S$/ S are calledschema construct.
-
8/9/2019 4-dbms
5/54
Schema name is S$/ S-+ %,+t'pe *+, %T0record
*+, -I :String1 *+, - S%:String1 2IT-%,ST:integer1 end
t'pe % ST," +0record % ST-I :integer1 % ST-2$" :String1 % ST-ST+ T0String1 % ST-%IT30String1 % ST- $/0integer1 end
t'pe S$/ S0record
% ST-I 0integer1 *+, -I 0String1 *+, -4T30integer1 *+, -*+I% 0integer1 end
$ttributes (orelds& or dataitems)
$ttributes (orelds& or dataitems)
$ttributes (orelds& or data
items)
Schema de ned using databaselan ua e
-
8/9/2019 4-dbms
6/54
$ database s'stem can have severalschemas partitioned according to the levelsof abstraction.
In general Schema can be categorised intwo parts.
5) /ogical Schema
6) *h'sical Schema
-
8/9/2019 4-dbms
7/54
The logical schema is concerned with
e!ploiting the data structures o7ered b' a"S in order to make the schema
understandable to the computer.
The physical schema deals with themanner in which the conceptual databaseshall get represented in the computer as astored database.
-
8/9/2019 4-dbms
8/54
The logical schema is the most importantas programs use it to constructapplications.
The ph'sical schema is hidden beneath thelogical schema and can usuall' be changedeasil' without a7ecting applicationprograms.
"Ss provide / and S / in order tomake the speci cation of both the ph'sicaland logical schema eas' for the $.
-
8/9/2019 4-dbms
9/54
Subschema:-
It is a subset of the schema and inheritsthe same propert' that a schema has.
The plan(or schema) for a view is oftencalled subschema.
Subschema refers to an applicationprogrammer8s (user8s) view of the data itemt'pes and record t'pes& which he or sheuses.
-
8/9/2019 4-dbms
10/54
Subschema gives the users a window
through which he or she can view onl'that part of the database& which is ofinterest to him.
i7erent application programs can havedi7erent view of data.
Individual application programs canchange their respective subschemawithout e7ecting subschema views ofothers.
-
8/9/2019 4-dbms
11/54
The "S software derives the
subschema data re9uested b' applicationprograms from schema data.
The database administrator ( $)
ensures that the subschema re9uested b'application programs is derivable fromschema.
The change in ph'sical organisation ofdata& application programs for subschemaneed not be changed or modi ed.
-
8/9/2019 4-dbms
12/54
-
8/9/2019 4-dbms
13/54
I2ST$2% S:-
;hen the schema framework is lled
in the data item values or the contentsof the database at an' point of time (orcurrent contents)& it is referred to as aninstance of the database.
The term instance is also called asstate of the database or snapshot.
-
8/9/2019 4-dbms
14/54
PRO UC!-I PRO UC!- "SC U#I!-COS!
$56 ST / $/"I+$? =@@@
6@@
==
-
8/9/2019 4-dbms
15/54
-
8/9/2019 4-dbms
16/54
CUS!-I PRO -I )!& U#I!-PRIC"
5@@5 $56 5@@ C&F@@
5@@@ 6@ =&@@@
5@5@ == 55@ >&>@@
5@@5 $6DE
-
8/9/2019 4-dbms
17/54
!*R""-("+"( $!$ '$S"
$RC*I!"C!UR" The atabase Task Group ( TG)
appointed b' the %onference on atas'stems and /anguages (%, $S3/)&produced a proposal for general architecturefor database s'stems.
The TG proposed a two-tier architecturewith a s'stem view called the schema andusers views called subschemas.
-
8/9/2019 4-dbms
18/54
In 5DF>& $2SI-S*$+% ($merican 2ationalStandards Institute- Standard *lanning and+e9uirements %ommittee) produced athree-tier architecture with a s'stemcatalog.
The three-tier architecture consists of thefollowing three levels:
5) Internal level6) %onceptual level
-
8/9/2019 4-dbms
19/54
serview 5
serview 5
serview 5
serview 5
*******
%onceptual Schema
Internal Schema
*h'sicalatabase
Ailele
le
le
le
!ternal Schema!ternal
level( e ned b'user orapplication
program)%onceptual level( e ned b' $)
Internal /evel( e ned b'$)
*h'sical/evel
-
8/9/2019 4-dbms
20/54
The view at each of the levels is describedb' a scheme or schema.
The term view& scheme and schema areused interchangeabl'.
The / is used to de ne the %onceptualand e!ternal schemas.
The S4/ are used to describe the aspects
of the ph'sical (or internal schema).
Information about the internal&conceptual and e!ternal schemas is stored
in the s'stem catalog.
-
8/9/2019 4-dbms
21/54
The following e!ample speci es three levelscorresponding to three views (namel'internal& conceptual and e!ternal views).
Customer
%ust-id %ust-name
%ust-street
%ust-cit' %ust-bal
T'pe % ST," +0record% ST-I 0integer1
% ST-2$" 0string1% ST-ST+ T0string1% ST-%IT30string1% ST- $/0integer1
end
Integrated recordde nition of % ST," +record
-
8/9/2019 4-dbms
22/54
-
8/9/2019 4-dbms
23/54
The second level of abstraction is theconceptual or global ie .
CUS!O%"R
% ST-I :integer (=)
% ST-2$" :string (6@)
% ST-ST+ T:string (=@)
% ST-%IT3:string (5@)
% ST- $/:integer (E)
$t the conceptual or global level& the database containsinformation concerning an entit' t'pe called % ST," +. achindividual customer has a % ST-I (= digits)& % ST-2$" (6@characters)& % ST-ST+ T(=@ %haracters) and % ST- $/(E digits).
-
8/9/2019 4-dbms
24/54
The third level is the highest level of
abstraction seen b' user or applicationprogram and is called the e!ternal view oruser view.
The conceptual view is the sum total of userview or e!ternal view of data.
CUS!-#$%"
CUS!-CI!& CUS!-I
CUS!-#$%"
CUS!-'$(
S +S I ;5 S +S I ;6
The user view or logical or e!ternal schema of the database inwhich one view contains two elds or data items and other view
contains three elds or data items . There is onl' oneconceptual schema and one internal
-
8/9/2019 4-dbms
25/54
Internal (e el:-
It is the physical representation of thedatabase on the computer and this ieis found at the lo est le el ofabstraction of database.
!he le el indicates ho the data illbe stored in the database and describes
the data structures, le structures andaccess methods to be used by thedatabase.
It describes the ay the '%S and
-
8/9/2019 4-dbms
26/54
Internal level is concerned with the
following activities:
5) Storage space allocation for data andstorage
6) +ecord descriptions for storage withstored siJes for data items.
-
8/9/2019 4-dbms
27/54
The process arriving at a good internal (orph'sical) schema is called physicaldatabase design.
The internal schema is written using S)( orinternal data de nition language (internal /).
-
8/9/2019 4-dbms
28/54
Conceptual (e el:-It is the middle level in the three-tier
architecture.
$t this level of database abstraction& allthe database entities and relationshipsamong them are included.
It contains the logical structure of theentire database as seen b' the $.
-
8/9/2019 4-dbms
29/54
It is complete view of the datare9uirements of the organisation that isindependent of an' storage considerations.
The conceptual schema de nesconceptual view.
It is also called the logical schema.
There is onl' one conceptual schema perdatabase.
-
8/9/2019 4-dbms
30/54
%onceptual level is concerned with thefollowing activities:-
5) $ll entities& their attributes and theirrelationships.
6) %onstraint on the data
-
8/9/2019 4-dbms
31/54
=) %hecks to retain data consistenc'and integrit'.
>) Securit' information.
-
8/9/2019 4-dbms
32/54
"1ternal le el:-
It is the users view of the database.
It is the highest level of data abstraction.
,nl' those portions of the database ofconcern to a user or application programare included.
In e!ternal level& the di7erent views ma'have di7erent representations of the
same data.
-
8/9/2019 4-dbms
33/54
$dvantages of Three-tier $rchitecture:-
The main ob ective of the three-tier
database architecture is to isolate eachuser8s view of the database from the wa'the database is ph'sicall' stored orrepresented.
5) ach user is able access same databut have a di7erent customiJed view of thedata as per their own needs.
-
8/9/2019 4-dbms
34/54
6) The user is not concerned about the ph'sicaldata storage details.
) The $ is able to change the conceptualstructure of the database without a7ecting allusers.
-
8/9/2019 4-dbms
35/54
%haracteristics of Three-tier $rchitecture:-
The following table shows degree ofabstraction& characteristics and t'pe of
"S used for the three levels.
-
8/9/2019 4-dbms
36/54
2eatures
$bstraction(e el
Physicalle el
Internallevel
Conceptual le el
"1ternal (e el
egrees of$bstraction /ow Medium ?igh "edium
egreeof3no
ledgere4
uired'ydatabase
?ardwareandsoftwaredependent
Hardwar e ands/wdepende
nt
?#w ands#wdependent
?#w andS#wdependent
?ierarchical"S
$ttentionre9uiredaboutph'sical-level details
Attentionrequiredabout
physicalleveldetail
$ttentionre9uiredaboutph'sical-lev eldetail
$ttentionre9uiredaboutph'sicalleveldetail
2etwork"S
$ttentionre9uiredabout
ph'sicallevel details
,,
&& &&
-
8/9/2019 4-dbms
37/54
$!$ I# "P"# "#C"
It is a ma or ob ective of implementing"S in an organisation.
It is the characteristics of a databases'stem to change the database schema atone level without having to change theschema at the ne!t higher level.
5) *h'sical ata Independence
6) /ogical ata Independence
-
8/9/2019 4-dbms
38/54
Physical ata Independence:-
Immunit' of the conceptual (ore!ternal) schemas to changes in the internalschema is referred to as ph'sical data
independence.
(ogical ata Independence:-
Immunit' of the e!ternal schemas (orapplication programs) to changes in theconceptual schema is referred to as logicaldata independence.
-
8/9/2019 4-dbms
39/54
"$**I2GS:-
The process of transforming re9uestsand results between the three levels arecalled mappings.
The "S is responsible for thismapping between internal& conceptual& ande!ternal schemas.
-
8/9/2019 4-dbms
40/54
The Three-tier architecture model providesthe following two-stage mappings.
5) %onceptual#Internal mapping
6) !ternal#%onceptual mapping
-
8/9/2019 4-dbms
41/54
The conceptual Schema is related to the internalschema through conceptual#internal mapping.
The conceptual#internal mapping de nes thecorrespondence between the conceptual view
and stored database.
It also allows an' di7erences in entit' names&attribute names& attribute orders& data t'pes and
so on& to be resolved.
$n' change in the structure of the storeddatabase& the conceptual#internal mapping is alsochanged accordingl' b' the structure of the $.
-
8/9/2019 4-dbms
42/54
-
8/9/2019 4-dbms
43/54
There could be one mapping betweenconceptual and internal levels
There are several mappings betweene!ternal and conceptual levels.
-
8/9/2019 4-dbms
44/54
$!$ %O "(S:-
$ model is an abstraction process .
$ data model also called database modelis a mechanism that provides this abstractionfor database application.
It represents the organisation itself.
-
8/9/2019 4-dbms
45/54
$ data model is a collection of mathematicall'
well-de ned concepts that help an enterpriseto consider and e!press the static andd'namic properties of data intensiveapplications.
It consists of the following
5) Static properties for e!1 ob ects&attributes& relationships.
6) 'namic properties for e!1 operations or
rules de ning new database states.
-
8/9/2019 4-dbms
46/54
ata "odels can be broadl' classi ed into thefollowing three categories:
5) +ecord based data models.
6) ,b ect-based data models.
-
8/9/2019 4-dbms
47/54
Record 'ased ata %odels:-
The' are used to specif' the overall
logical structures of the database.
In this model the database consists of anumber of !ed-format records possibl' of
di7erent t'pes.
-
8/9/2019 4-dbms
48/54
There are three record based data models
5) ?ierarchical data model
6) 2etwork data model
-
8/9/2019 4-dbms
49/54
Ob5ect 'ased ata %odels:-
The' are used to describe data and itsrelationships.
It uses concepts such as entities& attributesand relationships.
It has Ke!ible data structuring capabilities.
ata integrit' constraints can be e!plicitl'speci ed.
-
8/9/2019 4-dbms
50/54
The following are the common t'pes of,b ect-based data models:-
5) ntit'-+elationship model.
6) Semantic "odel.
-
8/9/2019 4-dbms
51/54
-
8/9/2019 4-dbms
52/54
-
8/9/2019 4-dbms
53/54
?ierarchical ata "odels:-
The model is represented b' an upside-down tree.
The following gure shows the model
-
8/9/2019 4-dbms
54/54
$
$ $
$ $
/evelL@ : +oot *arent
(node)
/evelL5: +oot %hildren
(Segments)
/evelL6: Segments(level-5 children)