Lecture 3
Relational Model
Types of Database Model
Populated Database State for COMPANY
Populated Database State for COMPANY
Entities and Attributes
Two entities, EMPLOYEE e1 and
COMPANY c1 and their attributes
Entities and Attributes Example
Attribute
Attribute Each attribute of a relation has a name
Set of allowed values for each attribute is called the
domain
Attribute values are (normally) required to be
atomic
The special value null is a member of every domain
Simple vs Composite Attribute
🞆Simple Attributes are atomic values, which cannot be
divided further.
Ssn
🞆Composite Attributes are made of more than one
simple attribute.
Relation Schema
Relation schema R
⚫ Denoted by R(A1, A2, ...,An)
⚫ Made up of a relation name R and a list of attributes, A1, A2, ..., An
🞆 Schema of a relation consists of
⚫ attribute definitions
🞆name
🞆type/domain
⚫ integrity constraints
Relational Schema - COMPANY Database
Relational Instance
🞆 The current values (relation instance) of a relation are specified by a
table
🞆 An element t of r is a tuple, represented by a row in a table
🞆 Order of tuples is irrelevant (tuples may be stored in an arbitrary
order)
Tuples
Database And Relations
🞆A database consists of multiple relations
🞆Information about company is broken up into parts, with
each relation storing one part of the information
🞆E.g.Employee: information about employee and their
department
Department: information about department
Dept_locations: information about department
locations.
Why Split Information Across
Relations?
🞆 Storing all information as a single relation such as
Company(Employee, Department, Department_Loc, ..)
results in
⚫ repetition of information
⚫ the need for null values
🞆 Normalization deals with the
designing of relational schemas.
Keys
Foreign
Alternate Primary
KEY
Candidat
Superkey
e
Candidate Key
🞆 It must contain unique values
🞆 Candidate key may have multiple attributes
🞆 It should contain minimum fields to ensure uniqueness
🞆 Uniquely identify each record in a table
🞆 Eg. Ssn : Candidate Key
Ssn, Ename: Not Candidate Key
Candidate Key Example
SSN and Dname can be considered as candidate keys
Candidate Key Drill
🞆 In the given table which columns can be considered as
candidate keys which help us to uniquely identify the
student record in the table?
Roll FirstName LastName
StudID Email
No
1 11 Tom Price
[email protected] m
m
om
Key Constraints
Simple Vs Composite Candidate Key
Ssn: Simple Key
Fname, Minit, Lname: Composite
Key
Fname, Address ????
Prime vs Non-prime Attribute
Relation R
Attributes
Prime Non-prime
Attributes Attributes
Belonging to Not belonging to
Candidate Keys Candidate Keys
Primary Key
🞆 Two rows can't have the same primary key value
🞆 The primary key field cannot be null.
🞆 The value in a primary key column can never be modified or
updated if any foreign key refers to that primary key.
🞆 Atmost one primary key for any relation.
i.e. any candidate key whose values are always NOT
NULL.
🞆 Eg. Ssn: Primary Key (no null value allowed)
Primary Key Drill
Identify Primary Key??
Alternate Key
🞆All candidate keys except primary keys.
🞆Allows NULL value
🞆More than 1 alternate key possible
🞆Eg. { Ename, Address, Bdate }
Alternate Key Example
Dnumber and Dname are qualified to become a primary key in
DEPARTMENT relation. But since Dnumber are the primary key, DName
becomes the alternate key.
Alternate Key Drill
Identify the candidate, primary and alternate
key in the following SCHOOL Database.
StudID Roll No FirstName LastName Email
m
Solution - Alternate Key Drill
SuperKey
A superkey is a group of single or multiple keys which identifies rows in
a table. A Super key may have additional attributes that are not needed
for unique identification.
EmpSSN EmpNum Empname
981234509 AB05 Shown
8
987651234 AB06 Roslyn
5
199937890 AB07 James
🞆 Eg. (EmpSSN, Empname) and (EmpNum, Empname) are superkeys.
Primary, Candidate and Super
Key
Set of attributes that
Minimal SuperKey uniquely identifies
records
Candidate key with
NO NULL values
Foreign Key
🞆 A foreign key is a set of attributes to create a relationship with
another relation.
🞆 Foreign keys help us to maintain data integrity and also allows
navigation between two
different instances of an entity.
Foreign Key Example
Foreign Key Drill
Identify the primary and foreign key in the following SCHOOL
database.
DeptCode DeptName
001 Science
002 English
005 Computer
Teacher ID Fname Lname
B002 David Warner
B017 Sara Joseph
B009 Mike Brunton
Solution - Foreign Key Drill
In this table, adding the foreign key in Deptcode to the Teacher name,
creates a relationship between the two tables.
This concept is also known as Referential Integrity.
Teacher ID DeptCode Fname Lname
B002 002 David Warner
B017 002 Sara Joseph
B009 001 Mike Brunton
Thanks!!
