Database%20Management%20Systems%20ISYS%20464

1
Database Management Systems ISYS 464

• David Chao

2
Introduction to Databases

• The most important component in an information
  system
• Created to support all levels of business
  operations
• Day-to-day operations
• TPS, CRM, ERP
• Decision-makings
• DSS, data warehouse
• Strategic plans

3
Definitions

• Database organized collection of logically
  related data
• A group of related files
• Data stored representations of meaningful
  objects and events
• Structured numbers, text, dates
• Unstructured images, video, documents
• Information data processed to increase knowledge
  in the person using the data
• Metadata data that describes the properties and
  context of user data

4
Example of Metadata
5
Traditional File-Based Systems

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

6
Example
7
Limitations of the File-Based Approach

• Duplication of data
• Data inconsistency
• Limited data sharing
• 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

8
Problems with Program-Data Dependency

• Each application program needs to include code
  for the metadata of each file
• Each application program must have its own
  processing routines for reading, inserting,
  updating, and deleting data

9
Example 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
• Questions
• How many students?
• What is average GPA?

10
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)
11
Database Approach

• Central repository of shared data
• The database holds not only the data but also a
  description of the data.
• System catalog (repository , data dictionary, or
  metadata)
• A central location where data descriptions are
  stored.
• Data about data
• Program-data independence

12
Advantages of the Database Approach

• Program-data independence
• The separation of data descriptions from the
  application programs that use the data.
• Allows the data to change without changing the
  application programs.
• Planned data redundancy
• Improved data consistency
• Improved data sharing
• Enforcement of standards

13
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.
• Support Ad Hoc queries

14
Database Management System

• A software system that is used to create,
  maintain, and provide controlled access to user
  databases

Order Filing System
Central database Contains employee, order,
inventory, pricing, and customer data
Invoicing System
DBMS
Payroll System
DBMS manages data resources like an operating
system manages hardware resources
15
Evolution of DB Systems
16
Database Schema

• External Schema
• User Views
• Subsets of Conceptual Schema
• Conceptual Schema
• This level describes what data is stored in the
  database and the relationships among the data.
• View of the data administrator
• E-R models
• Internal schema
• Logical schema
• Underlying implementation and design
• Relational table design
• Physical Schema
• File organizations, indexes

17
Figure 2-7 Three-schema architecture
Different people have different views of the
databasethese are the external schema
The internal schema is the underlying design and
implementation
18
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.

19
Three-Level ExampleEmployee Entity

• Conceptual design
• Employee entity with attributes EmpID, EmpName,
  DateOfBirth, Salary, and Sex.
• Internal level
• Logical schema
• EmpID 4 characters
• EmpName 30 characters
• DateOfBirth Date field 8 bytes
• Salary Number(7,2)
• Sex 1 character
• Physical schema
• 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

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

21
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

22
The Range of Database Applications

• Personal databases
• Desktop, PDA/Smart Phone
• Workgroup databases
• Departmental/divisional databases
• Enterprise database
• Enterprise Resource Planning (ERP)
• Integrate all enterprise functions
  (manufacturing, finance, sales, marketing,
  inventory, accounting, human resources)
• Data Warehouse
• Integrated decision support system derived from
  various operational databases

23
The three components in a database application

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

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

25
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.
26
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.

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

28
The Web as a Database Application Platform

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

29
Figure 2-9 Three-tiered client/server database
architecture
30
Major Database Management Activities

• Creating database
• Updating database
• Querying database

31
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

32
Two Approaches to Database and IS Development

• SDLC
• System Development Life Cycle
• Detailed, well-planned development process
• Time-consuming, but comprehensive
• Long development cycle
• Prototyping
• Rapid application development (RAD)
• Cursory attempt at conceptual data modeling
• Define database during development of initial
  prototype
• Repeat implementation and maintenance activities
  with new prototype versions

33
Systems Development Life Cycle(see also Figures
2.4, 2.5)
34
(No Transcript)
35
Updating Database

• Insertions, deletions, modifications
• Update pattern
• Insertion only, no modification
• Concurrent processing
• Read/Write
• Transaction management

36
Querying Database

• Relational algebra
• SQL
• QBE

37
New Developments in Database

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

38
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
• Other database management technologies