Transcript Document

INFORMATION
SYSTEMS
FALL 2011
LOGISTICS
Class website: http://rogerking.me
• Please subscribe
Reaching me: [email protected]
• 303 437 7419
ASSIGNMENTS
Two exams
Software project
Each worth 1/3 of your grade
IMPORTANT
Please respond to the query on the rogerking.me!
IN A NUTSHELL
What is a DBMS?
The notion of a global state
OLTP vs. OLAP
The need for speed
The quest for semantic richness (and the conflict)
The simplicity of the relational model
Where is the semantics? - SQL
SQL – the declarative query language based on sets
Atomic and isolated transactions – lots of short ones
LANGUAGE COMPLICATIONS
Extreme incompatibility between SQL and host languages
Lack of integrity constraints
• because a valid query does not equal a correct query
• What runs isn’t necessarily creating a state that represents the real
world
Value semantics and not object semantics
• Passing individual values up to host program
• Easy misinterpretation of the database schema
THE BASICS
SQL: data definition language and data manipulation language
What is in a relational schema?
•
•
•
•
•
•
Tables/relations
Columns/attributes
Primary keys (PK) and foreign keys (FK)
The basic domains: numbers, characters, time, counters
View tables
Nulls
INTERESTING COMPLICATION
Does x = y? Yes/No
Introduce null values – Does x = y
3-valued logic
A FIX FOR THE LACK OF
SEMANTICS
Trigger-based protocols
“Check” constraints on tables
“Assertion” constraints on database as a whole
Basic form:
• Run a query whenever the table or database is changed
3 stages of a more general form of a trigger:
• When (query1) run
• If (query2 ) true or non-empty
• Run (query3)
IMPORTANT OBSERVATIONS
Creating/updating schema is done offline
• So, no combining DDL commands with DML commands
Most of the data semantics is in the host program, not in the database
state
No native notion of time … OLAP comes later…
State changes with each transaction’s conclusion
DATABASE PROTECTION
Keep the database on its own server
Only a few people are allowed to write SQL
Only a tiny few people can update the database
Only a very tiny few people can delete tuples
Allow people to only exercise these rights on specific tables