relational concepts
TRANSCRIPT
-
8/12/2019 Relational Concepts
1/24
The Relat io n al Mo d el
Chapter 3
-
8/12/2019 Relational Concepts
2/24
Histo ry o f th e Relat ion al Mod el
The objectives of the relational model :
allow a high degree of data independence, applicationprograms should not be affected by modifications to
the internal data representation, file organisationrecord orderings or access paths
deal with data semantics, consistency and redundancyproblems, the database files should be based on
normalised relationsenable the expansion of set-oriented data manipulationlanguages, using a many-records-at-a-time logic.
-
8/12/2019 Relational Concepts
3/24
Terminology
RelationA relation is a table with rows and columnsMathematical definition
AttributeAn attribute is a named column of a relation
DomainA domain is the set of allowable values for one ore more
attributes
TupleA tuple is a row of a relation
-
8/12/2019 Relational Concepts
4/24
The DOMA IN c o nc ept
The domain concept is used to describe a set of possiblevalues for an attribute. This offers more flexibility.
Domain descriptionDomain part-number character 6Domain part-name character 20Domain color character 6Domain weight numeric 4Domain location character 15
Relation PART(P# : Domain part-numberpname : Domain part-namecolor : Domain colorweight : Domain weightcity : Domain location
-
8/12/2019 Relational Concepts
5/24
Sam p le Database
S S# SNAME STATUS CITYS1 Smith 20 LondonS2 Jones 10 ParisS3 Blake 30 ParisS4 Clark 20 LondonS5 Adams 30 Athens
P P# PNAME COLOR WEIGHT CITY
P1 Nut Red 12 LondonP2 Bolt Green 17 ParisP3 Screw Blue 17 RomeP4 Screw Red 14 LondonP5 Cam Blue 19 LondonP6 Cog Red 19 London
SP S# P# QTYS1 P1 300S1 P2 200S1 P3 400
S1 P5 200S1 P6 100S2 P1 300S2 P2 400
S3 P2 200S4 P2 200S4 P4 300S4 P5 400
-
8/12/2019 Relational Concepts
6/24
Term ino log y 2
DegreeThe degree of a relation is the number of attributes it contains
CardinalityThe cardinality of a relation is the number of tuples it contains
Relational databaseA collection of normalised relations
Relation SchemaA relation name followed by a set of attribute and domain namepairs
-
8/12/2019 Relational Concepts
7/24
A lterna tive Term inolo gy
Fo rm al L o g ic al term Clas s ic al
Relation Table File
Tuple Row Record
Attribute Column Field
-
8/12/2019 Relational Concepts
8/24
Prop ert ies o f a Relat io n
A relation in a database has a unique name.
Each cell of the relation contains exactly one atomicvalue.
Each attribute has a distinct name within a relation.
The values of an attribute are all from the samedomain.
column homogenious
The order of the attributes has no significance.
Each tuple is distinct; there are no duplicate tuples.
The order of tuples has no significance, theoretically.
-
8/12/2019 Relational Concepts
9/24
Math em atical Relat io n s
Mathematical definition of relation Consider two sets, D 1 and D 2, where D 1 = {2, 4} and D 2
= {1, 3, 5}.
Cartesian product is D 1 D2, the set of all orderedpairs, first element is member of D 1 and secondelement is member of D 2.
Alternative way is to find all combinations ofelements with first from D 1 and second from D 2.
D1 D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
-
8/12/2019 Relational Concepts
10/24
Math em atical Relat io n
Any subset of Cartesian product is a relation.For example
R = {(2, 1), (4, 1)}
May specify which pairs are in relation usingsome condition for selection. For example,second element is 1
R = {( x , y ) | x D1, y D2, and y = 1}
-
8/12/2019 Relational Concepts
11/24
Relat io n al K ey s
Superkey An attribute or a set of attributes that uniquelyidentifies a tuple within a relation.
-
8/12/2019 Relational Concepts
12/24
Relat io n al K ey s
Candidate Key
A superkey (K) such that no proper subset is asuperkey within the relation.
In each tuple of R, the values of K uniquely identifythat tuple (uniqueness).
No proper subset of K has the uniqueness property
(irreducibility).
-
8/12/2019 Relational Concepts
13/24
Relat io n al K ey s
Primary Key Candidate key selected to identify tuples uniquelywithin relation.
Alternate Keys Candidate keys that are not selected to be theprimary key.
-
8/12/2019 Relational Concepts
14/24
Relat io n al K ey s
Foreign Key An attribute or set of attributes within one relationthat matches candidate key of some (possibly same)relation.
-
8/12/2019 Relational Concepts
15/24
Relat io n al In tegr i ty
Null (value) Represents a value for an attribute that is currentlyunknown or is not applicable for this tuple.Deals with incomplete or exceptional data.Null represents the absence of a value and is not thesame as zero or spaces, which are values.
-
8/12/2019 Relational Concepts
16/24
Relat io n al In tegr i ty
Entity IntegrityIn a base relation, no attribute of a primary key canbe null.
Referential IntegrityIf foreign key exists in a relation, either the foreignkey value must match a candidate key value of some
tuple in its home relation or foreign key value mustbe wholly null.
-
8/12/2019 Relational Concepts
17/24
Relat io n al In tegr i ty
Enterprise ConstraintsAdditional rules specified by users or databaseadministrators.
-
8/12/2019 Relational Concepts
18/24
Views
Base relationa named relation, corresponding to an entity in the conceptualschema, whose tuples are physically stored in the database.
Viewa view is the dynamic result of one or more relationaloperations operating on the base relations to produce anotherrelation. A view is a virtual relation that does not actually existin the database but is produced upon request by a particular
user, at the time of request.
-
8/12/2019 Relational Concepts
19/24
Views
A view is a virtual relation that does not actually existin the database but is produced upon request, at timeof request.
Contents of a view are defined as a query on one ormore base relations.
Views are dynamic, meaning that changes made tobase relations that affect view attributes areimmediately reflected in the view.
-
8/12/2019 Relational Concepts
20/24
Purp os e of View s
Provides a powerful and flexible securitymechanism by hiding parts of database fromcertain users.
Permits users to access data in a customizedway, so that same data can be seen bydifferent users in different ways, at same time.
It can simplify complex operations on baserelations.
-
8/12/2019 Relational Concepts
21/24
Updating View s
All updates to a base relation should beimmediately reflected in all views thatreference that base relation.
If view is updated, underlying base relationshould reflect change.
-
8/12/2019 Relational Concepts
22/24
Updating View s
However, there are restrictions on types ofmodifications that can be made through views:
Updates are allowed if query involves a single baserelation and contains a candidate key of baserelation.Updates are not allowed involving multiple baserelations.Updates are not allowed involving aggregation orgrouping operations.
-
8/12/2019 Relational Concepts
23/24
Updating View s
Classes of views are defined as theoreticallynot updateable, theoretically updateable andpartially updateable.
-
8/12/2019 Relational Concepts
24/24
Relat ion al Datab ase A rc h i tectu re
Data Languageapplication
view 1 view 2
Base tableB1
Base tableB2
Base tableB3
Base tableB4
Stored fileS1
Stored fileS2
Stored fileS3
Stored fileS4
External level
Conceptual level
Internal level