Module I

1
INTRODUCTION TO DBMS

Unit Structure
1.0 Objectives
1.1 Introduction to DBMS
1.2 Overview of DBMS
1.3 Advantages of DBMS
1.4 Levels of abstraction
1.5 Data independence
1.6 DBMS architecture

1.0 OBJECTIVES

After going through this unit, you will able to:

 To introduce the concept of the DBMS with respect to the
relational model
 Define database, DBMS, overview of DBMS, level of abstraction,
DBMS architecture
 Learning Data models and its different types
 designing the database schema with the use of appropriate data
types for storage of data in database
 To create, manipulate, query and back up the databases.

1.1 INTRODUCTION

A database is a collection of information that is organized so that it

can be easily accessed, managed and updated. Database systems are
designed to manage large bodies of information. Management of data
involves both defining structures for storage of information and providing
mechanisms for the manipulation of information. In addition, the database
system must ensure the safety of the information stored, despite system
crashes or attempts at unauthorized access. If data are to be shared among
several users, the system must avoid possible anomalous results. Because
information is so important in most organizations, computer scientists
have developed a large body of concepts and techniques for managing
data.

1
DATABASE MANAGEMENT SYSTEM
A database-management system (DBMS) is a collection of
interrelated data and a set of programs to access those data. This is a
collection of related data with an implicit meaning and hence is a
database.

1.2 OVERVIEW

The collection of data, usually referred to as the database,

contains information relevant to an enterprise. The primary goal of a
DBMS is to provide a way to store and retrieve database information that
is both convenient and efficient. By data, we mean known facts that can
be recorded and that have implicit meaning. For example, consider the
names, telephone numbers, and addresses of the people you know. You
may have recorded this data in an indexed address book, or you may
have stored it on a diskette, using a personal computer and software
such as DBASE IV or V, Microsoft ACCESS, or EXCEL. A datum – a unit of
data – is a symbol or a set of symbols which is used to represent
something. This relationship between symbols and what they represent is
the essence of what we mean by information. Hence, information is
interpreted data – data supplied with semantics. Knowledge refers to the
practical use of information. While information can be transported,
stored or shared without many difficulties the same cannot be said about
knowledge. Knowledge necessarily involves a personal experience.
Referring back to the scientific experiment, a third person reading the
results will have information about it, while the person who conducted
the experiment personally will have knowledge about it. The DBMS is a
general purpose software system that facilitates the process of defining
constructing and manipulating databases for various applications.

1.3 ADVANTAGES

Data Independence: Application programs should be as

independent as possible from details of data representation and storage.
The DBMS can provide an abstract view of the data to insulate
application code from such details.

Efficient Data Access: A DBMS utilizes a variety of sophisticated

techniques to store and retrieve data efficiently. This feature is especially
important if the data is stored on external storage devices.

Data Integrity and Security: If data is always accessed through the DBMS,
the DBMS can enforce integrity constraints on the data. For example,
before inserting salary information for an employee, the DBMS can check
that the department budget is not exceeded. Also, the DBMS can enforce

2
access controls that govern what data is visible to different classes of
users.

Concurrent Access and Crash Recovery: A database system allows

several users to access the database concurrently. Answering different
questions from different users with the same (base) data is a central
aspect of an information system. Such concurrent use of data increases
the economy of a system. An example for concurrent use is the travel
database of a bigger travel agency. The employees of different branches
can access the database concurrently and book journeys for their clients.
Each travel agent sees on his interface if there are still seats available for
a specific journey or if it is already fully booked. A DBMS also protects
data from failures such as power failures and crashes etc. by the recovery
schemes such as backup mechanisms and log files etc.

Data Administration:
When several users share the data, centralizing the administration
of data can offer significant improvements. Experienced professionals,
who understand the nature of the data being managed, and how
different groups of users use it, can be responsible for organizing the data
representation to minimize redundancy and fine-tuning the storage of
the data to make retrieval efficient.

Reduced Application Development Time:

DBMS supports many important functions that are common to
many applications accessing data stored in the DBMS. This, in
conjunction with the high-level interface to the data, facilitates quick
development of applications. Such applications are also likely to be more
robust than applications developed from scratch because many important
tasks are handled by the DBMS instead of being implemented by the
application.

1.4 TYPES OF USERS IN DBMS:

Database Administrator:
One of the main reasons for using DBMSs is to have central
control of both the data and the programs that access those data. A person
who has such central control over the system is called a database
administrator (DBA). DBA is responsible for authorizing access to the
database, coordinating and monitoring its use, and acquiring software and
hardware resources as needed.

Naive users:
Naive users are unsophisticated users who interact with the system
by invoking one of the application programs that have been written
previously. For example, a bank teller who needs to transfer $50 from
account A to account B invokes a program called transfer. This program
3
asks the teller for the amount of money to be transferred, the account from
which the money is to be transferred, and the account to which the money
is to be transferred.

Application programmers:
Application programmers are computer professionals who write
application programs. Application programmers can choose from many
tools to develop user interfaces. Rapid application development (RAD)
tools are tools that enable an application programmer to construct forms
and reports without writing a program.

Sophisticated users:
Sophisticated users interact with the system without writing
programs. Instead, they form their requests in a database query language.
They submit each such query to a query processor, whose function is to
break down DML statements into instructions that the storage manager
understands. Analysts who submit queries to explore data in the database
fall in this category.

Specialized users:
Specialized users are sophisticated userswho write specialized
database applications that do not fit into the traditional data-processing
framework.

1.5 LEVELS OF ABSTRACTION IN A DBMS:

Hiding certain details of how the data are stored and maintained. A
major purpose of database system is to provide users with an “Abstract
View” of the data. In DBMS there are 3 levels of data abstraction. The
goal of the abstraction in the DBMS is to separate the users request and
the physical storage of data in the database.

Physical Level:
 The lowest Level of Abstraction describes “How” the data are
actually stored.
 The physical level describes complex low level data structures in
detail.

Logical Level:
 This level of data Abstraction describes “What” data are to be
stored in the database and what relationships exist among those
data.
 Database Administrators use the logical level of abstraction.

4
View Level:
 It is the highest level of data Abstracts that describes only part of
entire database.
 Different users require different types of data elements from each
database.
 The system may provide many views for the some database.

Figure 1.1: Level of Abstraction

1.6 DATA INDEPENDENCE:

A very important advantage of using DBMS is that it offers Data

Independence. The ability to modify a scheme definition in one level
without affecting a scheme definition in a higher level is called data
independence.

There are two types:

1. Physical Data Independence
2. Logical Data Independence

Physical Data Independence:

 The ability to modify the physical schema without causing
application programs to be rewritten.
 Modifications at this level are usually to improve performance.

Logical Data Independence:

 The ability to modify the conceptual schema without causing
application programs to be rewritten
 Usually done when logical structure of database is altered

5
  Logical data independence is harder to achieve as the application
programs are usually heavily dependent on the logical structure of
the data.

1.7 DBMS ARCHITECTURE

A database system is partitioned into modules that deal with each

of the responsibilities of the overall system. The functional components of
a database system can be broadly divided into the storage manager and the
query processor components.

The storage manager is important because databases typically

require a large amount of storage space. Some Big organizations Database
ranges from Giga bytes to Terabytes. So the main memory of computers
cannot store this much information, the information is stored on disks.
Data are moved between disk storage and main memory as needed. The
query processor also very important because it helps the database system
simplify and facilitate access to data. So quick processing of updates and
queries is important. It is the job of the database system to translate
updates and queries written in a nonprocedural language.

6
 Figure 1.2: Database Architecture

Storage Manager:
A storage manager is a program module that provides the interface
between the low level data stored in the database and the application
programs and queries submitted to the system. The storage manager is
responsible for the interaction with the file manager. The storage manager
translates the various DML statements into low-level file-system
commands. Thus, the storage manager is responsible for storing,
retrieving, and updating data in the database.

Storage Manager Components:

Authorization and integrity manager: It tests for the satisfaction of
integrity constraints and checks theauthority of users to access data.
Transaction manager which ensures that the database itself remains in a
consistent state despite systemfailures, and that concurrent transaction
executions proceed without conflicting.

7
File manager: which manages the allocation of space on disk storage and
the data structures used to representing information stored on disk.
Buffer manager:It is responsible for fetching data from disk storage into
main memory. Storage managerimplements several data structures as part
of the physical system implementation. Data files are used to store the
database itself. Data dictionary is used to stores metadata about the
structure of the database, in particular the schema of the database.

Query Processor Components:

DDL interpreter:It interprets DDL statements and records the definitions
in the data dictionary.
DML compiler: It translates DML statements in a query language into an
evaluation plan consisting of low-level instructions that the query
evaluation engine understands.
Query evaluation engine:It executes low-level instructions generated by
the DML compiler.
Application Architectures:
Most users of a database system today are not present at the site of
the database system, but connect to it through a network. We can therefore
differentiate between client machines, on which remote database users’
work, and server machines, on which the database system runs.



8
 2
DATA MODELS
Unit Structure
2.0 Introduction
2.1 Types Of Data Models

2.0 INTRODUCTION

Data models define how the logical structure of a database is

modelled. Data Models are fundamental entities to introduce abstraction
in a DBMS. Data models define how data is connected to each other and
how they are processed and stored inside the system. The very first data
model could be flat data-models, where all the data used are to be kept in
the same plane. Earlier data models were not so scientific, hence they
were prone to introduce lots of duplication and update anomalies. The
following models are

2.1 TYPES OF DATA MODELS:

An Object Based Logical Model also known as conceptual

data model which provides representation according to the way many
users perceive data. Most popular conceptual data model is Entity
Relationship Model which is based on the concepts of entity, relationship
and attributes. Object based logical models provide flexible structuring
capabilities and allow data constraints to be specified explicitly.

2.1.1 Entity Relationship Model

The ER (Entity relationship) based on the collection of basic
objects, called entities and relationships among these objects. The
diagrammatic notation associated with ER model, are also known as ER
diagrams. The ER model employs three basic concepts entity sets
relationship sets and attributes. An entity is an object in the real world
that is distinguishable from all other objects. An entity set is a set of
entities of the same type that share the same properties or attributes.
Attributes are descriptive properties possessed by all members of an
entity set.

2.1.2 Object Oriented Model

The object oriented data model is an adaptation of the object
oriented programming language paradigm to database systems. The model
is based on the concept of encapsulating data and code that operates on
that data in an object. Entities in the sense of the ER model are
9
represented as objects with attributes values represented by instance
variables within the object. The values stored in an instance variable are
itself an object. Thus a containment relationship i.e. is-part-of relationship
is established among objects.

2.1.3 Physical data model

This model provides details of how data is stored on the computer
storage media and meant for software specialist. This model hides many
details of data storage on disk but can be implemented on a computer
system directly. I is used in traditional commercial DBMS and based on
the concepts of record structure with fixed format; hence it is also known
as record based data model. The use of fixed length records simply the
physical implementation of the database. The relational model is a
primary data model in commercial data processing application.

2.1.4 Relational data model

This model uses a collection of tables to represent both data and
the relationship among data. Tables are known as relations in relational
database. Each relation consists of multiple columns and each column has
unique name. This table has one column for each domain and one row for
each tuple. Each column has a unique name which is called as attribute of
the relation. The set of attributes are called as relation schema.

2.1.5 Network data model

The network model allows more general connections among the
nodes. Network model has the ability to handle many –to – much
relationship. The network data model is an abstraction of the design
concepts used in the implementation of database.

2.1.6 Hierarchical data model

Data is sorted hierarchically in a tree like structure using parent
child relationship, either in top down or bottom up approach. This model
uses pointers to navigate between stored data using hierarchical tree.
Based on one to many relation.

2.1.7 Client server architecture:

The client/server architecture was developed to deal with
computing environment in which a large number of PCs, workstations, file
servers, printers, data base servers, Web servers, e-mail servers, and other
software and equipment are connected via a network. The idea is to define
specialized servers with specific functionalities. For example, it is possible
to connect a number of PCs or small workstations as clients to a file server
that maintains the files of the client machines. Another machine can be
designated as a printer server by being connected to various printers; all
print requests by the clients are forwarded to this machine. Webservers or
e-mail servers also fall into the specialized server category. The resources
provided by specialized servers can be accessed by many client machines.
The clientmachines provide the user with the appropriate interfaces to
utilize these servers, as well as with local processing power to run local
10
applications. This concept can be carried over to other software packages,
with specialized programs such as a CAD(computer-aided design)
package being stored on specific server machines and being made
accessible to multiple clients. Some machines would be client sites only
(for example, diskless workstations or workstations or PCs with disks that
have only client software installed).

Figure 2.1: Client/Server architecture



11
 3
ENTITY RELATIONSHIP MODEL
Unit Structure
3.0 Introduction
3.1 Types of Attributes

3.0 INTRODUCTION

Entity-Relationship Model or simply ER Model is a high-level data

model diagram. In this model, we represent the real-world problem in the
pictorial form to make it easy for the stakeholders to understand. It is also
very easy for the developers to understand the system by just looking at
the ER diagram. We use the ER diagram as a visual tool to represent an
ER Model. ER diagram has the following three components:

 Entities: Entity is a real-world thing. It can be a person, place, or even

a concept. Example: Teachers, Students, Course, Building,
Department, etc are some of the entities of a School Management
System.
 Attributes: An entity contains a real-world property called attribute.
This is the characteristics of that attribute. Example: The entity teacher
has the property like teacher id, salary, age, etc.
 Relationship: Relationship tells how two attributes are
related. Example: Teacher works for a department.
 Relationship set: A relationship set is a set of relationships of the
same type. Formally it is a mathematical relation on (possibly non-
distinct) sets. If are entity sets, then a relationship set R is a subset of
Where is a relationship. For example, consider the two
entity sets customer and account.
 Key Constraints: All the values of primary key must be unique. The
value of primary key must not be null.
 Participation Constraints: We can capture participation constraints
involving one entity set in a binary relationship, but little else (without
resorting to CHECK constraints)
 Weak entities: In a relational database, a weak entity is an entity that
cannot be uniquely identified by its attributes alone; therefore, it must
use a foreign key in conjunction with its attributes to create a primary
key.

12
 Aggregation: In aggregation, the relation between two entities is
treated as a single entity. In aggregation, relationship with its
corresponding entities is aggregated into a higher level entity.

3.1 TYPES OF ATTRIBUTES:

1. Simple Attributes
Simple attributes are atomic attributes with independence meaning
which cannot be further divided. For example employee’s phone is an
atomic attribute.

2. Composite Attributes:
Composite are made up of more than one attributes. It can divide
into smaller subparts, which represent more basic attributes with
independent meanings. They sometimes form a hierarchy. The value of a
composite attribute is the combination of the values of its components
atomics attributes. For example, the Address attribute of the EMPLOYEE
entity can be subdivided into Street_address, City, State and Zip.
Composite attributes are useful to model situations in which a user
sometimes refers to the composite attribute as a unit but at other times
refers specifically to its components.

Figure 3.1: Composite attribute

Single valued attributes

Single valued attributes consist of individual or single value for a
particular entity. For example Employee id attributes refers to only one
employee ID. Age attribute for a person. There may be instances where an
attribute has a set of values for a specific entity. Suppose we add to the
instructor entity set.

Multivalued Attributes:
Multi valued attributes has a group of values for a specific entity.
Multi valued attributes comes with upper and lower limits the number of
values to be specified for an entity. For example an employee may have
more than one phone number.

13
Stored Attributes: Stored attributes consist of attributes that are fetched
directly from the entity. For Date_of_Birth
Derived Attributes: Data that is derived using the data stored in the
stored attributes set are known as Derived attributes. For example Age can
be calculated using the stored date_of _birth attribute.
Entity type: an entity type defines a collection or set of entities that have
the same attributes. Each entity type in the database is described by its
name and attributes. For example a college may want to store similar
information concerning each of the students. Students can be entity types
that share the same attributes, but each entity has its own values for each
attribute.
Entity Set: The collection of all entities of a particular entity type in the
database at any point in time is called an entity set. The entity set is
usually referred to using the same name as the entity type. For example
student refers to both a type of entity as well as the current set of all
student entities in the database.
Relationship: An association among several entities is known as
Relationship.

RELATIONSHIP

Figure 3.2: Relationship

Relationship set:
A relationship set is a set of relationship of the same type. It is a
mathematical relation on n>=2 entity sets. Diamonds represents the relationship
sets. If E1,E2,E3…….En are entity set then a relationship set R is a subset
of {(e1,e2….en) | e1 ∈E1, e2 ∈E2…..en∈En where (e1,e2,……en) is a
relationship.

Figure 3.3: Relationship set

Degree of relationship type

The degree of a relationship type is the number of participating
entity types. A relationship type of degree two is called binary which are
the most common one. A relationship type of degree three is called
ternary. Higher degree relationships are more complex. Relationship in
14
databases is often binary. Some relationships that appear to be non-binary
could actually be better represented by several binary relationships.

For example one could create a ternary relationship parent, relating a child
to his mother and father, such a relationship could also be represented by
two binary relationships, mother and father relating a child to his mother
and father separately. Using the two relationships mother and father
provides us a record of a child’s mother, even if we are not aware of the
father’s identity; a null value would be required if the ternary relationship
parent is used. Using binary relationship sets is preferable in this case. For
simplicity purpose it is always possible to replace a non-binary (n-ary, for
n>2) relationship set by a number of distinct binary relationship sets.

Mapping Cardinality
The relationship set are of one to one, one to many, many to one or many
to many. To distinguish among these types, either a directed line ( ) or
an undirected line ( )between the relationship set and the entity set is
drawn.
 One to one:
A directed line is drawn from the relationship set advisor to both
entity sets instructor and student. This indicates that an instructor
may advise at most one student, and a student may have at most
one advisor.
 One to many:
A directed line is drawn from the relationship set advisor to the
entry set instructor and an undirected line to the entity set student.
This indicates that an instructor may advise many students, but a
student may have at most one advisor.
 Many to one:
An undirected line drawn from the relationship set advisor to the
entity set instructor and a directed line to the entity set student.
This indicates that an instructor may advise at most one student,
but a student may have many advisors.
 Many to many:
an undirected line drawn from the relationship set advisor to both
entity sets instructor and student. This indicates that an instructor
may advise many students, and a student may have many advisors.

15
 Figure 3.4: mapping cardinalities

One to One One to many

Many to one Many to many

Key constraint:
A Key or uniqueness constraint on the attributes of entities helps to
identify relationship uniquely, and thus distinguish relationship from each
other. No two entities are allowed to have exactly the same.

Specialization
Specialization is a process of creating sub parts of an entity type.
Generalization is a bottom-up approach, while Specialization is a top-
down approach. One higher level entity can be broken down into two
lower level entities by specialisation. The term "specialization" refers to a
subset of an entity set that shares certain common characteristics.
Normally, the superclass is described first, followed by the subclass and

16
its related attributes, and finally the relationship set. For example, In an
Employee management system, EMPLOYEE entity can be specialized as
TESTER or DEVELOPER based on what role they play in the company.

Figure 3.5: Specialization

Generalization
It’s a reverse process of abstraction, where in the difference
amongst the entity sets are suppressed and they are generalized together
into a single entity type. Distinctions are made explicitly in case of
generalization with top down approach. Commonality is defined using
generalization and expressed using containment relationship. It creates a
relationship between a higher level entities set to successive hierarch of
subclass entity set. The design process may also proceed in a bottom up
manner, in which multiple entities sets are synthesized into a higher level
entity set on the basis of common features. For example, Faculty and
Student entities can be generalized and create a higher level entity Person.

17
 Figure 3.6: Generalization

AGGREGATION
In aggregation, the relation between two entities is treated as a
single entity. In aggregation, relationship with its corresponding entities is
aggregated into a higher level entity. For example, Center entity offers the
Course entity act as a single entity in the relationship which is in a
relationship with another entity visitor. In the real world, if a visitor visits
a coaching center then he will never enquiry about the Course only or just
about the Center instead he will ask the enquiry about both.

Figure 3.7: Aggregation

18
Summary of Notation in ER diagram

Figure3.8 : summary of ER notation

Entity Vs Attributes
While identifying the attributes of an entity set, it is sometimes not
clear whether a property should be modelled as an attribute or as an entity
set (and related to the first entity set using a relationship set). For example,
consider adding address information to the Employees entity set. One
option is to use an attribute address. This option is appropriate if we need
to record only one address per employee, and it suffices to think of an
address as a string. An alternative is to create an entity set called
Addresses and to record associations between employees and addresses
using a relationship.

19
Entity vs Relationship
The nature of ER modelling can thus make it difficult to recognize
underlying entities, and we might associate attributes with relationships
rather than the appropriate entities. In general, such mistakes lead to
redundant storage of the same information and can cause many problems.



20
 4
RELATIONAL DATA MODEL
Unit Structure
4.0 Introduction
4.1 Relation
4.2 Attribute Types
4.3 Domain
4.4 Properties of Relations
4.5 Relational Model Notation
4.6 Characteristics of Relation

4.0 INTRODUCTION

In this chapter, we will study the concepts of relation, tuples and

attributes. We will further look at the meaning of the term integrity and the
various integrity constraints. The relational model is very simple and
elegant: a database is a collection of one or more relations, where each
relation is a table with rows and columns. This simple tabular
representation enables even novice users to understand the contents of a
database, and it permits the use of simple, high-level languages to query
the data. The major advantages of the relational model over the older data
models are its simple data representation and the ease with which even
complex queries can be expressed.

4.1 RELATION

A relation is a set of tuples. A database is a collection of relations.

A relation is a mathematical entity corresponding to a table. Each row in a
table represents a fact that corresponds to and entity or a relationship that
exists. Each row is called a tuple. Formally, the column headings of the
table are the attributes of a relation.

21
 attributes
(or columns)

tuples
(or rows)

Figure 4.1: Relation

4.2 ATTRIBUTE TYPES

The set of allowed values for each attribute is called the domain of
the attribute. Attribute values are (normally) required to be atomic; that is,
indivisible. The special value null is a member of every domain, indicated
that the value is “unknown”. The null value causes complications in the
definition of many operations. An attribute or a combination of attributes
that is used to identify the records uniquely is known as super key.
Candidate key is defined as minimal super key or irreducible super key;
used to identify the records uniquely. A candidate key that is used by the
database designer for unique identification of each row in a table is known
as primary key. A primary Key can consist of one or more attributes of a
table, known as composite key. The candidate key not chosen by database
designer as a primary key is known as alternate key. A foreign key is an
attribute or combination of attribute in one table that points to the primary
key of another table.

4.3 DOMAIN

A relation is subset of Cartesian product of a list of domains. A

table with n attributes must be subset of D1 * D2 * D3 * …… * Dn. A
domain can be Atomic or Non-Atomic. Atomic Domains are indivisible.
Non-Atomic Domain contains composite values.

22
4.4 PROPERTIES OF RELATIONS

o Name of the relation is distinct from all other relations.

o Each relation cell contains exactly one atomic (single) value
o Each attribute contains a distinct name
o Attribute domain has no significance
o tuple has no duplicate value
o Order of tuple can have a different sequence

4.5 RELATIONAL MODEL NOTATION

Following notations used in Relational model

1. A relation schema R of degree n is denoted by R(A1,A2,…..An).
2. The uppercase letters Q, R and S denote relation names.
3. The lowercase letters q, r and s denote relation states.
4. The letters t, u and v denote tuples.
5. In general, the name of a relation schema such as EMPLOYEE also
indicates the current set of tuples in that relation- the current relation
state whereas EMPLOYEES (Eid,Ename,…) refers only to the relation
schema.
6. An attribute can be qualified with the relation name R to which it
belongs by using the dot notation R.A. For example
‘EMPLOYEE.Eid’ or ‘EMPLOYEE. Ename’. all attribute name in a
particular relation must be distinct.

4.6 CHARACTERISTICS OF RELATION

Following are some of the characteristics of relation.

1. Ordering of tuples in a relation:
a) A relation is defined as a set of tuples. Mathematically elements of a set
have no order among them hence tuples in a relation do not have any
particular order.
b) However, in a file, records are physically stored on disk or in memory,
so there always is an order among the records.
c) When we display a relation as a table the rows are displayed in a certain
order.

2. Ordering of values within a tuple and an alternative definition of a

relation:
a) according to the preceding definition of a relation, an n-tuple is an
ordered list of a n values, so the ordering of values in a tuple and hence
of attributes in a relation schema is important.

23
b) however, at a more abstract level, the order of attributes and their
values is not that important as long as the correspondence between
attributes and values is maintained.

3. Values and NULLs in the tuples:

a) Each value in a tuple is an atomic value that is; it is not divisible into
components within the framework of the basic relational model. Hence
composite and multivalued attributes are not allowed.
b) This model is sometimes called the flat relational model.
c) Much of the theory behind the relational model was developed with this
assumption in mind, which is called the first normal form assumption.
d) Hence multivalued attributes must be represented by separated
relations, and composite attributes are represented only by their simple
component attributes in the basic relational model.

RELATIONAL CONSTRAINTS
The meaning of constraint is Restriction. There are generally many
restrictions or constraints on the actual values in a database state.
Constraints on databases can generally be divided into five main
categories.
1. Domain constraint
2. Tuple Uniqueness constraint
3. Key constraint
4. Entity Integrity constraint
5. Referential Integrity constraint

1. Domain Constraint
Domain constraint defines the domain or set of values for an
attribute. It specifies that the value taken by the attribute must be the
atomic value from its domain. 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.

2. Tuple Uniqueness constraint

Tuple Uniqueness constraint specifies that all the tuples must be
necessarily unique in any relation.

3. Key constraint
Key constraint specifies that in any relation-All the values of
primary key must be unique. The value of primary key must not be null.

4. Entity Integrity constraint

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.
24
5. Referential Integrity constraint
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.

Figure 4.2: Referential integrity



25
 5
ER TO TABLE

Unit Structure
5.1 Rules for converting ER to Table

5.1 RULES FOR CONVERTING ER TO TABLE

1. Convert all the entities in the diagram to tables.

2. All single valued attributes of an entity is converted to a column of the
table.
3. Key attribute in the ER diagram becomes the primary key of the table.
Declare the foreign key column, if applicable.
4. any multi valued attributes are converted into new table.
5. any composite attributes are merged into same table as different
columns. Derived attributes can be ignored.

For example:

Figure 5.1: ER diagram

26
 There are the following steps which need to be considered before
developing table:

Entity type becomes a table:

a) In the given ER diagram, LECTURE, STUDENT, SUBJECT and
COURSE forms individual tables.

All single-valued attribute becomes a column for the table:

a) In the STUDENT entity, STUDENT_NAME and STUDENT_ID from
the column of STUDENT table.
b) Similarly COURSE_NAME and COURSE_ID form the column of
COURSE table and so on.

A key attribute of the entity type represented by the primary key:

a) In the given ER diagram, COURSE_ID, STUDENT_ID,SUBJECT_ID
and LECTURE_ID are the key attribute of the entity.

The multivalued attribute is represented by a separate table:

a) In the student table, a hobby is a multivalued attribute. So it is not
possible to represent multiple values in a single column of STUDENT
table.
b) Hence we create a table STUD_HOBBY with column name
STUDENT_ID and HOBBY. Using both the column, we create a
composite key.

Composite attribute represented by components:

a) In the given ER diagram student address is a composite attribute. It
contains CITY, PIN, DOOR#, STREET and STATE. In the
STUDENT table, these attributes can merge as an individual column.

Derived attributes are not considered in the table:

a) In the STUDENT table, age is the derived attribute. It can be calculated
at any point of time by calculating the difference between current date
and Date of Birth.

27
Figure 5.2: table structure of given ER diagram



28