CC-2141
Database Systems
Department of Computer Science
School of Systems & Technology - SST
Lecture 7 & 8
Chapter Objectives
In this chapter you will learn:
▪ The origins of the relational model.
▪ The terminology of the relational model.
▪ How tables are used to represent data.
▪ The connection between mathematical relations and relations in the relational model.
▪ Properties of database relations.
▪ How to identify candidate, primary, alternate, and foreign keys.
▪ The meaning of entity integrity and referential integrity.
▪ The purpose and advantages of views in relational systems.
2
What is an RDBMS?
• RDBMS stands for Relational Database
Management System.
• RDBMS is a program used to
maintain a relational database.
• RDBMS is the basis for all modern
database systems such as MySQL,
Microsoft SQL Server, Oracle, and
Microsoft Access.
• RDBMS uses SQL queries to access
the data in the database.
3
Introduction to relational model
▪ The relational model was first proposed by E. F. Codd (1970).
▪ The relational model’s objectives were:
• To allow a high degree of data independence.
• Application programs must not be affected by modifications to the internal data
representation.
• To provide substantial grounds for dealing with data semantics, consistency, and
redundancy problems.
• Codd Introduced the concept of normalized relations, that is, relations that have no
repeating groups.
• To enable the expansion of set-oriented data manipulation languages.
4
Relational Data Structure (Terminologies)
▪ Relation: A relation is a table with columns
and rows.
▪ Attribute: An attribute is a named column of
a relation.
▪ Domain: A domain is the set of allowable
values for one or more attributes.
▪ Tuple: A tuple is a row of a relation.
▪ Degree: The degree of a relation is the
number of attributes it contains.
▪ Cardinality: The cardinality of a relation is
the number of tuples it contains.
▪ Relational database: A collection of
normalized relations with distinct relation
names. 5
The Relational
Data Structure
6
The Relational Model Concepts
▪ The relational model is based on the mathematical concept of a relation,
which is physically represented as a table.
▪ An RDBMS requires that the database be perceived by the user as tables.
▪ This perception applies only to the logical structure of the database: that is,
the external and conceptual levels of the ANSI-SPARC architecture.
▪ In the relational model, relations are used to hold information about the
objects to be represented in the database.
▪ A relation is represented as a two dimensional table in which the rows of
the table correspond to individual records and the table columns correspond to
attributes.
7
Contd…
▪ The relational model uses a collection of tables to represent both data and
the relationships among those data.
▪ Each table has multiple columns, and each column has a unique name.
▪ Remember, tables are also known as relations.
▪ A relational database consists of a collection of tables, each of which is
assigned a unique name.
8
Properties of Relations
A relation has the following properties:
• the relation has a name that is distinct from all other relation names in the relational schema;
• each cell of the relation contains exactly one atomic (single) value;
• each attribute has a distinct name;
• the values of an attribute are all from the same domain;
• each tuple is distinct; there are no duplicate tuples;
• the order of attributes has no significance;
• the order of tuples has no significance, theoretically. (However, in practice, the order may
affect the efficiency of accessing tuples.)
9
Mathematical Relations
▪ To understand the true meaning of the term relation, we have to review some concepts from
mathematics.
▪ Suppose that we have two sets, D1 and D2, where D1 = {2, 4} and D2 = {1, 3, 5}.
▪ The Cartesian product of these two sets, written D1 x D2, is the set of all ordered pairs such that
the first element is a member of D1 and the second element is a member of D2.
▪ An alternative way of expressing this is to find all combinations of elements with the first from D1
and the second from D2. In our case, we have:
▪ D1 x D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
▪ Any subset of this Cartesian product is a relation. For example, we could produce a relation R
such that: R = {(2, 1), (4, 1)}
10
Database Relations
▪ Relation schema: A 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.
▪ E.g.,
{(branchNo: B005, street: 22 Deer Rd, city:
London, postcode: SW1 4EH)}
11
Database Relations
▪ Relational database schema: A
set of relation schemas, each with
a distinct name.
▪ 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}
12
Relational Keys
▪ Primary key: The candidate key that is selected to identify tuples uniquely within the
relation.
▪ Alternate Key: The candidate key other than the primary key is called an alternate key.
▪ Candidate key: An attribute or set of attributes claimed to be candidate as a superkey or
primary key or alternate key
▪ Superkey: The set of attributes that can uniquely identify a tuple is known as Super Key. A
super key is a group of single or multiple keys that identifies rows in a table.
▪ Foreign key: An attribute, or set of attributes, within one relation that matches the candidate
key of some other (master) relation.
▪ Composite Key: Sometimes, a table might not have a single column/attribute that uniquely
identifies all the records of a table. To uniquely identify rows of a table, a combination of two
or more columns/attributes can be used. E.g., FULLNAME + DOB can be combined.
13
Relational Keys
14
Representing Relational Database Schemas
15
Integrity
Constraints
A null represents a value for an attribute that is unknown at the present time or is not applicable for this tuple.
16
Database Views
▪ A view in the relational model is a virtual or derived relation that is
dynamically created from the underlying base relation(s) when required.
▪ Views provide security and allow the designer to customize a user’s model.
▪ Not all views are updatable.
17
Purpose of
Views
18
Database Views (By Example)
19
Thankyou
Any Queries?
20
