=Databases
L01 – Introduction to Databases
Outline
• Definitions
• Types of Data Stored
• DBMS
• Types of Databases
• Structure of Data
• File Systems
• Database Systems
• Database Design
2
Databases
• Databases are specialized structures that
allow computer-‐based systems to store,
manage, and retrieve data very quickly.
(Coronel, Morris & Rob)
• The database is a shared, integrated computer
structure that stores a collection of end-‐user
data and metadata.
• Repository of data
3
Types of Data
Stored
• End-user data
– Raw facts stored by the user
– Example: employee or customer details
• Metadata
– Data about data
– Shows characteristics of end-‐user data and how
they are stored in the database
– Enables management of the end user data in the
database
4
Database Management System
(DBMS)
• DBMS is a collection of programs that
manages the database structures and controls
how the stored data are accessed
• DBMS is the platform used by end-‐users to
access the database
• DBMS are written by programmers using Java,
C# or other programming languages
5
Database Management System (DBMS)
• DBMS contains information about a particular enterprise
• Collection of interrelated data
• Set of programs to access the data
• An environment that is both convenient and efficient to use
• Database Applications:
• Banking: transactions
• Airlines: reservations, schedules
• Universities: registration, grades
• Sales: customers, products, purchases
• Online retailers: order tracking, customized recommendations
• Manufacturing: production, inventory, orders, supply chain
• Human resources: employee records, salaries, tax deductions
• Databases can be very large.
• Databases touch all aspects of our lives
University Database Example
⚫ Application program examples
– Add new students, instructors, and courses
– Register students for courses, and generate class
rosters
– Assign grades to students, compute grade point
averages (GPA) and generate transcripts
⚫ In the early days, database applications were
built directly on top of file systems
Advantages of DBMS
• Manages interaction between end-‐users and
the database
• Hides complexities of the database internal
structure from the end user
• Enables data in the database to be shared by
multiple users
• Each user can have their own user view
specific to their needs
• Provides improved data security
8
Role of the DBMS
9
Functions of DBMS
• Data dictionary management
• Data storage management
• Data transformation and presentation
• Security management
• Multiuser access control
• Backup and recovery management
• Data integrity management
10
Types of
Databases
• Database classification based on number of
users:
– Single-‐user database
– Multiuser database
– Desktop database
– Workgroup database
• Classification based on location of data:
– Centralized database
– Distributed database
11
Types of Databases
cntd
• Classification based on type of data:
– General-‐purpose databases
– Discipline specific databases
• Classification based on how data are intended to
be used
– Operational database or Online transaction
processing (OLTP)
– Analytical database
• Data warehouse
• Online analytical processing (OLAP)
12
Structure of
data
• Databases can be classified based on the
degree to which the data are structured
– Unstructured data
– Structured data
– Semi-‐structured data
• Extensible Markup Language (XML) Database
– Supports the storage and management of semi-‐
structured XML data
13
Some Popular
DBMS
• MS Access
• MS SQL Server
• IBM DB2
• MySQL
• Oracle RDBMS
• OpenOffice Base
14
File
System
• The database evolved from the file system (manual and
computerized) used to store and process data in the past
• Owing to the limitations of the file system, the database
system was created
• Some of the limitations were:
– Prolonged development time/too much programming
– Slow response to reports request
– Difficult systems administration
– Poor data security
– Limited data sharing
– Structural dependency
– Data redundancy
15
Drawbacks of using file systems to store data
– Data redundancy and inconsistency
⚫ Multiple file formats, duplication of information in
different files
– Difficulty in accessing data
⚫ Need to write a new program to carry out each new
task
– Data isolation — multiple files and formats
– Integrity problems
⚫ Integrity constraints (e.g., account balance > 0)
become “buried” in program code rather than being
stated explicitly
⚫ Hard to add new constraints or change existing ones
Drawbacks of using file systems to store data (Cont.)
• Atomicity of updates
• Failures may leave database in an inconsistent state with partial updates
carried out
• Example: Transfer of funds from one account to another should either complete
or not happen at all
• Concurrent access by multiple users
• Concurrent access needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies
• Example: Two people reading a balance (say 100) and updating
it by withdrawing money (say 50 each) at the same time
• Security problems
• Hard to provide user access to some, but not all, data
Database systems offer solutions to all the above
problems
Characteristics DBMS
• Traditionally, data was organized in file formats. DBMS was a new
concept then, and all the research was done to make it overcome the
deficiencies in traditional style of data management. A modern DBMS
has the following characteristics −
• Real-world entity − A modern DBMS is more realistic and uses real-
world entities to design its architecture. It uses the behavior and
attributes too. For example, a school database may use students as
an entity and their age as an attribute.
• Relation-based tables − DBMS allows entities and relations among
them to form tables. A user can understand the architecture of a
database just by looking at the table names.
Characteristics
• Isolation of data and application − A database system is entirely
different than its data. A database is an active entity, whereas data is
said to be passive, on which the database works and organizes. DBMS
also stores metadata, which is data about data, to ease its own
process.
• Less redundancy − DBMS follows the rules of normalization, which
splits a relation when any of its attributes is having redundancy in
values. Normalization is a mathematically rich and scientific process
that reduces data redundancy.
Characteristics
• Consistency − Consistency is a state where every relation in a
database remains consistent. There exist methods and techniques,
which can detect attempt of leaving database in inconsistent state. A
DBMS can provide greater consistency as compared to earlier forms
of data storing applications like file-processing systems.
• Query Language − DBMS is equipped with query language, which
makes it more efficient to retrieve and manipulate data. A user can
apply as many and as different filtering options as required to
retrieve a set of data. Traditionally it was not possible where file-
processing system was used.
Characteristics
• ACID Properties − DBMS follows the concepts
of Atomicity, Consistency, Isolation, and Durability (normally
shortened as ACID). These concepts are applied on transactions,
which manipulate data in a database. ACID properties help the
database stay healthy in multi-transactional environments and in
case of failure.
• Multiuser and Concurrent Access − DBMS supports multi-user
environment and allows them to access and manipulate data in
parallel. Though there are restrictions on transactions when users
attempt to handle the same data item, but users are always unaware
of them.
Characteristics
• Multiple views − DBMS offers multiple views for different users. A
user who is in the Sales department will have a different view of
database than a person working in the Production department. This
feature enables the users to have a concentrate view of the database
according to their requirements.
Characteristics
• Security − Features like multiple views offer security to some extent
where users are unable to access data of other users and
departments. DBMS offers methods to impose constraints while
entering data into the database and retrieving the same at a later
stage. DBMS offers many different levels of security features, which
enables multiple users to have different views with different features.
For example, a user in the Sales department cannot see the data that
belongs to the Purchase department. Additionally, it can also be
managed how much data of the Sales department should be
displayed to the user. Since a DBMS is not saved on the disk as
traditional file systems, it is very hard for miscreants to break the
code.
Database Users
• A typical DBMS has users with different rights and permissions who
use it for different purposes. Some users retrieve data and some back
it up. The users of a DBMS can be broadly categorized as follows −
Database Users
• Administrators − Administrators maintain the DBMS and are
responsible for administrating the database. They are responsible to
look after its usage and by whom it should be used. They create
access profiles for users and apply limitations to maintain isolation
and force security. Administrators also look after DBMS resources like
system license, required tools, and other software and hardware
related maintenance.
Database Users
• Designers − Designers are the group of people who actually work on
the designing part of the database. They keep a close watch on what
data should be kept and in what format. They identify and design the
whole set of entities, relations, constraints, and views.
Database Users
• End Users − These days all the modern applications, web or mobile,
store user data. How do you think they do it? Yes, applications are
programmed in such a way that they collect user data and store the
data on DBMS systems running on their server. End users are those
who actually reap the benefits of having a DBMS. End users are the
one who store, retrieve, update and delete data. End users can range
from simple viewers who pay attention to the logs or market rates to
sophisticated users such as business analysts.
Database
Systems
• The database system is a collection of
components that allows the collection,
storage, management, and use of data within
a database environment.
• The database system’s components are
hardware, software, people, procedures and
data
31
Components of DBMS
⚫ The database management system can be divided
into five major components, they are:
✓ Hardware
✓ Software
✓ Data
✓ Procedures
✓ Database Access Language
DBMS Components
DBMS Components: Hardware
⚫ When we say Hardware, we mean computer, hard disks, I/O
channels for data, and any other physical component involved
before any data is successfully stored into the memory.
⚫ When we run Oracle or MySQL on our personal computer,
then our computer's Hard Disk, our Keyboard using which we
type in all the commands, our computer's RAM, ROM all
become a part of the DBMS hardware.
DBMS Components: Software
⚫ This is the main component, as this is the program which
controls everything. The DBMS software is more like a
wrapper around the physical database, which provides us
with an easy-to-use interface to store, access and update data.
⚫ The DBMS software is capable of understanding the
Database Access Language and intrepret it into actual
database commands to execute them on the DB.
DBMS Components: Data
⚫ Data is that resource, for which DBMS was designed. The motive
behind the creation of DBMS was to store and utilise data.
⚫ In a typical Database, the user saved Data is present and meta data is
stored.
⚫ Metadata is data about the data. This is information stored by the
DBMS to better understand the data stored in it.
⚫ For example: When I store my Name in a database, the DBMS will
store when the name was stored in the database, what is the size of the
name, is it stored as related data to some other data, or is it independent,
all this information is metadata.
DBMS Components: Procedures
⚫ Procedures refer to general instructions to use a database
management system. This includes procedures to setup and
install a DBMS, To login and logout of DBMS software, to
manage databases, to take backups, generating reports etc.
Language
⚫ Database Access Language is a simple language designed to write
commands to access, insert, update and delete data stored in any
database.
⚫ A user can write commands in the Database Access Language and
submit it to the DBMS for execution, which is then translated and
executed by the DBMS.
⚫ User can create new databases, tables, insert data, fetch stored data,
update data and delete the data using the access language.
Components of the Database
System
39
Basic Database
Terminologies
• Data: raw facts such as student name, age,
date of birth
• Field: Used to store data
• Record: logically connected set of fields
describing an entity
• File: collection of related records
40
Draw backs of the Database
Systems
• Even though the database system has its
advantages there are a couple of problems
associated with its use:
– Increased costs
– Management complexity
– Keeping it up to date
– Vendor dependency
41
Importance of Database
Design
• Database design consists of a set of activities
that are carried out to create the database
structure to be used to store end-‐user data
• Data is a very valuable asset to every
organization, this makes it important to
carefully design where the data will be stored
42
Database Development
Process
• Database design must conform to the design
of the information systems within which it
exists
• The framework for database development,
evaluation and revision is known as the
Database Life Cycle (DBLC)
43
Database Life
Cycle
44
DBL
C
• Establish requirements
– Database initial study
• Analysis
– Situation analysis
– Problem definition
– Define objectives
– Define scope and boundaries
• Design
– Create the conceptual design
– Select the DBMS software
– Create logical design
– Create physical design
45
DBL
C
• Implementation
– Install DBMS
– Create the database(s)
– Load data
• Testing
– Run tests
– Fine-‐tuning
– Evaluate the database and application programs
• Maintenance
– Make changes and updates
– Make enhancements
46
Database Design
Strategies
• Top-‐down design
• Bottom-‐up design
• Centralized design
• Decentralized design
47
Thank
you!
48