Relational Model

Unit-2

K.NAGA JYOTHI MA,M.Tech

 Relational Model

UNIT- II

Relational Model: Introduction to relational model, Codd’s rules, concepts of domain,

attribute, tuple, relation, constraints (Domain, Key constraints, integrity constraints) and
their importance , concept of keys (super key, candidate key, primary key, surrogate
key, foreign key) , relational Algebra & relational calculus.

Normalization: Purpose of Normalization or schema refinement, concept of functional

dependency, normal forms based on functional dependency(1NF, 2NF and 3 NF),
Boyce-codd normal form(BCNF)

Q) Write about Codd Rules?

E.F Codd was a Computer Scientist who invented Relational model for
Database management. Based on relational model, Relation database was
created. Coded proposed 13 rules popularly known as Codd's 12 rules to
test DBMS's concept against his relational model.
Rule zero This rule states that for a system to qualify as an RDBMS, it
must be able to manage database entirely through the relational
capabilities.
1. Information Rule: Data stored in Relational model must be a value
of some cell of a table.
2. Guaranteed Access Rule: Every data element must be accessible
by table name, its primary key and name of attribute whose value is
to be determined.
3. Systematic Treatment of NULL values: NULL value in database
must only correspond to missing, unknown or not applicable values.
4. Active Online Catalog: Structure of database must be stored in an
online catalog which can be queried by authorized users.
5. Comprehensive Data Sub-language Rule: A database should be
accessible by a language supported for definition, manipulation and
transaction management operation.
6. View Updating Rule: Different views created for various purposes
should be automatically updatable by the system.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

7. High level insert, update and delete rule: Relational Model

should support insert, delete, update etc. operations at each level of
relations. Also, set operations like Union, Intersection and minus
should be supported.
8. Physical data independence: Any modification in the physical
location of a table should not enforce modification at application
level.
9. Logical data independence: Any modification in logical or
conceptual schema of a table should not enforce modification at
application level. For example, merging of two tables into one should
not affect application accessing it which is difficult to achieve.
10. Integrity Independence: Integrity constraints modified at
database level should not enforce modification at application level.
11. Distribution Independence: Distribution of data over various
locations should not be visible to end-users.
12. Non-Subversion Rule: Low level access to data should not be
able to bypass integrity rule to change data.
Q) What is Key in relational data model? Explain different
types of Keys?
Or
Q) Explain the concept of KEYS in Relational Data Model?
Database Keys:
 Keys are very important part of Relational database.
 They are used to establish and identify relation between tables.
 They also guarantee that each record within a table can be uniquely identified
by combination of one or more fields within a table. Keys
There are five types of keys
 Super key
 Candidate key
 Primary key

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 Secondary key
 Composite key.
Super Key:-
 Super Key is defined as a set of attributes within a table that uniquely
identifies each record within a table. Super Key is a superset of
Candidate key.
Candidate Key
 Candidate keys are defined as the set of fields from which primary key
can be selected. It is an attribute or set of attribute that can act as a
primary key for a table to uniquely identify each record in that table.
Primary Key
 Primary key is a candidate key that is most appropriate to become
main key of the table. It is a key that uniquely identify each record in a
table.
Example: Studno StuName fee group
Stuno is primary key
Secondary or Alternative key
 The candidate key which is not selected for primary key is known as
secondary keys or alternative keys
Composite Key
 A Key that consists of two or more attributes that uniquely identify an entity
occurrence is called Composite key. But any attribute that makes up the
Composite key is not a simple key in its own.
Example: E_id E-Id Amount E-id is composite

Q) Write all about Relational data model Concepts ?

A relational model was introduced by E.F Codd.
 A Relational Database management System(RDBMS) is a database
management system based on relational model.
 In relational model, data is represented in terms of tuples(rows).
 Relational database is a collection of tables. Which data can be accessed easily.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 Relational Database is most commonly used database. It consists of number of

tables and each table has its unique name
Relation:
 It is a table which has rows and columns in the data model,
where rows represent records and columns represents the
attributes.
Tuples:
 A single row of a table, which contains a single record for that
relation, is called a tuple.
Attributes:
 Columns in a table are called attributes of the relation
Cardinality of a relation:
 The number of tuples in a relation determines its cardinality. In
this case, the relation has a cardinality of 4.
Degree of a relation:
 Each column in the tuple is called an attribute. The number of
attributes in a relation determines its degree. The relation has a
degree of 5.
Domain:
 A domain definition specifies the kind of data represented by the attribute.
Properties of a Relation A relation with N columns and M rows (tuples) is said to be of
degree N and cardinality M. This is Student_Table which shows the relation of degree three
and cardinality five.

Explain Relational or Referential Integrity?

 Data integrity is the overall completeness, accuracy and
consistency of data.
 It ensures that data entered into the data base must be
complete, accurate, valid and consistent. It is another form of
data base protection.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

Data Integrity
 Data Integrity validates the data before getting stored in the columns
of the table. SQL Server supports four type of data integrity:
Entity Integrity
 Entity Integrity can be enforced through indexes, UNIQUE constraints
and PRIMARY KEY constraints
Domain Integrity
 Domain integrity validates data for a column of the table. It can be
enforced using:
o Foreign key constraints,
o Check constraints,
o Default definitions
o NOT NULL.
Referential Integrity
 FOREIGN KEY and CHECK constraints are used to enforce Referential
Integrity.
User-Defined Integrity
 It enables you to create business logic which is not possible to
develop using system constraints. You can use stored procedure,
trigger and functions to create user-defined integrity

The characteristic properties of a relation are as follows:

 All entries in a given column are of the same kind or type.
 Attributes are unordered- The order of columns in a relation is immaterial. The
display of a relation in tabular form is free to arrange columns in any order.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 No duplicate tuples. A relation cannot contain two or more tuples which have the
same values for all the attributes. i.e., In any relation, every row is unique.

 There is only one value for each attribute of a tuple. The tuple should have only one
value.
 Tuples are unordered. The order of rows in a relation is immaterial. One is free to
display a relation in any convenient way.
*****
Q) Explain Relational or Referential Integrity?
Referential integrity is a relational database concept in which multiple tables share a
relationship based on the data stored in the tables, and that relationship must remain consistent.
Relational Integrity is also called Referential Integrity. Tables are related using data values.
Usually, these are the values of numeric keys instead of text fields. Every database relation is
involving just two tables, a parent table and a child table. Relational databases implement RI
using primary keys and foreign keys. The parent table contains the primary key and the
child table contains the foreign key which references (or "points" to, if you prefer) the parent
table.
Referential Integrity rule:
The referential integrity rule says that the database must not contain any unmatched
foreign key values. This implies that:
 A row may not be added to a table with a foreign key unless the referenced value exists
in the referenced table.
 If the value in a table that's referenced by a foreign key is changed (or the entire row is
deleted), the rows in the table with the foreign key must not be "leave alone."

K.NAGA JYOTHI MA,M.Tech

 Relational Model

In general, there are three options available when a referenced primary key value changes
or a row is deleted. The options are:
 Disallow. The change is completely disallowed.
 Cascade. For updates, the change is cascaded to all dependent tables. For deletions, the
rows in all dependent tables are deleted.
 Nullify. For deletions, the dependent foreign key values are set to Null.
Syntax:
CREATE TABLE child ( column definitions,
[constraint name] FOREIGN KEY (fk columns) REFERENCES parent [(pk
columns)] ON DELETE
CASCADE | SET NULL | SET DEFAULT | NO ACTION ON UPDATE
CASCADE | SET NULL | SET DEFAULT | NO ACTION
)
Examples:
1. create table branch ( bname char(15) not null, bcity char(30), assets integer, primary
key (bname) check (assets>= 0))
2. create table account ( account# char(10) not null, bname char(15), balance integer,
primary key (account#)
foreign key (bname) references branch, check (balance>= 0))
******

Relational Algebra
 A query language is a language in which user requests to retrieve some information
from the database. The query languages are considered as higher level languages
than programming languages. Query languages are of two types,
 Procedural Language
 Non-Procedural Language
 In procedural language, the user has to describe the specific procedure to retrieve the
information from the database.
Example: The Relational Algebra is a procedural language.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 In non-procedural language, the user retrieves the information from the database
without describing the specific procedure to retrieve it.
Example: The Tuple Relational Calculus(TRC) and the Domain Relational
Calculus(DRC) are non-procedural languages.
Limitations Of Relational Algebra
Although relational algebra seems powerful enough for most practical purposes, there
are some simple and natural operators on relations which cannot be expressed by relational
algebra.

Q) Write a short note on relational algebra and its operations?

 The relational algebra is a procedural query language.

 It consists of a set of operations that take one or two relations (tables) as input and
produce a new relation, on the request of the user to retrieve the specific information,
as the output.
 The relational algebra contains the following operations,
1) Selection 2) Projection 3) Union
4) Rename 5) Set-Difference 6) Cartesian product
7) Intersection 8) Join 9) Divide
 The Selection, Projection and Rename operations are called unary operations
because they operate only on one relation.
 The other operations operate on pairs of relations and are therefore called binary
operations.

Selection ( ) operation:
 The Selection is a relational algebra operation that uses a condition to select rows from
a relation.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 A new relation (output) is created from another existing relation by selecting only rows
requested by the user that satisfy a specified condition.
 The lower greek letter ‘sigma ’ is used to denote selection
operation. Syntax: Selection condition (relation_name)
For example, to list the regno> 102 from
Student_Table.
σRegno>102(Student_table)

Projection (π) operation:

 The projection is a relational algebra operation that creates a new relation by deleting
columns from an existing relation

 i.e., a new relation (output) is created from another existing relation by selecting only
those columns requested by the user from projection and is denoted by letter pi (π )
For example, to get a name from Student_Table.

πName(Student_Table)

Union
 It combines the similar columns from two tables into one resultant table.
 All columns that are participating in the UNION operation should be Union Compatible.
 This operator combines the records from both the tables into one.
 If there are duplicate values as a result, then it eliminates the duplicate.
 The resulting records will be from both table and distinct.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

πCust-name(Borrower) U π Cust-name (Depositor)

Intersection:
 This operator is used to pick the records from both the tables which are common to
them.
 In other words it picks only the duplicate records from the tables.
 Even though it selects duplicate records from the table, each duplicate record will be
displayed only once in the result set.
 It should have UNION Compatible columns to run the query with this operator.

πCust-name (Borrower) ∩ πCust-name (Depositor)

Minus
 This operator is used to display the records that are present only in the first table or
query, and doesn’t present in second table / query.
 It basically subtracts the first query results from the
second. πCust-name (Borrower) - πCust-name
(Depositor)

PRODUCT
 This command would show all possible pairs of rows from both tables being used.
 This command can also be referred to as the Cartesian Product.
 Consider the following two tables

K.NAGA JYOTHI MA,M.Tech

 Relational Model

The Cartesian product will result

JOIN
 Join is combination of Cartesian product followed by selection process.
 This operator takes two or more tables and combines them into one table.
 This can be used in combination with other commands to get specific information.
 There are several types of the Join command.
 The Natural Join, Equi-jion, Theta Join, Left Outer Join and Right Outer Join.
DIVIDE
 DIVIDE has specific requirements of the table.
 One of the tables can only have one column and the other table must have two columns
only.
Q) Write all about Relational Calculus?
 Relational calculus is an alternative to relational algebra.
 In contrast to the algebra, which is procedural, the relational calculus is non-procedural
or declarative.
 It allows user to describe the set of answers without showing procedure about how they
should be computed.
 Relational calculus has a big influence on the design of commercial query languages
such as SQL and QBE (Query-by Example).
Relational calculus is of two types,
 Tuple Relational Calculus (TRC)
 Domain Relational Calculus (DRC)
 Variables in TRC takes tuples (rows) as values and TRC had strong influence on SQL.
Variables in DRC takes fields (attributes) as values and DRC had strong influence on
QBE.
Tuple Relational Calculus (TRC)

K.NAGA JYOTHI MA,M.Tech

 Relational Model

The tuple relational calculus, is a non-procedural query language because it gives

the desired information without showing procedure about how they should be computed.A
query in Tuple Relational Calculus (TRC) is expressed as { T | p(T) }
Where,
T-tuple variable,
P(T)-‘p’ is a condition or formula that is true for ‘T’.
In addition to that we
use,
T[A]-to denote the value of tuple T on attribute A and
T.r -to denote that tuple T is in relation r.
Example TRC query
student (rollNo, name, degree, year, sex, deptNo, advisor )

department (deptId, name, hod, phone )

Obtain the details of all girl students in the MathsDept (deptId = 2)
{s| student(s)^ s.sex=‘F’^ s.deptNo=2}
Domain Relational Calculus (DRC)
 A Domain Relational Calculus (DRC) is a variable that comes in the range of the
values of domain (data types) of some columns (attributes).A Domain Relational
Calculus query has the form,
{ < x1, x2, …., xn> | p( < x1, x2, …., xn> ) }
 Where, each xi is either a domain variable or a constant and p(< x1, x2, …., xn>)
denotes a DRC formula.
 A DRC formula is defined in a manner that is very similar to the definition of a TRC
formula. The main difference is that the variables are domain variables.
Example:
Find all loan details in loan relation.
{ < N, D, A > | < N, D, A >∃ loan }
This query gives all loan details such as loan_no, loan_date, loan_amt for all
loan table in a bank. Each column is represented with an initials such as N-loan_no, D
– loan_date, A –loan_amt. The condition < N, D, A > ∃ loan ensures that the domain
variables N, D, A are restricted to the column domain.

K.NAGA JYOTHI MA,M.Tech

 Relational Model

Query By Example (QBE)

 Query by example (QBE) is a query method implemented in most database systems,
most notably for relational databases.
 QBE was created by Moshe Zloof at IBM in the 1970s in parallel to SQL’s
development.
 It is a Graphical Query Language where users can input commands into a table like
conditions and example elements.
 It's a common feature in most database programs.
 The principle of QBE is that it is merely an abstraction between the user and the real
query that the database system will receive. In the background, the user's query is
transformed into a database manipulation language form such as SQL, and it is this
SQL statement that will be executed in the background.

Normalization
 Normalization is the process of organizing the data in the database.
 Normalization is used to minimize the redundancy from a relation or set of
relations. It is also used to eliminate undesirable characteristics like Insertion,
Update, and Deletion Anomalies.
 Normalization divides the larger table into smaller and links them using
relationships.
 The normal form is used to reduce redundancy from the database table.
Why do we need Normalization?

K.NAGA JYOTHI MA,M.Tech

 Relational Model

The main reason for normalizing the relations is removing these anomalies.
Failure to eliminate anomalies leads to data redundancy and can cause data integrity
and other problems as the database grows. Normalization consists of a series of
guidelines that helps to guide you in creating a good database structure.
Data modification anomalies can be categorized into three types:
 Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new
tuple into a relationship due to lack of data.
 Deletion Anomaly: The delete anomaly refers to the situation where the deletion
of data results in the unintended loss of some other important data.
 Updatation Anomaly: The update anomaly is when an update of a single data
value requires multiple rows of data to be updated.
Types of Normal Forms:
Following are the various types of Normal forms:
Normal Form Description
1NF  A relation is in 1NF if it contains an atomic value.
2NF  A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional
dependent on the primary key.
3NF  A relation will be in 3NF if it is in 2NF and no transition dependency exists.
BCNF  A stronger definition of 3NF is known as Boyce Codd's normal form.
4NF  A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-
valued dependency.
5NF  A relation is in 5NF. If it is in 4NF and does not contain any join dependency,
joining should be lossless.

First Normal Form (1NF)

 A relation will be 1NF if it contains an atomic value.
 It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.
 First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

EMPLOYEE table:
EMP_ID EMP_NAME EMP_PHONE EMP_STATE
14 John 7272826385, UP
9064738238
20 Harry 8574783832 Bihar

K.NAGA JYOTHI MA,M.Tech

 Relational Model

12 Sam 7390372389, Punjab

8589830302
The decomposition of the EMPLOYEE table into 1NF has been shown below:
EMP_ID EMP_NAME EMP_PHONE EMP_STATE
14 John 7272826385 UP
14 John 9064738238 UP
20 Harry 8574783832 Bihar
12 Sam 7390372389 Punjab
12 Sam 8589830302 Punjab

Second Normal Form (2NF)

 In the 2NF, relational must be in 1NF.
 In the second normal form, all non-key attributes are fully functional dependent on the primary key
Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher
can teach more than one subject.
TEACHER table
TEACHER_ID SUBJECT TEACHER_AGE
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a
candidate key. That's why it violates the rule for 2NF.
To convert the given table into 2NF, we decompose it into two tables:
TEACHER_DETAIL table:
TEACHER_ID TEACHER_AGE
25 30
47 35
83 38

TEACHER_SUBJECT table:
TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer

Third Normal Form (3NF)

K.NAGA JYOTHI MA,M.Tech

 Relational Model

 A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
 If there is no transitive dependency for non-prime attributes, then the relation must be in
third normal form.
 A relation is in third normal form if it holds atleast one of the following conditions for
every non-trivial function dependency X → Y.
1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.
Example:
EMPLOYEE_DETAIL table:
EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY
222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal
Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on

Candidate key: {EMP_ID}
Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.
Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on
EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super
key(EMP_ID). It violates the rule of third normal form.
That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP>
table, with EMP_ZIP as a Primary key.
EMPLOYEE table:
EMP_ID EMP_NAME EMP_ZIP
222 Harry 201010
333 Stephan 02228
444 Lan 60007
555 Katharine 06389
666 John 462007
EMPLOYEE_ZIP table:
EMP_ZIP EMP_STATE EMP_CITY
201010 UP Noida
02228 US Boston
60007 US Chicago

K.NAGA JYOTHI MA,M.Tech

 Relational Model

06389 UK Norwich
462007 MP Bhopal

Boyce Codd normal form (BCNF)

 BCNF is the advance version of 3NF. It is stricter than 3NF.
 A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
 For BCNF, the table should be in 3NF, and for every FD, LHS is super key.
Example: Let's assume there is a company where employees work in more than one
department.
EMPLOYEE table:
EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO
264 India Designing D394 283
264 India Testing D394 300
364 UK Stores D283 232
364 UK Developing D283 549
In the above table Functional dependencies are as follows:
1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY table:
EMP_ID EMP_COUNTRY
264 India
264 India
EMP_DEPT table:
EMP_DEPT DEPT_TYPE EMP_DEPT_NO
Designing D394 283
Testing D394 300
Stores D283 232
Developing D283 549
EMP_DEPT_MAPPING table:
EMP_ID EMP_DEPT
D394 283
D394 300
D283 232
D283 549
Functional dependencies:

K.NAGA JYOTHI MA,M.Tech

 Relational Model

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Now, this is in BCNF because left side part of both the functional dependencies is a key.

Fourth normal form (4NF)

 A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued
dependency.
 For a dependency A → B, if for a single value of A, multiple values of B exists, then the
relation will be a multi-valued dependency.
Example
STUDENT
STU_ID COURSE HOBBY

21 Computer Dancing

21 Math Singing

34 Chemistry Dancing

74 Biology Cricket

59 Physics Hockey

 The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.
 In the STUDENT relation, a student with STU_ID, 21 contains two
courses, Computer and Math and two hobbies, Dancing and Singing. So there is a
Multi-valued dependency on STU_ID, which leads to unnecessary repetition of data.
 So to make the above table into 4NF, we can decompose it into two tables:
STUDENT_COURSE
STU_ID COURSE
21 Computer
21 Math
34 Chemistry
74 Biology
59 Physics
STUDENT_HOBBY
STU_ID HOBBY
21 Dancing

K.NAGA JYOTHI MA,M.Tech

 Relational Model

21 Singing
34 Dancing
74 Cricket
59 Hockey

Fifth normal form (5NF)

 A relation is in 5NF if it is in 4NF and not contains any join dependency and joining
should be lossless.
 5NF is satisfied when all the tables are broken into as many tables as possible in
order to avoid redundancy.
 5NF is also known as Project-join normal form (PJ/NF).
Example
SUBJECT LECTURER SEMESTER
Computer Anshika Semester 1
Computer John Semester 1
Math John Semester 1
Math Akash Semester 2
Chemistry Praveen Semester 1

 In the above table, John takes both Computer and Math class for Semester 1 but he
doesn't take Math class for Semester 2. In this case, combination of all these fields
required to identify a valid data.
 Suppose we add a new Semester as Semester 3 but do not know about the subject and
who will be taking that subject so we leave Lecturer and Subject as NULL. But all three
columns together acts as a primary key, so we can't leave other two columns blank.
 So to make the above table into 5NF, we can decompose it into three relations P1, P2 &
P3:
P1
SEMESTER SUBJECT
Semester 1 Computer
Semester 1 Math
Semester 1 Chemistry
Semester 2 Math
P2
SUBJECT LECTURER
Computer Anshika
Computer John
Math John
Math Akash

K.NAGA JYOTHI MA,M.Tech

 Relational Model

Chemistry Praveen
P3
SEMSTER LECTURER
Semester 1 Anshika
Semester 1 John
Semester 1 John
Semester 2 Akash
Semester 1 Praveen

K.NAGA JYOTHI MA,M.Tech