CSC3318
Database Management 1
Databases, Database Design, and
Architecture
Database Management 1
Reading
Thefollowing books may be useful additional
reading:
Beginning Database Design: From Novice to
Professional (Clare Churcher, Apress, ISBN 1-
59059-769-9)
Head First SQL (Lynn Beighley, O'Reilly, ISBN 0-
596-52684-9)
Database Management 1
What is a database?
Any collection of data can be described as a
database
Computerised database systems are now very
commonplace
Information is stored in a database every time
we:
use a bank account
book a travel ticket
make an appointment with a doctor
etc.
Database Management 1
Database mangement systems
A database is simply the collection of data
which you need to store
To actually store the data, and to do anything
useful with it, you need a Database
Management System (DBMS)
A DBMS controls the way the data is stored
on the computer, and provides ways of
getting data in and out of the system
Database Management 1
Data models
The way in which data is organised for
storage in a database is known as the data
model
Early computer databases developed in the
1960’s used a hierarchical model
Similar to the way files and folders are still
organised in modern computer file systems
Most data does not fit very well into a simple
hierarchy
Database Management 1
Data models
Hierarchical data
“Real-world” data – no clear hierarchy
Database Management 1
Relational databases
Relativelycomplex data like this is better
handled with the relational model
Devised by Edgar Codd around 1970
Most databases nowadays are relational
databases
although there are others: object databases, XML
databases, “NoSQL” databases
A database management system which uses
the relational model is called an RDBMS
Database Management 1
Relational databases
Database Management 1
Databases and Enterprise
Information Systems
Web pages GUI UI Layer
Command Domain
Objects Objects
Data Access Business Layer
Objects
Database Database Layer
Database Management 1
Database servers
Desktop PC
database accessed as file or
Application through local server
Database
Desktop Application
Client
Application
network or internet
Desktop PC
connections
Server
Client Database
Desktop PC
Application
Client-Server Application
Desktop PC
Client
Application
database accessed through
network server
Web browser
Desktop PC
Servers
Server
Enterprise Database
Web browser
Application
Desktop PC
Enterprise Web Application
Web browser
Desktop PC
Database Management 1
Popular RDBMSs
Microsoft Access
aimed at small businesses, and useful for desktop applications
and systems with a small number of users
Microsoft SQL Server, Oracle, IDM DB2
scalable and secure, and widely used by large organisations
MySQL
open-source and quite powerful, widely used in web sites
Microsoft SQL Server Compact, JavaDB, SQLite
compact DBMSs, suitable for mobile devices in particular
...and many more
Database Management 1
RDBMS tools
Most
RDBMSs include tools to create
complete application, for example:
form designers – to allow data entry forms to be
created for the user interface
report designers – to present data to the user
stored procedures – to perform processing of
data according to business rules
Database Management 1
RDBMS and other tools
Can use your RDBMS and its tools for
everything, or
Can use the RDBMS as a component and
use other tools and programming languages
to create the other components
Database Management 1
Data Manipulation Language (DML)
Language for accessing and manipulating the
data organized by the appropriate data
model
DML also known as query language
Two classes of languages
Procedural – user specifies what data is required
and how to get those data
Non-procedural – user specifies what data is
required without specifying how to get those data
SQL is the most widely used query language
Database Management 1
SQL – the language of relational
databases
Todevelop applications which use relational
databases you usually need to use SQL
Structured Query Language
This is the language which is used to define
queries
A query is a request to a DBMS for some
specific information
Relational databases are sometimes referred
to as SQL databases
Database Management 1
SQL example
SQL queries can be quite easy to understand
For example, the following query finds the
last name of all the customers in a database:
SELECT lastName FROM Customers;
SQL can also be used to add, update or
delete data, and to build the database in the
first place
Database Management 1
SQL standards
SQL is supposed to be a standard language
which is supported by all RDBMSs
In fact, you need to be careful because there
are some important differences between the
versions of SQL used by different systems
Different versions of SQL standards (SQL92,
SQL99,etc.)
Different implementations by RDBMS vendors
Database Management 1
Designing a database
A well-designed database helps to make sure
that the data stored is accurate and
consistent and can be retrieved easily
What do we mean by inconsistencies?
It would, for example, be inconsistent to store
a booking without storing the details of the
customer making the booking
With careful design, we can make sure the
database won’t allow this to happen
Database Management 1
Steps in designing a database
Determining the intended uses of the system
Creating a data model
Implementing the database
Database Management 1
The data model
Datamodel = domain model classes which
represent entities we need to store
permanently
User
Booking
-name
-adults
-address
-children
-username
-bookingdate
-password 1..1 0..* -status
-datejoined
0..*
Package 1..1
-location
-name Tour
-description -departuredate
-adultprice -offer
-childprice 1..1 0..*
-departure
Database Management 1
Data modelling techniques
We are using object-oriented techniques with
UML to design our data model
There are other methods which are also
commonly used in database design
One widely used method is called Entity
Relationship Modelling (ERM)
Represents the data model as an Entity
Relationship Diagram (ERD)
Database Management 1
From data model to database
Need to consider how the data model can be
represented in a specific RDBMS
RDBMS software has specific ways of
representing and enforcing the entities,
attributes and relationships in the data model
For example, a data model entity is
represented as a table in the relational
database
Database Management 1
Representing the data model
in an RDBMS
Database Management 1
Database Users
Users are differentiated by the way they expect to
interact with the system
Application programmers – interact with system through
DML calls
Sophisticated users – form request in a database query
language
Specialized users – write specialized database
applications that do not fit into the traditional data
processing framework
Naïve users – invoke one of the permanent application
programs that have been written previously
E.g. people accessing database over the web, bank tellers, clerical staff
Database Management 1
Database Administrator
Coordinates all the activities of the database system; the
database administrator has a good understanding of the
enterprises information resources and needs.
Database administrator’s duties include:
Schema definition
Storage structure and access method definition
Schema and physical organization modification
Granting user authority to access the database
Specifying integrity constraints
Monitoring performance and responding to changes in
requirements
Database Management 1
Architecture of DBMS
There are three levels or layers of DBMS
architecture
External level
Conceptual level
Internal level
Database Management 1
Architecture of DBMS
Database Management 1
External Level
This level is concern with the user
Highest or top level of data abstraction (the
user has no knowledge of the DBMN
software and hardware or physical storage)
All database users work on external level of
DBMS
Database Management 1
Conceptual Level
This level is between the user level and the
physical storage view
There is only one conceptual view for single
database
It hides the details of physical storage
structures and concentrates on describing
entities, data types, relationships, user
operations, and constraints.
Database Management 1
Internal Level
This is the lowest level of data abstraction (it has the knowledge of
the software and hardware)
At this level, it keeps the information about the actual representation
of the entire database i.e. the actual storage of the data on the disk
in the form of records or blocks
It is close to the physical storage method
The internal level is the level that tells us what data is stored in the
database and how. At least the following aspects are considered at
this level: storage allocation, access paths etc.
The internal level does not deal with the physical devices directly.
Instead it views a physical device as a collection of physical pages
and allocates space in terms of logical pages
Database Management 1
Working of the three Level
Architecture
Database Management 1
