Database management

1
Database management

• Week 8 Lecture 1

2
From Data we derive Information

• So the Management of data is at the heart of our
  Information System
• Information is derived from data by processing
  relating, summarising, grouping etc

Knowledge
Information
Data
3
The DBMS (Data Base Management System)

• Sits on top of the O/S File System
• Defines the structure in which the data is to be
  organised
• Adds and changes data in the DB
• Maintains the integrity of the data
• Finds and presents data to a process

Application processes
Middleware
DBMS
Command language (Shell)
TCP/IP
File manager
Ethernet ATM etc
Device drivers
Task management (scheduler)
Memory management
4
We are going to discuss

• Relational Databases for
• Transaction processing
• Data warehousing
• There are others
• Network databases
• Hierarchical databases
• Object oriented databases
• Hypermedia databases

5
Assumed knowledge

• Concept of tables,
• Primary and foreign keys
• Basic SQL
• Record locking
• A Transaction

6
The Relational model

• Was defined by Dr E F Codd in the 1970s
• He initially defined 12 rules or characteristics
  see list by CJ Date in reading material
• He developed Relational calculus from which SQL
  was derived
• Allows read and update by set particularly
  important in providing an efficient DB server
• Definition of the data (the meta data) is
  imbedded in the database, encouraging data
  independence
• Tables provide structural independence

7
System Issues in DBMS

• Database design The Entity/Relationship model
• Islands of Information
• Need for an organisation wide data dictionary
• Database tuning
• Design for
• Update Transaction processing
• Read Data warehousing
• Distributed databases

8
Database design

• Central to the design of an Information System
• The Entity/Relationship model is the key design
  tool
• Conceptual/logical model
• Physical model
• Normalisation v Redundancy
• Great for TP databases
• Not necessarily so good for Data warehouses
• Data Integrity

9
Islands of Information

• File systems, which were hard coded into
  application programs started this
• Still a problem with department/division view of
  data, but different views not invalid
• The desire and ability to move quickly
• Needs strong guidance from senior management
  but not easy to obtain
• Now we have inter firm barriers
• Can be overcome with data warehouses

10
Organisation wide Data dictionary

• To avoid
• Islands of information
• Different names for same attribute
• Different domains for an attribute or table (an
  airline had 60 different definitions of
  passenger)
• To ensure
• Careful thinking about organisational data
• Clear definition of attributes
• The problem
• system that affects one department may need
  agreement of others
• slows the process
• Requires compromise

11
Database tuning

• Best place to start to improve system performance
• The role of the DBA but needs input from the
  system designers
• Tuning needs a broad and deep understanding of
• The application(s)
• The DBMS
• The Operating system
• The Hardware
• Get the facts volumes, query plans

12
Five basic principles of tuning

• Think globally Act locally
• Partitioning breaks bottlenecks
• Start-up costs are high Running costs are low
• Render unto server what is due unto server
• Be prepared for trade offs

Source Database Tuning, Shasha Bonnet
13
TP v Data warehousing

• TP current data, DW historical data
• TP normalised, DW some redundant data useful
• 10 times more reads than writes
• Writes update
• tables and indexes
• Single records
• Need write locks
• TP needs speed
• Data warehouses
• Have more data
• Need more indexes
• Change less often
• Complex queries large joins
• Combine data from many TP databases data
  scrubbing

14
Distributed databases

• TP and Data warehouse
• Partitioned geographically
• Multiple copies for queries
• Serialisation a problem for updates
• Replication
• Two phase commit
• Buffer tables to avoid two phase commit