Introduction to Database Systems

1
Introduction to Database Systems

• CMPSC363/607
• Lecture 1

2

• Classes D218A, 1000 AM 1150 AM MW
• Instructor Zhiyong (John) Xu
• Office F437B
• Office hours
• 130 PM 330 PM, MW or by Appointment.
• Email zxu_at_mcs.suffolk.edu
• URL http//cartan.cas.suffolk.edu/zxu/teaching/F
  -08/C363/C363.htm

3
Course Overview

• Background and History
• Data Models
• E/R Model
• Relational Model
• Object Oriented Model
• XML and Database
• Database Programming
• Relational Algebra
• SQL
• Object Oriented

4
Textbook

• Jeffrey D. Ullman and Jennifer Widom, A First
  Course in Database Systems, Third Edition,
  Prentice Hall, 2007
• Hector Garcia-Molina, Jeffrey D. Ullman and
  Jennifer Widom, Database Systems The Complete
  Book, Second Edition, Prentice Hall, 2008
• Other useful textbooks
• Fundamentals of Database Systems (Elmasri and
  Navathe)
• An Introduction to Database Systems (C. J. Date)
• Database Management Systems (Ramakrishnan)

5
Prerequisites

• Data Structures
• Algorithm
• Any Programming Language
• Discrete Math etc.

6
Tentative Schedule

• Introduction (1-2 classes)
• E/R Model (2-3 classes)
• Relational Model (3-4 classes)
• Other Models (2-3 classes)
• Relational Algebra (3-4 classes)
• SQL (4-5 classes)
• Constraints and triggers (1-2 classes)
• System Aspects of SQL (3-4 classes)
• XML (3-4 classes)

7
Course Load

• 10 - 12 homework assignments
• 1 project
• 2 or 3 Midterm Exams
• A Final Exam
• Homework and project assignments are due in the
  beginning of class one or two weeks that follows.
  Late homework will not be accepted, and a score
  of zero will be assigned for that assignment.

8
Course Policy

• exams no access to any material nor discussion
  with anyone (except the instructor) is allowed.
• assignments solutions should be developed
  independently. Stealing, giving or receiving any
  code drawings, diagrams, text, or designs from
  another person is not allowed.
• max penalty for academic dishonesty F in the
  course reported to the university.

9
Grading Policy

• The grade will be calculated using the following
  weights
• Homework and Quizzes 25
• Project
  15
• Mid-term Exams 30
• Final Exam
  30
• A 90, B 80, C70, D 60, F lt60

10

• Starting from now
• Something completely different!

11
What is Database?

• Essentially, a database is a set of data, or a
  collection of information. These data are
  related.
• Examples
• Student Information
• human genome sequence
• company products information

12
More Examples

• Airline Reservation Systems
• Reservations by a single customer on a single
  flight, including such information as assigned
  seat or meal preference
• Information about the flight, the airports thy
  fly from and to, the departure and arrival times,
  etc.
• Ticket prices, requirements, and availability
• Banking Systems
• Customers, accounts, loans and the balances
• Deposit or withdraw money

13
Whats DBMS?

• DataBase Management System (DBMS)
• A powerful tool for creating and manage large
  amounts of data efficiently and allowing it to
  persist over long periods of time, safely. It
  also provides efficient accesses to multiple
  users.
• It is used to manage databases.

14
DBMS capabilities

• Persistent Storage
• Programming Interface
• Transaction management

15
DBMS capabilities (Cont.)

• Allow users to create databases and specify their
  schema, using data-definition language.
• Allow users to query and modify the data, using
  data-manipulation language.
• Support the storage of very large amount of data
  over a long period.
• Data consistency and failure recovery.

16
File System

• Why not using file system?
• Can be used to store data information for a long
  period as well.
• Consistency and failure recovery capability
• Problems
• no query language, no efficient access for a data
  item
• support of the creation of database is limited
• consistency control is not adequate
• Management of Data is complex

17
DBMS Evolution

• File Systems
• Hierarchical Model (Tree-based)
• Network Model (Graph-based)
• Relational Model
• Object Oriented Model
• Object/Relational Model

18
Relational Database System

• Ted Codd 1970
• The view of data relations
• relations, tuples, attributes
• tables, rows, columns
• Queries could be expressed in high level
  language, it is simple and efficient
• It is widely used in most commercial systems

19
Example 1.1
Bank Account Information (Accounts)
20
Example 1.1 (Cont.)

• Check the balance
• SELECT balance
• FROM Accounts
• WHERE accountNo 67890
• Check the accountNo
• SELECT accountNo
• From Accounts
• WHERE typesavings AND balance lt 0

21
Major DBMS Products

• Oracle
• IBM DB2, Informix
• Microsoft SQL Server, Access
• Sybase
• MySQL
• Postgres
• All are "relational" (or "object-relational")
  database systems at their core.

22
Schema versus data

• Schema describes how data is to be structured -
  defined at set-up time, rarely changes (part of
  the "metadata")
• Data is actual "instance" of database, may change
  rapidly
• Comparable to types and variables in programming
  languages

23
Data Definition Language (DDL)

• Commands for setting up schema of database
• Process of designing schema can be complex, may
  use design methodology and/or tool

24
Data Manipulation Language (DML)

• Commands to manipulate data in database
  RETRIEVE, INSERT, DELETE, MODIFY
• Also called "query language"

25
People

• DBMS implementer builds system
• Database designer establishes schema
• Database administrator loads data, keeps whole
  thing running
• Database user queries/modifies data

26
DBMS Evolution Trends

• Size
• smaller and smaller
• using personal computer, similar to
  applications such as spreadsheet and word
  processing
• bigger and bigger
• terabytes or even petabytes data, using
  tertiary storage and parallel computing
  techniques.
• Architecture
• Client-server architecture (Two-Tier)
• client database Server
• Multi-Tier architecture
• client application server
  database server

27
DBMS Trends (Cont.)

• Versatile Data type
• integers, characters
• small storage requirement
• videos, audios
• large storage requirement
• Integration
• Data warehouses, data mining

28
(No Transcript)
29
Overview of DBMS

• DDL commands
• DBA DDL Complier Execution engine
• Query Processing
• Answering the query
• User Query Compiler Execution engine
  
• Transaction processing

30
Storage and Buffer Management

• Buffer manager
• Storage manager
• Data the contents of the database
• Metadata (Schema) structure of the database
• Statistics data properties
• Indexes

31
Transaction Processing

• Transaction manager
• Logging
• Concurrency control
• Deadlock resolution

32
Query Processor

• Query complier
• Query parser
• Query preprocessor
• Query optimizer
• Execution engine

33
Outline of Database System

• Database design
• Determine the database structure
• Database programming
• Perform database operations
• Database system implementation
• Build the database

34
Next Class

• E/R Concepts