|
|
|
Database Management System
(DBMS)
Relational Database
Manangement System (RDBMS)
Collections of
data
Data may be collected,
manipulated and retrieved in various ways:
- plain text editor - simple
editing and retrieval
- word processor - adds tables
and simple calculations
- spreadsheet programme - adds
more sophisticated calculations
- database management system (DBMS) - adds
formats, structures, rules, ...
Reasons for a DBMS
A DBMS is a software package
for defining and managing a database.
A 'real' database includes
- definitions of
- field names
- data formats (text?
binary? integer? etc.)
- record structures
(fixed-length? pointers? field order, etc.)
- file structures
(sequential? indexed? etc.)
- rules for validating and
manipulating data
- data
Database
organization
Four main types of database
organization:
- Flat
- Hierarchical
- Relational
- Object-oriented
Flat databases
A single kind of record with a
fixed number of fields. 
Notice the repetition of data, and thus an increased chance of
errors.
Hierarchical
databases
Hierarchical relationships among different types of data.
- can be
very easy to answer
some questions, but very difficult to answer others
- if one-to-many relationship
is violated (e.g., a patient can have more than one physician)
then the hierarchy becomes a network
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #1:
--- ------ ------ ---- ----- --- What is average weight of
123 SMITH 123456 150 4605 123 babies delivered by Jones
220 JONES 392382 177 4912 220 to mothers with heights
238427 162 5204 123 > 170 cm?
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT
------ ----- ---- ---
238427 7511 61.8 2.5
392382 7512 57.4 3.6
392382 7706 58.2 3.4
238427 7801 64.1 2.7
123456 7803 60.3 3.4
649308 7902 58.7 2.9
732293 7906 59.2 2.2
123456 8005 62.3 3.7
649308 8101 57.9 3.1
649308 8205 55.2 1.4
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #1:
--- ------ ------ ---- ----- --- What is average weight of
123 SMITH 123456 150 4605 123 babies delivered by Jones
220 JONES 392382 177 4912 220 to mothers with heights > 170 cm?
238427 162 5204 123
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT First we Join tables 1 and 2.
------ ----- ---- ---
238427 7511 61.8 2.5
392382 7512 57.4 3.6 PAT# HT BIRTH PH# PHNAME
392382 7706 58.2 3.4 ------ ---- ----- --- ------
238427 7801 64.1 2.7 123456 150 4605 123 SMITH
123456 7803 60.3 3.4 392382 177 4912 220 JONES
649308 7902 58.7 2.9 238427 162 5204 123 SMITH
732293 7906 59.2 2.2 649308 174 5409 123 SMITH
123456 8005 62.3 3.7 732293 155 5810 220 JONES
649308 8101 57.9 3.1
649308 8205 55.2 1.4 Next we Project the columns that
we're interested in.
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #1:
--- ------ ------ ---- ----- --- What is average weight of
123 SMITH 123456 150 4605 123 babies delivered by Jones
220 JONES 392382 177 4912 220 to mothers with heights > 170 cm?
238427 162 5204 123
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT Here are the 3 Projected columns.
------ ----- ---- ---
238427 7511 61.8 2.5 PHNAME HT PAT #
392382 7512 57.4 3.6 ------ ---- ------
392382 7706 58.2 3.4 SMITH 150 123456
238427 7801 64.1 2.7 JONES 177 392382
123456 7803 60.3 3.4 SMITH 162 238427
649308 7902 58.7 2.9 SMITH 174 649308
732293 7906 59.2 2.2 JONES 155 732293
123456 8005 62.3 3.7
649308 8101 57.9 3.1
649308 8205 55.2 1.4 Next we Select the records with
PHNAME=JONES and HT>170.
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #1:
--- ------ ------ ---- ----- --- What is average weight of
123 SMITH 123456 150 4605 123 babies delivered by Jones
220 JONES 392382 177 4912 220 to mothers with heights > 170 cm?
238427 162 5204 123
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT One record satisfies the
------ ----- ---- --- criteria.
238427 7511 61.8 2.5
392382 7512 57.4 3.6 PHNAME HT PAT#
392382 7706 58.2 3.4 ------ ---- ------
238427 7801 64.1 2.7
123456 7803 60.3 3.4 JONES 177 392382
649308 7902 58.7 2.9
732293 7906 59.2 2.2 Next we Join this with Table 3.
123456 8005 62.3 3.7
649308 8101 57.9 3.1
649308 8205 55.2 1.4
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #1:
--- ------ ------ ---- ----- --- What is average weight of
123 SMITH 123456 150 4605 123 babies delivered by Jones
220 JONES 392382 177 4912 220 to mothers with heights > 170 cm?
238427 162 5204 123
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT The Join with table 3 results
------ ----- ---- --- in 2 records.
238427 7511 61.8 2.5
392382 7512 57.4 3.6 PHNAME HT PAT# DELIV MWT BWT
392382 7706 58.2 3.4 ------ ---- ------ ----- ---- ---
238427 7801 64.1 2.7 JONES 177 392382 7512 57.4 3.6
123456 7803 60.3 3.4 JONES 177 392382 7706 58.2 3.4
649308 7902 58.7 2.9
732293 7906 59.2 2.2 From these we can calculate the
123456 8005 62.3 3.7 average weight.
649308 8101 57.9 3.1 We have carried out 4
649308 8205 55.2 1.4 relational operations
(Join, Project, Select and Join).
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #2:
--- ------ ------ ---- ----- --- How many babies with
123 SMITH 123456 150 4605 123 birth weights > 2.5 kg
220 JONES 392382 177 4912 220 have been delivered
238427 162 5204 123 by each physician?
649308 174 5409 123
732293 155 5810 220
3. PAT# DELIV MWT BWT
------ ----- ---- ---
238427 7511 61.8 2.5
392382 7512 57.4 3.6
392382 7706 58.2 3.4
238427 7801 64.1 2.7
123456 7803 60.3 3.4
649308 7902 58.7 2.9
732293 7906 59.2 2.2
123456 8005 62.3 3.7
649308 8101 57.9 3.1
649308 8205 55.2 1.4
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #2:
--- ------ ------ ---- ----- --- How many babies with
123 SMITH 123456 150 4605 123 birth weights > 2.5 kg
220 JONES 392382 177 4912 220 have been delivered
238427 162 5204 123 by each physician?
649308 174 5409 123
732293 155 5810 220
First we Select BWT > 2.5
3. PAT# DELIV MWT BWT
------ ----- ---- --- PAT# DELIV MWT BWT
238427 7511 61.8 2.5 ------ ----- ---- ---
392382 7512 57.4 3.6 392382 7512 57.4 3.6
392382 7706 58.2 3.4 392382 7706 58.2 3.4
238427 7801 64.1 2.7 238427 7801 64.1 2.7
123456 7803 60.3 3.4 123456 7803 60.3 3.4
649308 7902 58.7 2.9 649308 7902 58.7 2.9
732293 7906 59.2 2.2 123456 8005 62.3 3.7
123456 8005 62.3 3.7 649308 8101 57.9 3.1
649308 8101 57.9 3.1
649308 8205 55.2 1.4 Next we Project the columns
we're interested in.
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #2:
--- ------ ------ ---- ----- --- How many babies with
123 SMITH 123456 150 4605 123 birth weights > 2.5 kg
220 JONES 392382 177 4912 220 have been delivered
238427 162 5204 123 by each physician?
649308 174 5409 123
732293 155 5810 220
Here are the projected columns.
3. PAT# DELIV MWT BWT
------ ----- ---- --- BWT PAT#
238427 7511 61.8 2.5 --- ------
392382 7512 57.4 3.6 3.6 392382
392382 7706 58.2 3.4 3.4 392382
238427 7801 64.1 2.7 2.7 238427
123456 7803 60.3 3.4 3.4 123456
649308 7902 58.7 2.9 2.9 649308
732293 7906 59.2 2.2 3.7 123456
123456 8005 62.3 3.7 3.1 649308
649308 8101 57.9 3.1
649308 8205 55.2 1.4 Next we Join this with
Table 2.
1. PH# PHNAME 2. PAT# HT BIRTH PH# Question #2:
--- ------ ------ ---- ----- --- How many babies with
123 SMITH 123456 150 4605 123 birth weights > 2.5 kg
220 JONES 392382 177 4912 220 have been delivered
238427 162 5204 123 by each physician?
649308 174 5409 123
732293 155 5810 220
Results of Join with Table 2
3. PAT# DELIV MWT BWT
------ ----- ---- --- BWT PAT# HT BIRTH PH#
238427 7511 61.8 2.5 --- ------ ---- ----- ---
392382 7512 57.4 3.6 3.6 392382 177 4912 220
392382 7706 58.2 3.4 3.4 392382 177 4912 220
238427 7801 64.1 2.7 2.7 238427 162 5204 123
123456 7803 60.3 3.4 3.4 123456 150 4605 123
649308 7902 58.7 2.9 2.9 649308 174 5409 123
732293 7906 59.2 2.2 3.7 123456 150 4605 123
123456 8005 62.3 3.7 3.1 649308 174 5409 123
649308 8101 57.9 3.1
649308 8205 55.2 1.4 Now we can count the babies.
We've done 3 relational
operations.
Object-oriented
databases
Object-oriented analysis is another way to
model the world, involving abstraction, encapsulation, modularity and hierarchy (with inheritance).
Abstraction
Consider only features which
are necessary for the problem at hand.
For example, a person may be defined by the ability to
ask for the ID number, age, height and weight.
Encapsulation
The internal structure of an
object is hidden.
For example, all we know is
that we can ask for the age, not whether the age is continually
recalculated or calculated on demand from birth date and current
date.
Modularity
Grouping classes and objects
into `cohesive and loosely coupled modules'.
For example, the classes person and cat might be implemented within the same
module because they share many things (like how to calculate age).
Hierarchy
Objects are instances of classes, e.g., JDoe is an instance of the class patient
Classes and objects form two
different hierarchies.
- Class hierarchy ('kind'):
e.g., patient is a kind of person. A class inherits (and
specializes) the characteristics of its parent class.
- Object hierarchy ('part'):
e.g., the object JDoe.height is a
part of the object JDoe.
Examples of class
hierarchies
- Event
- Person
- Numeric_value
- Height
- Weight
- Date
- Date_of_birth
- Date_of_event
| Date_of_event, Patient, Physician
Date_of_event,
Patient, Physician
Date_of_event, Patient, Physician,
Weight_m, Weight_b
Address
Address, Phys_num, Name
Address,
Pat_num, Height, Date_of_birth
Range,
Arith_ops
Range, Arith_ops
Range,
Arith_ops
Range, Arith_ops, Date_ops
Range,
Arith_ops, Date_ops
Range, Arith_ops, Date_ops
|
Example of object
hierarchy
- Delivery
- Date_of_event
- Patient
- Address
- Pat_num
- Height
- Date_of_birth
- Physician
- Address
- Pat_num
- Height
- Date_of_birth
Examples of
objects
Persons
- physician(1) [smith, 123]
- physician(2) [jones, 220]
- patient(1) [123456, 150,
4605]
- patient(2) [649308, 174,
5409]
- patient(3) [238427, 162,
5204]
- patient(4) [732293, 155,
5810]
- patient(5) [392382, 177,
4912]
Events
- delivery(1) [7803,
physician(1), patient(1), 60.3, 3.4]
- delivery(2) [8005,
physician(1), patient(1), 62.3, 3.7]
- delivery(3) [7902,
physician(1), patient(2), 58.7, 2.9]
- delivery(4) [8101,
physician(1), patient(2), 57.9, 3.1]
- delivery(5) [8205,
physician(1), patient(2), 55.2, 1.4]
- delivery(6) [7511,
physician(1), patient(3), 61.8, 2.5]
- delivery(7) [7801,
physician(1), patient(3), 64.1, 2.7]
- delivery(8) [7906,
physician(2), patient(4), 59.2, 2.2]
- delivery(9) [7512,
physician(2), patient(5), 57.4, 3.6]
- delivery(10) [7706,
physician(2), patient(5), 58.2, 3.4]
Centralized
databases
A centralized database (e.g.,
hospital information system) can be very big and complex, but offers
the following advantages:
- shared data, reduced
redundancy
- fewer inconsistencies in
data
- enforcement of standards
- security restrictions
- balancing of conflicting
requirements
- data independence
Historical note: `The minimum computer
memory required for average data bases varies from 30K to 160K
bytes.' (Fundamentals of Data Base
Systems, S.M. Deen, 1977, p. 5). This memory requirement of 20
years ago is less than the size of the level-2 cache in a current
entry-level PC.
Data independence is important
because:
- Different applications will
need to look at data in different ways.
- One must be able to change
storage mechanisms and formats without having to modify all
application programmes. For example:
- method of representation
of numeric and alphanumeric data (e.g., changing date format to
avoid Y2000 problem)
- units (e.g., metric vs.
furlongs)
- coding (e.g., ICD-9 vs.
descriptive text)
- record structure
- file structure
(sequential, sorted, indexed, etc.)
|
|
