Transcript What is a database

Database Design Concepts
Info 1408
Lecture 3
What is a database?
Objectives
Introduce the three layers of data
abstraction
Introduce the terms physical and
Logical data independence
Towards a definition ………
Take a moment or two to think
about how you would define a
database – write down the
key words you would use.
Describe a database to your
neighbour
A typical domestic ‘database’
Is this Organised? Efficient? Easily accessible?
Flexible? ……
good for much other than keeping things off the
floor?
Paper based databases
•Have been around for a long time
•Data is organised and quickly
accessible
So is easier to maintain – fewer
errors
•But is not all that flexible – things
are filed in one place only (why
would it not be a good idea to
have duplicates)
A working definition
A collection of interrelated data items that
are treated as a unit –
Andy Oppel
Computer based Databases
Are managed by a
We will explore some features of a DBMS ……..
Layers of data abstraction
Physical data independence
Logical data independence
Some important features of a
DBMS
Moves data to and from physical data files
Manages multiple users
Manages transactions as an ‘all or nothing’
unit of work
Supports a Query Language to retrieve data
from the database
Manages back ups
Provides Security mechanisms
Layers of Data abstraction
Allows us to consider User views of the
data – which can be different for each
user. More about this later in the term
This is achieved by thinking of
3 Layers of abstraction
What the user sees
External
layer
Logical
layer
How the database
organises the data for
viewing
Physical
layer
How the computer’s
operating system stores
the data
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
The Physical layer
The data files that actually contain the data
Data could be stored over multiple disc drives – maximise
performance by running in parallel
(Microsoft is an exception in that everything is stored in one file – this limits
the number of concurrent users, this is why it is not used for large
enterprises.)
The DBMS works with the computer’s Operating system (OS) to
efficiently manage the file storage.
The user of the database does not need to know how the DBMS
does this. They just ask for the information and the DBMS supplies
it. A little like you ordering a meal at a restaurant, it just arrivesyou don’t know how it was cooked or what the ingredients are.
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
Logical Layer
The logical layer transforms the physical data into a
common structure.
This structure is the one that the designer would
recognise. It covers all the data requirements of all
the users. This is what we will be working on when
we start to design databases later on.
This ‘abstracted’ format is known as a schema
Depending on the DBMS data could be formatted as
2 dimensional tables or a hierarchical structure
(similar to an organisation chart)
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
External Layer
This is the layer where users interact with the
database and issue queries
The DBMS handles the transformation of
selected items from the data structures in the
logical layer to form each user view
These user views can be stored for future
use, or may be used only temporarily and
then discarded – an ad hoc query.
When we start to create a database we
need to look at each user requirementsThese requirements will give us all of
the views required
Using some methods ( that we will
learn) we can create the Internal
schema which will organise the data so
all user requirements can be met.
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
Physical Data Independence
There can be differing degrees of
independence
Prior to systems having data
independence any change to the way
data was stored would require a change
to every computer program that used
the data – time consuming and
expensive
External
layer
User
View 2
User
View 1
User
View ….
Logical Data
independence
Internal
Schema
Logical
layer
Physical Data
independence
Physical
layer
Database
File
Database
File
Database
File
Database
File
Database
File
Logical Data Independence
The ability to make changes to the
logical layer without disrupting existing
users and processes
Activities such as:


Adding a new database object
Adding data items to an existing object
What key words would you
now use to describe a
database to a colleague?
Take a moment to jot down
the key words
Does your definition include
words such as..
secure
data
efficient
flexible
organised
consistent
maintainable
Error free
accessible
Robust
Reliable
Summary
We have covered the three levels of
abstraction
Physical and logical data independence.
References
Databases Demystified by Andy Oppel
(Published By McGraw-Hill, ISBN 0-07225364-9)
Data Analysis for database Design by
David Howe (published by Arnold
Second edition.)