Database
Management System
(DBMS)
Introduction:
It is a software that permits an organization to centralize data,
manage them efficiently and provide access to the stored data by
application program.
It acts and an interface between application program and the physical
data files.
When the application program calls for a data item, such as gross
pay, the DBMS finds this item in the database and presents it to the
application program.
DBMS is a set of programs that provide users with tools to create and
manage database.
Continued:
Managing a database refers to the process of adding, deleting,
accessing, modifying and analyzing data stored in a database.
An organization can access the data by using query and reporting
tools that are part pf the DBMS.
DBMS also provide the mechanism for maintaining the integrity of
stored data, managing security, user access and recovering info if
system fails.
Relational Database Model:
It is based on the concept of 2- dimensional tables.
It is designed with a number of related tables.
Each of these tables contains records and attributes.
Records – listed in rows
Attributes - listed in columns.
These related tables can be joined when they contain common
columns.
Examples: Microsoft access , Oracle.
Every record in the database must contain at least one field that
uniquely identifies that record so that it can be retrieved, updated
and sorted.
Relational Database Tables:
Keywords:
1)Flat File:
One big table, that contains all of the records and attributes.
Such a design would entail for too much data redundancy.
2) Data Model:
It is a diagram that represents entities in a database and their
relationships.
3)Entity:
An entity is a person, place, thing or event- such as a customer, an
employee or a product about which information is maintained.
Entities are typically identified in the users work environment.
A record generally describes an entity.
Keywords:
4)Attribute:
It is the identifier field.
Each character or quantity of a particular entity is called an attribute.
Attributes describe the entity’s characteristics.
Ex: a) Entity: Customer ; Attribute: Customer name
b)Entity: An employee ; Attribute : Employee number
c) Entity : A product ; Attribute: Product color.
Keys used in Model:
I ) Primary key:
This identifier field( or attributes) is called the primary key.
Ex: Enrollment number is used as primary key.
II) Secondary Key:
It is another field that has some identifying info, but typically does not
identify the record with complete accuracy.
Eg: Major project – number/topic/batch
Secondary key should not be the primary key, because many students can
have the same major.
Therefore, it cannot be uniquely identify an individual student.
III) Foreign key:
It is a field( or group of fields) in one table that uniquely identifies a row
of another table.
It is used to establish and enforce a link between two tables.
Advantages:
The uniqueness of the primary key tells the DBMS which records are
joined with other in related tables.
This feature allows user great flexibility in the variety of queries they
can make.
Disadvantages:
Because large-scale database can be composed of many interrelated
tables, the overall design can be complex, leading to slow search and
access times.
Need of Data warehouses and Data
mart
Need an organised way to make access of info easy.
Transactional databases are designed to be updated.
The organization databases are designed to process millions of
transactions every day.
Therefore complicated queries might take a longtime to answer, and
they also
might degrade performance of the databases.
Transactional databases are designed to access a single record at a
time.
Data Warehouse
A data warehouse is a database that stores current and
historical data of potential interest to decision makers
throughout the company.
The data originate in many core operational transactions, customer
accounts and manufacturing and may include data from web site
transactions.
The data warehouse consolidates and standardize information
from different operational databases so that the information can be
used across the enterprise for management analysis and decision
making.
They are designed to access large group of related records.
Because data warehouses are too expensive they are used primarily
by large companies.
Data Mart
A data mart is a low-cost, scaled- down version of a data warehouse
that is designed for the end user, needs in a strategic business
unit(SBU) or an individual department.
It support local rather than central control by conferring power on the
user group.
Generally; groups that need a single or a few business intelligence
applications, require only a data mart, rather than a data warehouse.
Difference between Data warehouse
and Data Mart
Basic characteristics:
1) Organized by business dimension or subjects:
Data are organized by subject.
This arrangement differs from transactional systems, where data are
organized by business process such as order entity, inventory,
control and accounts receivable.
2) Use online analytical process:-
Database we online transaction processing where business transactions are
processed online as soon as they occur.
The objective are speed and efficiency.
Data warehouse are designed to support decision makers but not OLTP, use
online analytical processing.
Use online analytical process:-
Database we online transaction processing where business transactions are processed
online as soon as they occur.
The objective are speed and efficiency.
Data warehouse are designed to support decision makers but not OLTP, use online
analytical processing.
Meta Data:
It is important to maintain data about the data, known as meta data,
in the data warehouse.
Both IT personnel who operate and manage the data warehouse and
the user who access the data need metadata.
It personnel need information about data sources; database; table
and column names; refresh schedules and data usage measures.
