CS580 Advanced Database Topics Chapter 1 Irena Pevac

1
CS580 Advanced Database Topics Chapter 1
Irena Pevac
2
Traditional Database Applications

• saving, checking, and credit card bank accounts
• hotel room reservations
• flight reservations
• library catalog
• medical records
• IRS records

3
Modern Databases

• Multimedia Databases
• store pictures, video clips, and sound
• Geographic Information System (GIS)
• store and analyze maps, weather data, satellite
  images
• Data Warehouses and on-line Analytical Processing
  (OLAP) Systems
• extract and analyze useful information from very
  large database systems
• Real-time and Active Databases
• control industrial and manufacturing process
• WWW Databases
• browsing the WWW and improve search techniques

4
Database Definition

• Database is a collection of related data
• Data known facts with an inherent meaning
• Represents some aspect of real world (miniworld
  Universe of Discourse UoD)
• It is a logically coherent collection of data
  with some inherent meaning. A random assortment
  of data is not a database.
• A database is designed, built and populated with
  data for a specific purpose. It has some
  intended group of users, and some preconceived
  applications in which these users are interested

5
Database Management System DBMS

• A collection of programs that enable users to
  create and maintain a database
• It facilitates process of defining, constructing,
  and manipulating databases for various
  applications
• Defining the data involves specifying the data
  types, structures and constraints for the data
  stored in DB
• Constructing the DB is the process of storing the
  data itself on some storage medium that is
  controlled by the DBMS
• Manipulating a DB includes querying to retrieve
  information, update data in DB, and generate
  reports.

6
Database Examples

• DB System
• page 6 Fig 1.1
• UNIVERSITY DB
• page 7 Fig 1.2

7
File Processing vs. Databases

• DB System contains DB itself plus the definition
  of the structure of DB called metadata stored in
  catalog.
• The catalog is used by DBMS and by users who want
  to know the structure of DB.
• General purpose DBMS is not written for a
  specific DB. It works with any DB.

• In file processing each application stores the
  definition of data as well.
• File processing system can access only specific
  data
• In file processing systems any change to the
  structure of the file requires changing all
  programs that access that file.

8
DB vs.. File Processing

• DBMS software can access diverse DBs by
  extracting the DB definition from the catalog and
  then using these definitions.

• Each new task require new program to process
  files that store data
• Change of data require change in all files that
  contain that data

9
DB

• DB is self describing. It contains not only
  itself but also a description of its structure.
• The structure of the DB is stored separately of
  the access DBMS software. A change in DB
  structure (one new column in a table) does not
  require any changes in DBMS software.

10
Program-data independence.

• The structure of data files is stored in the DBMS
  catalog separately from the access programs. This
  property is called program-data independence.

11
Object-oriented and object-relational databases

• In object-oriented and object-relational
  databases users can define operations on data as
  part of DB definitions.
• An operation or function is specified in two
  parts
• interface (or signature) includes operation name
  and data type of its parameters
• implementation (or method) is specified
  separately and can be changed without affecting
  the interface.

12
Program-operation independence

• User application programs can operate on data
  by invoking these operations through their names
  and arguments, regardless of implementation for
  methods. This is called program-operation
  independence.

13
Data abstraction

• The property that allows
• program data independence
• and
• program-operation independence
• is called data abstraction

14
Multi-user DBMS

• allows multiple users to access the DB at the
  same time.
• must include concurrency control
• when two different users do withdrawal at
  different places at the same time
• when two different reservation clerks try to
  assign the same seat to a passenger
• the update should be correct
• this are on-line transaction processing (OLTP)
  applications

15
Database sizes

• small
• one person typically defines constructs and
  manipulates the DB
• large
• few hundred users
• several persons are involved in design, use and
  maintenance of DB

16
Actors on the scene

• people whose job involves the use of a large DB

17
DB administrators

• a chief administrator
• oversees and manages DB, DBMS and application
  programs
• DB administrators
• administer these resources,
• authorize access to DB,
• coordinate and monitor its use,
• acquire software and hardware resources as needed
• are responsible for response time of the system

18
DB Designers

• responsible for identifying the data to be stored
  in DB
• identify the structure of the data in miniworld
• communicate with users of DB (to understand
  requirements)
• communicate with those who ordered DB (to
  understand business rules and structure of the
  data)
• design comes before DB is created and populated
  with data
• final design must support all user views and needs

19
End Users

• those who access the DB to
• query
• update
• generate reports

20
Casual users

• occasionally access the DB
• may need different information each time
• use SQL to specify their requests
• typically are managers or other occasional
  browsers

21
Naive or parametric end users

• majority of users are naïve users
• constantly query and/or update a DB
• use standard type of queries and updates
• canned transactions carefully programmed and
  tested
• bank tellers (check balances, post withdrawals
  and deposits)
• reservations clerks (check availability, make
  reservations, cancel reservations)

22
Sophisticated end users

• include engineers, scientists, business analysts
• familiarize themselves with DB to implements
  their applications to meet their complex needs

23
Stand alone users

• maintain personal DB
• use ready made program package with ready GUI
  interface of easy to use menus
• user of a tax package

24
System Analysts and Application Programmers
(Software Engineers)

• System analyst
• determine the requirements from end users
• develop canned transactions

• Application Programmers
• implement the specifications as programs
• should be familiar with DBMS

25
Workers behind the scene

• DBMS System designers and implementers
• Tool developers
• Operators and maintenance personnel

26
DBMS System designers and implementers

• design and implement the DBMS modules and
  interfaces as a software package

27
Tool developers

• design and implement tools,
• the software that facilitate design
• the software to improve performance
• design tools (case tools for E-R modeling, Salsa
  (Tabledesigner for SOM modeling)
• packages for performance monitoring
• natural language and GUI interfaces
• prototyping, simulation, test data generation

28
Operators and maintenance personnel

• system administration personnel responsible for
  the actual running and maintenance of the
  hardware and software environment for DB system

29
Advantages of using DBMS

• Controlling Redundancy

30
Controlling Redundancy

• In file processing,
• There is a lot of data repetition
• every user group maintains its own files for
  handling its data - processing applications..
• course registration office,
• accounting office
• both have individual file for students
• additional logical control
• duplication of effort
• wasted storage space
• files with same data may become inconsistent

• In DB approach
• data stored only once
• saves space
• controls redundancy

31
Restricting Unauthorized Access

• some users are not authorized to access entire DB
• financial information is confidential
• student grades are confidential
• SSN are confidential
• credit card numbers are confidential
• some users may only be permitted to retrieve the
  data
• some users are allowed to retrieve and update the
  data
• this requires controlling access to DB
  (passwords)
• DBMS should allow security and authorization
  system
• DBA use it to create accounts and to specify
  account restrictions

32
Providing Persistent Storage for Prog Objects and
Data Structures

• Databases can be used to provide persistent
  storage for program objects and data structures
• Programming languages have complex data
  structures (records, classes)
• The values of program variables are discarded
  when program terminates, unless the programmer
  explicitly stores them in permanent files
• Object - oriented DB are compatible with
  programming languages such as C, Java, Delphi
• DBMS software automatically performs any
  necessary conversions
• A complex object can be stored permanently in an
  OO DBMS
• Such object is called persistent
• Persistent object survives program termination
• It can be retrieved by another program
• OO DB offer data structure compatibility with one
  or more OO languages
• Traditional DB suffer from impedance mismatch
  problem

33
Permitting Inferencing and Actions Using Rules

• Some DB called deductive DB systems provide
  capabilities for defining deduction rules for
  inferencing new information from stored DB facts.
• Active DB systems provide active rules that can
  automatically initiate actions.

34
Providing Multiple User Interfaces

• There should be different interface for different
  users.
• GUI becomes standard.
• WWW access to a DB or Web- enabling a DB becomes
  common.

35
Representing Complex Relationships Among Data

• One record may be related to several other
  records.
• One customer record may be related to several
  order records.
• DBMS must enable representation of complex
  relationships among data

36
Enforcing Integrity Constraints

• In most mini - worlds, data should satisfy
  certain integrity constraints
• For EMPLOYEES Age should be 16 -70
• For STUDET Grades values should be A, B, C, D,
  F
• EmployeeID should have unique value for each
  employee
• For each section of the course there must be at
  least one related course.

37
Constraints

• Constraints should be identified and specified at
  design time.
• Some constraints can be specified to DBMS system.
• Other constraints must be enforced by application
  programs or at data entry time.
• Some errors may be detected that way, but not
  all.
• For instance age of 167 can be detected as error,
  but age 45 instead of 54 cannot be detected since
  either may be legal data.

38
Backup and Recovery

• A DBMS must provide facilities for recovering
  from hardware or software failures.
• The backup and recovery subsystems of the DBMS is
  responsible for recovery.
• If failure happens in the middle of the
  transaction, the recovery subsystem should
  restore DB to the state it was in before the
  program started executing.

39
Implications of Using DB

• Enforcing standards
• Large organizations can enforce standards when
  using DB
• Reduced application development time
• Once a DB is set up and running it takes
  substantially less time to create new
  applications.
• For a single application file approach may be
  better.
• Flexibility
• Change of requirements implies change the
  structure of DB
• Change of structure does not affect data already
  entered into DB.

40
Implications of Using DB

• Availability of Up-to-Date Information
• A DBMS makes DB available to all users. After any
  update, all other users can immediately see this
  update. This is essential for transaction
  processing.
• Economics of Scale
• Reduction of overlap that existed in file
  processing, DBMS reduces overall cost of
  operation and management.

41
When not to use DBMS

• The DB applications are simple, well defined, and
  not expected to change.
• There are stringent real-time requirements for
  some programs that may not be met because of DBMS
  overhead.
• Multiple user access to data is not required.