CHAPTER-I

DATABASE MANAGEMENT SYSTEM (DBMS)

TOPICS COVERED

 INTRODUCTION
 DATA vs. INFORMATION
 DATABASE
 DATABASE MANAGEMENT SYSTEM
 APPLICATIONS OF DBMS
 DATABASE MODELS
 RELATIONAL DATABASE MODEL (RDBMS)
 RDBMS TERMINOLOGIES
 CONCEPT OF KEYS
 TYPES OF KEYS
 FOREIGN KEY CONCEPT
 EXERCISE

INTRODUCTION

Data is considered to be the most useful and expensive resource in current times because we require it for many
different purposes. Data can be described as a collection of facts and figures. It may exist in

 Physical form such as printed or written on paper, stone, leaf etc.

 Digital or in electronics form such as text, image, document, audio, video etc.

It is needless to say the advantages of storing data in electronic form instead of physical form. We generate billions of
megabytes of data every hour, and a significant amount of it is processed to get our required information in almost no time. So,
who is processing these enormous amounts of data? The answer is computerized algorithms.

In this unit, we are going to learn about software tools and various concepts related to the processing of massive
amount of data called Database Management System or DBMS in short.

DATA vs. INFORMATION

 An unorganized collection of facts and figures in any form is called data.

 A block of data may not carry specific meaning and may not suit to specific needs.
 In order to get something useful, we need to interpret, process, organize the data we have.
 The processed data is called information.
 Data doesn’t depend on information; information depends on data.
 Information is sufficient for decision making not data.

Fig_1: Data vs. Information

Usually, we are interested in information; but the information is something that we get after processing the data. So
both information and data is equally important for us.

DATABASE
 A database is a well-organized and systematic collection of structured data. In simple words, we can say a database is a
computer file where a large amount of data is actually stored and maintained.

Flat file Database vs. Relational Database

Data can also be stored and processed in a normal file called flat file (e.g. inside a text file, spreadsheet etc.). But storing
data in a flat file is not a good idea especially when the amount of the data is large. A database system has the following
advantages over the file management system.

 Data redundancy: duplicate copies of data.

 Data inconsistency: Multiple copies of data mismatching.
 Absence of proper security mechanism.
 Limited data sharing.
 Difficulty in accessing data.
 Data integrity problems (data present is either incorrect or inconsistent)

We may conclude that storing data in a database instead of a normal file makes the management of data much easier
and convenient.

DATABASE MANAGEMENT SYSTEM

A database management system (or DBMS in short) is a specially designed software pack for handling a huge amount of
data present inside a database.

Apart from the basic operations of data handling (such as adding new data items, updating existing data, deleting
irrelevant data, querying the data etc.) a DBMS also provides the following advantages / features / needs.

 Manage large amount of data.

 Removing redundant data.
 Efficient processing of queries and Information retrieval.
 Handle the security of the data.
 Handling parallel or simultaneous transactions.
 Providing multi-user access.
 Providing data recovery against a crash.

Popular DBMS Software are: MySQL, Microsoft Access, Oracle, LibreOffice Base, Microsoft SQL Server etc.

APPLICATIONS OF DBMS

The DBMS is required whenever we need a lot of data to be managed. Here are the examples where database systems
are used.

Banking For customer information, account activities, payments, deposits, loans, etc.

Airlines For reservations and schedule information.

Universities For student information, course registrations, colleges and grades.

Sales Use for storing customer, product & sales information.

DATABASE MODELS

While storing the data in a database, the first thing we do is deciding the structure or model of the data. The data model
decides how the data is structurally stored inside the database. There are various data models like:

 Entity-relationship data model.

 Object-oriented data model.
 Relational data model.
 Hierarchical data model etc.
 Out of the above, the most popular and convenient is the relational database model. In the upcoming chapters, we are
going to cover concepts about the relational database model.

RELATIONAL DATABASE MODEL (RDBMS)

The relational database was proposed by E. F. Codd at IBM. There are numerous RDBMS software available including:
MySQL, Microsoft Access, Oracle, PostgreSQL, SQLite, IBM DB2, LibreOffice Base, Microsoft SQL Server etc. MySQL and Oracle are
the most popular ones.

A relational database stores the data in tabular form; tables hold data in the form of rows and columns. Inside a
database, multiple such tables are interlinked with each other.

The tables in a relational database are also called relations. There are a few other terminologies frequently used in a
relational database system.

Before discussing these let's see how a table or relation looks like.

E.g.-1 A database table: Order.

RDBMS TERMINOLOGIES
There various terms associated with RDBMS lets discuss them one by one.

1. Relation
In relational DB model data is stored inside relations. A relation is a table of rows and columns where the data are
present.
The example-1 describes a relation named Order. You can see the rows and columns in the relation. Other examples are:
Student relation, Employees relation etc.

2. Attribute
The columns in the relation are called attributes. The attributes are names that describes an entity. These are also
known as Field or Column.

In the example-1, REGION, NAME, ITEM etc. are the attributes of Order relation. You may think of attributes of a student
relation such as: Name, class, section, grade, address etc.

3. Tuple
A row in a relation is called a tuple. A tuple holds complete information about an entity. It is also called as a Record /
Row.

In the example-1, East, Jones, Pencil, 95, 1.99, 189.05 is a tuple or record in the order relation.

4. Degree
The number of attributes of a relation is called the degree of that relation.
 The degree of the Order relation in example-2, is 6. Think of another relation: Student (Name, Roll no., Age, Class,
Section) is having degree 5.
 A relation of degree 1 is called unary relation.
 A relation of degree 2 is called a binary relation.
 Similarly ternary relation is a relation of degree 3.
5. Cardinality
The number of tuples or records a relation holds is called cardinality.
The cardinality of the Order relation in example-2, is 6. Similarly a Student relation holding data of 100 students has the
cardinality as 100.
6. Domain
For an attribute, domain is the set of allowed values. For example: The domain of age can be 1 to 120. The domain of
days can be Sunday, Monday... Saturday. Etc.
Guess the domain of Month and day in a date.

6. Data item
The smallest unit of information in a table is called data item.
In the table Order: East, Central, Pencil, etc. are called data items. Note that there are m*n number of data items
present in a table where m is the degree and n is the cardinality of the table.

CONCEPT OF KEYS
The key attribute is an important concept in a database environment. For smooth functioning, every record in a relation
must be uniquely identified.
 A key (or key attribute) is an attribute in a relation used to uniquely identify each and every record in that table.
 The key attribute has different values in each record i.e. no redundant (duplicate) data item exists in a key attribute.
 Multiple key attributes may exist in a relation.

TYPES OF KEYS
We know, the keys are the attributes present in a relation those help to identify records or tuples uniquely.

1. Primary key: The primary key is used to uniquely identify each and every record of a relation.

 Out of the available key attributes, any one is selected by the database administrator and designated as the primary
key.
 SQL has special provision for primary keys; hence choosing primary key is important. They are exist only one primary
key for a relation.

2. Candidate keys: From the pool of keys, candidate keys are the ones which are capable of being selected as the primary key.

 A relation may have multiple number of candidate keys.

4. Alternate key: All the candidate keys, those who could not become the primary key are called alternate keys.

6. Composite key: A key that consists of more than one attribute is called composite key.

 This is a key composed of more than one attributes.

3. Super keys: Any superset of a candidate key is called super key.

 Super keys are theoretical concept used for refining the database.
 A relation may have multiple number of super keys.

FOREIGN KEY CONCEPT

Foreign key is used to relate/link two relations. Also called as Reference key. It is a column (or a combination of
columns) whose values match a Primary Key of another table.

Consider two relations Student and Department with the following data.
 NAME ROLL AGE DEPT NO
Rahul 1 16 300
Ramesh 2 18 200
Krishna 3 16 300
Bharat 4 17 400
Rakesh 5 15 200
Rita 6 17 300
E.g.-2 A database table: Student.

DEPT NAME DEPT NO HOD

Chem 200 Dr. Hudda
Phy 300 Dr. Raghav
Maths 400 Dr. Mukesh
E.g.-3 A database table: Department.
Can you guess the department name and HOD name of a student whose name is Krishna?

Clearly, “Krishna belongs to Physics Department, Dr. Raghav is the HOD” is the answer to this question.

You must have noticed, the data we searched for is present in both the tables. Because of the common attribute i.e.
DEPT NO, the two tables are linked and the data is retrieved.

Here, DEPT NO (of Student table) works as a foreign key for Student relation. Note that DEPT NO is also the primary key
of Department relation.

Hence, a foreign key of a relation points to primary key of another relation.

_________________________________________TRY_IT

1. A relational database consists of a collection of

a) Tables
b) Fields
c) Records
d) Keys
2. A ________ in a table represents a relationship among a set of values.
a) Column
b) Key
c) Row
d) Entry
3. For each attribute of a relation, there is a set of permitted values, called the ________ of that attribute.
a) Domain
b) Relation
c) Set
d) Schema
4. Database __________ which is the logical design of the database, and the database _______ which is a snapshot of the data
in the database at a given instant in time.
a) Instance, Schema
b) Relation, Schema
c) Relation, Domain
 d) Schema, Instance
5. Course(course_id, sec_id, semester) Here the course_id,sec_id and semester are __________ and C-++ourse is a _________
a) Relations, Attribute
b) Attributes, Relation
c) Tuple, Relation
d) Tuple, Attributes
6. The tuples of the relations can be of ________ order.
a) Any
b) Same
c) Sorted
d) Constant
7. Which one of the following cannot be taken as a primary key?
a) Id
b) Register number
c) Dept_id
d) Street
8. An attribute in a relation is a foreign key if the _______ key from one relation is used as an attribute in that relation.
a) Candidate
b) Primary
c) Super
d) Sub
9. Fill in the blanks
a) A Computer-Based record keeping system is called__________.
b) Data can be stored either in __________ or in __________ form.
c) DBMS stands for __________
d) When multiple copies of data mismatch the data is called __________.
e) The tables in an RDBMS is called __________.
f) The set of allowed values for an attribute is called__________.
g) A row in a relation is also called as a __________ or a __________.
h) Data in a database is stored inside __________.
i) A collection of fields representing a complete unit of information is called __________.
j) The collection of data on a particular topic is stored as a __________.
k) Th
l) e real world object about which we store information inside our database is called __________.
10. State whether true or false.
a) Data is more important than information.
b) DBMS provides limited data sharing.
c) Multiple copies of same data is called inconsistent data.
d) RDBMS is the most popular database model.
e) A flat file is different from a database file.
f) Information is to be processed to get the desired data.
11. Explain the differences.
a) Data and information
b) Flat file and Database
c) Primary key and Candidate key.
d) Attributes and records
e) Data redundancy and data inconsistency.

12. Consider the following tables: Student and Class for answering the questions below.

Student
 Class

a. Identify the key and the non-key attributes of Student table.

b. Identify the most suitable attribute for Primary key for student table.
c. List the alternate keys for Student table.
d. Which attribute can be selected as the foreign key for Student table?
e. What is the domain of the field Class in Class table?
f. What is the difference between the degree and cardinality of the Student relation?
13. What is DBMS? List out and explain the features and needs of DBMS. Give some examples of DBMS software.
14. Explain why database management system is preferred over file management system.
15. Discuss the concept of foreign key in detail.