BIT2103 - Database Management Systems

CHAPTER 1
Data and Database Management

Bachelor of Information Technology

BIT2103 - Database Management Systems
Course Description
This course introduces database design and creation using
a DBMS product. Emphasis is on data dictionaries,
normalization, data integrity, data modeling, and creation
of simple tables, queries, reports, and forms. Upon
completion, students should be able to design and
implement normalized database structures by creating
simple database tables, queries, reports, and forms.

Bachelor of Information Technology

BIT2103 - Database Management Systems
Course Objectives

• By the end of this course unit, students should be able

to:
• Discuss the physical database design process of
producing an efficient and tuned database;
• Explain when de-normalization is preferred over
normalization, and use vertical and horizontal
partitioning for data distribution;
• Elaborate on data storage and indexing options, and
perform query optimization

Bachelor of Information Technology

BIT2103 - Database Management Systems
Course Learning Outcomes

At the end of this course unit, students should be able to:

• Analyze the data and data organization needs of organizations;
• Apply the Entity-Relationship (E-R) Model for building information
systems' data models;
• Transform an E-R diagram into a relational model, and use
normalization to create a database relational schema;
• Use SQL for database creation, manipulation, and control;
• explain the client/server model, and describe the key components used to
implement internet database environments;
• perform basic database administration tasks
• design and implement normalized database structures by creating simple
database tables, queries, reports, and forms.

Bachelor of Information Technology

BIT2103 - Database Management Systems
Unit 1 - Introduction to Databases
Unit 2 - Data Modeling and Functional Dependency:
Unit 3 - Functional Dependency and Normalization:
Unit 4 - Higher Normal Forms and Transaction Management:
Unit 5 - SQL:
Extraction from Database,
Demonstrations

Bachelor of Information Technology

 1 •

Data Base &Database Management Syste

___________Overview____________
DBMS
These DBMS Notes provide basic and advanced
concepts of Database. The notes have been organized
designed for beginners and professionals (both).
Database management system is software that is used
to manage the database.
The DBMS notes include all topics of DBMS such as
introduction, ER model, keys, relational model, join
operation, SQL, functional dependency, transaction,
concurrency control,
etc._______________________________________________
 Data Base &Database Management System
3

What is Database
The database is a collection of inter-related data
which is used to retrieve, insert and delete the data
efficiently. It is also used to organize the data in the
form of a table, schema, views, and reports, etc.
> For example: The college Database organizes the
data about the admin, staff, students and faculty etc.
> Using the database, you can easily retrieve,
insert,edit and delete the information.
4

Database Management System

> Database management system is a software which is used
to manage the database. For example: MySQL, Oracle, etc
are a very popular commercial database which is used in
different applications.
> DBMS provides an interface to perform various operations
like database creation, storing data in it, updating data,
creating a table in the database and a lot more.
> It provides protection and security to the database. In the
case of multiple users, it also maintains data consistency.
5

DBMS allows users the following tasks:

> Data Definition: It is used for creation,
modification, and removal of definition that defines
the organization of data in the database.
> Data Updation: It is used for the insertion,
modification, and deletion of the actual data in the
database.
6 DBMS allows users the following tasks:
> Data Retrieval: It is used to retrieve the data from the

database which can be used by applications for various

purposes.
> User Administration: It is used for registering and
monitoring
> users, maintain data integrity, enforcing data security,
dealing with concurrency control, monitoring
performance and recovering information corrupted by
unexpected failure.
7

Characteristics of DBMS
> It uses a digital repository established on a server to store
and manage the information.
> It can provide a clear and logical view of the process that
manipulates data.
> DBMS contains automatic backup and recovery procedures.
> It can reduce the complex relationship between data.
> It is used to support manipulation and processing of data.
> It is used to provide security of data.
> It can view the database from different viewpoints

according to the requirements ofthe user.

T 2
8

Advantages of DBMS
> Controls database redundancy: It can control data
redundancy because it stores all the data in one single
database file and that recorded data is placed in the
database.
> Data sharing: In DBMS, the authorized users of an
organization can share the data among multiple users.
> Easy Maintenance: It can be easily maintainable due to the
centralized nature of the database system.
9

> Reduce time: It reduces development time and

maintenance need.
> Backup: It provides backup and recovery subsystems
which create automatic backup of data
from hardware and software failures and restores the data
if required.
> multiple user interface: It provides different types of
user interfaces like graphical user interfaces, application
program interfaces
10

Disadvantages of DBMS
> Cost of Hardware and Software: It requires a high
speed of data processor and large memory size to
run DBMS software.
> Size: It occupies a large space of disks and large
memory to run them efficiently.
11

> Complexity: Database system creates additional

complexity and requirements.
> Higher impact of failure: Failure is highly
impacted the database because in most of the
organization, all the data stored in a single database
and if the database is damaged due to electric failure
or database corruption then the data may be lost
forever.
12
Design
What is Database Design?
Database design is a collection of steps that help
create, implement, and maintain a business's data
management systems. The primary purpose of
designing a database is to produce physical and logical
models of designs for the proposed database system.
What is a Good Database Design?
UNIVERSITY
13 Livu

A good database design process is governed by specific rules.

The first rule in creating a database design is to avoid data

redundancy. It wastes space and increases the probability of
faults and discrepancies within the database.

The second rule is that the accuracy and comprehensiveness of

information are imperative.

A database containing erroneous information will lead to

inaccurate analysis and reporting
What is a Good Database Design? (cont.)
14

Consequently, it can mislead decision-makers and

adversely affect a company's performance.

Therefore, it's important to keep things rules in mind

when designing the database for your organization.
Characteristics of a good Database Design
A well-designed database is one that:
> Distributes
your data into tables based on specific subject areas to
decrease data redundancy
> Delivers the database the information needed to link the data in the
tables
> Provides support and guarantees the precision and reliability of data
> Caters to your information processing and reporting requirements
> Functions interactively with the database operators
Importance of Database Design
> Database design defines the database structure used
for planning, storing, and managing information. In
order to ensure data accuracy, you must design a
database that only stores relevant and valuable
information.
> A well-designed database is essential to guarantee
information consistency, eliminate redundant data,
efficiently execute queries, and improve the database's
performance._________________________
Importance of Database Design(cont.)
17

The reliability of data depends on the table structure,

whereas creating primary and unique keys guarantees
uniformity in the stored information. You can avoid
data replication by forming a table of probable values
and using a key to denote the value.
So, whenever the value changes, the alteration
happens only once in the main table.
 Database Development Life Cycle
18
09
UNIVERSITY

There are various stages in database development.

However, it is not necessary to follow each of the
steps sequentially. The life cycle can be divided into
three phases:
> requirement analysis,
> database designing,
> implementation.

© ISBAT UNIVERSITY - 2020. 12/1/2021

1- Requirement Analysis
19
UNIVERSITY
Livu

Requirement analysis requires two steps:

• Planning: In this stage of database development, the

plan of the entire Database Development Life Cycle
is decided. It also requires an analysis of the
organization's information systems strategy.
• Defining the system: This stage explains and lays out
the proposed database system's scope.
2- Database designing
20

The actual database designing takes into account two key models:

> Logical model: It is concerned with using the given requirements to

create a database model. The complete design is laid out on paper at
this stage, without considering any specific database management
system (DBMS) requirement or physically implementing it.
> Physical model: This stage comes after the logical model and
therefore involves physically implementing the logical model. It takes
the DBMS and other physical implementation factors into
consideration.
 3- Implementation X|
A'
x;
.1 ~ r
\°0°
J

£2- ISBAT^jj
UNIVERSITY
21 Livu

The implementation phase of the database

development life cycle is concerned with:

• Data conversion and loading: It involves importing

and converting data from the old system into the new
database.
• Testing: Finally, this stage identifies errors in the new
system and ensures all the database requirement
© ISBAT UNIVERSITY -2020. 12/1/2021

specifications are met.

 22
Database Designing Techniques
The two most common techniques used to design a database
include:
> Normalization: Tables are organized in such a way that it decreases
data redundancy and dependency. Larger tables are divided into
smaller tables and are linked together using relationships.
» Entity-Relationship (ER) Modelling: A graphical database design
approach models entities, attributes and defines relationships among
them to signify real-life objects. An entity is any real-world item that's different or
unique from the surroundings.
 How to Design Database:
23

The first question you need to ask when designing a

database is, how will you specify the structure of the
database? Database designing generally starts with
identifying the purpose of your database. The relevant
data is then collected and organized into tables.
Next, you specify the primary keys and analyze
relationships between different tables for an efficient data design.
After refining the tables, the last step is to apply normalization rules for
table standardization.
 Steps of Designing Database
24

1
-Define the objective of your database The
first step is to determine the purpose of your database.
For example, if you are running a small home-based
business, you can design a customer database that
maintains a list of consumer info to generate emails
and reports.
Hence, understanding the importance of a database is
vital.
2- Locate and consolidate the necessary data
25

The next step is to collect all kinds of information you

might want to store in the database. Begin with the
existing data and mull over the questions you want
your database to answer.
It will help you decide which data needs to be
recorded.
 3-Distribute the data into tables

26

Once you have amassed all the necessary data items,

the next step is to divide them into main entities or
subject areas. For example, if you are a retailer, some
of your main entities could be products, customers,
suppliers, and orders.
Each entity will then become a separate table.
 4- Change data items into columns
27

Data is segregated into tables, such that every data

item becomes a field and is shown as a column. For
instance, a customer table might include fields like
name, address, email address, and city.
After determining the preliminary set of columns for
every table, you can refine them.
. For instance, you can record customer names in two
distinct columns: first name and last name
4- Change data items into columns (cont.)
28

Likewise, you can store the address in five distinct

columns based on address, town, state, zip code, and
region. It will make it more convenient for you to filter
information
 5- Identify primary keys
29

The next step to improve your database design is to

select a primary key for every table. This primary key
is a column or a set of columns used to pinpoint each
row distinctively. For instance, in your customer table,
the primary key could be customer ID. It will allow
you to identify unique rows based on the customer ID.
 5- Identify primary keys (cont.)
30

More than one primary key can also exist, called a composite
key, including multiple columns. For example, in your Order
Details table, the primary keys could be order ID and product
ID. The composite key can be made using fields with similar
or varying data types.

Similarly, if you wish to get an idea of your product sales, you

can identify the product ID from the Products table and the
order number or ID from the Orders table.
 Determine how tables are related
31

After dividing data into tables, information needs to be

brought together in a meaningful manner. So, you can
explore each table and identify the connection between
the tables. If needed, you can add fields or form new
tables to simplify the relationship based on the types
of information.

Below is an example of different entity types

and relationship types.
 Entity type Relationship type Entity type

Branch Has Staff

Staff Manages Proper ty_for_R e nt
SupervisedBy Supervisor
SupponedBy Secretary
SetsUp Interview
Organizes I A.rasc_Agreemenl
CarryOut Inspection
Supervisor Supervises Staff
Property_foi_Rcnt IsAva ila bleA t Branch
MdnagedBy Staff
OwnedBy Owner

Pri vatejD wncr Owns Propcrty_for_Rc nt

Rusiness_Owner Owns Proper ty_for_R e nt
Advert Describes Property_£or_Rent
Place din Newspaper
Interview With Client
Cl iert PropertyforR e nt
Proper tyforR e nt
Holds Leasc_ Agreement
Le ase_ A gr eenie nt AssociatedWith Properly for_Re nt
Inspection Made Of Property_for_Re
Database nt
Design Methodology - 12

© ISBAT UNIVERSITY - 2020. 12/1/2021

33

In this step, you will create one-to-one, one-to-many,

and/or many-to-many relationships between different
table entries.
When a single item from a table is associated with an item
from another table, it's called a one-to-one (1:1) relationship.
In a one-to-many (1:M) relationship, an item in one table is
related to many items in the other table, such as one customer
placing several orders. A many-to-many (M:N) relationship
occurs if more than one table item is related to many items in
the other table.
34
 Enhance your database design
35

Now that you have all the required tables, fields, and
relationships, the next step is to refine your database
design by creating and populating your tables with
mockup information. Experiment with the sample data
by running queries or adding new items. It will help
you analyze your design for faults, and you will be
able to highlight possible errors. If needed, adjust your
design to mitigate those problems.
 Implement the normalization rules
36

The last step is to implement the normalization rules

for your database design. A systematic approach
removes redundancy and unwanted characteristics,
such as Insertion, Update, and Deletion irregularities.

The multi-step process stores data in a tabular form,

which helps eliminate redundant data from the relation
tables.
37

Thank you