Database Management Systems DBMS

1
Database Management Systems(DBMS)
2
Lecture Outline

• What is a database and what is it used for?
• Types of databases
• Parts of a database
• Database functions
• Databases and the Internet
• Examples

3
Database Management Systems (DBMS)

• Management of databases is one of the main
  applications of computers
• Major applications include
• student records
• bank accounts
• inventory and sales
• products and prices
• ...

4
Hierarchy of Data Organization

• Bits
• Characters
• Fields
• Records
• Files
• Data Bases

5
The Flat File
Last name
First name
Student number
Mark
Adams Brown Conner
John Cathy Chris
12345 67891 24681
72 81 65
6
File Management Systems

• Flat files have no relationship with any other
  files
• A file-management system accesses only one file
  at a time

7
Database

• A database is a collection of related and
  cross-referenced files
• The objective is to minimize redundancy and
  simplify processing

8
The Traditional Approach to Databases

• Databases were programmed from scratch using
  languages like COBOL or FORTRAN
• Separate files and programs for each application
• Data and application programs were closely linked
• Many of these databases are still in use

9
Problems with Traditional Approach

• Changes are difficult to make
• many programs may need to be rewritten
• Possibility of errors and inconsistencies
• Much duplication and redundancy
• For example, suppose a customer address exists in
  several different files
• If the customer moves, the address needs to be
  changed in several places

10
The Y2K Problem

• Many old databases used a two digit field to
  represent the year
• e.g., 97 for 1997
• The first two digits were assumed to be 19
• This saved memory and disk space
• Nobody expected these programs to still be in use
  by 2000!

11
The Y2K Problem

• Original programmers are long retired
• Few young programmers know COBOL
• Billions of lines of old COBOL code must be
  searched for 2-digit dates
• This is known as the Year 2000 (Y2K) Problem, or
  the Millennium Bug

12
Modern Approach to Data Bases

• Database Management Systems (DBMS) were developed
  to solve these problems
• Advantages
• Greater access to information
• data is independent from the application program
• easier to extract information from database
• Better control
• advanced data structures control data redundancy
• More efficient software development
• programming is done at a higher level
• the program is independent of the data

13
Fundamental Approaches to DBMS

• Hierarchical DBMS
• Network DBMS
• Relational DBMS
• Object-oriented DBMS

14
Hierarchical DBMS

• The first widely used DBMS technology
• Largely superseded by newer approaches
• Still important due to large installed base
  (legacy systems)

15
Network DBMS

• This is an extension of the hierarchical approach
• Groups of data called segments are linked
  logically
• The logical structure of the data base is called
  a schema
• Queries to the database are easy to make

16
Relational DBMS

• This is the most common type, especially for PCs
• Data is organized in the form of two- dimensional
  tables (equivalent to flat files)
• Tables are related through common fields
• Unlike the network approach, there is no
  predetermined relationship between data
• Usually slower but more flexible than network
  DBMS
• The user can easily extract information without
  programming

17
Relational DBMS
Student number
ES250b mark
Table 1
Table 2
18
Object-Oriented DBMS

• Relational databases are suitable for
  applications dealing mostly with alphanumeric
  data
• Applications such as CAD deal with complex data
  types (eg. geometry data)
• Objects consist of both data and methods
  (procedures) that act on the data
• Object-oriented DBMS are relatively new, but will
  become increasingly important in the future

19
Typical Capabilities of DBMS

• Create and maintain a database
• add, delete, revise records
• Extract and list records meeting certain criteria
• Make an inquiry (e.g. What were the total sales
  for 1997?)
• Sort records in ascending or descending order
• Provide on-screen forms for data input
• Generate formatted reports and charts

20
Querying the Database

• Query by example
• specify conditions
• e.g., list all students with marks gt 80
• Structured Query Language (SQL)
• standard method for accessing databases

21
Client/server Databases

• Traditional databases are located centrally
• Client/server technology permits data to reside
  at many locations on a network
• Users can query the database without regard for
  its physical location

22
Databases and the Internet

• Databases can be accessed via the Internet
  through a Web interface
• Often an interface can be developed for legacy
  databases
• The Common Gateway Interface (CGI) is used for
  this
• CGI is a standard for passing information between
  a Web server and other programs on the server

23
Interfacing to Databases
CGI interface
Database
Web browser
HTTP server
Server
24
ExampleInternet Search Engines

• Keywords you enter are converted to queries
• Queries are submitted to database
• Results are returned as HTML

25
Example Library Catalogue

• Used to require dumb terminals for access
• Now accessed via the Internet
• Online Public Access Catalogue (OPAC)
• this uses telnet
• WebPac
• Web-based

26
ExampleES250b Student Website Directory

• Implemented as a Microsoft Access database on
  Hyperserver
• Web interface developed using Microsoft InterDev
• Contents of Web forms are inserted into database
• Database can be viewed and queried
• Database reports are converted to HTML for display

27
Summary

• Database management is one of the main
  applications of computers
• DBMS allow databases to be built and manipulated
  without low-level programming
• Several types of database relational and
  object-oriented most important today
• Many databases can be accessed using a Web
  browser