Database Management Systems ISYS 464

1
Database Management Systems ISYS 464

• Fall 2004
• David Chao

2
Introduction to Databases

• The most important component in an information
  system
• A group of related files
• Created to support business operations
• Day-to-day operations TPS
• Decision-makings MIS, DSS
• Strategic plans ESS
• Integrated to reduce data duplication and
  maintain data consistency

3
Traditional File-Based Systems

• A collection of application programs that perform
  services for the end-users. Each program defines
  and manages its own data.
• Example

4
Comma-Delimited File

• It stores each data item with a comma separating
  each item and places double quotes around string
  fields.
• Student file with fields SID, Sname, and GPA
• S5, Peter, 3.0
• S1, Paul, 2.5

5
Sequentially Accessing the Student File to
Compute Average GPA
Dim fileNumber, stCounter As Integer Dim
SID, SNAME As String Dim gpa, sumGpa As
Double fileNumber FreeFile()
FileOpen(fileNumber, "c\stdata.txt",
OpenMode.Input) Do While Not
EOF(fileNumber) Input(fileNumber,
SID) Input(fileNumber, SNAME)
Input(fileNumber, gpa) sumGpa
gpa stCounter 1 Loop
MessageBox.Show(sumGpa / stCounter.ToString)
6
Limitations of the File-Based Approach

• Duplication of data
• Data inconsistency
• Program-data dependence
• When file structure changed, all programs that
  access the file must be modified to conform to
  the new file structure.
• The definition of the data is embedded in the
  program.
• Fixed queries
• No facilities for asking unplanned, ad hoc queries

7
Database Approach

• The database holds not only the data but also a
  description of the data.
• System catalog (or data dictionary, or meta data)
• Data about data
• Program-data independence

8
Database management System (DBMS)

• A software that enables users to define, create,
  maintain, and control access to the database.
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Control access
• Security, integrity, concurrent access, recovery,
  support for data communication, etc.
• Utility services
• File import/export, monitoring facilities, etc.

9
ANSI-SPARC Architecture

• A three-level architecture to provide data
  independence
• External level The users view of the database.
  This level describes the part of the database
  that is relevant to each user.
• Conceptual level This level describes what data
  is stored in the database and the relationships
  among the data.
• Internal level The physical representation of
  the database on the computer. This level
  describes how the data is stored in the database.
• File organizations, indexes

10
Data Independence

• Data independence means that upper levels are
  unaffected by changes to lower levels.
• Logical data independence
• Changes to the conceptual level, such as the
  addition of new entities, attributes, or
  relationships, should be possible without having
  to change the existing external level design.
• Physical data independence
• Changes to the physical level, such as using a
  different file organization, indexes, should be
  possible without having to change the conceptual
  level design.

11
Three-Level Example

• Employee Entity
• Conceptual design
• EmpID 4 characters
• EmpName 30 characters
• DateOfBirth Date field 8 bytes
• Salary Number(7,2)
• Sex 1 character
• Physical level
• Record size 4 30 8 7 1 50 bytes
• Sequential file with index on EmpID field
• External level
• EmpAgeView
• EmpID, EmpName, AgeYear(Today())
  Year(DateOfBirth)
• EmpSalaryView EmpID, EmpName, Salary

12
Benefits of Using Views

• Views provide a level of security.
• Views provide a mechanism to customize the
  appearance of the database.
• Views provide a consistent, unchanging picture of
  the database, even if the underlying database is
  changed.

13
Advantages of DBMS

• Control of data redundancy
• Data consistency
• Support Ad Hoc queries
• Improved data integrity, security, backup and
  recovery, concurrency

14
Multi-User DBMS Architectures

• Teleprocessing
• One computer with a single CPU and a number of
  dumb terminals.
• Add burden on the central computer, which not
  only had to run the application programs and
  DBMS, but also had to carry out a significant
  amount of work on behalf of the terminals (such
  as formatting data for display)
• File-Server
• Applications run on workstations that contain a
  full copy of the DBMS. File-server acts as a
  shared hard disk drive. The DBMS on each
  workstation sends requests to the file-server for
  data, but none of the processing is done by the
  server.
• Generate a significant amount of network traffic.
• Client-Server
• Computer network development
• Balanced distributed processing

15
SQL queries
Client
Database Server
Results
Database Server A high processing power computer
with advanced DBMS. Client A PC that runs
database applications. SQL interface.
16
Client Functions

• Manages the user interface.
• Accepts and checks syntax of user input.
• Implements business rules.
• Generates database requests and transmits to
  server.
• Passes response back to user.

17
Database Server Functions

• Checks authorization.
• Accepts and processes database requests from
  clients.
• Ensures integrity constraints not violated.
• Performs query/update processing and transmits
  response to client.
• Provides concurrent database access, transaction
  management, and recovery control.

18
Advantages of Client-Server Architecture

• Enables wider access to databases.
• Increased performance Different CPUs can be
  processing applications in parallel.
• Hardware costs may be reduced Only server
  requires higher storage and processing power to
  manage the database.
• Network traffic is reduced Only database
  requests and results are sent.
• Increased database integrity.

19
Database Application

• It is a program that interacts with the database
  at some point in its execution by issuing an
  appropriate request (typically an SQL statement)
  to the DBMS.
• Database programming

20
The three components in a database application

• 1. Presentation user interface
• Menus, forms, reports, etc
• 2. Processing logic
• Business rules
• 3. Database

21
Categories of Database Applications

• One-Tier
• Legacy online transaction processing
• PC database application
• Two-Tier client/server
• Client-based presentation.
• Processing logic is buried either inside the user
  interface on the client or within the database on
  the server, or both.
• Three-Tier, N- tier
• Processing logic is separated from the interface
  and database.

22
Two-tier

• Simplicity
• Provides a basic separation of tasks. The client
  is primarily responsible for the presentation of
  data to user, and the server is primarily
  responsible for supplying data services to the
  client.
• Fat client
• More functions are placed on the client
• Fat server
• More functions are placed on the server.

23
Three-Tier

• 1. User interface, 2. Business logic and data
  processing layer, 3. Database server.
• Advantage
• Implementing business rules as components.
• Objects that provide services to other client
  applications.
• Application maintenance is centralized.
• Separation of the business logic from the
  database functions.
• Fit naturally to the Internet environment.

24
The Web as a Database Application Platform

• Three-tier architecture
• Browser, web server, database server, processing
  logic
• Advantages
• Cross-platform support
• Graphical user interface

25
The Web as a Database Application Platform

• Disadvantages
• Reliability Internet is unreliable and slow.
• Security
• Costs hardware and software 20, marketing 24,
  content development 56.
• Potentially enormous peak load.

26
Major Databse Management Activities

• Creating database
• Updating database
• Querying database

27
Creating Database

• Analysis
• System analysis
• Data Flow Diagram, UML
• Data modeling
• ERD
• Design
• Maps the data model on to a target database
  model.
• Implementation Efficiently store and retrieve
  data
• File organization and index

28
Updating Database

• Insertions, deletions, modifications
• Insertion only, no deletion
• Concurrent processing
• Read/Write
• Transaction management

29
Querying Database

• Relational algebra
• SQL
• QBE

30
New Developments in Database

• Object-Oriented database
• Object-Relational database
• Decision support with data warehouse
• Web based database applications
• XML database

31
Course Overview

• An introduction to the three-level database
• Conceptual level
• Data modeling, ERD, Normalization
• Physical level
• File organizations and index
• External level
• Relational algebra, SQL, QBE
• Database management techniques