Database

Concepts
In This Chapter

.
DATABASE CONCEPTS

O
11.1 Introduction
11.1 INTRODUCTION

C
11.2 Purpose of Databases

A database system is basically a computer 11.3 Database Abstraction

&
based record keeping system. The collection of 11.4 Different Data Models
data, usually referred to as the database, contains
AI
11.5 The Relational Model
information about one particular enterprise. It 11.6 Comparison of Data Models
R
maintains any information that may be necessary to the
decision-making processes involved in the management
of that organization.
T
PA

A database may also be defined as a collection of inter-

related data stored together to serve multiple applications ; the
data is such stored so that it is independent of programs which
AN

use the data ; a common and con-

Database A database is a trolled approach is used in adding
collection of interrelated data new data and in modifying and retrie-
H

and a database system is ving existing data within the database.

basically a computer based
D

The data is structured so as to provide a

record keeping system.
foundation for future application development.
The intention of a database is that the same collection of data should serve
as many applications as possible. Hence, a database is often conceived of as the
repository of information needed for running certain functions in a corporation or
organization. Such a database would permit not only the retrieval of data but also the
continuous modification of data needed for control of operations. It may be possible to
search the database to obtain answers to queries or information for planning purposes.

11.2 PURPOSE OF DATABASES

A database system should be a repository of the data needed for an organization’s data
processing. That data should be accurate, private, and protected from damage. It should be
organized so that diverse applications with different data requirements can employ the data.
Different application programmers and different end users will have different views of the data
which must be derived from a common overall data structure. Their methods of accessing or searching
the data will differ.
(11.1)
11.2 Support Material

The ways in which end users want to utilize existing data will constantly change, and
in some cases demands for new uses of the data will arise rapidly and urgently. The extent
to which these demands can be satisfied determines the overall value of the database
system.
In a typical file-processing system, permanent records are stored in various files. A
number of different application programs are written to extract records from and add
records to the appropriate files. But this scheme has a number of major limitations and
disadvantages, such as data redundancy (duplication of data), data inconsistency,
unsharable data, unstandardized data, insecure data, incorrect data etc. A database
management system is answer to all these problems as it provides a centralized control of the
data.
Let us consider some of the advantages provided by the database systems and see
how a database system overcomes the above mentioned problems.

.
1. Databases reduce the data redundancy to a large extent

O
Data redundancy means duplication of data. Non-database systems maintain

C
separate copy of data for each application. For example, in a college, student records are
maintained and the hostel also maintains records of all those students who live in hostel.

&
Though the records for hosteler students are already being maintained by
Data Redundancy Duplication the college, the hostel keeps a separate copy of it. As far as everything
AI
of data is known as Data goes well, the above mentioned file processing system works well. But
Redundancy.
R
duplication of data may lead to inconsistency or incorrect data at times.
Let us see how.
T

Suppose the permanent address of a hosteler gets changed. The hosteler student
PA

informs about it to the hostel authorities and the changed address is reflected in the
student’s record. But see there is a huge bug here. The data for the same student has not
been changed in the college records. If the student forgets to inform the college authorities
AN

or college people themselves forget to update the student’s record, the same student’s
record differs in two different files. Now, which record of the two would be considered
accurate? Say, the college has to send certain information by post. Then, what will happen?
H

The wrong data will get processed. The only way out is that every file that is maintaining
D

the same record should be updated. But because of some human errors, certain files may
be left out and this will lead to inconsistent
data. The other problem of redundancy is
Database
system DBMS unnecessary wastage of storage space.
Application The database systems do not maintain
program 1 Database separate copies of the same data. Rather, all
User 1
the data are kept at one place and all the
Application applications that require data refer to the
program 2
User 2 centrally maintained database (collection of
data). (See Fig. 11.1). Now, if any change is
Application
program 3 to be made to data, it will be made at just
one place and the same changed infor-
User 3
Application mation will be available to all the appli-
program 4
cations referring to it. Thus redun- dancy
gets controlled and so are the problems
associated with it.
Fig. 11.1 Centrally Controlled Database System.
UNIT 2-1 : Data Management
11.3
DATABASE CONCEPTS

We do not mean to suggest that all redundancy should necessarily be eliminated.

Sometimes there are sound business or technical reasons for maintaining multiple copies
of the same data. In a data base system, however, redundancy can be controlled upto the
desired extent and the system is aware of the redundancy, if any and assumes the
responsibility for propagating updates.

2. Databases can control data inconsistency to a large extent

This is really a corollary of the previous point. When the redundancy is not
controlled, there may be occasions on which the two entries about the same data do not
agree (that is, when one of them stores the updated information and the other does not). At
such times, database is said to be inconsistent. Obviously, an inconsistent database will
provide incorrect or conflicting information.
By controlling redundancy, the inconsistency is also controlled. Even if there is some
redundancy retained in the database due to some technical reasons, the database

.
management system ensures that any change made to either of the two entries is

O
automatically made to the other. This process is known as propagating updates.

C
3. Databases facilitate sharing of data
Sharing of data means that individual pieces of data in the database may be shared

&
among several different users, in the sense that each of those users may have access to the
AI
same piece of data and each of them may use it for different purposes.
The database management system makes sure that not only the existing applications
R

can share the data in the database, but also that new applications can be developed to
operate against that same stored data. Or it can be said that the data requirements of new
T

applications may be satisfied without having to create any new stored files.
PA

4. Databases enforce standards

The database management systems can ensure that all the data (that is stored
AN

centrally) follow the applicable standards. There may be certain standards laid by the
company or organization using the database. Or there may be certain industry standards
that must be satisfied by the data. Similarly, there may be national or international
H

standards. Standardizing stored data formats is particularly desirable as an aid to data

D

interchange or migration between systems.

5. Databases can ensure data security

Data Security Data Security refers
to protection of data against The information stored inside a database is sometimes of
accidental or intentional disclosure great value to a corporation. Therefore, it must be kept secure and
to unauthorized persons, or unautho- private.
rized modification or destruction.
A database management system ensures data security and
privacy by ensuring that the only means of access to the database is
through the proper channel and also by carrying out authorization
checks whenever access to sensitive data is attempted.
Privacy of Data Privacy of Data
refers to the rights of individuals 6. Integrity can be maintained through databases
and organizations to determine for By integrated database we mean unification of several otherwise
themselves when, how, and to what distinct data files, with any redundancy among those files partially
extent information about them is to or wholly eliminated. When a database contains data employed by
be transmitted to others. many different users it is important that the data items and
11.4 Support Material

associations between data items not be destroyed. Hardware failures and various types of
accidents will occur occasionally. The storage of data and its updation, and insertion
procedures, defined by the database, are such that the system can (easily) recover from
these circumstances without harm to the data.
In addition to protecting data from systems problems, the database management
system designs certain integrity checks to ensure that data values confirm to certain
specified rules. For example, a date cannot be like 25/25/12 ; it is invalid date. Or say the
number of days-worked for an employee cannot exceed the number of working days in a
month. Therefore, a database management system defines integrity checks like range checks
to check for the values that must lie within certain range of values ; and value matching to
check for the values that must be present already. (e.g., while preparing pay slips, the
employee-number must be a valid one i.e., it must be present in the employee-master).
With the above mentioned advantages, the purpose of databases is very obvious.
That is, databases are preferred so as to ensure consistent, sharable, standardized, integrated

.
and secure data, managed effectively so as to serve the requirements of the organization

O
owning it.

C
11.3 DATABASE ABSTRACTION

&
As we know that a collection of interrelated files and a set of programs that allow
AI
users to access and modify these files is known as a database management system. A major
purpose of a database system is to provide the users only that much information that is
R

required by them. This means that the system does not disclose all the details of data,
rather it hides certain details of how the data is stored and maintained. A good database
T

system ensures easy, smooth and efficient data structures in such a way so that every type
PA

of database user : end user, application system analyst, and physical storage system analyst, is
able to access its desired information efficiently. (An end user is a person who is not a
computer-trained person but uses the database to retrieve some information. For example,
AN

in a bank database, a customer, who wants to know how much balance remains in his
account, is an end-user. An application system analyst is the one who is concerned about all of

?
H

the database of logical level i.e., what all data constitute

1. What is the function of a database the database, what are the relationships between the
D

management system ? data-entities etc. without considering the physical

2. What is data redundancy ? What are the implementation details. The third type of user, the physical
problems associated with it ? storage system analyst is concerned with the physical
3. How do database management systems implementation details of the database i.e., how would the
overcome the problems associated with database be stored on which storage device ? What will be
data redundancy ? the starting address of the database ? What will be the
4. How do database management systems storage technique ? etc. etc.)
ensure data security and privacy ?
Since the requirements of different users differ from one
5. Define the following terms :
another, the complexity of the database is hidden from
(i) integrated database them, if needed, through several levels of abstraction is
(ii) shared database order to simplify their interaction with the system. The
(iii) view various levels of database implementation are being
(iv) database system discussed in the following section.
(v) data security
(vi) data integrity.
UNIT 2-1 : Data Management
11.5
DATABASE CONCEPTS

11.3.1 Various Levels of Database Implementation

A database is implemented through three general levels : internal, conceptual and
external so as cater to the needs of its users.

1. Internal Level (Physical Level)

The lowest level of abstraction, the internal level, is the one closest to physical storage.
This level is also sometimes termed as physical level. It describes how the data are actually
stored on the storage medium. At this level, complex low-level data structures are
described in details.

2. Conceptual Level
This level of abstraction describes what data are actually stored in the database. It also
describes the relationships existing among data. At this level, the database is described
logically in terms of simple data-structures. The users of this level are not concerned with

.
how these logical data structures will be implemented at the physical level. Rather, they

O
just are concerned about what information is to be kept in the database.

C
3. External Level (View Level)

&
This is the level closest to the users and is concerned with the way in which the data
are viewed by individual users. Most of the users of the database are not concerned with
AI
all the information contained in the database. Instead, they need only a part of the database
relevant to them. For example, even though the bank database stores a lot many
R

information, an account holder (a user) is interested only in his account details and not with
the rest of the information stored in the database. To simply such users’ interaction with
T

the system, this level of abstraction is defined. The system, thus provides many views for
PA

the same database. The following Fig. 11.2 illustrates the interrelationship among these
three levels of abstraction.
AN

Sales Officer Purchase Officer

H

View 1 View 2 External Level

External View 1 View 2 View 3 Item-Name Item-Name
D

Level (Individual User Views)

Price Reorder Quantity

(Application Programs are

used to fetch the desired
Conceptual information)
Level
Conceptual
Item-Number Character (6)
Item-Name Character (20)
Physical Price Numeric (5 + 2)
Level Reorder-quantity Numeric (4)

Internal
Stored-Item Length = 40
Item # Type = Byte(6), offset = 0, Index = Ix
Name Type = Byte(20), offset = 6,
Price Type = Byte(8), offset = 26,
ROQ Type = Byte(4), offset = 34,

Fig. 11.2 The Three Levels of Data Abstraction.

11.6 Support Material

11.3.2 Concept of Data Independence

As a database may be viewed through three levels of abstraction, any
Data Independence The ability
change at a level may affect other levels’ schemes. Since the databases
to modify a scheme definition in
one level without affecting a keep on growing, there may be frequent changes at times. This should
scheme definition in the next not lead to the redesigning and reimplementation of the database. The
higher level is called Data concept of data independence proves beneficial in such a context.
Independence.
There are two levels of data independence : physical and logical.

1. Physical Data Independence

Physical Data Independence refers to the ability to modify the scheme followed at the
physical level without affecting the scheme followed at the conceptual level. That is, the
application programs remain the same even though the scheme at physical level gets
modified. Modifications at the physical level are occasionally necessary in order to

.
improve performance of the system.

O
C
2. Logical Data Independence
Logical Data Independence refers to the ability to modify the conceptual scheme without

&
causing any changes in the schemes followed at view levels. The logical data independence
ensures that the application programs remain the same. Modi- fications at the conceptual
AI
level are necessary whenever logical structures of the database get altered because of some
unavoidable reasons. (For example, the introduction of paternity leave in the employee
R

database for the first time.)

It is more difficult to achieve logical data independence than the physical data
T

independence. The reason being that the application programs are heavily dependent on
PA

the logical structure of the database.

The abstract data types in modern programming languages implement the concept of
AN

data independence to a large extent. Both hide implementation details from the users. This
allows users to concentrate on the general structure rather than low-level implementation
details.
H

11.4 DIFFERENT DATA MODELS

D

The external level and conceptual level use certain data structures to help utilize the
database efficiently. How are these data structures decided ? What data structures and
associated operators should the system support ? The answer to this very crucial question
depends upon the approach or model being used for database management. The three data
models that are used for database management are :
m Relational data model m Hierarchical data model
m Network data model

?
11.4.1 The Relational Data Model
1. What are the three levels of data ab- In relational data model, the data is organized into tables
straction ? (i.e., rows and columns). These tables are called relations.
2. What do you understand by data inde- A row in a table represents a relationship among a set of
pendence ? values. Since a table is a collection of such relationships, it is
3. What are two types of data inde- generally referred to using the mathematical term relation,
pendence ? How are they different ? from which the relational data model derives its name.
UNIT 2-1 : Data Management
11.7
DATABASE CONCEPTS

Let us see how a sample database can be represented in relational form. The sample
database being shown here has three tables (relations) : Suppliers, Items, Shipments.

Suppliers (Supp#, Supp-Name, Status, City)

Items (Item#, Item-Name, Price)
Shipments (Supp#, Item#, Qty-supplied) [coloured fields represent primary keys1]

Suppliers
Supp# Supp-Name Status City
S1 Britannia 10 Delhi
S2 New Bakers 30 Mumbai
S3 Mother Dairy 10 Delhi
S4 Cookz 50 Bangalore
S5 Haldiram 40 Jaipur

.
O
Items Shipments

C
Item# Item-Name Price Supp# Item# Qty-supplied
I1 Milk 15.00 S1 I2 10

&
I2 Cake 5.00 S1 I3 20
I3 Bread 9.00 S1 I6 20
AI
I4 Milk Bread 14.00 S2 I4 20
R
I5 Plain Biscuit 6.00 S2 I5 10
I6 Cream Biscuit 10.00 S3 I1 10
T

I7 Ice Cream 16.00 S3 I7 10

PA

I8 Cold Drink 8.00 S4 I8 30

I9 Namkeen 15.00 S5 I9 30
AN

Fig. 11.3 Sample Database in Relational Form.

H

Notice, that here each supplier has a unique supplier number, exactly one name, status
value and location. Likewise, we assume that each item has a unique item number, name,
D

price ; and also that, at any given time, no more than one shipment exists for a given
supplier/item combination.
Each of these three tables closely resembles a conventional sequential file, with rows
of the table corresponding to records of the file and columns corresponding to fields of the
records. Each of these tables is actually a special case of the construct known in
mathematics as a relation. (More about relations and their characteristics, we shall learn
under section 11.5.1).
Rows of relations are generally referred to as tuples and the columns are usually
referred to as attributes.
The relational data model is based on a collection of tables (relations). The user of the
(relational) database system may query these tables, insert new tuples, delete tuples, and
modify tuples. There are several languages for expressing these operations. One such
language is relational query language. The relational algebra is a procedural language that

1. Primary-key is the key-field that can uniquely identify a row in a relation.

11.8 Support Material

defines the basic operations used within the relational query language. The section 11.5,
The Relational Model deals with relational data base management systems in details. But
before that you must be introduced to two other data models : network and hierarchical.

11.4.2 The Network Data Model

In the relational data model, the relationships among data are represented by a
collection of tables. The network model differs from the relational model in that data is
represented by collections of records and relationships among data are represented by links.
In a network database, the collection of records are connected to one another by means of
links. A record is a collection of fields (attributes), each of which contains only one data value.
By a link we mean that it is an association between precisely two records. Now, let us
see how the same sample database, shown earlier in relational form, can be defined in
network model. (Fig. 11.4) The structure shown in Fig. 11.4 is known as arbitrary graph.

.
S1 Britannia ... S2 New Bakers ... S3 Mother Dairy ... S4 Cookz ... S5 HaldiRam ...

O
C
10 20 20 20 10 10 10 30 30

&
AI
R

I2 Cake... I6 Cream Bis. I5 Plain Bis. I7 IceCream... I8 Cold Drink ...

T
PA

I3 Bread... I4 Milk Bread... I1 Milk ... I9 Namkeen...

Fig. 11.4 Sample Database in Network Form.

AN

Notice that, in the shipments table the supplier S1 supplies three items I2, I3 and I6 in
the quantities 10, 20 and 20 respectively. Now, this relationship has been shown in Fig. 11.5
H

as follows (following figure extracts a part of the Fig. 11.4).

D

See also, since only one quantity (10) is being supplied for item I2, I2 joins with only
one quantity. If there are more values for a same value (say X), the links form a chain and
the last value in the chain points back to the same value X. See there is a chain of
10 ® 20 ® 20. The last value 20 in the chain for supplier S1 points back to S1.
In network data model, while mapping to files, links are implemented by adding
pointer fields to records that are associated via a link. Each record must have one pointer
field for each link with which it is associated.
The operations on a network database are performed through a data manipulation
language for network model. The operations that can be performed on a network database,
include find, insert, delete,
S1 Britannia 10 Delhi modify etc. The inserting or
removing records involve
10 20 20
connect, disconnect, and reconnect
operations.

I2 Cake 5.00 I3 Bread 9.00 I6 Cream Bis. 10.00

Fig. 11.5 The Relationships are implemented through links in Network model.
UNIT 2-1 : Data Management
11.9
DATABASE CONCEPTS

11.4.3 The Hierarchical Data Model

In the network model, the data is represented by collections of records and
relationships among data and are represented by links. This is true of hierarchical model as
well. The only difference is that in the hierarchical model, records are organized as trees
rather than arbitrary graphs. You can say that hierarchical model represents relationship
among its records through parent child
S1 Britannia 10 Delhi relationships that can be easily represented
through tree like structures.
I2 Cake 5.00
Therefore, a hierarchical database is also a
I3 Bread 9.00 10 collection of records connected to one another
I6 Cream Bis. 10.00 through links. Fig. 11.6 shows a possible
20
hierarchical view for the suppliers-and-items
20
database. In this view the data is represented by
a simple tree structure, with suppliers superior
S2 New Bakers 30 Mumbai

.
to items.

O
I4 Milk Bread 14.00
The record type at the top of the tree — the

C
supplier record type in our example - is usually
I5 Plain Biscuit 6.00 20 known as the root. In general, the root may have

&
10 any number of dependents, each of these
dependents may have any number of lower-
AI
level dependents, and so on, to any number of
S5 Haldiram 40 Jaipur levels.
R

The operations on a hierarchical database are

I9 Namkeen 15.00 performed through a data manipulation language
T

for hierarchical data model. The operations that

PA

30 can be performed on hierarchical database

Fig. 11.6 Sample Database in Hierarchical Form include retrieval, insertions, deletion and
(Suppliers superior to items). modifications of records.
AN

11.5 THE RELATIONAL MODEL

H

The relational model was propounded by E.F. Codd of the IBM and has since been
D

acknowledged as a very important concept in DBMS (Data Base Management Systems)

technology. The relational model has established itself as the primary data model for
commercial data processing applications. Its success in this field has led to its application
outside data processing in systems for computer-aided design and other environments.
Let us explore this model in details.

11.5.1 Terminology
Different terms used in the relational model are being discussed here.

? 1. Name the different data models

available for database systems. Which
of them is the most preferred one ?
Relation
In general, a relation is a table i.e., data is arranged in rows
and columns.
2. What are the similarities and
A relation has the following properties :
differences between network and
hierarchical data models ? 1. In any given column of a table, all items are of the
3. What do you mean by relational same kind whereas items in different columns may
database ? not be of the same kind.
 11.10 Support Material

2. For a row, each column must have an atomic (indivisible) value and also for a
row, a column cannot have more than one value.
3. All rows of a relation are distinct. That is, a relation does not contain two
rows which are identical in every column. That is, each row of the relation
can be uniquely identified by its contents.
4. The ordering of rows within a relation is immaterial. That is, we cannot
retrieve any thing by saying that from row number 5, column name is to be
accessed. There is no order maintained for rows inside a relation.
5. The columns of a relation are assigned distinct names and the ordering of
these columns is immaterial.

Domain
A domain is a pool of values from which the actual values appearing in a given
column are drawn. For example, the values appearing in the Supp# column of both the
Suppliers table and the Shipments table are drawn from the underlying domain of all valid

.
O
supplier numbers. (See Fig. 11.7). A domain is said to be atomic if elements of the domain
are considered to be indivisible units. For example, the set of integers is an atomic domain but

C
the set of all sets of integers is a nonatomic domain.

Supplier Numbers
S4
&
AI
See, the Supp# columns are drawing their
Domain S1 S2
values from supplier numbers domain
R

S6

S3 S5
T
PA

S7
Shipments
Suppliers

Supp# Item# Qty-supplied Supp# Supp-Name Status City

AN

S1 I2 10
S1 Britannia 10 Delhi
S1 I3 20
S2 New Bakers 30 Mumbai
S1 I6 20
H

S3 Mother Dairy 10 Delhi

S2 I4 20
D

S2 I5 10 S4 Cookz 50 Bangalore
S3 I1 10 S5 Haldiram 40 Jaipur
S3 I7 10
S4 I8 30
S5 I9 30

Fig. 11.7 Supp# in Suppliers and Shipments draw values from same domain.

In the sample database, observe that relations Suppliers and Shipments have a
common domain (Supplier numbers domain) and so do Items and Shipments (Item
numbers domain).
NOTE
A crucial feature of relational See that in the table Shipments the supplier S1 is supplying 10 units of
data model is that associations item I2 and from the table Suppliers it can be said that a Delhi based
between rows are represented supplier Britannia is supplying 10 units of item I2. That is how the
solely by data values in columns association between rows (S1, I2, 10 of Shipments and S1, Britannia, 10,
drawn from a common domain. Delhi of Suppliers) can be represented.
UNIT 2-1 : Data Management
11.11
DATABASE CONCEPTS

Tuple
Tuples The rows of a relation
are known as Tuples. The rows of tables (relations) are generally referred to as Tuples (usually
pronounced to rhyme with “couples’’).

Attributes
Attributes The columns of a The columns/fields of tables (relations) are generally referred to as
relation are known as attributes.
Attributes.
Degree
The number of attributes in a relation determine the degree of a relation.
Degree The number of attri- A relation having 3 attributes is said to be a relation of degree 3.
butes in a relation is called Similarly, a relation having n attributes is said to be a relation of degree
Degree. n. Relations of degree one are said to be unary, relations of degree two
are binary, relations of degree three are ternary, ....., and relations of
degree n are n-ary.

.
O
Cardinality The number of Cardinality

C
rows in a relation is known as The number of tuples (rows) in a relation is called the cardinality of the
Cardinality. relation ; e.g., the cardinality of Shipments relation is 9. Similarly, the

&
cardinality of Suppliers relation is 5.

11.5.2 Views
AI
A view is a kind of table whose contents are taken from other tables depending upon
R

a condition. Views do not contain data of their own. The contents of a view are determined
by carrying out the execution of the given query (the given condition). Let us elaborate the
T

concept.
PA

The kinds of tables (relations) that you have come across with until now are called
base tables. These are the tables that actually contain data. There is another kind of tables,
the views. Views are tables whose contents are derived from other tables
AN

View A View is a (virtual)

table that does not really exist (their base tables) everytime they are referred to. There is no stored file
in its own right but is instead created for storing the contents of a view. Rather only the definition of a
H

derived from one or more view is stored. Everytime a view is referred to, its contents are derived
underlying base table(s). from its underlying base table/s) using its stored definition. Thus, a view
D

is also referred to as a virtual table.

Following Fig. 11.8 explains the concept of a view. Views are like windows through
which you view desired information that is actually stored in a base table.

CREATE VIEW GoodItems AS (name of the view)

SELECT * FROM Items (name of the base table)

WHERE Price > 12 ;

Definition (the condition)
of the view
GoodItems (A Virtual Table based on Items table)

Item# Item-Name Price

I1 Milk 15.00
Fig. 11.8 I4 Milk Bread 14.00
The concept I7 Ice Cream 16.00
of Views. I9 Namkeen 15.00
11.12 Support Material

A view can be used just like any other table. It can be queried, updated, inserted into,
deleted from, and joined with other tables and views.
Views greatly extend the control you have over your data. They are an excellent way
to give people access to some but not all of the information in a table. Even Read-only views
can also be created which means that they can be queried, but they cannot be subjected to
update commands.
(Though there is a lot that can be discussed about views yet we are not going further
in explaining views as it is beyond the scope of this chapter and the book).

11.5.3 Structure of Relational Databases

Consider the Items table of our sample database. It has three attributes : Item#,
Item-Name, and Price. For each attribute, there is a set of permitted values, called the domain
of that attribute. For the attribute Item-Name, for example, the domain is the set of all item
names. Let D2 be this set (of item names). D1 be the set of item numbers, and let D3 be set
of prices. Now any row of Items must be a 3-type (i.e., must have values for 3 attributes),

.
O
say (x1, x2, x3) where x1 is an item number, x2 is the item name and x3 is the price of the
item. That means x1 is in domain D1, x2 is in domain D2, and x3 is in domain D3. In

C
general, the table Items will contain only a subset of the set of all possible rows. Therefore,
Items is a subset of

&
D1 ´ D2 ´ D3.
AI
In general, a table having n attributes must be a subset of
D1 ´ D2 ´ ...... ´ Dn - 1 ´ Dn .
R

11.5.3A Keys
T

It is important to be able to specify how rows in a relation are distinguished conceptually,

rows are distinct from one another, but from a database perspective the difference among
PA

them must be expressed in terms of their attributes. Keys come here for a rescue.

Primary Key
AN

Within a given relation, a set of one or more attributes having values that are unique
within the relation and thus are able to uniquely identify that tuple, is said to be the primary
H

key of the relation.

D

Every relation does have a primary key. In our sample database, Supp# is
Primary Key A primary key is the primary key for Suppliers as it contains unique value for each tuple in
a set of one or more attributes the relation. Similarly, Item# is the primary key for Items and the
that can uniquely identify combination of Supp# and Item# is the primary key for the Shipments
tuples within the relation.
relation.
In some tables, combination of more than one attribute provides a unique
value for each row. In such tables, the group of these attributes is declared
as primary key. In such cases, the primary key consists of more than one
attribute, it is called composite-primary-key.
The primary key is nonredundant i.e., it does not have duplicate values in the
Candidate Keys All attribute same relation. The non-primary-key attributes of a table can be referred to as
combinations inside a relation non-key attributes.
that can serve as primary key
are Candidate Keys as they are Candidate Key
candidates for the primary key Occasionally we may encounter a relation in which there is more than one
position. attribute possessing the unique identification property.
UNIT 2-1 : Data Management
11.13
DATABASE CONCEPTS

In our sample database, there are two candidate keys Supp# and Supp-Name in the
Suppliers relation. Both of these attributes contain unique values for each tuple. Similarly,
in Items, Item# and Item-Name are candidate keys.
In the case of two or more candidate keys, the database analyst decides one of them as
the primary key for the relation.

Alternate Key
In case of two or more candidate keys, only one of them serves as the
Alternate Key A candidate primary key. The rest of them are alternates only.
key that is not the primary key
In Suppliers table, Supp-Name is the alternate key and in Items table
is called an Alternate Key.
Item-Name is the alternate key.

Foreign Key
A foreign key is used to represent the relationship between two tables. A foreign key

.
is a non-key attribute (or a group of non-key attributes) whose value is derived from the

O
primary-key of another table. Or in other words, a non-key attribute of a
Foreign-Key A non-key table, which is the primary-key of some other table is known as

C
attribute, whose values are foreign-key.
derived from the primary key

&
of some other table, is known The table in which this non-key attribute i.e., the foreign-key attribute
as Foreign-Key in its current exists, is called a Foreign table or Detail table, and the table that
AI
table. defines the Primary-key, which the foreign-key of detail-table refers to, is
called Primary table or Master table.
R

11.5.4 The Relational Algebra

T

The relational algebra is a collection of operations on relations. Each operation takes

PA

one or more relations as its operand(s) and produces another relation as its result. The
operations defined in relational algebra include select, project, cartesian product, union, set
AN

difference, set intersection, natural join, division etc. The

? 1. Define the following terms :

select and project are unary operations since they operate
on one relation. The other operations are binary
H

(a) relation (b) tuple

operations as they operate upon pairs of relations.
(c) attribute (d) domain
D

Let us discuss each of these operations individually.

(e) primary key (f) candidate key
(g) cartsian product (h) degree 11.5.4A The Select Operation
2. What are views ? How are they useful ? The select operation selects tuples (horizontal subset) from a
3. Define the following : relation that satisfy a given predicate (i.e., a given
(i) primary key (ii) candidate key condition) [see Fig. 11.9(a)]. The selection is denoted by
(iii) alternate key (iv) foreign key. lowercase Greek letter s (sigma). To select those tuples
4. What is an Alternate Key ? from Items relation where the price is more than 14.00,
we shall write
5. What is the importance of a Primary
Key in a table ? Explain with a suitable s price > 14.00 (Items)
example.
6. What do you understand by the terms That means from table Items (table name given in
Primary Key and Degree of a relation in parenthesis), select (denoted by s) the tuples satisfying
relational database ? the condition price > 14.00. The relation that results from
7. What do you understand by the terms the above query is as shown in Fig. 11.9(b). The Items
Candidate Key and Cardinality of a relation is the same as in our sample database being
relation in relational database ? used in this chapter.
11.14 Support Material

Item# Item-Name Price

I1 Milk 15.00
I2 Cake 5.00
I3 Bread 9.00
I4 14.00
Item# Item-Name Price
Milk Bread
I5 Plain Biscuit 6.00 I1 Milk 15.00
I6 Cream Biscuit 10.00 I7 Ice cream 16.00
I7 Ice Cream 16.00
I9 Namkeen 15.00
I8 Cold Drink 8.00
I9 Namkeen 15.00

(a) (b)

Fig. 11.9 (a) SELECT operation ; (b) Result of s price > 14.00 (Items)

.
O
Similarly, tuples of the Suppliers where the city is “Delhi’’ can be selected as
s city = “Delhi” (Suppliers)

C
In general, in a selection predicate (condition), all

&
Item# Item-Name Price relational operators (=, ¹ , <, £, >, ³) may be used. Also,
more than one condition may be combined using the
AI
I5 Plain Biscuit 6.00
connectives and (denoted by Ù) and or (denoted by Ú).
I8 Cold Drink 8.00
Thus, to find those tuples pertaining to prices between
R

5.00 and 9.00 from relation Items, we shall write

Fig. 11.10 Result of s price > 5.00 Ù price < 9.00 (Items).
s price > 5.00 Ù price < 9.00 (Items)
T
PA

The result of this query is shown in Fig. 11.10.

The selection predicate may include comparisons between two attributes. For
example, if we have a relation Customer as shown below :
AN

Cust# Cust-Name Banker-Name Amount Balance

H

C001 Anjuman Rakesh 17510.00 17510.00

D

C002 Reva Reva 21000.00 22300.00

C003 Aastha Ravi 25199.00 24801.00

Fig. 11.11 The relation named Customer.

And we want to find out the names of those customers who have the same name as
their banker, we may write
s cust-Name = banker-Name (Customer)
Similarly, to find out the details of those customers who have their amount more than
their balance, we’ll write
s amount > balance (Customer)
The relations resulting from above two queries are shown below in Fig. 11.12.

Cust- Banker- Cust- Banker-

Cust# Amount Balance Cust# Amount Balance
Name Name Name Name

C002 Reva Reva 21000.00 22300.00 C003 Astha Ravi 25199.00 24801.00

(a) (b)
Fig. 11.12 (a) Result of s cust-name = banker-name (Customer), (b) Result of s amount > balance (Customer).
UNIT 2-1 : Data Management
11.15
DATABASE CONCEPTS

11.5.4B The Project Operation

The project operation yields a “vertical’’
Supp# Supp-Name Status City subset of a given relation in contrast to the
S1 Britannia 10 Delhi “horizontal’’ subset returned by select
S2 New Bakers 30 Mumbai operation. That is, the projection lets you
S3 Mother Dairy 10 Delhi
select specified attributes in a specified
order [see Fig. 11.13(a)]. As the result is also
S4 Cookz 50 Bangalore
a relation, the duplicating tuples are
S5 Haldiram 40 Jaipur
automa- tically removed. Projection is
(a) denoted by Greek letter pi ( p). To project
Supp-Name City Supplier names and their cities from the
Britannia Delhi relation Suppliers, we shall write
New Bakers Mumbai
pSupp-Name, City (Suppliers)
Mother Dairy Delhi

.
Cookz Bangalore That means, from table Suppliers (table

O
Haldiram Jaipur name is given in parenthesis), project

C
(b) (denoted by p) the attributes Supp-Name and
City. The relation resulting from this query
Fig. 11.13 (a) The PROJECT operation will select the vertical subset

&
is as shown in Fig. 11.13(b).
of entire table. (b) Result of pSupp-Name, City (Suppliers).
AI
However, if you give the project query as follows :
R

pCity, Supp-Name (Suppliers)

the resulting relation will be as shown in Fig. 11.14.
T

City Supp-Name
PA

Delhi Britannia
Mumbai New Bakers
AN

Delhi Mother Dairy

Bangalore Cockz
H

Jaipur Haldiram
D

Fig. 11.14. Result of pCity, Supp-Name (Suppliers).

It is obvious from the above table that you can specify the order of attributes in the
resulting relation. Since these operations result in a relation, project operation can also be
applied on a resulting relation of a query. For example, if you are interested only in the
names of those items that are costlier than ` 14.00, you may write it as
pItem-name (s prime > 14.00 (Items))
Item-Name City First the inner query is evaluated and then the
Milk Delhi outer query is evaluated using the relation that
resulted from the inner query. The result of
Ice cream Mumbai
above query is as shown below in Fig. 11.15(a).
Namkeen Bangalore
Duplicating tuples are automatically removed
Jaipur in the resulting relation. For instance, if you write
(a) (b) pCity (Suppliers)
Fig. 11.15 Result of (a) pItem-Name (s price > 14.00 (Items)) the resulting relation will be as shown in
(b) pCity (Suppliers). Fig. 11.15(b).
11.16 Support Material

11.5.4C The Cartesian Product Operation

The cartesian product is a binary operation and is denoted by a cross (´). The Cartesian
product of two relations A and B is written as A ´ B. The cartesian product yields a new
Student relation which has a degree (number of attributes) equal
to the sum of the degrees of the two relations operated
Stud# Stud-Name Hosteler
upon. The number of tuples (cardinality) of the new
S001 Meenakshi Y relation is the product of the number of tuples of the two
S002 Radhika N relations operated upon. The cartesian product of two
relations yields a relation with all possible combinations
of the tuples of the two relations operated upon.
Instructor
Inst# Inst-Name Subject
All tuples of first relation are concatenated with all the
I01 English
tuples of second relation to form the tuples of the new
K. Lal
relation. Let us consider how does it work. Let us assume
I02 R.L. Arora Maths
there are two relations Student and Instructor as shown

.
O
in Fig. 11.16.
Fig. 11.16 Student and Instructor Relations.

C
The cartesian product of these two relations, Student ´ Instructor, will yield a
relation that will have a degree of 6 (3 + 3 : sum of degrees of Student and Instructor)
Student ´ Instructor
&
and a cardinality 4 (2 ´ 2 : product of
AI
cardinalities of two relations). The
Stud# Stud-Name Hosteler Inst# Inst-Name Subject resulting relation has been shown in
S001
R
Meenakshi Y I01 K. Lal English Fig. 11.17(a).
S001 Meenakshi Y I02 R.L. Arora Maths See the resulting relation contains all
T

S002 possible combinations of tuples of the

Radhika N I01 K. Lal English
PA

two relations.
S002 Radhika N I02 R.L. Arora Maths The illustration of cartesian product of
two tables has been given in Fig. 11.17(b).
AN

Fig. 11.17 (a) The Cartesian Product of Student and Instructor tables.

Table A Table B
H

1 ABC 15 101 M Engl.

D

2 DEF 30 102 R Maths

Table A ´ B is

Combination of 1 ABC 15 with 101 M Engl. is 1 ABC 15 101 M Engl.

102 R Maths 1 ABC 15 102 R Maths

Combination of 2 DEF 30 with 101 M Engl. is 2 DEF 30 101 M Engl.

102 R Maths 2 DEF 30 102 R Maths

Thus Table storing cartesian product A ´ B is as shown below :

1 ABC 15 101 M Engl.

1 ABC 15 102 R Maths
2 DEF 30 101 M Engl.
2 DEF 30 102 R Maths

Fig. 11.17 (b) Cartesian Product of two tables.

UNIT 2-1 : Data Management
11.17
DATABASE CONCEPTS

11.5.4D The Union Operation

The union operation is a binary operation that requires two relations as its operands. It
produces a third relation that contains tuples from both the operand relations
[Fig. 11.18(a)]. The union operation is denoted by È. Thus, to denote the union of two
relations X and Y, we will write as X È Y.
Before we consider and see how it works, one thing must be remembered about union
that is, both the operand relations must be union-compatible. That means, for a union
operation A È B to be valid, the following two conditions must be satisfied by the two
operands A and B :
1. The relations A and B must be of the same degree. That is, they must have the
same number of attributes.
2. The domains of the ith attributes of A and the ith attribute of B must be the same.
In Fig. 11.12, two relations have been shown that display result from the queries
(a) s Cust-name = banker-name (Customer) and (b) s amount > balance (Customer).

.
O
Now, if you want to obtain union of these two relations (shown in Fig. 11.12), you may

C
write
(s cust-name = banker-name (Customer)) È (s amount > balance (Customer))

&
And the resultant relation will be as shown in Fig. 11.18(b).
AI
Drama Song
A B
R

Rollno Name Age Rollno Name Age

13 Kush 15 2 Manya 15
T

17 Swati 14 10 Rishabh 15
PA

13 Kush 15 AÈB
Result of Song È Drama will be
AN

Rollno Name Age

2 Manya 15 Notice that one duplicating
H

tuple (13, Kush, 15) has

10 Rishabh 15
been automatically removed
D

13 Kush 15
17 Swati 14
(a)

Cust# Cust-Name Banker-Name Amount Balance

C002 Reva Reva 21000.00 22300.00
C003 Aastha Ravi 25199.00 24801.00

(b)
Fig. 11.18 (a) Union operation, (b) Result of (s cust-name = banker-name (Customer) ) È (s amount > balance (Customer) ) . (Named as NewCust)

Do remember that in the resultant relation, all the duplicating tuples will auto-
matically be removed.

11.5.4E The Set Difference Operation

The set difference operation, denoted by - (minus) allows us to find tuples that are in
one relation but not in another [Fig. 11.19(a)]. The expression A - B results in a relation
11.18 Support Material

containing those tuples in A but not in B. Let us assume that the relation shown in
Fig. 11.18 has been named as NewCust. Now the result of difference between Customer
(shown in Fig. 11.11) and NewCust will be as shown in Fig. 11.19(b).

Drama Song
A B
Rollno Name Age Rollno Name Age
13 Kush 15 2 Manya 15
17 Swati 14 10 Rishabh 15
13 Kush 15 A-B
Result of Drama – Song will be

Rollno Name Age

17 Swati 14

.
(a) Result of Drama - Song

O
C
Cust# Cust-Name Banker-Name Amount Balance
C001 Anjuman Rakesh 17510.00 17510.00

(b) Result of Customer – NewCust.

&
AI
Fig. 11.19 Set Difference Operation
R

NOTE 10.5.4F The Set Intersection Operation

The set intersection operation finds tuples that are common to the two
T

Any relational algebra expre-

ssion using set intersection can operand relations [Fig. 11.20(a)]. The set intersection operation is
PA

be rewritten by replacing the denoted by Ç . That means A Ç B will yield a relation having tuples
intersection operation with a common to A and B. The result produced by Customer Ç NewCust
pair of set difference opera-
AN

(customer shown in Fig. 11.11, NewCust shown in Fig. 11.18) has been
tions as :
shown in Fig. 11.20(b).
A Ç B = A - (A - B ).
H

Drama Song
D

Rollno Name Age Rollno Name Age A B

13 Kush 15 2 Manya 15
17 Swati 14 10 Rishabh 15
13 Kush 15
AÇB
Result of Song Ç Drama will be

Rollno Name Age

13 Kush 15
(a)

Cust# Cust-Name Banker-Name Amount Balance

C002 Reva Reva 21000.00 22300.00
C003 Aastha Ravi 25199.00 24801.00
(b)

Fig. 11.20 (a) Set Intersection operation, (b) Result of Customer Ç NewCust.
UNIT 2-1 : Data Management
11.19
DATABASE CONCEPTS

11.6 COMPARISON OF DATA MODELS

Data models can be evaluated on the basis of usability, implementability and
performance.
How easy are they for learning purpose and usage ?
How well they define and manipulate data structures ?
Data experts have been debating for some time which DBMS data model is the ‘best’.
The answer depends, in part, on philosophical orientation. The hierarchical model is the
oldest model and has long been the most popular on mainframes. People who think the
user (or programmer) should be able to have some control over the details of storage

? 1.

2.
What is the function of select and
project operations ?
What is cartesian product ? How is it
allocation and search paths often prefer network systems.
But rarely network models have gone commercial ;
mostly they remain confined to development or testing
different from join operation ? stages. People who believe that users should be isolated

.
from the mechanisms used to access data, and those who

O
3. What does union operation do ? What
are the conditions to carry out union assign high priority to a model that is easy to understand

C
operation ? and construct, frequently favour relational systems.
4. What are the set difference and set However, few database systems are fully relational.

&
intersection operations ? How are they Instead they graft relational features on a basic
different ? hierarchical/network structure.
AI
R

L e t Us R e v i s e
T

Ò A collection of data is referred to as database and a database (management) system is basically a computer based
PA

record keeping system.

Ò Database systems help reduce data redundancy, data inconsistency and facilitate sharing of data, standardization
of data and data security.
AN

Ò A database is implemented through three general levels : internal (closest to physical storage), conceptual (describes entire
data and its relationships) and external (closest to the users).
Ò Data independence allows modification of a scheme definition without affecting other scheme definitions.
H

Ò There are two levels of data independence : logical and physical.

Ò
D

There are three models available for database management : relational, network & hierarchical.
Ò A relational data model organizes the data into tables known as relations.
Ò A network data model represents data by collection of records and relationships among data are represented by
links (pointers).
Ò A hierarchical data model represents data by records organized in form of trees and the relationships among data
are represented by links.
Ò A view is a virtual table derived from one or more underlying base tables.
Ò There is no stored file created for storing a view’s contents, rather, only the view definition is stored.
Ò The relational algebra is a collection of operations on relations. The various operations of relational algebra are
select, project, cartesian product, union, set difference, set intersection, join etc.
Ò The selection extracts tuples from a relation depending upon a condition (symbol s).
Ò The projection extracts columns from a relation. (Symbol p).
Ò The cartesian product yields a relation with all possible combinations of the tuples of the two relations operated
upon. (Symbol ´).
Ò The union yields a relation containing tuples from both the operand relations. (Symbol È).
Ò The set difference finds tuples that are in one relation but not in another. (Symbol -).
Ò The set intersection finds tuples that are common to the two operand relations (Symbol Ç).
11.20 Support Material

Solved Problems
1 What is a database system ? What is its need ?
Solution. A database is a collection of interrelated data and a database system is basically a computer
based record keeping system.
A typical file processing system suffers from some major limitations like data redundancy (duplication of
data), data inconsistency, unsharable data, unstandardized data, insecure data, incorrect data etc. On the other
hand, a database system overcomes all these limitations and ensures continues efficiency.
The advantages provided by a database system are :
(i) Reduced data redundancy (ii) Controlled data inconsistency (iii) Shared data
(iv) Standardized data (v) Secured data (vi) Integrated data.
Therefore, to have the systems with increased performance and efficiency, the database systems are preferred.

2 Can you think of disadvantages of using a database system ? What are these ?
Solution. With the complex tasks to be performed by database systems, certain things may crop up that

.
O
may be termed as disadvantages of using database system. These are :
1. Security may be compromised without good controls.

C
2. Integrity may be compromised without good controls.

&
3. Extra hardware may be required.
4. Performance overhead may be significant.
AI
5. System is likely to be complex.

How many types of users work on database systems ?

R
3
Solution. A primary goal of a database system is to provide an environment for retrieving infor- mation
T

from and storing new information into the database. There are three different types of database system
users, differentiated by the way they expect to interact with the system.
PA

1. End User. An end user is a person who is not a computer trained person but uses the database to
retrieve some information. For example, in a railway reservation system, a customer retrieving the
AN

train-details is an end user.

2. Application System Analyst. This user is concerned about all of the database at logical level i.e., what
all data constitutes the database ? What are the relationships between the data-entities etc. without
H

considering the physical implementation details.

3. Physical Storage System Analyst. This user is concerned with the physical imple- mentation details
D

of the database such as which storage device, which storage technique should be used ? etc.

4 What are the various levels of data abstraction in a database system ?

Solution. There are three levels of data abstraction :
1. Internal Level (Physical Level). This level describes how the data is actually stored on the storage
medium. At this level, complex low-level data structures are described in details.
2. Conceptual Level. This level describes what data are actually stored in the database. It also describes
the relationships existing among data. At this level, the database is described logically in terms of
simple data-structures.
3. External Level (View Level). This level is concerned with the way the data is viewed by individual
users. Only a part of the database relevant to the user(s) is provided to them through this level.
5 What are the various data models available for database systems ?
Solution. A data model is a collection of conceptual tools for describing data, data relationships, data
semantics etc. There are generally three data models available : relational, network and hierarchical model.
1. Relational Model. The relational model represents data and relationships among data by a collection
of tables known as relations, each of which has a number of columns with unique names.
UNIT 2-1 : Data Management
11.21
DATABASE CONCEPTS

2. Network Model. The network model represents data by collections of records and relationships
among data are represented by links which can be viewed as pointers. The records in the database are
organized as collection of arbitrary graphs.
3. Hierarchical Model. The hierarchical model is similar to the network model in the sense that data
and relationships among data are represented by records and links respectively. It differs from the
network model in that the records are organized as collections of trees rather than arbitrary graphs.
The relational model differs from the network and hierarchical models in that it does not use pointers
or links. Instead, the relational model relates records by the values they contain.

6 What are views ? How are they useful ?

Solution. A view is a virtual table that does not really exist in its own right but is instead derived from one
or more underlying base table(s). The view is a kind of table whose contents are taken upon other tables
depending upon a given query condition. No stored file is created to store the contents of a view rather its
definition is stored only.
The usefulness of views lies in the fact that they provide an excellent way to give people access to some but

.
not all of the information in a table.

O
7 Differentiate between Candidate Key and Primary Key in context of RDBMS. (Delhi 2008)

C
Solution. Candidate key. A candidate key is the one that is capable of becoming primary key i.e., a field or
attribute that has unique value for each row in the relation.

&
Primary key is a designated attribute or a group of attributes whose values can uniquely identify the tuples
AI
in the relation.

8 Give a suitable example of a table with sample data and illustrate Primary and Candidate Keys in it.
R

(Delhi 2012)
Solution. Example : Table : Class 11
T
PA

AdmNo RollNo Name Marks

1011 1 Rahat 85
AN

1083 2 Irfan 75
2011 3 Maya 63
1000 4 Shaun 60
H

999 5 Sukhi 92
D

1200 6 Zoya 86

In the table, columns AdmNo and RollNo have unique values for each row, so both are candidates to
become primary keys. Hence both of these columns are Candidate Keys. Out of these two, we can assign one
as Primary key and the other one will become alternate key.
Candidate keys : AdmNo, RollNo
Primary key : RollNo
Alternate key : AdmNo

9 Differentiate between Candidate Key and Alternate Key in context of RDBMS. (Outside Delhi 2008)
Solution. Candidate key. A candidate key is the one that is capable of becoming primary key i.e., a field or
attribute that has unique value for each row in the relation.
A candidate key that is not a primary key is called an Alternate Key.

10 Differentiate between primary key and alternate key. (Delhi 2007)

Solution. Primary Key. It is the set of one or more attributes that can uniquely identify tuples within a relation.
Alternate Key. It is a candidate key which is not primary key.
11.22 Support Material

11 What is the importance of a Primary Key in a table ? Explain with a suitable example. (Outside Delhi 2007)
Solution. A Primary Key is a set of one or more attributes that can uniquely identify tuples within the
relation. For example, in the following table Student, the column Rollno can uniquely identify each row in
the table, hence Rollno is the primary key of the following table.

Rollno Name Marks Grade

1 . . .
2 . . .
3 . . .
4 . . .

12 What is a primary key in a table ? (Outside Delhi 2003)

Solution. A Primary Key is a set of one or more attributes that can uniquely identify tuples within the
relation

.
O
13 What is relation ? Define the relational data model. (Delhi 2002)

C
Solution. A relation is a table having atomic values, unique rows and unordered rows and columns.
The relational model represents data and relationships among data by a collection of tables known as

&
relations, each of which has a number of columns with unique names.
AI
14 What is an Alternate Key ? (Delhi 2006)
Solution. A candidate key that is not a primary key is called an Alternate Key. In Suppliers table if there
R

are two candidate keys - SuppId and Supp_Name and SuppId is the primary Key then Supp_Name is the
alternate key.
T
PA

15 What do you understand by the terms Primary Key and Degree of a relation in relational database ? (Delhi 05)
Solution. Primary key is an attribute or a group of attributes whose values can uniquely identify the
tuples in the relation.
AN

Degree. Number of attributes in a relation are called its degree.

16 What do you understand by the terms Candidate Key and Cardinality of a relation in relational database ?
H

(Outside Delhi 2005)

D

Solution. Candidate key. A candidate key is the one that is capable of becoming primary key i.e., a field or
attribute that has unique value for each row in the relation.
Cardinality of a relation represents number of rows in the relation.

17 Differentiate between the terms Degree and Cardinality in context of RDBMS (Delhi 2008C)

Or
What is the difference between degree and cardinality of a table ? What is the degree and cardinality of the
following table ? (Delhi 2013)

Eno Name Salary

101 John Fedrick 45000
103 Raya Mazumdar 50600

Solution. Refer to problem 14 and 15 for definition of degree and cardinality.

Degree : 3
Cardinality : 2
UNIT 2-1 : Data Management
11.23
DATABASE CONCEPTS

18 What do you understand by Primary Key ? Give a suitable example of Primary Key from a table containing
some meaningful data. (Outside Delhi 2010)
Solution. A primary key is a designated attribute or group of attributes whose values can uniquely
identify the tuples in the relation.
Example Table : ITEM

Ino Item Quantity

I01 Pen 560
I02 Pencil 340
I04 CD 540
I09 DVD 200
I10 Floppy 400

.
Primary Key

O
19 What do you understand by Candidate Keys in a table ? Give a suitable example of Candidate keys from a

C
table containing some meaningful data. Or (Delhi 2010)

&
What are candidate keys in a table ? Give a suitable example of candidate keys in a table. (Delhi 2009)
Solution. A candidate key is the one that is capable of becoming primary key i.e., a field or attribute that
AI
has unique value for each row in the relation.
R
Example Table : ITEM

Ino
T

Item Quantity
PA

I01 Pen 560

I02 Pencil 340
I04 CD 540
AN

I09 DVD 200

I10 Floppy 400
H
D

Candidate Keys

20 What is the purpose of a key in a table ? Give an example of a key in a table. (Outside Delhi 2009)
Soluion. An attribute/group of attributes in a table that identifies each tuple uniquely is known as a Key
(also called Primary Key).
Example Table : Item

Ino Item Qty

I01 Pen 560
I02 Pencil 780
I03 CD 450
I09 Floppy 700
I05 Eraser 300
I03 Duster 200

Key
11.24 Support Material

21 What do you understand by Union and Cartesian Product operations in relational algebra ? (Delhi 2011)

Or
Explain the concept of Cartesian Product between two tables, with the help of appropriate example.
(Outside Delhi 2014)
Or
Explain the concept of Union between two tables, with the help of appropriate example. (Delhi 2014)

Soluion. Cartesian Product. The Cartesian product of two relations A and B is written as A ´ B. The
cartesian product of two relations yields a relation with all possible combinations of the tuples of the two
relations operated upon.
For example, given two relations Student and Instructor as shown below :
Student Instructor

Stud# Stud-Name Hosteler Inst# Inst-Name Subject

.
O
S001 Meenakshi Y I01 K. Lal English

C
S002 Radhika N I02 R.L. Arora Maths

S003 Abhinav N

&
The cartesian product of these two relations, Student ´ Instructor, will yield a relation as :
AI
Stud# Stud-Name Hosteler Inst# Inst-Name Subject
R

S001 Meenakshi Y I01 K. Lal English

T

S001 Meenakshi Y I02 R.L. Arora Maths

PA

S002 Radhika N I01 K. Lal English

S002 Radhika N I02 R.L. Arora Maths
AN

S003 Abhinav N I01 K. Lal English

S003 Abhinav N I02 R.L. Arora Maths
H

Union. The union operation produces a third relation that contains tuples from both the operand
D

relations which must be union-compatible. To denote the union of two relations X and Y, we write as X È Y.

Table X Table Y

No Name No Name
1 Toy 3 Pencil
2 Drum

SQL Statement
SELECT * FROM X UNION SELECT * FROM Y ;

will give table : X UNION Y

No Name
1 Toy
2 Drum
3 Pencil
UNIT 2-1 : Data Management
11.25
DATABASE CONCEPTS

22 What do you understand by Selection and Projection operations in relational algebra ? (O.D. 2011)
Soluion. Selection means selecting some rows (tuples) from a relation according to given condition
e.g., s price > 20.2 (Item)
Project operation yields a vertical subset of a given relation(i.e., select all tuples containing only given
columns of a relation).
e.g., pNAME, DESIG (Employee)

23 Observe the following table CANDIDATE carefully and write the name of the RDBMS operation out of
(i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to
produce the output as shown in RESULT. Also, find the Degree and Cardinality of the RESULT.

Table : CANDIDATE Table : RESULT

NO NAME STREAM NO NAME

.
C1 AJAY LAW C3 ROHAN

O
C2 ADITI MEDICAL

C
C3 ROHAN EDUCATION
C4 RISHAV ENGINEERING

& (CBSE D 2017)

AI
Soluion. SELECTION and PROJECTION, both operations have taken place.
Result : Degree = 2 ; Cardinality = 1
R

24 Observe the following table MEMBER carefully and write the name of the RDBMS operation out of
T

(i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to
PA

produce the output as shown in RESULT. Also, find the Degree and Cardinality of the RESULT.

Table : MEMBER Table : RESULT

AN

NO MNAME STREAM NO MNAME STREAM

M001 JAYA SCIENCE M002 ADITYA HUMANITIES
H

M002 ADITYA HUMANITIES

D

M003 HANSRAJ SCIENCE

M004 SHIVAK COMMERCE
(CBSE OD 2017)
Soluion. SELECTION operation ; Degree = 3, Cardinality = 1

25 A database is to contain information about persons and skills. At a particular time the following persons are
represented in the database, and their skills are as indicated.
Person Skills
Anuj Programming
Bhawna Operating and Programming
Chetan Engineering and Programming
David Operating and Engineering

For each person, the database contains various personal details, such as address. For each skill it contains an identification
of the appropriate basic training course, an associated job grade code, and other information. The database also contains the
date each person attended each course, where applicable (the assumption is that attendance at the course is essential
before the skill can be said to be acquired ). Sketch a relational view for this data.
11.26 Support Material

Solution. Relational view

Person Skill
Pname Address Skill-Name Course Jobcode
Anuj .......... Programming - -
Bhawna .......... Operating - -
Chetan ..........
Engineering - -
David ..........

Perskill

Pname SkillName Date

Anuj Programming -
Bhawna Operating -
-

.
Bhawna Programming

O
Chetan Engineering -

C
Chetan Programming -
David Operating -

&
David Engineering -
AI
Solved Problems
R
Thinking Skills

1. What is a relation ? What is the difference between a tuple and an attribute ?

Higher Order

(CBSE Delhi 1998)

PA

Solution. A relation is a table having atomic values, unique rows and unordered
rows and columns.
AN

A row in a relation is known as tuple whereas a column of a table is known as an

attribute.
2. What is data redundancy ? What are the problems associated with it ?
H

Solution. Duplication of data is data redundancy. It leads to the problems like

D

wastage of space and data inconsistency.

3. How many types of users work on database systems ?
Solution. A primary goal of a database system is to provide an environment for
retrieving information from and storing new information into the database. There are
three different types of database system users, differentiated by the way they expect
to interact with the system.
1. End User. An end user is a person who is not a computer trained person but
uses the database to retrieve some information. For example, in a railway
reservation system, a customer retrieving the train-details is an end user.
2. Application System Analyst. This user is concerned about all of the database
at logical level i.e., what all data constitutes the database ? What are the
relationships between the data-entities etc. without considering the physical
implementation details.
3. Physical Storage System Analyst. This user is concerned with the physical
implementation details of the database such as which storage device ? Which
storage technique should be used ?
UNIT 2-1 : Data Management
11.27
DATABASE CONCEPTS

4. What are the various data models available for database systems ?
Solution. A data model is a collection of conceptual tools for describing data, data
relationships, data semantics etc. There are generally three data models available :
relational, network and hierarchical model.
1. Relational Model. The relational model represents data and relationships
among data by a collection of tables known as relations, each of which has a
number of columns with unique names.
2. Network Model. The network model represents data by collections of records
and relationships among data are represented by links which can be viewed
as pointers. The records in the database are organized as collection of arbitrary
graphs.
3. Hierarchical Model. The hierarchical model is similar to the network model
in the sense that data and relationships among data are represented by
records and links respectively. It differs from the network model in that the

.
records are organized as collections of trees rather than arbitrary graphs.

O
The relational model differs from the network and hierarchical models in that it

C
does not use pointers or links. Instead, the relational model relates records by the
values they contain.

&
AI
Glossary
R

Alternate Key A candidate key which is not primary key.

Attribute A column of a relation.
T

Base Table A table having an independent existence ; represented in storage by a distinct

PA

stored file.
Candidate Keys Attribute combinations in a relation that can serve as a primary key.
AN

Cardinality Number of tuples in a relation.

Data Independence Ability to modify a scheme definition within a database without affecting other
scheme definitions.
H

Data Redundancy Duplication of data.

D

Database Collection of interrelated data.

Database System A computer based recordkeeping system.
Degree Number of attributes in a relation.
Domain A pool of values from which the actual values appearing in a given column are
drawn.
Foreign Key Non-key attribute, which is primary key of another table.
Logical Data Independence The property of being able to change the overall logical structure of the database
without changing the application program’s view of data.
Physical Data Independence The property of being able to change the physical structure of the data without
changing the logical structure.
Physical Database A database in the form in which it is stored on the storage media, including
pointers or other means of interconnecting it.
Primary Key Set of one or more attributes that can uniquely identify tuples within a relation.
Relation Data arranged in rows and columns and having certain properties.
Relational Algebra A language providing a set of operators for manipulating relations.
Tuple A row of a relation.
11.28 Support Material

Assignments
Short Answer Questions

1. Draw a diagram explaining various levels of data Write relational expressions for questions 12 – 25
abstraction. using the above relations.
2. What is meant by “data independence’’ ? Make a
12. List employees’ first and last names.
list of data independence capabilities.
13. Give all information on employees in manu-
3. What are the main differences between a file-pro-
facturing systems.
cessing system and a database management system ?
14. Just give employee names in manufacturing
4. This chapter has described some of the major
systems.
advantages of a database system. What are the
disadvantages ? 15. Give employee names outside manufacturing
systems.
5. Explain the difference between physical and

.
logical data independence. 16. List employee names and their departments for

O
employee numbers between 100 and 300.
6. Illustrate the difference between the three levels
17. List employees numbers, names and educational

C
of data abstraction.
levels for three education levels 16, 18, and 20.
7. What is a relation ? What is the difference
18. List employee number and name in the

&
between a tuple and an attribute ? (Delhi 1998)
manufacturing systems for job code 54.
8. What is a relation ? Define the properties of a
AI
relation. 19. Same request as above with education level
below 15.
9. Summarise the major differences between a
R

relation and a traditional file. 20. List those employees’ last name and monthly
salary plus bonus, whose job code is at least three
10. Give a network data-structure diagram for the
times their education level and whose annual
T

following relational database :

basic salary plus bonus exceed 100000.
PA

(a) lives (person-name, street, city)

21. List employee last name, work departments and
works (person-name, company-name, salary)
monthly salary for those in Services, Planning and
located-in (company-name, city)
Information centre departments.
AN

manages (person-name, manager-name)

22. List employee last name, work department,
(b) course (course-name, room, instructor) department number, and department name.
H

enrolment (course-name, student-name, grade) 23. List employee number, name and department
number for employees who are managers.
D

11. For the relational databases mentioned in

questions 10, give an hierarchical data-structure. 24. Which employees earn more than their
managers ?
25. List suppliers who supply only red parts.
Give the following relations
26. Give a suitable example of a table with sample
EMPL (FirstName, Lastname, Workdept, Empno, data and illustrate Primary and Alternate Keys
Educlevel, Jobcode, Salary, Bonus, Hiredate) in it. (Outside Delhi 2012)
Dept (Deptno, Deptname, Mgrno, Adurdept) 27. Explain the concept of candidate keys with the
Supplier (Sno, Pno) help of an appropriate example.
Part (Pno, colour) (Outside Delhi 2013)