LNCT College of Technology, Bhopal

BT-205[Basic Computer Engineering]

UNIT- V(DBMS Notes)

Database:
A database is a collection of information that is organized so that it can
easily be accessed, managed, and updated.
Database Management System (DBMS):
DataBase Management System (DBMS) is a software package that allows
data to be effectively stored, retrieved and manipulated and the data stored in
a DBMS packege can be accessed by multiple users and by multiple
application programs like (SQL Server, Oracle, Ms-Access) .

Comparison of Traditional File-Based Approach and Database

Approach
At the beginning, you should understand the rationale of replacing the
traditional file-based system with the database system.

File-based System
File-based systems were an early attempt to computerize the manual filing
system. File-based system is a collection of application programs that
perform services for the end-users. Each program defines and manages its
data.
However, five types of problem are occurred in using the file-based
approach:

1. Separation and isolation of data

When data is isolated in separate files, it is more difficult for us to access
data that should be available. The application programmer is required to
synchronize the processing of two or more files to ensure the correct data is
extracted.

2. Duplication of data
When employing the decentralized file-based approach, the uncontrolled
duplication of data is occurred. Uncontrolled duplication of data is
undesirable because:

i. Duplication is wasteful
ii. Duplication can lead to loss of data integrity
 3. Data dependence
Using file-based system, the physical structure and storage of the data files
and records are defined in the application program code. This characteristic
is known as program-data dependence. Making changes to an existing
structure are rather difficult and will lead to a modification of program.
Such maintenance activities are time-consuming and subject to error.

4. Incompatible file formats

The structures of the file are dependent on the application programming
language. However file structure provided in one programming language
such as direct file, indexed-sequential file which is available in COBOL
programming, may be different from the structure generated by other
programming language such as C. The direct incompatibility makes them
difficult to process jointly.

Fixed queries / proliferation of application programs

File-based systems are very dependent upon the application programmer.
Any required queries or reports have to be written by the application
programmer. Normally, a fixed format query or report can only be
entertained and no facility for ad-hoc queries if offered.

Database Approach:
In order to overcome the limitations of the file-based approach, the concept
of database and the Database Management System (DMS) was emerged in
60s.

Advantages
A number of advantages of applying database approach in application system
are obtained including:

1. Control of data redundancy

The database approach attempts to eliminate the redundancy by integrating
the file. Although the database approach does not eliminate redundancy
entirely, it controls the amount of redundancy inherent in the database.

2. Data consistency
By eliminating or controlling redundancy, the database approach reduces the
risk of inconsistencies occurring. It ensures all copies of the data are kept
consistent.
3. More information from the same amount of data
With the integration of the operated data in the database approach, it may be
possible to derive additional information for the same data.

4. Sharing of data
Database belongs to the entire organization and can be shared by all
authorized users.

5. Improved data integrity

Database integrity provides the validity and consistency of stored data.
Integrity is usually expressed in terms of constraints, which are consistency
rules that the database is not permitted to violate.

6. Improved security
Database approach provides a protection of the data from the unauthorized
users. It may take the term of user names and passwords to identify user
type and their access right in the operation including retrieval, insertion,
updating and deletion.

7. Enforcement of standards
The integration of the database enforces the necessary standards including
data formats, naming conventions, documentation standards, update
procedures and access rules.

8. Economy of scale
Cost savings can be obtained by combining all organization's operational
data into one database with applications to work on one source of data.

9. Balance of conflicting requirements

By having a structural design in the database, the conflicts between users or
departments can be resolved. Decisions will be based on the base use of
resources for the organization as a whole rather that for an individual entity.

10. Improved data accessibility and responsiveness

By having an integration in the database approach, data accessing can be
crossed departmental boundaries. This feature provides more functionality
and better services to the users.

11. Increased productivity

The database approach provides all the low-level file-handling routines. The
provision of these functions allows the programmer to concentrate more on
the specific functionality required by the users. The fourth-generation
environment provided by the database can simplify the database application
development.

12. Improved maintenance

Database approach provides a data independence. As a change of data
structure in the database will be affect the application program, it simplifies
database application maintenance.

13. Increased concurrency

Database can manage concurrent data access effectively. It ensures no
interference between users that would not result any loss of information nor
loss of integrity.

14. Improved backing and recovery services

Modern database management system provides facilities to minimize the
amount of processing that can be lost following a failure by using the
transaction approach.

Disadvantages
In split of a large number of advantages can be found in the database
approach, it is not without any challenge. The following disadvantages can
be found including:

1. Complexity
Database management system is an extremely complex piece of software.
All parties must be familiar with its functionality and take full advantage of
it. Therefore, training for the administrators, designers and users is required.

2. Size
The database management system consumes a substantial amount of main
memory as well as a large number amount of disk space in order to make it
run efficiently.

3. Cost of DBMS
A multi-user database management system may be very expensive. Even
after the installation, there is a high recurrent annual maintenance cost on the
software.
4. Cost of conversion
When moving from a file-base system to a database system, the company is
required to have additional expenses on hardware acquisition and training
cost.

5. Performance
As the database approach is to cater for many applications rather than
exclusively for a particular one, some applications may not run as fast as
before.

6. Higher impact of a failure

The database approach increases the vulnerability of the system due to the
centralization. As all users and applications reply on the database
availability, the failure of any component can bring operations to a halt and
affect the services to the customer seriously.

DBMS Architecture:
1. External view: This is a highest level of abstraction as seen by user. This
level of abstraction describes only the part of entire database. It is based on
the conceptual model, is the end user view of data environment. Each
external view described by means of a schema called an external schema or
subschema.

2. Conceptual level: At this level of database abstraction all the database

entities and the relationships among them are included. One conceptual view
represents the entire database. the conceptual schema defines ths conceptual
view.

3. Internal(physical) level : This lowest level of abstraction. it closest to

physical storage device. It describes how data are actually stored on the
storage medium. The internal schema, which contains the definition of the
stored record, the method representing the data fields, expresses the internal
view and the access aids used.
Data Independence:
1. The ability to modify a scheme definition in one level without affecting a
scheme definition in a higher level is called data independence.

2. There are two kinds:

o Physical data independence

 The ability to modify the physical scheme without causing

application programs to be rewritten

 Modifications at this level are usually to improve performance

o Logical data independence

 The ability to modify the conceptual scheme without causing

application programs to be rewritten

 Usually done when logical structure of database is altered

3. Logical data independence is harder to achieve as the application

programs are usually heavily dependent on the logical structure of the
 data. An analogy is made to abstract data types in programming
languages.
Types of Database Users:
Users are differentiated by the way they expect to interact with the system:
4. · Application programmers - interact with system through DML calls.
5. · Sophisticated users - form requests in a database query language.
6. · Specialized users - write specialized database applications that do not fit into the
traditional data processing framework.
7. ·Naive users - invoke one of the permanent application programs that have been
written previously.

Database models
 E-R Model
 Relational Model
 Network Model
 Hierarchy model

Relational Model: It is an implementation model first go through the ER model

SSN Name Address

111 Jay Bhopal
123 Prakash Bly
126 Deep Berly
128 Ram Mumbai

Entity Set- Table

Entity- Row
Attribute-column

Entity Relationship (ER) Model

The most popular high-level conceptual data model is the ER model. It is frequently used
for the conceptual design of database applications.

The diagrammatic notation associated with the ER model, is referred to as the ER

diagram. ER diagrams show the basic data structures and constraints.
Entity Types, Entity Sets, Attributes and Keys
3. The basic object of an ER diagram is the entity. An entity represents a ‘thing’ in the
real world.
4. Examples of entities might be a physical entity, such as a student, a house, a product

SSN Name Address

111 Jay Bhopal
123 Prakash Bly
126 Deep Berly
128 Ram Mumbai
etc, or conceptual entities such as a company, a job position, a course, etc.
5. Entities have attributes, which basically are the properties/characteristics of a
particular entity.

Examples of entities and attributes:

There are several types of entities. Including:

 Simple vs. Composite
 Single-valued vs. Multi-valued
 Stored vs. Derived

Simple vs. Composite Attributes

 Composite attributes can be divided into smaller subparts, which represent more basic
attributes, which have their own meanings.

 A common example of a composite attribute is Address. Address can be broken down

into a number of subparts, such as Street Address, City, Postal Code. Street Address
may be further broken down by Number, Street Name and Apartment/Unit number.

 Attributes that are not divisible into subparts are called simple or atomic attributes.

 Composite attributes can be used if the attribute is referred to as the whole, and the
atomic attributes are not referred to. For example, if you wish to store the Company
Location, unless you will use the atomic information such as Postal Code, or City
separately from the other Location information (Street Address etc) then there is no
need to subdivide it into its component attributes, and the whole Location can be
designated as a simple attribute.
 What are examples of other composite attributes?

Single-Valued vs. Multi-valued Attributes

 Most attributes have a single value for each entity, such as a car only has one model, a
student has only one ID number, an employee has only one data of birth. These
attributes are called single-valued attributes.
 Sometimes an attribute can have multiple values for a single entity, for example, a
doctor may have more than one specialty (or may have only one specialty), a
customer may have more than one mobile phone number, or they may not have one at
all. These attributes are called multi-valued attributes.
 Multi-valued attributes may have a lower and upper bounds to constrain the number
of values allowed. For example, a doctor must have at least one specialty, but no
more than 3 specialties.

Stored vs. Derived Attributes

7. If an attribute can be calculated using the value of another attribute, they are called
derived attributes.
8. The attribute that is used to derive the attribute is called a stored attribute.
9. Derived attributes are not stored in the file, but can be derived when needed from the
stored attributes.

Null Valued Attributes

 There are cases where an attribute does not have an applicable value for an attribute.
For these situations, the value null is created.
 A person who does not have a mobile phone would have null stored at the value for
the Mobile Phone Number attribute.
 Null can also be used in situations where the attribute value is unknown. There are
two cases where this can occur, one where it is known that the attribute is valued, but
the value is missing, for example hair color. Every person has a hair color, but the
information may be missing. Another situation is if mobile phone number is null, it is
not known if the person does not have a mobile phone or if that information is just
missing.
Complex Attributes
 Complex attributes are attributes that are nested in an arbitrary way.
 For example a person can have more than one residence, and each residence can have
more than one phone, therefore it is a complex attribute that can be represented as:
 {Multi-valued attributes are displayed between braces}
 (Complex Attributes are represented using parentheses)

E.g.
{AddressPhone({Phone(AreaCode, PhoneNumber)}, Address(StreetAddress(Number,
Street, ApartmentNumber), City, State, Zip))}

Entity Types, Entity Sets, Keys and Value Sets

Entity Types and Entity Sets
 An entity type defines a collection of entities that have the same attributes. Each
entity type in the database is described by its name and attributes. The entity
share the same attributes, but each entity has its own value for each attribute.

Entity Type Example:

 Entity Type:
Student
 Entity Attributes:
StudentID,
Name,
Surname,
Date of Birth,
Department
 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 type (Student) and the entity set
(Student) can be referred to using the same name.

Entity Set Example:

 Entity Type: Student
 Entity Set:
[123, John, Smith, 12/01/1981, Computer Technology]
[456, Jane, Doe, 05/02/1979, Mathematics]
[789, Semra, Aykan, 02/08/1980, Linguistics]
The entity type describes the intension, or schema for a set of entities that share the same
structure. The collection of entities of a particular entity type is grouped into the entity
set, called the extension.

Key Attributes of an Entity Type

1. An important constraint on entities of an entity type is the uniqueness constraint.
2. A key attribute is an attribute whose values are distinct for each individual entity
in the entity set.
3. The values of the key attribute can be used to identify each entity uniquely.
4. Sometimes a key can consist of several attributes together, where the combination
of attributes is unique for a given entity. This is called a composite key.
5. Composite keys should be minimal, meaning that all attributes must be included
to have the uniqueness property.
6. Specifying that an attribute is a key of an entity type means that the uniqueness
property must hold true for every entity set of the entity type.
7. An entity can have more than one key attribute, and some entities may have no
key attribute. Those entities with no key attribute are called weak entity types.

Value Sets (Domains) of Attributes

 Each simple attribute of an entity is associates with a domain of values, or value set,
which specifies the set of values that may be assigned to that attribute for each entity.
For example, date of birth must be before today’s date, and after 01/01/1900, or the
Student Name attribute must be a string of alphabetic characters.
 Value sets are not specified in ER diagrams.

ER Diagram Notation

- Entity

- Attribute

- Multi-valued Attribute

- Composite Attributes
Key Attributes

Company Database Example

The company database keeps track of a company’s employees, departments and projects.
Suppose that after the requirements collection and analysis phase, the database designers
provided the following description of the part of the company to be represented by the
database.

5. The company is organized into department. Each department has a unique name a
unique number and a particular employee who manages the department. We keep
track of the start date when that employee began managing the department. A
department may have several locations.
6. A department controls a number of projects, each of which has a unique name, a
unique number and a single location.
7. We store each employees name, ID number, address, salary, sex and birth date.
An employee is assigned to one department but may work on several projects,
which are not necessarily controlled by the same department. We keep track of
the number of hours per week that an employee works on each project. We also
keep track of the direct supervisor of each employee.
8. We want to keep track of the dependents of each employee for insurance purposes.
We keep each dependent’s first name, sex, birth date and relationship to the
employee.

From the information given above, we can identify 4 entities.

1. Department – Name, Number, Locations, Manager, and Manager Start Date.

2. Project – Name, Number, Location and Controlling Department.
3. Employee – Name, ID Number, Sex, Address, Salary, Birth Date, Department. Both
Name and Address can be a composite attribute, however it was not specified in the
requirements.
4. Dependent – Employee, Dependent Name, Sex, Birth Date, Relationship
The information about the projects an employee works on can be represented in two
ways. One, we can include a multi-valued composite, attribute, WorksOn(Project, Hours)
in the Employee entity, or we can include a multi-valued composite attribute,
Workers(Employee, Hours).

Relationship Types, Relationship Sets, Roles and Structural Constraints

8. Looking at the example above, there are several implicit relationships among the
entity types.
9. Whenever an attribute of one entity type refers to another entity type, some
relationship exists.
10. For example, Manager of a department refers to an employee who manages the
department, Controlling Department of the project, refers to the department that
controls the project. Supervisor of an employee refers to the employee who
supervises that employee.
11. In an ER diagram, these references are not represented as attributes, but as
relationships.
Relationship Types, Sets and Instances
15. A relationship type, R, among entities, defines a relationship set among entities from
the entity types.

Role Names
16. Each entity type that participates in a relationship type plays a particular role in the
relationship.
17. The role name shows the role that an particular entity from the entity type plays in
each relationship.
18. Example: In the Company diagram, in the Works For relationship type, the employee
plays the role of employee or worker, and the department entity plays the role of
department or employer.
19. In some cases the same entity participates more than once in a relationship type in
different roles.
20. For example, the Supervision relationship type relates an employee to a supervisor,
where both the employee and supervisor are of the same employee entity type,
therefore the employee entity participates twice in the relationship, once in the role of
supervisor, and once in the role of supervise.
Database Languages:(SQL- Structured Query Language)

 DDL(Data Definition Language)

  DML(Data Manipulation Language)
 DCL(Data Control Language)
 TCL(Transaction Control Language)

DDL:
Data Definition Language (DDL) is a standard for commands that define the different
structures in a database. DDL statements create, modify, and remove database objects
such as tables, indexes, and users.
 CREATE - to create a database and its objects like (table, index, views, store
procedure, function, and triggers)

 ALTER - alters the structure of the existing database

 DROP - delete objects from the database
 TRUNCATE - remove all records from a table, including all spaces allocated for
the records are removed
 COMMENT - add comments to the data dictionary
 RENAME - rename an object

DML
DML is short name of Data Manipulation Language which deals with data
manipulation and includes most common SQL statements such SELECT, INSERT,
UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data
in a database.

 SELECT - retrieve data from a database

 INSERT - insert data into a table
 UPDATE - updates existing data within a table
 DELETE - Delete all records from a database table
 MERGE - UPSERT operation (insert or update)
 CALL - call a PL/SQL or Java subprogram
 EXPLAIN PLAN - interpretation of the data access path
 LOCK TABLE - concurrency Control
DCL
DCL is short name of Data Control Language which includes commands such as
GRANT and mostly concerned with rights, permissions and other controls of the database
system.

 GRANT - allow users access privileges to the database

 REVOKE - withdraw users access privileges given by using the GRANT
command

TCL
TCL is short name of Transaction Control Language which deals with a transaction within
a database.

 COMMIT - commits a Transaction

 ROLLBACK - rollback a transaction in case of any error occurs
 SAVEPOINT - to rollback the transaction making points within groups
 SET TRANSACTION - specify characteristics of the transaction

Primary Key:-A primary key is a special relational database table column (or combination
of columns) designated to uniquely identify all table records.
A primary key’s main features are:

It must contain a unique value for each row of data.

It cannot contain null values.

A primary key is either an existing table column or a column that is specifically generated
by the database according to a defined sequence.

Database Administrator Roles and Responsibilities:

A Database Administrator, Database Analyst or Database Developer is the person

responsible for managing the information within an organization. As most companies
continue to experience inevitable growth of their databases, these positions are probably
the most solid within the IT industry. In most cases, it is not an area that is targeted for
layoffs or downsizing. On the downside, however, most database departments are often
understaffed, requiring administrators to perform a multitude of tasks.

Depending on the company and the department, this role can either be highly specialized
or incredibly diverse. The primary role of the Database Administrator is to adminster,
develop, maintain and implement the policies and procedures necessary to ensure the
security and integrity of the corporate database. Sub roles within the Database
Administrator classification may include security, architecture, warehousing and/or
business analysis. Other primary roles will include:

 Implementation of data models

 Database design
 Database accessibility
 Performance issues
 Capacity issues
 Data replication
 Table Maintainence

Elements of Database System:

 Database schema
 Schema objects
 Indexes
 Tables
 Fields and columns
 Records and rows
 Keys
 Relationships
 Data types