introduction to database management systems. information instructor: csilla farkas office:...

Introduction to Database Management Systems

Information

• Instructor: Csilla Farkas• Office: Swearingen 3A43• Office Hours: Monday, Wednesday 4:15 pm – 5:30 pm,

electronically, or by appointment• Telephone: 576-5762• E-mail: [email protected]• Class homepage:

http://www.cse.sc.edu/~farkas/csce520-2014/csce520.htm

Farkas CSCE 520 2

http://www.cse.sc.edu/~farkas/csce520-2013/csce520.htm

Prerequisite

• CSCE 245 (CSCE 240, EECE 352) or • GEOG 563 or • Permission of the instructor

Farkas CSCE 520 3

Text books

• Joel Murach, Murach's Oracle SQL and PL/SQL (Training & Reference), Mike Murach & Associates (August 1, 2008), ISBN-13: 978-1890774509

Recommended text book:• J. D. Ullman and J. Widom: A First Course in

Database Systems, 3rd edition, Prentice Hall, ISBN: 013600637X – online resources are available

Farkas CSCE 520 4

Grading

• Test 1: 15%, Test 2: 15%, Test 3: 30% Assignments: 40%

• Total score that can be achieved: 100• Final grade: 90 < A, 87 < B+ <=90, 80< B <= 87, 77 <

C+ <= 80, 68 < C <= 77, 62 < D+ <= 68, • 52 < D <= 62, F <= 52

Farkas CSCE 520 5

Course Policies

• Code of Student Academic Responsibility • Incompletes

Farkas CSCE 520 6

APOGEE Students

• Access recorded lectures• Contact instructor via

– Phone (during office hours)– Email– Or any time at a prearranged time

• Need to attend exams as scheduled or arrange for proctored testing

• Need to arrange for Oracle demo

Farkas CSCE 520 7

Tentative Schedule• Week 1: Introduction• Week 2: ER model• Week 3: Relational model• Week 4: Project overview and Oracle accounts• Week 5: Relational Algebra• Week 6: SQL• Week 7: Relational Design• Week 8: Constraints• Week 9: Transactions• Week 10: Logical query languages• Week 11: Data storage and Data representation• Week 12: Index structures• Week 13: System Failures• Week 14: Concurrency control• Week 15: Summary and Review

Farkas CSCE 520 8

Test Schedule

• Test 1: late September• Test 2: early November• Test 3: December 12, 4:00 -6:00 pm

Farkas CSCE 520 9

Questions?

Introduction

• Evolution of Database Management Systems

• Overview of Database Management Systems

• Database-System Design

Farkas CSCE 520 11

Database Management System (DBMS)

• Databases touch all aspects of our lives • DBMS:

– Collection of interrelated data– Set of programs to access the data

• Convenient and efficient processing of data• Database Applications

Farkas CSCE 520 12

Evolution of Database Systems

• Early days: database applications built on top of file systems

• Drawbacks of using file systems to store data:– Data redundancy and inconsistency– Difficulty in accessing data– Atomicity of updates – Concurrency control– Security– Data isolation — multiple files and formats– Integrity problems

Farkas CSCE 520 13

Abstraction• View level: application programs hide details of data

types. • Logical level: What is the data?

type employee = recordname : string;address : string;salary: real;

end;• Physical level: How the data is stored?

Farkas CSCE 520 14

Data Models

• A collection of tools for describing – Data – Relationships among data items– Semantics of stored data– Database constraints

Farkas CSCE 520 15

Data Models

• Entity-Relationship model• Relational model• Other models:

– Network – Hierarchical– Object-oriented– Semi-structured – Steaming data

Farkas CSCE 520 16

Database Management Systems

• Smaller and smaller systems– Past: large and expensive DBMS– Present: DBMS in most personal computers

• More and more data stored– Past: few MB– Present: terabyte (1012 bytes), petabyte (1015

bytes)

Farkas CSCE 520 17

Data Tsunami

Database Users

• Users are differentiated by the way they interact with the system

• Database Administration: responsible for the structure or schema of the database (DDL), coordinates all activities regarding the database

• Application programmers – interact with system through DML calls

• Sophisticated users – form requests in a database query language

• Naive users – invoke one of the permanent application programs that have been written previously

Farkas CSCE 520 18

Data Definition Language (DDL)

• Defines the database schema and constraints

• DDL compiler data dictionary• Metadata – data about data

Farkas CSCE 520 19

Data Manipulation Language (DML)

• Accessing and manipulating the data– DML – query language

• Query Languages – Procedural – user specifies what data is required and how to get

those data – Nonprocedural – user specifies what data is required without

specifying how to get those data

• SQL: nonprocedural query language

Farkas CSCE 520 20

Transaction Management• Transaction: unit of work to be executed atomically and

in isolation from other transactions• Transaction-manager: ensures that the database

remains in a consistent – system failures – transaction failures

• Concurrency-control: interaction among the concurrent transactions to ensure consistency

Farkas CSCE 520 21

ACID Properties• Atomicity: all-or-nothing of the transaction’s effect will

take place• Consistency: each transaction leaves the system in a

consistent state• Isolation: each transaction must appear to be executed

as if no other transactions are executed at the same time• Durability: effect of a transaction must never be lost

after the transaction is completed

Farkas CSCE 520 22

Transaction Processing

• Logging:– Log manager– Recovery manager

• Concurrency control– Multiple transactions– Locking protocols

• Deadlock resolution

Farkas CSCE 520 23

Database System Studies• Design of the database

– What to store, structure, semantics– Functionality requirement– Trade offs– Security

• Database programming– How to express database operations, capability

requirements, etc.• Database implementation

– Query, transaction processing, storage, efficiency

Farkas CSCE 520 24

Murach’s Oracle SQL and PL/SQL, C1

© 2008, Mike Murach & Associates, Inc.Slide 25

Chapter 1

An introduction to relational databases

and SQL



A simple client/server system

Client

Databaseserver

Network

Client

Client



The three hardware components of a client/server system Clients

Server

Network

Terms to know Local area network (LAN)

Wide area network (WAN)

Enterprise system



Client software, server software, and the SQL interface

SQL queries

Results

Client Database serverApplication softwareData access API

Database management systemDatabase



Server software Database management system (DBMS)

The DBMS does the back-end processing

Client software Application software

Data access API (application programming interface)

The client software does the front-end processing

The SQL interface SQL queries

SQL stands for Structured Query Language



An application that uses an application server

ClientUser interface

Application serverBusiness components

SQL queries

Response Results

Database serverDBMS

Database

User request



A simple web-based system

ClientWeb browser

Database serverDBMS

Database

Web serverWeb applications

Web services

User request

Response

SQL queries

Results

Internet

User request

Response



First database releases Oracle 1979

DB2 1985

SQL Server 1987

Primary platforms Oracle Unix

OS/390 and z/OS

DB2 Unix OS/390 and z/OS

SQL Server Windows

Next Class: Relational Database Modeling

Farkas CSCE 520 33

introduction to database management systems. information instructor: csilla farkas office:...

Documents

data storage

data relationships

data convenient

data storedpast

data representationweek

data redundancy

details of data types

instructor farkascsce