CS122aSlides07 - ICS122A EECS116 Introduction to Data...

1
ICS122A / EECS116
Introduction to Data Management
Spring 2009
Prof. Mike Carey
Bren School of ICS
UC Irvine
Slides based on previous CS122a lecture notes as well as
material borrowed from U-Wisconsin, Stanford, & Berkeley

ICS122A/EECS116 Notes 02
2
Announcements
•
HWs and projects
–
HW #2 due today
–
HW #3 available today
–
Project Part 1 due this Thursday
•
Discussion section meetings
–
On Friday, Alex will get you going on Project Part 2
–
In particular, he’ll help get you moving on the use of
either MySQL or DB2 (you get to pick)
–
You should at least have downloaded the RDBMS of your
choice and have it with you (to install) on Friday
•
Today
–
It’s SQL time!

ICS122A/EECS116 Notes 08
3
SQL – Historical Perspective
•
Developed by IBM in early/mid 1970s
–
Designed by Don Chamberlin and Raymond Boyce
–
Goal was a simple, declarative, easy to use query language
–
Adopted (and vigorously developed) by Oracle as well
•
Standardized by ANSI and ISO
–
First standardized in 1986 by ANSI: SQL1 (
a.k.a.
SQL86)
–
Revised in1992: SQL2 (SQL92); ~580 pp. of syntax/semantics
–
In 1999, ANSI/ISO released SQL3 (SQL99) with additions for:

Support for multimedia data

Addition of abstract data types and object-orientation (e.g., type/table hierarchies)

Support for calling programmed functions from within SQL
•
Every vendor has a slightly different version of SQL
–
Most versions largely agree on the SQL92 core functionality
–
And some are developing new ones… ( Ex: FaceBook!)

ICS122A/EECS116 Notes 08
4
Dimensions of SQL
•
Data definition language (DDL)
–
Create/alter/drop tables and their columns
–
Specify constraints (primary keys, foreign keys, and more)
•
Query language (DML)
–
Relationally complete, supports aggregation and grouping
–
Declarative – say what you want, not how to retrieve it
–
Relatively easy language to learn to use
•
Updates (also DML)
–
Insert, delete, and update rows of tables
•
View definition language (also DDL)
–
Define virtual tables (using SQL queries)
–
Limited support for updates through views

ICS122A/EECS116 Notes 08
5
Dimensions of SQL (cont.)
•
Embedded SQL (language bindings)
–
SQL embedded in a variety of host languages

C, C++, PERL, Smalltalk, Java (vendor dependent)
–
Impedance mismatch: SQL manipulates relations that
are sets/bags, while programming languages typically
process one data instance at a time
•
Transaction Control (also DML)
–
Commands to specify beginning and end of
transactions (all-or-nothing units of work)

ICS122A/EECS116 Notes 08
6
SQL DDL
CREATE TABLE Dept
( dno int,
dname varchar(30) not null,
mgr char(15)
);
CREATE TABLE Emp
( ename char(15) not null,
dno int default 0,
sal int
);
(We’ll cover more constraints later, including keys and foreign keys)
Don’t allow null values
Default value is 0 if no value is given

ICS122A/EECS116 Notes 08
7
Domain Types
•
char(n) : fixed length character string
•
varchar(n) : variable-length character string
•
int or integer : integer value
•
smallint