DBMS fundamentals for beginners

Basic DBMS Tutorials

Computer Basics
Fundamentals
M.S.Office
Operating System
Web Development
HTML
DHTML
XML/XSL
ASP
Databases
DBMS
Networking
Windows Nt Server
Front Page Pub.
Graphics
Macromedia Flash
Adobe Photoshop
Languages
C
C++

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.

Example of flat data organization 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
- Ultrasound
- Delivery
Person
- Physician
- Patient
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
  - Range
  - Arith_ops
  - Date_ops
- Patient
  - Address
  - Pat_num
  - Height
  - Date_of_birth
    - Range
    - Arith_ops
    - Date_ops
- Physician
  - Address
  - Pat_num
  - Height
  - Date_of_birth
    - Range
    - Arith_ops
    - Date_ops

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.)