Introduction to Database Systems

1
Lecture-1

• Introduction to Database Systems

2
Daily Interaction with Databases

• Trip to a bank
• Processes conducted?
• Withdraw, deposit, etc
• Stored data?
• Customers, accounts , balances, employees, etc
• Book purchase from eBay (paid thru PayPal)?
• Book inventory, customer, credit card, credit
  card company, sellers balance, your balance, etc
  

3
Basic Definitions

• Database A collection of related data
• Article? Phonebook?
• Data Known facts that can be recorded and have
  an implicit meaning
• Information
• Knowledge
• Mini-world Some part of the real world about
  which data is stored in the database
• For example, student grades and transcripts at a
  university
• This aspect of the university

4
Basic Definitions

• An integrated shared repository of operational
  data of interest to an enterprise
• INTEGRATED it must be the unification of several
  distinct files and entities some of which are
  interrelated
• SHARED same data can be used by more than 1 user
  (concurrently)
• REPOSITORY implies persistence
• OPERATIONAL DATA data on accounts, parts,
  patients, students, employees, genes, stocks,
  pixels,...
• Non-operational data I/O data, transient data in
  buffers, queues...
• ENTERPRISE bank, warehouse, hospital, school,
  corp., gov. agency, person

5
Basic Definitions

• Databases can be manual
• e.g. a library database that stores cards on all
  available books usually sorted by authors name,
  subject, and title
• Database Management System (DBMS) A collection
  of programs that facilitates the creation and
  maintenance of a computerized database
• Usually, general-purpose complex software
• Database System The DBMS software together with
  the database itself
• Title of this course
• Sometimes, the applications are also included but
  this is not a rule

6
(No Transcript)
7
Example of a Database
8
Example of a Database

• Mini-world for the example
• Part of a UNIVERSITY environment
• Some mini-world entities
• STUDENTs
• COURSEs
• SECTIONs (of COURSEs)
• PREREQUISITEs (of COURSEs)
• GRADE_REPORTs (of STUDENTs)
• Some mini-world relationships
• COURSEs are offered in specific SECTIONs
• STUDENTs take SECTIONs
• COURSEs have prerequisite COURSEs

9
Example of a Database

• Every entity has a set of records
• HINT In analogy to OOP, an entity is an object
  class while a record is an object instance
• To define a database
• Describe the structure of every record
• A set of properties called data elements
• Each has a name and a data type
• Some other restrictions or constraints (e.g.
  valid major)
• To construct a database
• Store the actual records in their corresponding
  tables
• To manipulate a database
• Query, insert, edit, delete

10
File Processing

• File-Based Systems
• Ancestors of database systems
• Collection of application programs that perform
  services for end users (e.g. reports)
• Each program defines and manages its own data
• Each user defines and implements their own files
  for the specific task they are performing
• University example (see next slide)
• Registrar's Office Grade reporting
• Business Office Student balances

11
File-Based Processing
1 Student Name, Address, Class, Courses, Grades,
Instructors 2 Student Name, Address, Class,
Tuition, Fees, Holds Both need student data (not
the same exact data though) but each maintains
their own files (and programs to manipulate the
data) !!! Redundancy!
12
Limitations of File-Based Approach

• Program-Data dependence
• File structure is defined in the program code
• Not easy to add a new field or change some design
  issues
• Separation and isolation of data
• Each program maintains its own set of data
• Users of one program may be unaware of
  potentially useful data held by other programs

13
Limitations of File-Based Approach

• Incompatible file formats (even if they are
  aware)
• Programs are written in different languages for
  different file formats, and so cannot easily
  access each others files
• Duplication (Redundancy) of data
• Same data is held by different programs ? Wasted
  space
• (Inconsistency) Potentially different values
  and/or different formats for the same item
• Fixed Queries/Proliferation of application
  programs
• Programs are written to satisfy particular
  functions
• Any new requirement needs a new program
• E.g. University wants to know which department
  has highest new freshmen enrolment ? must write
  new code for functionality

14
Main Characteristics of the Database Approach

• Why replace file-based systems with database
  systems?
• Self-describing nature of a database system A
  DBMS catalog stores the description of the
  database
• The description is called meta-data (tables,
  fields, types, etc)s
• This allows the same DBMS software to work with
  different databases (general-purpose)
• Insulation between programs and data a.k.a.
  program-data independence.
• Allows flexibility in changing the data, its
  storage structures and operations without having
  to change the DBMS access programs

15
Main Characteristics of the Database Approach

• Data Abstraction A data model is used to hide
  storage details and present the users with a
  conceptual view of the database
• Users dont care how things are physically stored
• Where record1 ends and record2 starts on stored
  files
• File-based systems need to know this info
• Support of multiple views of the data Each user
  may see a different view of the database, which
  describes only the data of interest to that user
• Easy to do
• Registrars office Vs. Business office
• Student Name, Address, Class, Courses, Grades,
  Instructor
• Student Name, Address, Class, Tuition, Fees, Holds

16
Main Characteristics of the Database Approach

• Sharing of data and multi-user transaction
  processing allowing a set of concurrent users to
  retrieve and to update the database
• Concurrency control recovery within the DBMS
  guarantee that each transaction is correctly
  executed (or completely aborted as we shall see
  later)
• OLTP (Online Transaction Processing) is a major
  part of database applications
• In file processing, every file is usually
  accessed by a single user at a time

17
Additional Advantages for using the Database
Approach

• Restricting unauthorized access to data
• Providing storage structures for efficient query
  processing
• Indexes (similar indexes at the end of books)
• Reduced application development time (1/6th)
• No need to worry about how to store and process
  your data
• No low level processing as in file-based systems
• Just define it and construct it

18
Additional Advantages for using the Database
Approach

• Storing objects
• In OO programming languages, once the program
  exits, the values of class properties are
  discarded unless the programmer explicitly stores
  them in files and reloads them every time the
  program runs
• Conversion cost (memory storage is different than
  file storage and vice versa) --- how to store a
  tree in a file ?
• Object Persistence
• Representing complex relationships among data
• Enhances our understanding of the data
• Enforcing integrity (semantics) constraints on
  the database
• Easy to specify and automatically enforced
• Salarygt0
• Every section (grade) record must be related to a
  course (student) record

19
Additional Advantages for using the Database
Approach

• Availability of up-to-date consistent information
• Every update done by any user is visible to all
• Very important for on-line transaction systems
  such as airline, hotel, car reservations
• Economies of scale by consolidating data and
  applications across departments
• Wasteful overlap of resources and personnel can
  be avoided
• Centralized powerful equipment shared by all
• No need to buy powerful equipment for every
  department to run its file-based applications
• Each department could get cheaper ones for its
  own
• E.g. Access terminals

20
When not to use a DBMS

• A DBMS may be unnecessary
• If the database and applications are simple, well
  defined, and not expected to change
• If there are stringent real-time requirements
  that may not be met because of DBMS overhead
• Usually slower than file-based systems because
  they are more complex and general
• Analogy A program that operates a machine as
  printer machine VS an OS
• If concurrent access to data by multiple users is
  not required
• When no DBMS may suffice
• If the database system is not able to handle the
  complexity of data because of modeling
  limitations
• If the database users need special operations not
  supported by the DBMS

21
Database Users

• Users may be divided into those who
• Actually use and control the content
• Actors on the Scene
• E.g., database administrators and data entry
  personnel (end users)
• Enable the database to be developed and the DBMS
  software to be designed and implemented
• Workers Behind the Scene
• E.g., database designers
• Reading for next time