Introduction to Database Systems

1
Lecture-1

• Introduction to Database Systems

2
Review

• Data vs. Information vs. Knowledge
• What is?
• Database
• DBMS
• Database System

3
(No Transcript)
4
Example of a Database
5
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

6
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

7
File Processing

• Problems with file-based Systems
• Program-data dependence
• Data separation
• Incompatible file formats
• Inconsistency due to redundancy
• Fixed functionality

8
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

9
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

10
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

11
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

12
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

13
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

14
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

15
Example

• Homeland Security applications to combat
  terrorism
• Find all passengers on an air flights who match a
  certain profile allowing some uncertainty!
• Age, Education, Name, Origin, etc
• Data Mining/Neighborhood Queries

16
Schemas versus Instances

• Database Schema The description of a database
• Includes descriptions of the database structure
  and the constraints that should hold on the
  database
• Schema
• Database Instance The actual data stored in a
  database at a particular moment in time
• Also called database state (or occurrence or
  snapshot)
• Valid State
• A state that satisfies the structure and
  constraints of the database
• DBMS responsible for assuring it by referring to
  the meta-data
• Schema constructs and constraints are stored in
  catalog as meta-data

17
(No Transcript)
18
(No Transcript)
19
Database Schema Vs. Database State

• Major Distinction
• The database schema changes very infrequently
  (class VS object)
• Schema evolution due to change in application
  requirements
• The database state changes every time the
  database is updated
• Schema is also called intension, whereas state is
  called extension

20
Data Models

• Data Model A set of concepts to describe the
  structure (e.g. entities/relationships) of a
  database
• Many forms of data models
• Makes data abstraction possible
• Hides details of physical data storage from the
  user
• Conceptual (high-level, semantic) data models
• Provide concepts that are close to the way many
  users perceive data
• Also Use high-level concepts as entities,
  attributes, and relationships to represent the
  real world
• Examples of the concepts in a UNIVERSITY
  mini-world

21
(No Transcript)
22
Categories of Data Models

• Physical (low-level, internal) data models
• Provide concepts that describe details of how
  data is stored in the computer
• Record formats, record orderings and access paths
• Target computer specialists and not typical end
  users
• E.g. an implementation of a database on a
  specific DMBS system
• Logical data models
• Provide concepts that fall between the first two
• balancing user views with some computer storage
  details
• Relational data model
• Legacy data models
• Network and hierarchical data models

23
(No Transcript)
24
Three-Schema Architecture

• Project
• Defines schemas at three levels (next slide)
• Internal schema at the internal level to describe
  physical storage structures and access paths
• Typically uses a physical data model
• Conceptual schema at the conceptual level to
  describe the structure and constraints for the
  whole database for a community of users
• Uses a conceptual or an logical data model
• External schemas (many) at the external level to
  describe the various user views
• Usually uses the same data model as the
  conceptual level

25
Three-Schema Architecture

• Mappings among schema levels are needed to
  transform requests and data. Programs refer to an
  external schema, and are mapped by the DBMS to
  the internal schema for execution ? data
  independence

26
Data Independence

• In a DBMS that fully supports data independence,
• When a schema at a lower level is changed
• Only the mappings between this schema and
  higher-level schema(s) need to be changed
• The higher-level schemas themselves are unchanged
• The application programs need not be changed
  since they refer to the external schemas