Introduction to Database Management Systems

1
Introduction to Database Management Systems

• HIIB62
• Databases and Data Modelling
• Contributions by Bill Davey, Arthur Adamopoulos,
  David Kelly, Vince Bruno, Hossein S. Zadeh and
  Ian Searle

2
Why Data/Information is needed?

• Help in decision making
• For operational/transactional functions of the
  organization
• Data Management is needed to
• collect
• store
• retrieve

3
The Data Knowledge Continuum
Data Facts and figures without context or meaning
Information Data made meaningful by processing
or definition
Knowledge Information incorporated into a world
view
4
Data

• Examples of data
• ISYS2038
• 99255793
• 2.75
• 3000
• Brown
• NEC 200
• 19 Feb 1989

5
Data

• Data made meaningful by Metadata
• RMIT Course CodeISYS2038
• Jacks home phone[phone]
• Weight of bananas bought today2.75
• Melbournes postcode3000
• Jacks family nameBrown
• Model NumberNEC 200
• Jacks DOB19 Feb 1989

6
Goal Build Business Application
Tools Database Design SQL (queries) Programmin
g
Program
Design
SQL
Best Spend your time on design and SQL.
Program
Design
SQL
Worst Compensate for poor design and limited SQL
with programming.
7
DBMS

• Database
• A collection of data stored in a standardized
  format, designed to be shared by multiple users.
• an organised collection of logically related
  data
• MF, Chapter 1, Ed5, pg 4
• Database Management System (DBMS)
• Software that defines a database, stores the
  data, supports a query language, produces
  reports, and creates data entry screens. (DML)

8
Example of non-DBMS Databases
"95 Rocky Road Eaglemont 3084",18/6/2005
100500,"Garage Door","Joe Bloggs -
""Handyhusband Co""","Flat 3/24 Dorset St
Rosanna","Double D Estate Agency a/c R J Brown,
Landlords",1.00,"Fixit oil canister",29.95 "95
Rocky Road Eaglemont 3084",18/6/2005
100500,"Garage Door","Joe Bloggs -
""Handyhusband Co""","Flat 3/24 Dorset St
Rosanna","Double D Estate Agency a/c R J Brown,
Landlords",1.00,"1 hour x 30",30.00 "95 Rocky
Road Eaglemont 3084",18/6/2005 100500,"Garage
Door","Joe Bloggs - ""Handyhusband Co""","Flat
3/24 Dorset St Rosanna","Double D Estate Agency
a/c R J Brown, Landlords",1.00,"Sundry
Materials",5.00 "22 Busy Street Rosanna
3084",14/6/2005 151000,"Letterbox broken beyond
repair","Joe Bloggs ""Handyhusband Co""","Flat
3/24 Dorset Rd Rosanna","Mr Al Rush",1.00,"Letterb
ox - post reused",78.00 "22 Busy Street Rosanna
3084",14/6/2005 151000,"Letterbox broken beyond
repair","Joe Bloggs ""Handyhusband Co""","Flat
3/24 Dorset Rd Rosanna","Mr Al Rush",1.00,"Bracket
",3.78 "22 Busy Street Rosanna 3084",14/6/2005
151000,"Letterbox broken beyond repair","Joe
Bloggs ""Handyhusband Co""","Flat 3/24 Dorset Rd
Rosanna","Mr Al Rush",1.00,"Concrete
mix",8.00 "22 Busy Street Rosanna
3084",14/6/2005 151000,"Letterbox broken beyond
repair","Joe Bloggs ""Handyhusband Co""","Flat
3/24 Dorset Rd Rosanna","Mr Al Rush",1.50,"Labour
_at_ 30 per hour",45.00 "16/198 Melbourne Street
Northcote 3070",4/6/2005 102000,"Noise and
window smashed overnight","LT Shine,
Glaziers","55 Main Rd Northcote","BM Dagg 16/198
Melbourne Rd Northcote",1.00,"2 AJ type glass
pannel 600x1.2m",151.00 "16/198 Melbourne Street
Northcote 3070",4/6/2005 102000,"Noise and
window smashed overnight","LT Shine,
Glaziers","55 Main Rd Northcote","BM Dagg 16/198
Melbourne Rd Northcote",1.00,"Misc",48.15
9
Example of non-DBMS Databases
10
Drawbacks of old File methods

• Uncontrolled Duplication
• Wastes space
• Hard to update all files
• Inconsistent data
• Inflexibility
• Hard to change data
• Hard to change programs
• Limited data sharing
• Poor enforcement of standards
• Poor programmer productivity
• Excessive program maintenance

11
File Method Problems

• Files defined in program
• Cannot read file without definition
• Hard to find definition
• Every time you alter file, must rewrite code
• Change in a program/file will crash other code
• Cannot tell which programs use each file

• Multi-user problems
• Concurrency
• Security
• Access
• Backup Restore
• Efficiency
• Indexes
• Programmer talent
• System
• Application

12
File Method/3GL
Files
Programs
Payroll
Pay History
Data Definition File 1 File 2
Benefits
Benefits
Data Definition File A File 2 File C
Employee
Employee Choices
13
Example of File Method
COBOL
Employee File
File Division 01 Employees 02 ID 02 Name 02
Address 01 Department 02 ID 02 . . .
112 Davy Jones 999 Elm Street . . . 113 Peter
Smith 101 Oak St . . .

• Add to file (e.g. MobilePhone)
• Write code to copy employee file and add empty
  cell phone slot.
• Find programs using employee file.
• Modify file definitions.
• Modify reports (as needed)
• Recompile, fix new bugs.
• Easier Keep two employee files?

02 MobilePhone
More programs
File Division 01 Employees ...
14
Traditional File Processing
File 1
File N
15
Example 2 Code change

• Do while not EOF(1)
• Input ID, Name, Office, Phone
• Loop
• Input file
• 5867, Lars, HJ234, 59847
• What happens when you add a mobile phone?

16
Advantages of Database Approach

• Minimal data redundancy.
• Data consistency.
• Integration of data.
• Sharing of data.
• Enforcement of standards.
• Ease of application development.
• Uniform security, privacy and integrity.
• Data independence.

17
Database Management Approach

• Data is most important
• Data defined first
• Standard format
• Access through DBMS
• Queries, Reports, Forms
• Application Programs
• 3GL Interface
• Data independence
• Change data definition without changing code
• Alter code without changing data
• Move/split data without changing code

All Data
DBMS
Program1
Program2
Queries Reports
18
Database System
DBMS
Operations
Data Specification
...
Data Specification
19
Modifying Data with DBMS

• Add MobilePhone to employee table
• Open table definition
• Add data element
• If desired, modify reports
• Use report writer
• No programming
• Existing reports, queries, code will all run as
  before with no changes.

Field Name Data Type Description EmployeeID Numbe
r Autonumber.. TaxpayerID Text Federal
ID LastName Text FirstName Text . . .
Phone Text . . . MobilePhone Text Cellular .
. .
20
DBMS Features/Components

• Report writer
• Forms generator (input screens)
• Application generator
• Communications
• 3GL Interface

• Database engine
• Storage
• Retrieval
• Update
• Query Processor
• Data dictionary
• Utilities
• Security

21
DBMS Engine, Security, Utilities
Data Tables
Product ItemID Description 887 Dog food 946 Cat
food
Order OrderID ODate 9874 3-3-97 9888 3-9-97
Customer CustomerID Name 1195 Jones 2355 Rojas
Database Engine
Product ItemID Integer, Unique Description Text,
100 char
Customer CustomerID Integer, Unique Name Text, 50
char
Data Dictionary
Security
User Identification Access Rights
Concurrency and Lock Manager
Utilities
Backup and Recovery
Administration
22
Meta-Data

• Data about data
• Known as Data Dictionary or Repository
• Help us describe what the raw data is, which is
  stored in our database
• Describe the semantics of the data stored
• Naming
• Headings/Caption
• Validation
• Formatting
• Scale, etc

23
Computability of Data

• We need to be able to calculate new pieces of
  data and information from the raw data we have.
  Computing on the data.
• No need to store computable data.
• We also need to present information in readable
  forms and formats.

Date of birth from database
AGE 46
1/12/1958
Calculation (using SQL)
TODAYS DATE - DOB
24
Basic DBMS Components

• Tables/Relation/Files
• Records/Fields/Rows
• Fields/Attributes/Columns
• Data Dictionary/Repository
• DDL, DML DCL

25
Database Tables (Access)
26
Database Tables (Oracle)
27
DBMS Query Processor
All Data
Database Engine Data Dictionary
Query Processor
28
DBMS Report Writer
All Data
Database Engine Data Dictionary
Query Processor
Report Writer
Report Format and Query
29
DBMS Input Forms
All Data
Database Engine Data Dictionary
Query Processor
Form Builder
Input Form Design
30
DBMS Components
All Data
Database Engine Data Dictionary Security
Communication Network
3GL Connector
Query Processor
Form Builder
Report Writer
Program
Application Generator
31
Examples of Database Systems

• Oracle
• Postgres, Mysql (Open Source)
• Ingres, Informix (Unix)
• DB2, SQL/DS (IBM)
• Access (Microsoft)
• MS SQL Server (Microsoft )
• Many older (Focus, IMS, ...)
• Many limited PC (dBASE, Paradox, )

32
Network Database
Entry point
Customer
Order
Items Ordered
Items
Entry point
33
Relational Database
Customer(CustomerID, Name,
Order(OrderID, CustomerID, OrderDate,
ItemsOrdered(OrderID, ItemID, Quantity,
Items(ItemID, Description, Price,
34
Object-Oriented DBMS
Customer
Order
CustomerID Name
OrderID CustomerID
Add Customer Drop Customer Change Address
NewOrder DeleteOrder
OrderItem
Item
OrderID ItemID
ItemID Description
OrderItem DropOrderItem
New Item Sell Item Buy Item
35
Why dont all developers use a DBMS?

• Most new projects nowadays do use a DBMS
• Need specialized personnel
• Programmers
• Designers/Analysts
• Database administrators
• Need to define data for organization
• Cost
• PC 400 - 2000
• Large 100,000

36
How do you sell a DBMS approach?

• Applications change a lot, but same data.
• Need for ad hoc questions and queries.
• Need to reduce development times.
• Need shared data.
• Improve quality of data.
• Enable users to do more development.

37
DBMS variants

• single user or multi-user
• centralised or distributed
• transactional or data warehouse
• relational database structure
• hierarchical database structure
• object orientated database structure

38
People Associated with DBMS

• Data Administrator (DA)
• Database Administrator (DBA)
• End Users
• System Analysts and Application Programmers
• DBMS Designers and Implementers
• Operators and Maintenance Personnel

39
Risks and Costs of DBMS

• Main drawbacks are list as follows
• Organisational conflicts
• Development project failure
• System failure
• Overhead costs
• Need for sophisticated personnel