COP4710 Databases

1
COP4710 Databases

• Fall 2009
• Instructor Yicheng Tu

2
Introduction to Database Systems

• 8/27/09

3
Roadmap

• Basic Concepts
• Functionalities of Database Systems
• Why Study Database Systems?
• An Abstract View of Data
• Database Languages
• Relational Databases
• History of Database Systems

4
Basic Concepts

• Data
• Information
• Database (DB)
• Database Management System (DBMS)
• Information Systems

5
Functionalities of DB Systems

• Maintain, query (large) datasets
• Desirable Features
• Efficiency in query processing
• Recovery from system crashes
• Concurrent data access
• Convenience in application development
• Data integrity and security

6
How about Simple Solutions?

• In the Pre-DBMS era, file systems DB systems
• Being simple is good?

7
Case Study

• Following data is available
• Patient (SSN, names, diagnosis, treatments)
• Doctor (SSN, HR info, names, patients)
• Question how to organize the files?

8
Two Unique Things about DB

• Efficient processing of ad-hoc queries
• E.g., find the average score of all the NBA games
  where there are at least two players scored over
  40
• Data Independence
• Applications insulated from how data is
  structured and stored

9
Why Study DB Systems?

• Two different views on DB
• Application developer
• DBMS developer (vendors/researchers)
• Database Administrator (DBA)
• Smart webmasters?
• All must understand how a DBMS works!
• Who can live without DB?
• DBMS encompasses most of CS
• OS, language, theory, AI,

10
Data Abstraction

• Physical level describes how a record (e.g.,
  customer) is stored.
• Logical level describes data stored in database,
  and the relationships among the data.
• type customer record
• customer_id string customer_name
  string customer_street string customer_city
  integer
• end

11
Data Abstraction 3 Layers
12
Data Abstraction - View

• Application programs hide details of data types.
  
• Views can also hide information (such as an
  employees salary) for security purposes.
• Helps query processing - views are the results of
  queries

13
Database Languages - I

• Data Manipulation Language (DML)
• Those for accessing and manipulating the data
  organized by the appropriate data model
• Retrieval
• Insertion/deletion
• Modification
• a.k.a., query language
• Two classes of languages
• Procedural what data is required and how to get
  those data
• Declarative (nonprocedural) what data is
  required without specifying how to get those data

14
Databse Languages - II

• Data Definition Language
• Defines the database schema
• create table account (account-number
  char(10), balance
  integer)
• Specifies other properties
• Domain constraints
• Referential integrity
• Assertions
• Authorization/security
• DDL outputs data dictionary with metadata

15
Data Models

• Data model collection of high-level descriptions
  for
• Data
• Data relationships
• Data constraints
• Can emphasize storage (e.g., relational model) or
  the underlying entity (e.g., semantic model such
  as ER)
• Relational model being the dominant one

16
Relational Model

• Data organized in tables
• Row record
• Column attributes

17
A Relational Database
18
A Brief History - I

• Early 1960s
• Integrated Data Store from GE
• By Charles Bachman, Network data model
• Late 1960s and 1970s
• IMS and SABRE
• Network and hierarchical data models
• Ted Codd defines the relational data model
• System R and Ingres prototype (UC Berkeley)

19
A Brief History - II

• 1980s
• SQL becomes industrial standard
• Transaction management, Jim Gray
• Object-oriented database systems
• 1990s
• Decision support and data-mining applications
• More data types (multimedia, text, etc)
• 2000s
• XML and XQuery standards
• Data stream management
• Automated database administration