Relational Database Systems:

1
Course Introduction

• Relational Database Systems
• Creating a relational database (DDL)
• Formulating SQL queries (DML)
• Embedded SQL
• Relational algebra
• Relational model, relational integrity, etc.
• Data Modeling
• Entity-relationship model
• Converting an E-R schema into a relational schema
• Web-Based Database Applications

2
Information Management

• Modeling an enterprise, which is an
  application-world with
• Entities (e.g., students, courses)
• Relationships (e.g., Garfield is taking CS 311)
• Creating a database with a database management
  system (DBMS).
• Maintaining a database
• Inserting, updating, and deleting data
• Backing up the database
• Maintaining security
• Supporting users

3
Data Encoding
Information
Data Modeling
Entities, Relationship, etc.
Numbers, Strings, Records, Pointers, etc.
Bits, Bytes, Pages, etc.
Electronic Charges, Magnetic polarization, etc.
4
Advantages of Integrated Data Management

• Data sharing
• No (logical) redundancy of stored data
• Simple and efficient data access
• Reduced application development time
• Data integrity and security
• Concurrent access, recovery from crashes
• Uniform data administration
• Economy

5
Database v.s. DBMS

• A database is an integrated collection of data.
• A database management system (DBMS) is a software
  package designed to create and manage a database.
• The data stored in a database are organized
  according to the data model supported by the
  DBMS.
• A relational database, for example, stores the
  data in a collection of tables.

6
DBMS Functions

• The following functions can be performed for any
  application
• Data definition
• Data manipulation
• Security and data integrity
• Recovery and concurrency control
• Data dictionary
• Performance tuning

7
DBMS
A general software package that can be used to
create database systems for different applications
Application Development Costs
8
Problems with Storing Data in Files

• Data stored in different files cannot be easily
  related.
• Accessing desired records may not be easy.
• Efficient protection against inconsistency caused
  by multiple concurrent users not easy to
  implement.
• Effective crash recovery not supported.
• Security and access control not enough.

9
Why Study Databases?

• Shift from computation to information
• scramble to webspace
• scientific applications
• Datasets increasing in diversity and volume.
• Digital libraries
• interactive video
• Environmental protection
• DBMS encompasses most of CS
• OS
• Theory
• Data Structures, Algorithms, Languages
• Multimedia

10
Data Model

• A data model is a collection of concepts for
  describing data.
• The relational model of data is the most widely
  used data model today.
• Main concept relation, basically a table with
  rows and columns.
• Relations can represent entities with attributes
  and associations among entities.
• A schema is a description of the structure of
  data, using the a given data model.

11
Levels of Abstraction
View 1
View 2
View 3

• Views describe how users see the data.
• The conceptual schema defines logical structure
  of the data.
• The physical schema describes how the data are
  stored on physical devices.

Conceptual Schema
Physical Schema

• Schemas are defined using DDL data is
  modified/queried using DML.

12
Data Independence

• Applications insulated from how data is
  structured and stored.
• Physical data independence
• Protection from changes in physical structure of
  data.
• Data should be accessible even when storage media
  and/or formats change.
• Logical data independence
• Protection from changes in logical structure of
  data.
• Old applications should work.

13
Languages

• Host Languages
• C, C
• Java, C
• PHP, Python
• Data Sublanguages (DSLs)
• SQL
• Data Definition Language (DDL)
• Used to define the structure of a database
• Data Manipulation Language (DML)
• Used to access and manipulate data
• CODASYL DBTG Language

14
DDL, Schema, and Database

• DDL
• A language for defining a database schema
• Database Schema
• A definition of the structure of a database as a
  collection of type definitions (record type
  declarations)
• Intension (sic)
• Database
• A collection of instances (occurrences) of types
• Extension

15
DBA (Database Administrator)

• Defines the conceptual schema
• Defines the internal schema
• Talks to the users
• Defines backup and recovery procedures
• Conducts performance tuning
• Conducts security management

16
Layered Architecture of a DBMS
Query Optimization and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
17
Three Tier Database System Architecture
End users
Applications
Clients
DBMS
Server
Database
18
Distributed Data Access

Client Machines
Communication Network
Server Machines
19
Distributed Database System
Communication Network
20
Summary

• A database is an integrated collection of data
  shared by possibly multiple applications.
• A DBMS is a general software package for creating
  and managing a database.
• A DBMS supports query languages, recovery from
  system crashes, concurrent access, quick
  application development, data integrity, and
  security.
• Levels of abstraction give data independence.
• A DBMS typically has a layered architecture.
• DBAs hold responsible jobs and are paid well.
• DBMS RD is one of the broadest areas in CS.