By Zakir Hussain

Shakara Institute of Technology

UNIT-1
Overview of DBMS

Database is a collection of related data and data is a collection of facts and figures that can be
processed to produce information.

Mostly data represents recordable facts. Data aids in producing information, which is based on facts.
For example, if we have data about marks obtained by all students, we can then conclude about toppers
and average marks

A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information.

Characteristics
Traditionally, data was organized in file formats. DBMS was a new concept then, and all the research
was done to make it overcome the deficiencies in traditional style of data management. A modern
DBMS has the following characteristics −

 Real-world entity − A modern DBMS is more realistic and uses real-world entities to design
its architecture. It uses the behavior and attributes too. For example, a school database may use
students as an entity and their age as an attribute.
 Relation-based tables − DBMS allows entities and relations among them to form tables. A
user can understand the architecture of a database just by looking at the table names.
 Isolation of data and application − A database system is entirely different than its data. A
database is an active entity, whereas data is said to be passive, on which the database works and
organizes. DBMS also stores metadata, which is data about data, to ease its own process.
 Less redundancy − DBMS follows the rules of normalization, which splits a relation when any
of its attributes is having redundancy in values. Normalization is a mathematically rich and
scientific process that reduces data redundancy.
 Consistency − Consistency is a state where every relation in a database remains consistent.
There exist methods and techniques, which can detect attempt of leaving database in
inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of
data storing applications like file-processing systems.
 Query Language − DBMS is equipped with query language, which makes it more efficient to
retrieve and manipulate data. A user can apply as many and as different filtering options as
required to retrieve a set of data. Traditionally it was not possible where file-processing system
was used.
 ACID Properties − DBMS follows the concepts of Atomicity, Consistency, Isolation,
and Durability (normally shortened as ACID). These concepts are applied on transactions,
which manipulate data in a database. ACID properties help the database stay healthy in multi-
transactional environments and in case of failure.
 Multiuser and Concurrent Access − DBMS supports multi-user environment and allows
them to access and manipulate data in parallel. Though there are restrictions on transactions
when users attempt to handle the same data item, but users are always unaware of them.

1
By Zakir Hussain
Shakara Institute of Technology
 Multiple views − DBMS offers multiple views for different users. A user who is in the Sales
department will have a different view of database than a person working in the Production
department. This feature enables the users to have a concentrate view of the database according
to their requirements.
 Security − Features like multiple views offer security to some extent where users are unable to
access data of other users and departments. DBMS offers methods to impose constraints while
entering data into the database and retrieving the same at a later stage. DBMS offers many
different levels of security features, which enables multiple users to have different views with
different features. For example, a user in the Sales department cannot see the data that belongs
to the Purchase department. Additionally, it can also be managed how much data of the Sales
department should be displayed to the user. Since a DBMS is not saved on the disk as
traditional file systems, it is very hard for miscreants to break the code.

Users
A typical DBMS has users with different rights and permissions who use it for different purposes.
Some users retrieve data and some back it up. The users of a DBMS can be broadly categorized as
follows −

 Administrators − Administrators maintain the DBMS and are responsible for administrating
the database. They are responsible to look after its usage and by whom it should be used. They
create access profiles for users and apply limitations to maintain isolation and force security.
Administrators also look after DBMS resources like system license, required tools, and other
software and hardware related maintenance.
 Designers − Designers are the group of people who actually work on the designing part of the
database. They keep a close watch on what data should be kept and in what format. They
identify and design the whole set of entities, relations, constraints, and views.
 End Users − End users are those who actually reap the benefits of having a DBMS. End users
can range from simple viewers who pay attention to the logs or market rates to sophisticated
users such as business analysts.

2
By Zakir Hussain
Shakara Institute of Technology
Difference between File System and DBMS
Basics File System DBMS

The file system is a way of arranging the

DBMS is software for managing the
files in a storage medium within a
database.
Structure computer.

Data Redundant data can be present in a file

In DBMS there is no redundant data.
Redundancy system.

Backup and It doesn’t provide Inbuilt mechanism for It provides in house tools for backup
Recovery backup and recovery of data if it is lost. and recovery of data even if it is lost.

Query There is no efficient query processing in Efficient query processing is there in

processing the file system. DBMS.

There is more data consistency

There is less data consistency in the file
because of the process of
system.
Consistency normalization.

It has more complexity in handling

It is less complex as compared to DBMS.
Complexity as compared to the file system.

DBMS has more security

File systems provide less security in
Security mechanisms as compared to file
comparison to DBMS.
Constraints systems.

It has a comparatively higher cost

It is less expensive than DBMS.
Cost than a file system.

In DBMS data independence exists,

mainly of two types:
There is no data independence.
Data 1) Logical Data Independence.
Independence 2)Physical Data Independence.

Multiple users can access data at a

Only one user can access data at a time.
User Access time.

The users are not required to write The user has to write procedures for
Meaning procedures. managing databases

3
By Zakir Hussain
Shakara Institute of Technology

Basics File System DBMS

Data is distributed in many files. So, it is Due to centralized nature data

Sharing not easy to share data. sharing is easy

Data It give details of storage and representation It hides the internal details of
Abstraction of data Database

Integrity Integrity Constraints are difficult to Integrity constraints are easy to

Constraints implement implement

To access data in a file , user requires

No such attributes are required.
Attributes attributes such as file name, file location.

Example Cobol, C++ Oracle, SQL Server

The main difference between a file system and a DBMS (Database Management System) is the
way they organize and manage data.
1. File systems are used to manage files and directories, and provide basic operations for creating,
deleting, renaming, and accessing files. They typically store data in a hierarchical structure,
where files are organized in directories and subdirectories. File systems are simple and efficient,
but they lack the ability to manage complex data relationships and ensure data consistency.
2. On the other hand, DBMS is a software system designed to manage large amounts of structured
data, and provide advanced operations for storing, retrieving, and manipulating
data. DBMS provides a centralized and organized way of storing data, which can be accessed and
modified by multiple users or applications. DBMS offers advanced features like data validation,
indexing, transactions, concurrency control, and backup and recovery mechanisms. DBMS
ensures data consistency, accuracy, and integrity by enforcing data constraints, such as primary
keys, foreign keys, and data types.

Architecture of DBMS

 The DBMS design depends upon its architecture. The basic client/server architecture is used to
deal with a large number of PCs, web servers, database servers and other components that are
connected with networks.
 The client/server architecture consists of many PCs and a workstation which are connected via
the network.
 DBMS architecture depends upon how users are connected to the database to get their request
done.

4
By Zakir Hussain
Shakara Institute of Technology
Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of
two types like: 2-tier architecture and 3-tier architecture.

1-Tier Architecture
 In this architecture, the database is directly available to the user. It means the user can directly
sit on the DBMS and uses it.
 Any changes done here will directly be done on the database itself. It doesn't provide a handy
tool for end users.
 The 1-Tier architecture is used for development of the local application, where programmers
can directly communicate with the database for the quick response.

2-Tier Architecture
 The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications
on the client end can directly communicate with the database at the server side. For this
interaction, API's like: ODBC, JDBC are used.
 The user interfaces and application programs are run on the client-side.
 The server side is responsible to provide the functionalities like: query processing and
transaction management.

 To communicate with the DBMS, client-side application establishes a connection with the
server side.

5
By Zakir Hussain
Shakara Institute of Technology

Fig: 2-tier Architecture

3-Tier Architecture
 The 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.
 The application on the client-end interacts with an application server which further
communicates with the database system.
 End user has no idea about the existence of the database beyond the application server. The
database also has no idea about any other user beyond the application.
 The 3-Tier architecture is used in case of large web application.

Fig: 3-tier Architecture

6
By Zakir Hussain
Shakara Institute of Technology
Data Models
Data Model is the modeling of the data description, data semantics, and consistency constraints of the
data. It provides the conceptual tools for describing the design of a database at each level of data
abstraction. Therefore, there are following four data models used for understanding the structure of the
database:

1) Relational Data Model: This type of model designs the data in the form of rows and columns
within a table. Thus, a relational model uses tables for representing data and in-between relationships.
Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The
relational data model is the widely used model which is primarily used by commercial data processing
applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and
relationships among them. These objects are known as entities, and relationship is an association
among these entities. This model was designed by Peter Chen and published in 1976 papers. It was
widely used in database designing. A set of attributes describe the entities. For example, student_name,
student_id describes the 'student' entity. A set of the same type of entities is known as an 'Entity set',
and the set of the same type of relationships is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation,
and object identity, as well. This model supports a rich type system that includes structured and
collection types. Thus, in 1980s, various database systems following the object-oriented approach were
developed. Here, the objects are nothing but the data carrying its properties.

4) Semi structured Data Model: This type of data model is different from the other three data models
(explained above). The semi structured data model allows the data specifications at places where the
individual data items of the same type may have different attributes sets. The Extensible Markup
Language, also known as XML, is widely used for representing the semi structured data. Although
XML was initially designed for including the markup information to the text document, it gains
importance because of its application in the exchange of data.

7
By Zakir Hussain
Shakara Institute of Technology

ER (Entity Relationship) Diagram in DBMS

 ER model stands for an Entity-Relationship model. It is a high-level data model. This model is
used to define the data elements and relationship for a specified system.
 It develops a conceptual design for the database. It also develops a very simple and easy to
design view of data.
 In ER modeling, the database structure is portrayed as a diagram called an entity-relationship
diagram.

For example, Suppose we design a school database. In this database, the student will be an entity with
attributes like address, name, id, age, etc. The address can be another entity with attributes like city,
street name, pin code, etc and there will be a relationship between them.

Component of ER Diagram

8
By Zakir Hussain
Shakara Institute of Technology
1. Entity:

An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as
rectangles.

Consider an organization as an example- manager, product, employee, department etc. can be taken as
an entity.

a. Weak Entity

An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key
attribute of its own. The weak entity is represented by a double rectangle.

2. Attribute

The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.

9
By Zakir Hussain
Shakara Institute of Technology

a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It represents a primary key.
The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute: An attribute that composed of many other attributes is known as a composite
attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an
ellipse.

c. Multivalued Attribute: An attribute can have more than one value. These attributes are known as a
multivalued attribute. The double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.

10
By Zakir Hussain
Shakara Institute of Technology

d. Derived Attribute :An attribute that can be derived from other attribute is known as a derived
attribute. It can be represented by a dashed ellipse.

For example, A person's age changes over time and can be derived from another attribute like Date of
birth.

3. Relationship:

A relationship is used to describe the relation between entities. Diamond or rhombus is used to
represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship: When only one instance of an entity is associated with the relationship,
then it is known as one to one relationship.

For example, A female can marry to one male, and a male can marry to one female.

11
By Zakir Hussain
Shakara Institute of Technology

b. One-to-many relationship: When only one instance of the entity on the left, and more than one
instance of an entity on the right associates with the relationship then this is known as a one-to-many
relationship.

For example, Scientist can invent many inventions, but the invention is done by the only specific
scientist.

c. Many-to-one relationship: When more than one instance of the entity on the left, and only one
instance of an entity on the right associates with the relationship then it is known as a many-to-one
relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship: When more than one instance of the entity on the left, and more than
one instance of an entity on the right associates with the relationship then it is known as a many-to-
many relationship.

For example, Employee can assign by many projects and project can have many employees.

12
By Zakir Hussain
Shakara Institute of Technology
Type of Keys in DMBS
A key refers to an attribute/a set of attributes that help us identify a row (or tuple) uniquely in a table
(or relation). A key is also used when we want to establish relationships between the different columns
and tables of a relational database. The individual values present in a key are commonly referred to as
key values.

Why do we require Keys in DBMS?

We use a key for defining various types of integrity constraints in a database. A table, on the other
hand, represents a collection of the records of various events for any relation. Now, there might be
thousands of these records, and some of these might even be duplicated.

Thus, we need a way in which one can identify all of these records uniquely and separately, i.e.,
without any duplicates. This hassle is removed with the help of keys.

For example, let us consider a database of all the students who are studying in a college. What attribute
of all the students, according to you, will identify each of these people uniquely? We can refer to these
students by their names, departments, sections, and year. Similarly, we can also mention only the
university roll number and fetch all the other details based on that roll number.

The keys in DBMS can be a combination of multiple attributes (or columns), or they can be just one
single attribute. The primary motive of the keys is to provide every record with a unique identity of its
own.

Different Types of Database Keys

 Candidate Key
 Primary Key
 Super Key
 Alternate Key
 Foreign Key
 Composite Key

Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. For
Example, STUD_NO in STUDENT relation.
 It is a minimal super key.
 It is a super key with no repeated data is called a candidate key.
 The minimal set of attributes that can uniquely identify a record.
 It must contain unique values.
 It can contain NULL values.
 Every table must have at least a single candidate key.
 A table can have multiple candidate keys but only one primary key.
 The value of the Candidate Key is unique and may be null for a tuple.

13
By Zakir Hussain
Shakara Institute of Technology
 There can be more than one candidate key in a relationship.
Example:
STUD_NO is the candidate key for relation STUDENT.
Table STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

 The candidate key can be simple (having only one attribute) or composite as well.
Example:
{STUD_NO, COURSE_NO} is a composite
candidate key for relation STUDENT_COURSE.
Table STUDENT_COURSE
STUD_NO TEACHER_NO COURSE_NO

1 001 C001

2 056 C005

Note: In SQL Server a unique constraint that has a nullable column, allows the value ‘null‘ in that
column only once. That’s why the STUD_PHONE attribute is a candidate here, but can not be a
‘null’ value in the primary key attribute.
Primary Key
There can be more than one candidate key in relation out of which one can be chosen as the primary
key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for relation
STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).
 It is a unique key.
 It can identify only one tuple (a record) at a time.
 It has no duplicate values, it has unique values.
 It cannot be NULL.
 Primary keys are not necessarily to be a single column; more than one column can also be a
primary key for a table.
Example:
STUDENT table -> Student(STUD_NO, SNAME,
ADDRESS, PHONE) , STUD_NO is a primary key
Table STUDENT

14
By Zakir Hussain
Shakara Institute of Technology

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Super Key
The set of attributes that can uniquely identify a tuple is known as Super Key. For Example,
STUD_NO, (STUD_NO, STUD_NAME), etc. A super key is a group of single or multiple keys that
identifies rows in a table. It supports NULL values.
 Adding zero or more attributes to the candidate key generates the super key.
 A candidate key is a super key but vice versa is not true.
 Super Key values may also be NULL.
Example:
Consider the table shown above.
STUD_NO+PHONE is a super key.

Relation between Primary Key, Candidate Key, and Super Key

Alternate Key
The candidate key other than the primary key is called an alternate key.
 All the keys which are not primary keys are called alternate keys.
 It is a secondary key.
 It contains two or more fields to identify two or more records.
 These values are repeated.

15
By Zakir Hussain
Shakara Institute of Technology
 Eg:- SNAME, and ADDRESS is Alternate keys
Example:
Consider the table shown above.
STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key
(only one out of many candidate keys).

Primary Key, Candidate Key, and Alternate Key

Foreign Key
If an attribute can only take the values which are present as values of some other attribute, it will be
a foreign key to the attribute to which it refers. The relation which is being referenced is called
referenced relation and the corresponding attribute is called referenced attribute the relation which
refers to the referenced relation is called referencing relation and the corresponding attribute is called
referencing attribute. The referenced attribute of the referenced relation should be the primary key to
it.
 It is a key it acts as a primary key in one table and it acts as
secondary key in another table.
 It combines two or more relations (tables) at a time.
 They act as a cross-reference between the tables.
 For example, DNO is a primary key in the DEPT table and a non-key in EMP
Example:

16
By Zakir Hussain
Shakara Institute of Technology

Relation between Primary Key and Foreign Key

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. It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes
that can uniquely identify rows in a table.
 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.
Example:
FULLNAME + DOB can be combined
together to access the details of a student.

17
By Zakir Hussain
Shakara Institute of Technology
Integrity Constraints Rule
 Integrity constraints are a set of rules. It is used to maintain the quality of information.

 Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.
 Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints
 Domain constraints can be defined as the definition of a valid set of values for an attribute.

 The data type of domain includes string, character, integer, time, date, currency, etc. The value
of the attribute must be available in the corresponding domain.

Example:

18
By Zakir Hussain
Shakara Institute of Technology
2. Entity integrity constraints
 The entity integrity constraint states that primary key value can't be null.

 This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
 A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

 A referential integrity constraint is specified between two tables.

 In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of
Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

Example:

19
By Zakir Hussain
Shakara Institute of Technology
4. Key constraints
 Keys are the entity set that is used to identify an entity within its entity set uniquely.

 An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.

Example:

Data Dictionary:

 A Data Dictionary comprises two words i.e. Data which simply means information being
collected through some sources and Dictionary means where this information is available.
 A Data Dictionary can be defined as a collection of information on all data elements or
contents of databases such as data types, and text descriptions of the system. It makes it easier
for users and analysts to use data as well as understand and have common knowledge about
inputs, outputs, components of a database, and intermediate calculations.
Why Data Dictionary is Essential?
There is less information and details provided by data models. So, a data dictionary is essential and
needed to have proper knowledge and usage of contents. Data Dictionary provides all information
about names that are used in system models. Data Dictionary also provides information about entities,
relationships, and attributes that are present in the system model. As a part of the structured analysis
and design tool, the implementation of a data dictionary is done.

Types of Data Dictionary

Data Dictionary is basically of two types. We will discuss each of them.
 Integrated Data Dictionary
 Stand Alone Data Dictionary
1. Integrated Data Dictionary
Integrated Data Dictionary can be seen as a catalog that can be maintained by the relational database.
In previous databases, there is not any functionality of Integrated Data Dictionary, so they use Stand
Alone Data Dictionary.
There are two types of Integrated Data Dictionary.
 Active Data Dictionary: Active Database Dictionary is a type of database that is updated
automatically in case any changes are to be done to the database. These are self-updating.
 Passive Data Dictionary: Passive Databases are the databases that have to be maintained or
updated manually in case of any changes have been made to the system.

20
By Zakir Hussain
Shakara Institute of Technology
2. Stand Alone Data Dictionary
Stand Alone Data Dictionary is a type of flexible data dictionary as Database Administrator has ease of
managing data. It does not require data that is computer-based. It has no fixed format. But some
elements are common in this kind of database.
 Data Elements: It has the elements like name, datatype, validation rules, etc.
 Tables: These contain all the necessary information that is required for the table, how many
rows in the table, how many columns in the table, etc.
 Index: The index of the databases is to be stored here.
 Programs: These are used for accessing the database, and can include SQL Queries, Reports,
etc.
 Relationship between Data Elements: This stores the relationship among the different
databases, like cardinality, connectivity, etc.

Normalization
A large database defined as a single relation may result in data duplication. This repetition of data may
result in:

 Making relations very large.

 It isn't easy to maintain and update data as it would involve searching many records in relation.

 Wastage and poor utilization of disk space and resources.

 The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data into
smaller, simpler, and well-structured relations that are satisfy desirable properties. Normalization is a
process of decomposing the relations into relations with fewer attributes.

What is 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?

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.

21
By Zakir Hussain
Shakara Institute of Technology

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply
to individual relations. The relation is said to be in particular normal form if it satisfies
constraints.

Following are the various types of Normal forms:

Normal Description
Form

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.

22
By Zakir Hussain
Shakara Institute of Technology
Advantages of Normalization

 Normalization helps to minimize data redundancy.

 Greater overall database organization.

 Data consistency within the database.

 Much more flexible database design.

 Enforces the concept of relational integrity.

Disadvantages of Normalization

 You cannot start building the database before knowing what the user needs.

 The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF,
5NF.
 It is very time-consuming and difficult to normalize relations of a higher degree.

 Careless decomposition may lead to a bad database design, leading to serious problems.

Inclusion Dependency
 Multivalued dependency and join dependency can be used to guide database design although
they both are less common than functional dependencies.
 Inclusion dependencies are quite common. They typically show little influence on designing of
the database.
 The inclusion dependency is a statement in which some columns of a relation are contained in
other columns.
 The example of inclusion dependency is a foreign key. In one relation, the referring relation is
contained in the primary key column(s) of the referenced relation.
 Suppose we have two relations R and S which was obtained by translating two entity sets such
that every R entity is also an S entity.
 Inclusion dependency would be happen if projecting R on its key attributes yields a relation
that is contained in the relation obtained by projecting S on its key attributes.
 In inclusion dependency, we should not split groups of attributes that participate in an inclusion
dependency.
 In practice, most inclusion dependencies are key-based that is involved only keys.

23
By Zakir Hussain
Shakara Institute of Technology
Lossless-join decomposition:

Lossless-join decomposition is a process in which a relation is decomposed into two or more relations.
This property guarantees that the extra or less tuple generation problem does not occur and no
information is lost from the original relation during the decomposition. It is also known as non-additive
join decomposition.

When the sub relations combine again then the new relation must be the same as the original relation
was before decomposition.

Consider a relation R if we decomposed it into sub-parts relation R1 and relation R2.

The decomposition is lossless when it satisfies the following statement −

 If we union the sub Relation R1 and R2 then it must contain all the attributes that are available
in the original relation R before decomposition.
 Intersections of R1 and R2 cannot be Null. The sub relation must contain a common attribute.
The common attribute must contain unique data.

The common attribute must be a super key of sub relations either R1 or R2.

Here,

R = (A, B, C)

R1 = (A, B)

R2 = (B, C)

The relation R has three attributes A, B, and C. The relation R is decomposed into two relation R1 and
R2. . R1 and R2 both have 2-2 attributes.The common attributes are B.

The Value in Column B must be unique. if it contains a duplicate value then the Lossless-join
decomposition is not possible.

Draw a table of Relation R with Raw Data −

R (A, B, C)

A B C

24
By Zakir Hussain
Shakara Institute of Technology

A B C

12 25 34

10 36 09

12 42 30

It decomposes into the two sub relations −

R1 (A, B)

A B

12 25

10 36

12 42

R2 (B, C)

B C

25 34

36 09

42 30

Now, we can check the first condition for Lossless-join decomposition.

The union of sub relation R1 and R2 is the same as relation R.

25
By Zakir Hussain
Shakara Institute of Technology
R1U R2 = R

We get the following result −

A B C

12 25 34

10 36 09

12 42 30

The relation is the same as the original relation R. Hence, the above decomposition is Lossless-join
decomposition.

Dr Edgar F Codd (Codd`s Rules)

Dr E.F.Codd, also known to the world as the ‘Father of Database Management Systems’ had
propounded 12 rules which are in-fact 13 in number. The rules are numbered from zero to twelve.
According to him, a DBMS is fully relational if it abides by all his twelve rules. Till now, only few
databases abide by all the eleven rules. His twelve rules are fondly called ‘E.F.Codd’s Twelve
Commandments’. His brilliant and seminal research paper ‘A Relational Model of Data for Large
Shared Data Banks’ in its entirety is a visual treat to eyes.

Relational Database Management System

There is an unspoken rule in the jargon of Database Management Systems. As the databases that
implement all the E.F.Codd’s rules are scare, the unspoken rule has been gaining traction.

 If a management system or software follows any of 5-6 rules proposed by E.F.Codd, it qualifies
to be a Database Management System (DBMS).
 If a management system or software follows any of 7-9 rules proposed by E.F.Codd, it qualifies
to be a semi-Relational Database Management System (semi- RDBMS).
 If a management system or software follows 9-12 rules proposed by E.F. Codd, it qualifies to
be a complete Relational Database Management System (RDBMS).

26
By Zakir Hussain
Shakara Institute of Technology

Dr Edgar F Codd’s Twelve Commandments

Here is brief note on E.F Codd’s Twelve rules:

Rule 0 − Foundation rule

Any relational database management system that is propounded to be RDBMS or advocated to be a

RDBMS should be able to manage the stored data in its entirety through its relational capabilities.

Rule 1 − Rule of Information

Relational Databases should store the data in the form of relations. Tables are relations in Relational
Database Management Systems. Be it any user defined data or meta-data, it is important to store the
value as an entity in the table cells.

Rule 2 − Rule of Guaranteed Access

The use of pointers to access data logically is strictly forbidden. Every data entity which is atomic in
nature should be accessed logically by using a right combination of the name of table, primary key
represented by a specific row value and column name represented by attribute value.

Rule 3 − Rule of Systematic Null Value Support

Null values are completely supported in relational databases. They should be uniformly considered as
‘missing information’. Null values are independent of any data type. They should not be mistaken for
blanks or zeroes or empty strings. Null values can also be interpreted as ‘inapplicable data’ or
‘unknown information.’

Rule 4 − Rule of Active and online relational Catalog

In the Database Management Systems lexicon, ‘metadata’ is the data about the database or the data
about the data. The active online catalog that stores the metadata is called ‘Data dictionary’. The so
called data dictionary is accessible only by authored users who have the required privileges and the
query languages used for accessing the database should be used for accessing the data of data
dictionary.

Rule 5 − Rule of Comprehensive Data Sub-language

A single robust language should be able to define integrity constraints, views, data manipulations,
transactions and authorizations. If the database allows access to the aforementioned ones, it is violating
this rule.

Rule 6 − Rule of Updating Views

27
By Zakir Hussain
Shakara Institute of Technology
Views should reflect the updates of their respective base tables and vice versa. A view is a logical table
which shows restricted data. Views generally make the data readable but not modifiable. Views help in
data abstraction.

Rule 7 − Rule of Set level insertion, update and deletion

A single operation should be sufficient to retrieve, insert, update and delete the data.

Rule 8 − Rule of Physical Data Independence

Batch and end user operations are logically separated from physical storage and respective access
methods.

Rule 9 − Rule of Logical Data Independence

Batch and end users can change the database schema without having to recreate it or recreate the
applications built upon it.

Rule 10 − Rule of Integrity Independence

Integrity constraints should be available and stored as metadata in data dictionary and not in the
application programs.

Rule 11 − Rule of Distribution Independence

The Data Manipulation Language of the relational system should not be concerned about the
physical data storage and no alterations should be required if the physical data is centralized or
distributed.

Rule 12 − Rule of Non Subversion

Any row should obey the security and integrity constraints imposed. No special privileges are
applicable.

Almost all full scale DBMSs are RDMSs. Oracle implements 11+ rules and so does Sybase. SQL
Server also implements 11+ rules while FoxPro implements 7+ rules.

28