CUIT201: Database

Systems

Relational Database Model

Concepts

Lecture 5
Presentation layout
 Lecture Objectives
 Introduction
 Characteristics of a relational database
 Relational Keys
 Data integrity constraints
 Functional dependency
 Conclusion
 References
Lecture Objectives
At the end of the lecture the student should be
able to:
1. Define relational database schema, table,
attribute and tuple.
2. Describe the characteristics of a relation
3. Explain the roles of the different types of
relational keys
4. Understand how data integrity is maintained
5. Describe the concept of functional
dependency.
The Relational model
 The most popular DBMS model
 The relational model, proposed by IBM

researcher E. F. Codd(1970)
 It is based on predicate logic and set theory.
The relational model has three well-
defined components:

 A logical data structure represented by

relations (tables)
 A set of integrity rules to enforce that the

data is and remain consistent overtime

 A set of operations that define how data is

manipulated.
Relational Model Terminology -
Relation
 Data is treated as a collection of related relations(tables).
 A relation is perceived as a two-dimensional structure
(table) composed of rows and columns.
◦ a kind of set, also known as a dataset in Microsoft Access
◦ a subset of a Cartesian product
◦ an unordered set of ordered tuples
 A relation contains information about an entity example of
an entity information is STUDENT in a COLLEGE database
◦ STUDENT relation contains student details like a unique student
number, last name , first name, other names, title, address, gender,
programme enrolled for e.t.c.
 Each relation contains a group of related entity occurrences
◦ Each row in STUDENT relation is an instance of entity occurrence
Characteristics of a Relation
 Each table row (tuple) represents a single entity occurrence
within the entity set
 Each column represents an attribute, and has a distinct name
◦ An attribute is a property of an entity e.g StudentNumber
◦ Same as column name in a table
 Each row/column intersection contains a single data value
(atomic)
 All values in a single column must be of the same format
 Each column is defined over the same attribute domain
 The order of roes and column is not important
 Each table must have attribute(s) that uniquely identifies rows
(Primary key)
Relational Model Terminology –
Relational Schema
 A relational database is represented by a
relational schema.
 A relational schema is a textual

representation of the database relations

where each relation is listed by its name
followed by the list of its attributes in
parentheses. The primary key attribute(s) is
(are) underlined.
◦ STUDENT(StudentNumber, LastName, FirstName,
Title, TermAddress, Programme, YearEnrolled)
Relational Model Terminology-
Relational Keys CTD
 A key is a determinant
 A key determines other attributes
 The statement “A determines B” indicates that if
you know the value of attribute A, you can look
up (determine) the value of attribute B.
◦ For example, knowing the StudentNumber in the
STUDENT table you can determine student’s last name,
first name, term address, title, programme and year
 The shorthand notation for “A determines B” is
A → B.
 If A determines B, C, and D, you write A → B, C, D.
Relational Model Terminology-
Functional dependency
 The attribute B is functionally
dependent on A if A determines B.
 A B
 The attribute B is functionally

dependent on the attribute A if each

value in column A determines one and
only one value in column B.
Relational Model Terminology - Full
Functional Dependency
 Any attribute that is part of a key
(composite key) is known as a key attribute
or prime attribute
◦ A composite key is composed of more than one
attribute.
 If the attribute (B) is functionally
dependent on a composite key (A) but
not on any subset of that composite
key, the attribute (B) is fully
functionally dependent on (A).
Relational Model Terminology full
Functional Dependency ctd
 Given a relation Schema R(A,B,C,D) and a set of
functional dependencies (FDs) F on R
F={AB C, BD}
 Attribute C is fully dependent on PK AB because
there does not exist (∄) a subset of PK that
determines C
◦ A↛C and B ↛C
 Attribute D is not fully dependent on PK because
there exist (∃) a subset of AB that determines D
◦ ∃ B⊂AB D
◦ BD
Relational Model Terminology -
Relational Keys
 A key is an attribute(s) that uniquely
identifies a row in a table
 A key consists of one or more attributes

that determine all other attributes in a

table.
◦ For example given a relational schema
R(A,B,C,D) where attribute A is the primary key
(PK) attribute.
◦ Attribute determines all other attributes.
◦ If the value of attribute A is known you can
determine values of other attributes B,C,D
Examples of keys
 Composite key
 Super keys
 Candidate key
 Primary key
 Foreign key
 Secondary key
composite key
A key composed of more than one
attribute.
 A multi-attribute key
 Any attribute that is part of a key is

known as a key attribute

Given a relation Schema R(A,B,C,D) and a set of
functional dependencies (FDs) F on R
F={AB C, BD}
◦ A and B are key attributes
◦ PK is AB
Super key
 a super key (SK) is any key that uniquely
identifies each row.
 A super key functionally determines all of row

attributes
 A super key is not necessarily redundant it can

contain other attributes that are not prime or

key
 Given a relation Schema R(A,B,C,D) and a set

of functional dependencies (FDs) F on R

F={AB C, BD}
◦ The super keys for R are: AB, ABC, ABD and ABCD
Candidate key (CK)
 A candidate Key is a super key without
unnecessary attributes,
 A super-key with only key attributes
 a minimal super- key.
 Given a relation Schema R(A,B,C,D) and a

set of functional dependencies (FDs) F on

R
F={AB C, BD, AB}
◦ SKs= A, AB, AC, AD, ABC, ABD, ACD, ABCD
◦ Minimal super key = CK = A
Primary key (PK)
 A primary key is selected from candidate
keys.
 Within a table, each primary key value must

be unique to ensure that each row/tuple is

uniquely identified by the primary key.
 P.K. is also used to link related tables
 In that case, the table is said to exhibit entity

integrity.
To maintain entity integrity
 a null (that is, no data entry at all) is not permitted in the
primary key.
 A null is no value at all. It does not mean a zero or a space.
 A null is created when you press the Enter key or the Tab
key to move to the next entry without making a prior entry
of any kind. Pressing the Spacebar creates a blank (or a
space).
◦ Nulls, if used improperly, can create problems because
they have many different meanings. For example, a null
can represent:
 An unknown attribute value.
 A known, but missing, attribute value.
 A “not applicable” condition.
Entity Integrity Constraint
A primary Key value must exist
and it must not have a null value
Foreign key (FK)
A primary key in another table
 A (FK) is an attribute whose values

match the primary key values in the

related table.
 It is used to link tables.
 If the foreign key contains either

matching values or nulls, the table

that makes use of that foreign key is
said to exhibit referential integrity.
Referential Integrity Constraint
Ifa foreign key value exits,
the same value must exists
in another relation
otherwise it is null
Secondary key
A key that is used strictly for data
retrieval purposes.
 Note that a secondary key does not

necessarily yield a unique outcome.

 A less efficient secondary key could

yield dozens of matches, which could

then be combed for a specific match.
Relational database keys Summary
Key Property
Super key Attribute(s) that uniquely identifies rows in a table. Not
necessarily minimal
Candidate key Minimal(irreducible) super key. No subset of the key is
an super key
Primary Key A candidate key selected to uniquely identify records in
a table. No null values are permitted.
Foreign key A primary key in another table whose values must
match
Secondary Key Attribute(s) strictly used for data retrieval purposes
Importance of Relational Keys
 Ensure that each row in a table is
uniquely identifiable.
 Establish relationships among

relations (create links) and

 Ensure the integrity of the data.
Integrity Rules/ Constraints
 Relational database integrity rules are very
important to good database design.
 RDBMSs enforce integrity rules automatically.

However, it is much safer to make sure that

your application design conforms to the
entity and referential integrity rules.
Integrity Constraints
Entity Integrity Referential Integrity
Requirement All primary key values Foreign key value
are unique and must must reference values
not be null in the related table or
null
Purpose Each row will have a To link related tables,
unique identity, and to always have a
foreign key values valid entry
reference primary key
values
Question ?
 Explain following terms with suitable
example.
◦ Primary Key
◦ Candidate Key
◦ Foreign Key
 Define a NULL value? How do you retrieve null
values from the database?
Questions ctd
 What is the difference between a key and a
determinant?
 The schema R(A, B, C, D, E) has the following

functional dependencies.
AB  C
BC  D
CD  E
DE A
What are all of the keys of R?
Conclusion
 The relational model forms the bases of all
modern database models.
References
Elmasri, R. & Navathe, S., 2013. Fundamentals
of Database Systems. 4th ed. Boston: Digital
Vision.
Ramakrishnan, R. & Gehrke, J., 2010. Database
management Systems. 4th ed. New York:
MaGraw-Hill.
Rob, P. & Coronel, C., 2012. Database Systems:
Design Implementation and Management. 8th
ed. Massachuets: Course Technology.