Database Systems: An Overview, Lecture notes of Database Programming

Download Database Systems: An Overview and more Lecture notes Database Programming in PDF only on Docsity!

Chapter 1

Overview of Database Systems

Overview

 What is a database?

 What is a Database Management System (DBMS)?

 Why DBMS for managing data?

 What are data models?

 How to retrieve data and build database

Applications?

 How is application data represented in a DBMS?

 How DBMSs support concurrent access and failure

recovery?

 What are the main components of a DBMS?

 Who is involved with databases?

Database Applications

 Banking: all transactions

 Airlines: reservations, schedules

 Universities: registration, grades

 Sales: customers, products, purchases

 Manufacturing: production, inventory, orders,

supply chain

 Human resources: employee records,

salaries, tax deductions

Databases touch many aspects of our lives!

Files vs. DBMS

 Application must stage large datasets between

main memory and secondary storage (e.g.,

buffering, page-oriented access, 32-bit

addressing, etc.)

 Special code for different queries

 Must protect data from inconsistency due to

multiple concurrent users

 Crash recovery

 Security and access control

In the early days, database applications were

built on top of file systems

Why Study Databases??

 Shift from computation to information

 (^) at the “low end”: scramble to web space (a mess!)  (^) at the “high end”: scientific applications

 Datasets increasing in diversity and volume

 (^) Digital libraries, interactive video, Human Genome project, EOS project  (^) ... need for DBMS exploding

 DBMS encompasses most of CS

 (^) OS, languages, theory, AI, multimedia, logic

Data Models

 (^) A data model is a collection of high-level constructs for describing stored data that hides low-level storage details.  (^) A schema is a description of a particular collection of data, based on a given data model.  (^) The relational data model is the most widely used data model today.  (^) Main concept: relation , basically a table with rows and columns.  (^) Every relation has a schema , which describes the columns, or fields.  (^) For example, Students(sid: string, name: string, login: string, age: integer, gpa: real).

Hierarchical Data Model

The hierarchical model organizes data records as collections of trees

Levels of Abstraction

 (^) Many views (external schema) , single conceptual (logical) schema and physical schema.  (^) Views describe how users see the data.  (^) Conceptual schema (also called logical schema) defines logical structure based on data model.  (^) Physical schema describes the files and indexes used (i.e., the storage details).  Conceptual & external sche are defined using DDL  Data is modified/queried using DML Physical Schema Conceptual Schema View 1 View 2 View 3 External Schema Metadata stored in system catalogs

Examples: University Database

 Physical schema:

 (^) Relations stored as unordered files.  (^) The second column of Students is indexed by a B-tree.

 Conceptual schema:

 (^) Students(sid: string, name: string, login: string, age: integer, gpa:real)  (^) Courses(cid: string, cname:string, credits:integer)  (^) Enrolled(sid:string, cid:string, grade:string)

 External Schema (View):

 (^) Course_info(cid:string,enrollment:integer)

Data Independence

 Applications insulated from how data is structured

and stored.

 Logical data independence : Protection from

changes in logical structure of data.

 (^) Achieved by revising view definition in accordance with new logical schema. Users don’t see the change.

 Physical data independence : Protection from

changes in physical structure of data.

 (^) Logical structures (e.g., tables in RDB) are supported by different physical storage structures.

This is one of the most important benefits

of using a DBMS!

Logical Database Design

individual report group report individual group member of name group id submitted by submitted by period period hours hours date activity Entity-Relationship Diagram

Progress Reports Database

individual_report groups group_report

Example SQL Query

 List the group identifiers of all groups that have

begun implementation.

SELECT group-id

FROM individual_report, groups

WHERE individual_report.name =

groups.name

AND activity = ‘implementation’;

Programs for Database Access

 Provide interface for naive and/or untrustworthy users

void transfer(int acct1, int acct2, int amount) { exec sql begin declare section ; int acct1_balance; exec sql end declare section ; exec sql update accounts set balance = balance - :amount ; where account_number = :acct1; exec sql update accounts set balance = balance + :amount; where account_number = :acct2;