week03 - the relational data model
TRANSCRIPT
-
8/8/2019 Week03 - The Relational Data Model
1/23
1
Database I
Background
The Relational Data Model
-
8/8/2019 Week03 - The Relational Data Model
2/23
2
History of Relational Data
Model
Proposed by E.F. Codd, in his research paper Arelational model of data for large shared data banksin 1970
Objectives of RDM: High degree of data independence Application program must not be affected by modifications
to the internal data representation
Provide environment for dealing with data consistency and
redundancy problems The first prototype of Relational Database
Management System (RDBMS) System R wasdeveloped by IBMs San Joes Research Laboratory
in late 1970s
-
8/8/2019 Week03 - The Relational Data Model
3/23
3
History of Relational Data
Model (Cont.)
System R project led to two major
developments: The development of SQL (pronounced as See-
Quel)
Production of various commercial RDBMS
products during 1980, i.e. DB2 and SQL/DS from
IBM and ORACLE from ORACLE Corporation
-
8/8/2019 Week03 - The Relational Data Model
4/23
4
Basic Terminology
Relation A relation is a table with columns and rows In relational model a database is perceived by the user as
tables
However this perception applies only to the logicalstructure i.e. external and conceptual levels
Attribute A named column of a relation Attributes can appear in any order and the relation will still
be the same relation Tuple Tuple is a row of a relation It is also called the extension or state of a relations which
changes over the time Tuples are also called records
-
8/8/2019 Week03 - The Relational Data Model
5/23
5
Basic Terminology (Cont.)
Domain A set of allowable values for one or more attributes
Structure of relation, together with the specification
of the domains is called intension of a relation
Degree The number of attributes a relation contains
A relation with only one attribute would have degreeone can be called a unary relation
-
8/8/2019 Week03 - The Relational Data Model
6/23
6
Basic Terminology (Cont.)
A relation with two attribute is called binary and
one with three attributes are called ternary, and
after that the term n-ary is used
Cardinality The number of tuples a relation contains
The cardinality is the property of extension of the
relation
-
8/8/2019 Week03 - The Relational Data Model
7/23
7
Basic Terminology (Cont.)
-
8/8/2019 Week03 - The Relational Data Model
8/23
8
Basic Terminology (Cont.)
Examples of Attribute Domains
-
8/8/2019 Week03 - The Relational Data Model
9/23
9
Alternative Terminology
Formal Terms Alternative1 Alternative2
Relation Table File
Tuple Row Record
Attribute Column Field
-
8/8/2019 Week03 - The Relational Data Model
10/23
10
Mathematical Definition of
Relation
To understand the meaning of the term relation,
let us review some concepts from mathematics
Consider two sets, D1 & D2, where
D1 = {2, 4}
D2 = {1, 3, 5}
Cartesian product, D1 x D2, is set of all orderedpairs, where first element is member ofD1 and
second element is member ofD2D1 x D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
-
8/8/2019 Week03 - The Relational Data Model
11/23
11
Mathematical Definition of
Relation (Cont.)
Any subset of Cartesian product is a relation; e.g.
R= {(2, 1), (4, 1)}
We can specify which pairs are in relation using
some condition for selection; e.g. Second element is 1:
R= {(x, y) |xD1, yD2, and y= 1}
First element is always twice the second:S= {(x, y) |x D1, yD2, andx= 2y}
-
8/8/2019 Week03 - The Relational Data Model
12/23
12
Mathematical Definition of
Relation (Cont.)
Consider three sets D1, D2, D3 with Cartesian
Product D1 x D2 x D3; e.g.
D1 = {1, 3} D2 = {2, 4} D3 = {5, 6}D1 x D2 x D3 = {(1,2,5), (1,2,6), (1,4,5),
(1,4,6), (3,2,5), (3,2,6), (3,4,5), (3,4,6)}
Again any subset of these ordered triples is arelation
-
8/8/2019 Week03 - The Relational Data Model
13/23
13
Mathematical Definition of
Relation (Cont.)
So Cartesian product ofn sets (D1, D2, . . ., Dn) is:
D1 x D2 x . . . x Dn = {(d1, d2, . . . , dn) | d1D1, d2D2, . . . , dnDn}
Usually written as:nX D
i
i = 1
Any set ofn-tuples from this Cartesian product is a
relation on the n sets
-
8/8/2019 Week03 - The Relational Data Model
14/23
14
Database Relations
Now applying the previous concepts to
database, we have:
Relation Schema: Relation is defined by a set of attribute anddomain name pairs
In our example, the branch relation on next slide
has four attributes with corresponding domains(tuples)
So the branch relation can be represented as:
-
8/8/2019 Week03 - The Relational Data Model
15/23
15
Database Relations (Cont.)
{(BNo:B005, Street:22 Deer Rd, City:London,
Postcode:SW1 4EH)}
This explains why
table rows in
relational model
are called tuples
Relational schema Set of relation schemas, each with a distinct name
Attribute Domain
-
8/8/2019 Week03 - The Relational Data Model
16/23
16
Properties of Relation
Relation name is distinct from all other relation
names in relational schema
Each cell of relation contains exactly one single
value Each attribute has a distinct name
Values of an attribute are all from the same domain
Each tuple is distinct; there are no duplicate tuples Order of attributes has no significance
Order of tuples has no significance, theoretically
-
8/8/2019 Week03 - The Relational Data Model
17/23
17
Relational Keys
Super Key An attribute or set of attributes that uniquely
identifies a tuple in the relation
Candidate Key A super key (K) such that no proper subset is a
super key within the relation
In each tuple of R, values of K uniquely identifythat tuple (uniqueness)
No proper subset of K has the uniqueness
property (irreducibility)
-
8/8/2019 Week03 - The Relational Data Model
18/23
18
Relational Keys (Cont.)
Composite Key A key which consists of more than one attribute
Primary Key Candidate key selected to identify tuples uniquely within
relation
Alternate Keys Candidate keys that are not selected to be primary key
Foreign Key An attribute, or set of attributes, within one relation that
matches candidate key of some (possibly same) relation
-
8/8/2019 Week03 - The Relational Data Model
19/23
19
Relational Integrity
Relational Integrity or Integrity Constraints are
set of rules, which insure that the data is
accurate
Null represents value for an attribute that iscurrently unknown or not applicable for tuple
Null is not the same as zero or spaces, which
are values Domain Constraints Restriction on the set of values allowed for the
attribute of relation
-
8/8/2019 Week03 - The Relational Data Model
20/23
20
Relational Integrity (Cont.)
Entity Integrity Applies to the primary keys of base relations
Base relation is corresponding to an entity in the
conceptual schema, whose tuples are storedphysically in database
According to entity integrity in a base relation, no
attribute of a primary key can be null
Referential Integrity The foreign key value must match a candidate key
value of some tuple in its home relation or foreign key
value must be null
-
8/8/2019 Week03 - The Relational Data Model
21/23
21
Relational Integrity (Cont.)
Enterprise Constraints Rules specified by the users or DBAs of a
database
-
8/8/2019 Week03 - The Relational Data Model
22/23
22
Views
A view is a Virtual Relation or in other wordsa relation that does not exist
So the external level of the three level
architecture can consists of both the baserelations and views
Contents of a view are defined as a query onone or more base relations
Views are dynamic, meaning that changesmade to base relations that affect viewattributes are immediately reflected in the
view
-
8/8/2019 Week03 - The Relational Data Model
23/23
23
Purpose of View
Provides 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
Can simplify complex operations on baserelations
All updates to a base relation should beimmediately reflected in all views that
reference that base relation and vice versa