UNIT-II

Relational Model (part-1)

Introduction to Relational Model
 It was proposed by Dr. E.F. Codd. It uses the concept of
relations to represent each and every file.
 Relations are Two-Dimensional Tables.
 It is easy to implement and easy to simplification in the
operations to manipulate the data.
 This is the most popular data model. It is simple to implement.
 It uses the primary key and secondary key to connect any two
files.
 After designing the conceptual model of database using ER
diagram, we need to convert the conceptual model in the
relational model which can be implemented using any
RDBMS (Relational Data Base Management System) like
SQL, MY SQL etc.
 This simple tabular representation enables even new users to
understand the contents of a database, and it permits the use of
simple, high-level languages to query the data.
• A Relational Database Model consists of relations to connect
them by key fields.
• A relation has some attributes.
• The relation is represented in rows and columns.
• Each column of the relation is called an attribute.
• Each row in the relation is called a tuple.
• Each relation can have one unique column i.e. primary key.
• Each relation can have n-columns and n-tuple.
• Each relation is preceded by the name of that relation.
• The fields of the relations are separated by commas and placed
within the parentheses of the relation.
• The relational model represents data in the form of relations or
tables.
• This model is simple and it has all the properties and
capabilities required to process data with storage efficiency.
• Consider a relation STUDENT with attributes ROLL_NO,
NAME, ADDRESS, PHONE and AGE as shown in table.
• STUDENT TABLE/RELATION :

ROLL_NO NAME ADDRESS PHONE AGE

1 Nishma Hyderabad 9455123451 28

2 Sai Guntur 9652431843 27

3 Swetha Nellore 9156253131 26

4 Raji Ongole 9215635311 25

• Attribute: Attributes are the properties that define a relation.

Ex: ROLL_NO, NAME

• Tuple: Each row in a relation is known as tuple.

Ex :
3 Swetha Nellore 9156253131 26

• Degree: The number of attributes in the relation is known as

degree.
Ex: The degree of the given STUDENT table is 5
• Column: Column represent the set of values for a particular
attribute.
• The column ROLL_NO is extracted from the relation
STUDENT.
ROLL_NO
• Ex:
1

• Null values: The value which is not known or unavailable is

called NULL VALUE. It is represented by blank space.
• Cardinality: The number of tuples are present in the relation

is called as its cardinality.

• Ex: The Cardinality of the STUDENT table is 4.
• Relation instance: The set of tuples of a relation at a
particular instance of a time is called Relation Instance.
• Relational Schema: It is the logical blueprint of the relation
i.e., it describes the design and the structure of the relation. It
contains the table name, its attributes, and their types
• Relation schema: A relation schema represents the name of
the relation with its attributes.
• Ex: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and
AGE) is Relation schema for STUDENT.
• Relation Key: It is an attribute or a group of attributes that
can be used to uniquely identify an entity in a table or to
determine the relationship between two tables.
Advantages of using the relational model
• The advantages and reasons due to which the relational model
in DBMS is widely accepted as a standard are:
• Simple and Easy To Use - Storing data in tables is much
easier to understand and implement as compared to other
storage techniques.
• Manageability - Because of the independent nature of each
relation in a relational database, it is easy to manipulate and
manage. This improves the performance of the database.
• Query capability - With the introduction of relational algebra,
relational databases provide easy access to data via high-level
query language like SQL.
• Data integrity - With the introduction and implementation of
relational constraints, the relational model can maintain data
integrity in the database.
• Key and its importance :
• KEYS are attributes or sets of attributes that enable the
identification of a row or tuple within a relation or table.
• They establish connections between different tables and assist
in uniquely identifying a row by utilizing one or more columns
in the table.
• Keys play a vital role in locating distinct records or rows
within a table, and they serve as a means to find a unique
record or row within the database.
Relation keys can be of 6 different types:
1.Candidate Key
2.Super Key
3.Composite Key
4.Primary Key
5.Alternate Key
6.Foreign Key
Primary Key
• A primary key is used to ensure that data in the specific
column is unique. A column cannot have NULL values. It is
either an existing table column or a column that is specifically
generated by the database according to a defined sequence.
• Example: STUD_NO, as well as STUD_PHONE both, are
candidate keys for relation STUDENT but STUD_NO can be
chosen as the primary key (only one out of many candidate
keys).
Table STUDENT

STUD_N STUD_NAM STUD_PHON STUD_STAT STUD_COUN STUD_AG

O E E E T E

1 RAM 9865278251 Haryana I ndi a 20

2 RAM 9655470231 Punj ab I ndi a 19

3 SUJI T 7514290359 Raj asthan I ndi a 18

4 SURESH 8564103258 Punj ab I ndi a 21

Table STUDENT_COURSE

STUD_NO COURSE_NO COURSE_NAME

1 C1 DBMS

2 C2 Computer Networks

1 C2 Computer Networks
Properties of a Primary Key
• The Primary Key field shouldn’t be left NULL.
• The Primary Key column must contain a value.
• In that column, no two rows in the table may contain identical
values.
• If a foreign key in a DBMS refers to the primary Key, no value
may be altered or modified in this primary key column.
Example:

In the following example, StudID is a Primary Key.

StudID Roll No First Name LastName Email

1 11 Tom Price [email protected]
2 12 Nick Wright [email protected]

3 13 Dana Natan [email protected]

Foreign Key
• A foreign key is a column or group of columns in a relational
database table that provides a link between data in two tables.
It is a column (or columns) that references a column (most
often the primary key) of another table.
• Example: STUD_NO in STUDENT_COURSE is a foreign
key to STUD_NO in STUDENT relation.
• FOREIGN KEY is a column that creates a relationship
between two tables.
• The purpose of Foreign keys is to maintain data integrity and
allow navigation between two different instances of an entity.
• It acts as a cross-reference between two tables as it references
the primary key of another table.
• It is a key that serves as both a secondary key and a primary
key in two different tables.
• At any given time, it combines two or more relations.
• They serve as cross-references for the tables.
Candidate key
• A candidate key is an attribute or set of attributes that can
uniquely identify a tuple.Except for the primary key, the
remaining attributes are considered a candidate key.
• The candidate keys are as strong as the primary key.
• Candidate Key is a super key with no repeated attributes.
• The Primary key should be selected from the candidate keys.
Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only a single
primary key.
For example:
• In the EMPLOYEE table, id is best suited for the primary key.
The rest of the attributes, like SSN, Passport_Number,
License_Number, etc., are considered a candidate key.
Properties of Candidate key:
• It must contain unique values
• Candidate key in SQL may have multiple attributes
• Must not contain null values
• It should contain minimum fields to ensure uniqueness
• Uniquely identify each record in a table.
• Candidate key Example: In the given table Stud ID, Roll No,
and email are candidate keys which help us to uniquely
identify the student record in the table.

StudID Roll No First Name LastName Email

1 11 Tom Price [email protected]
2 12 Nick Wright [email protected]
3 13 Dana Natan [email protected]
• Example:candidate key
Alternate Key :
• There may be one or more attributes or a combination of
attributes that uniquely identify each tuple in a relation.
• These attributes or combinations of the attributes are called the
candidate keys.
• One key is chosen as the primary key from these candidate
keys, and the remaining candidate key, if it exists, is termed
the alternate key.
• In other words, the total number of the alternate keys is the
total number of candidate keys minus the primary key.
• The alternate key may or may not exist.
• If there is only one candidate key in a relation, it does not have
an alternate key.
• For example, employee relation has two attributes,
Employee_Id and PAN_No, that act as candidate keys. In this
relation, Employee_Id is chosen as the primary key, so the
other candidate key, PAN_No, acts as the Alternate key.
Properties of an alternate key
• Alternate keys refer to all keys that are not main keys.
• It’s a backup key.
• It has two or more fields that allow it to recognize two or more
records.
• These criteria are reiterated.
Super Key
• Super key is an attribute set that can uniquely identify a tuple.
A super key is a superset of a candidate key.
Properties of a super key
• A super key must ensure that each record in a table is unique.
• Nevertheless, the minimal collection of characteristics that can
guarantee uniqueness should be a super key.
• Multiple subsets that are likewise regarded as super keys can
exist for a super key.
• For example:In the above EMPLOYEE table,
for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two
employees can be the same, but their EMPLOYEE_ID can't be
the same. Hence, this combination can also be a key.
• The super key would be EMPLOYEE-ID (EMPLOYEE_ID,
EMPLOYEE-NAME), etc.
Composite key
• Whenever a primary key consists of more than one attribute, it
is known as a composite key.
• This key is also known as Concatenated Key.
• COMPOSITE KEY is a combination of two or more columns
that uniquely identify rows in a table.
• The combination of columns guarantees uniqueness, though
individually uniqueness is not guaranteed.
• Hence, they are combined to uniquely identify records in a
table.
• The difference between compound and the composite key is
that any part of the compound key can be a foreign key, but
the composite key may or maybe not a part of the foreign key.
• For example, in employee relations, we assume that an
employee may be assigned multiple roles, and an employee
may work on multiple projects simultaneously.
• So the primary key will be composed of all three attributes,
namely Emp_ID, Emp_role, and Proj_ID in combination.
• So these attributes act as a composite key since the primary
key comprises more than one attribute.
Properties of Composite key
• It acts as a primary key if there is no primary key in a table
• Two or more attributes are used together to make a composite
key.
• Different combinations of attributes may give different
accuracy in terms of identifying the rows uniquely.
Artificial key/surrogate key
• The key created using arbitrarily assigned data are known as
artificial keys.
• These keys are created when a primary key is large and
complex and has no relationship with many other relations.
• The data values of the artificial keys are usually numbered in a
serial order.
• For example, the primary key, which is composed of Emp_ID,
Emp_role, and Proj_ID, is large in employee relations. So it
would be better to add a new virtual attribute to identify each
tuple in the relation uniquely.
 Nul l
K ey Defi ni ti Uni que Al terat
Val u Purpose I ndex Usage
Type on ness i on
es

M ay be
U ni quel y D at a C reat
com pl ex
Pri ma i dent i fi e Not i nt egri t y es W i t hi n
Requi r ,
ry s each Al l o , record U ni qu sam e
ed im pact
Key record i n wed i dent i fi c e t abl e
ot her
a t abl e. at i on. I ndex
t abl es.

Dat a
Est abl i sh
consi st en More
es a M ay
Forei Not cy, Bet w ee fl exi bl
rel at i ons Al l o creat
gn Requi r rel at i ons n e f or
hi p wed e
Key ed hi p t abl es mai nt en
bet w een I ndex
mai nt enan ance.
t abl es.
ce.

Al t ernat i
ve uni que Backup M ay May
Candi Not W i t hi n
keys t hat Requi r pri m ary creat becom e
dat e Al l o sam e
coul d be ed key e pri mary
Key wed t abl e
pri m ary opt i ons. I ndex key.
keys.

C om bi nat i
on of M ay
C oncept ua C oncep
Super at t ri but e Requi r Al l o creat
l , not t ual N/A
Key s t hat ed wed e
enf orced. use
uni quel y I ndex
i dent i f y.

M ay be
Com bi ned C reat
Speci al i z com pl ex
Com po at t ri but e Not es W i t hi n
Requi r ed uni que ,
si t e s used as Al l o C om po sam e
ed i dent i fi c im pact
Key a si ngl e wed si t e t abl e
at i on. perf orm
key. I ndex
ance.

Ensures Enf orce Creat May be

W i t hi n
Uni qu col umn( s) Requi r Al l o uni quenes es used as
sam e
e K ey have ed wed s, no Uni qu pri mary
t abl e
uni que pri mary e key.
• Difference between Primary Key and Foreign Key
PRIM
ARY KEY FOREIGNKEY

A f orei gn key i s a col um n or group of

A pri m ary key i s used t o
col umns i n a rel at i onal dat abase
ensure dat a i n t he
t abl e t hat provi des a l i nk bet ween
speci fi c col umn i s uni que.
dat a i n t wo t abl es.

I t uni quel y i dent i fi es a I t ref ers t o t he fi el d i n a t abl e

record i n t he rel at i onal whi ch i s t he pri m ary key of anot her
dat abase t abl e. t abl e.

Onl y one pri m ary key i s Whereas more t han one f orei gn key i s
al l owed i n a t abl e. al l owed i n a t abl e.

I t i s a com bi nat i on of
I t can cont ai n dupl i cat e val ues and a
UNIQ UE and N ot N ul l
t abl e i n a rel at i onal dat abase.
const rai nt s.

I t does not al l ow NULL

I t can al so cont ai n NULL val ues.
val ues.

I t s val ue cannot be
I t s val ue can be del et ed f romt he
del et ed f romt he parent
chi l d t abl e.
t abl e.

I t const rai nt can be

I t const rai nt cannot be defi ned on
impl i ci t l y defi ned on t he
t he l ocal or gl obal t emporary t abl es.
t em porary t abl es.