Transcript Introduction - Computer Science and Engineering

Who is In Charge?

Dr. Yan Huang



NTDP F251,TTh 1:00-2:00pm
Or by appointment
TA: Jason Powell

4/7/2016
NTDP F236, TTh 12:00-2:00pm
Yan Huang - CSCE4350 - Introduction
1
Books


Text Book: Database System Concepts, 6th
edition, ISBN: 0073523321 Publisher: McGrawHill Science/Engineering/Math
Supplement:


4/7/2016
Elmasri, Navathe, Fundamentals Of Database Systems
W/cd, 6rd, Addison Wesley, ISBN: 0136086209
Hector Garcia-Molina, J. Ullman, J. Widom, Database
Systems - The Complete Book, Prentice Hall, ISBN 013-031995-3.
Yan Huang - CSCE4350 - Introduction
2
Evaluation Scheme





Exams 45%
Homework 30%
Project 20%
Class participation 5%
Pre-requisite: CSCE3110 (csci3400) data
structures
4/7/2016
Yan Huang - CSCE4350 - Introduction
3
Topics to be Covered









Conceptual Database Design
Relational Data Model
SQL
Relation Database Design
Access Methods
Transactions
XML
Data Warehousing/Data Mining (if time allows)
Big Data Management
4/7/2016
Yan Huang - CSCE4350 - Introduction
4
Chapter 1: Introduction




What is a DBMS?
A little history of DB
Major Components of a DBMS
Three Levels of Abstraction
4/7/2016
Yan Huang - CSCE4350 - Introduction
5
What is a DBMS?




DataBase Management System
A bunch of programs
Users use a language to ask questions about
data that a DBMS manages
DBMS understands the language and give
answers as fast as possible
4/7/2016
Yan Huang - CSCE4350 - Introduction
6
History



1960s: network model (CODASYL) and hierarchical (IMS).
1970: E.F. Codd’s seminal paper on relational model for databases
1970s:






1976: P. Chen proposed the Entity-Relationship (ER) model
1980s: Commercialization of RDBMS, Oracle was the first
Early 1990s: OO databases
Mid-later 1990s: Web DB/data warehousing/data mining



Ingres: Developed at UCB. This ultimately led to Ingres Corp., Sybase,
MS SQL Server, Britton-Lee, Wang's PACE.
System R: Developed at IBM San Jose and led to IBM's SQL/DS & DB2,
Oracle, HP's Allbase, Tandem's Non-Stop SQL.
asp, Front Page, Java Servlets, jsp, JDBC, Enterprise Java Beans,
ColdFusion, Dream Weaver, Oracle Developer 2000/OLAP
Earlier 2000s: consolidation of vendors, IBM (bought Informix),
Microsoft, and Oracle.
Today: Big data: HardoopDB, Hbase, NoSQL, MongoDB
4/7/2016
Yan Huang - CSCE4350 - Introduction
7
Worldwide Relational Database Management Systems 2007 Vendor Shares Full Report
4/7/2016
Yan Huang - CSCE4350 - Introduction
8
DBMS
Architecture
4/7/2016
Yan Huang - CSCE4350 - Introduction
9
Major Components of a DBMS




Query processor/Query optimizer
Transaction manager
Security/authorization manager
Storage manager
4/7/2016
Yan Huang - CSCE4350 - Introduction
10
Query Processor/Optimizer



Decompose user queries into primitives
Find the best way (IO efficient) to execute the
query
Two kinds of queries:

DML: data manipulation language


DDL: data definition language

4/7/2016
Insert, delete, update, select
Create/drop/alter a table/index/view
Yan Huang - CSCE4350 - Introduction
11
Query Languages

SQL: standard/declarative language


Example:




ISO SQL-86, SQL-89, SQL-92, SQL-99, SQL-2003,
SQL-2008, SQL-2011
SELECT name, age
FROM student
WHERE gender = “female”
Supported by most DB vendors
4/7/2016
Yan Huang - CSCE4350 - Introduction
12
Query Languages





Relational algebra: formal, procedural language
Query by example
Domain relational calculus
Tuple relational calculus
Datalog
4/7/2016
Yan Huang - CSCE4350 - Introduction
13
Transaction Manager


A transaction is unit of work either completed as
a full or not at all – atomicity
Example transaction:





Begin transaction
A = A+10
B = B – 10
End transaction
Other than atomicity, concurrency control of
transactions is also important
4/7/2016
Yan Huang - CSCE4350 - Introduction
14
Concurrency Control


Informally, concurrency control means
transactions executing currently should not
produce results other than a serial execution of
the same transactions
What will happen without concurrency control?



4/7/2016
Partial work will be seen by others – dirty read
Your work may be mistakenly over written by others
– dirty write
Example?
Yan Huang - CSCE4350 - Introduction
15
Security/Authorization

Define user roles



Admin
User
Define what users can access which part of the
database system:



4/7/2016
Tables
Indices
Views
Yan Huang - CSCE4350 - Introduction
16
Storage Manager



Manage how data are stored in physical space,
e.g. disk
How to read/write data
We can also create indices on existing data

4/7/2016
Storage of indices need to be managed as well
Yan Huang - CSCE4350 - Introduction
17
Application Architectures
Examples of Web
application servers
are Netscape
Application Server,
BEA Weblogic
Enterprise, Borland
AppServer and
IBM's WebSphere
Application Server


Two-tier architecture: E.g. client programs using ODBC/JDBC to
communicate with a database
Three-tier architecture: E.g. web-based applications, and
applications built using “middleware”
4/7/2016
Yan Huang - CSCE4350 - Introduction
18
Benefits of DBMSs Compared to
File systems





A unified database interface vs. customized
programs to interact with data
Data integrity
Currency control
Specialized fast query answering system, e.g.
indexing, query optimization
Example?
4/7/2016
Yan Huang - CSCE4350 - Introduction
19
Database Examples





Human resource systems
Library systems
Airline reservation systems
Product catalogs
Banking systems
4/7/2016
Yan Huang - CSCE4350 - Introduction
20
Three Levels of Abstraction




Conceptual layer – things and their relationships
Logical layer – how things and their relationships
are specified in a DBMS
Physical layer – how things and their
relationships are stored in physical space, e.g.
disks
The separation of these three layers allow the
change of one or more layers without affecting
the other layers
4/7/2016
Yan Huang - CSCE4350 - Introduction
21
Major Database Management
Systems









Oracle
Microsoft SQL-server
IBM DB2 + Informix
Sybase
MySQL
PostgreSQL
XML databases – native xml and relational database
extensions to support XML
Most have object-relational database extensions
Also some main memory databases: timesTen (acquired
by Oracle recently)
4/7/2016
Yan Huang - CSCE4350 - Introduction
22
In This Class, We Will









Talk about query languages: relational algebra, SQL
A logical data layer: relational database
How to design a relational database
Basic concepts in object oriented and object relational
databases (maybe)
XML
Access Control (indexing)
Transactions
Big data management basic concepts
Introduction to data warehousing/data mining if time
permits
4/7/2016
Yan Huang - CSCE4350 - Introduction
23