CSC 271-Database Systems
Lecture # 5
Today’s Lecture
◎ The Relational Model
Relational Model
◎ The Relational Model is more than 35 years old, and it's really the
foundation of database management systems
◎ Used by all major commercial database systems
◎ Very simple model
◎ Query with high-level languages: simple yet expressive
◎ Efficient implementations
History of Relational Model
◎ First introduced by Tęd Codd (in 1970).
◎ Uses concept of mathematical relation.
◎ First commercial implementations of the relational model
Oracle DBMS, SQL/DS system (IBM).
◎ Current popular RDBMSs → SQL Server & Access (Microsoft),
DB2 & Informix (IBM), MySQL etc.
◎ Standard for commercial RDBMS → SQL query language.
Relational Model Terminology
◎ Relational model represents data as a collection of tables.
◎ In table:
○ Each row is known as a tuple.
○ Each column has a name or attribute.
Relational Model Terminology..
◎ Relation: A relation is a table with columns and rows
◎ Attribute: An attribute is a named column of a relation
◎ Domain: Domain is the set of allowable values for one or more
attributes
◎ Tuple: A tuple is a row of a relation.
◎ Degree: Degree is the number of attributes in a relation
◎ Cardinality: Cardinality is the number of tuples in a relation
Relational Model Terminology..
◎ Relational Database: Relational Database is a collection of
normalized relations with distinct relation names
◎ Relation Schema: A relation schema represents the name of the
relation with its attributes.
◎ Relation instance (State): Relation instance is a finite set of tuples.
Relation instances never have duplicate tuples.
Examples of Attribute Domains
Alternative Terminology
Mathematical Relations
◎ Consider two sets, D1 & D2,
○ Where D1 = {2, 4} and D2 = {1, 3, 5}
◎ Cartesian product, D1 x D2 is,
○ Set of all ordered pairs, where first element is member of D1 and
second element is member of D2
➢ D1 x D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
○ Alternative way is to find all combinations of elements with first from
D1 and second from D2
Mathematical Relations..
◎ Any subset of Cartesian product is a relation; e.g.
○ R = {(2, 1), (4, 1)}
○ May specify which pairs are in relation using some condition for
selection; e.g.
○ Second element is 1:
➢ R = {(x, y) | x ∈ D1, y ∈ D2, and y = 1}
○ First element is always twice the second:
➢ S = {(x, y) | x ∈ D1, y ∈ D2, and x = 2y}
Mathematical Relations..
◎ Relating with database relations
○ Consider Branch relation in the example where
branchNo = {B002, B003} city = {London, Glasgow}
◎ Cartesian product, branchNo x city is,
{(B002, London), (B002, Glasgow), (B003, London), (B003, Glasgow)}
◎ Any subset of Cartesian product is a relation
Mathematical Relations..
◎ 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)}
○ Any subset of these ordered triples is a relation
Database Relations
◎ Relation schema
○ Named relation defined by a set of attribute and domain name pairs
○ Let A1, A2,...,An be attributes with domains D1, D2,..., Dn
○ Then the set {A1:D1, A2:D2, ... , An:Dn} is a relation schema
Database Relations..
◎ Branch relation schema
Branch = {branchNo:BranchNumbers, street: StreetNames, city: CityNames,
postcode:PostCodes}
◎ Tuple of a Branch relation
{branchNo:B005, street: 22 Deer Rd, city: London, postcode:SW1 4EH}
Relational Database Schema
◎ If R1, R2,..., Rn are a set of relation schemas, then we can
write the relational database schema, or simply relational
schema, R, as:
R= {R1, R2,..., Rn}
Properties of Relations
◎ Relation name is distinct from all other relation names in relational
schema
◎ Each cell of relation contains exactly one atomic (single) value
◎ Each attribute has a distinct name
◎ Values of an attribute are all from the same domain
Properties of Relations..
◎ Each tuple is distinct; there are no duplicate tuples
◎ Order of attributes has no significance
◎ Order of tuples has no significance, theoretically
Summary
◎ Relational model terminology
◎ Mathematical relations
◎ Database relations
◎ Properties of a relation
d
Thank You
