Transcript CS122_SUMMER_2009_LECTURE_01

Using Relational Databases and SQL
Lecture 1:
Introduction to Relational Databases and
MySQL
Steven Emory
Department of Computer Science
California State University, Los Angeles
Database Origin
Database comes from the open compound word
“Data Base”
Data Base was a military term (circa 1964)
Data Base implies data that is both persistent and
shared (think Air Force Base)
The military funded much of the research into the
early electronic computers of the 40’s and 50’s
because of World War II and the Korean War
What is a Database?
A structured collection of persistent data that is
typically meant to be shared
Used to model organizational activities
Structure is defined through a database model
No database model, no database!
Examples:
Airlines, Banks, Stores and Universities
Database Models
The Two Levels of a Database Model
Physical Level (how data is stored)
The things we don’t care about
Logical Level (how data is generalized)
The things we do care about
The Basic Models
Hierarchical Model (IBM’s IMS)
Network Model (CODASYL)
Relational Model (ALPHA, SEQUEL)
Physical/Logical Separation
Previous to the relational model, one had to write
a program that traversed pointers at the physical
level to extract data from a database (think like
programming in assembly language)
By abstracting the physical level and writing a
program at the logical level instead (think like
programming in Java), extracting data from a
database became easier
First proposed by Dr. Edgar Frank Codd.
Dr. Edgar F(rank) Codd
MA Mathematics, MA Chemistry
MS and PhD in Communication Sciences
ACM Turing Award (1981)
Relational Model
Proposed by Edgar F. Codd (circa 1969)
Database is a collection of tables (relations)
Relational comes from ‘Relational
Algebra/Calculus’ and not from ‘Relationships’
Relational model is very mathematical
Dominant database model
Thanks to Larry Ellison, founder of Oracle, who was
first to aggressively market a commercial relational
database product (Ellison is currently the 4th richest
person in the world)
Tables
Artists = Table (Relation)
ArtistID, City, Region, ... = Columns (Attributes)
Each row is called a Record (Tuple)
Data Types
Determines storage requirements
Common attribute types
String
Numeric (float, double, real)
Date and Time
Blobs and Clobs (images and other binary data)
Database Schemas
The definition of the database, where you define
The name of the database
Tables
Column names, types, and constraints
Relationships between tables
Stored functions and procedures
Schemas are typically represented by a schema
diagram
Database Schemas
Database Management Systems
(DBMS)
Software (programs) to manage databases
Creation/Deletion
Maintenance (inserting, updating, and deleting
records)
Security (encryption, user access control)
Common DBMSs
MySQL, PostreSQL (open source)
Oracle, DB2, MS SQL Server (commercial)
Database Management Systems
(DBMS)
You can have multiple databases, each with a
single schema
A separate database for each application
Toystore (First database)
Bookstore (Second database)
Furniture Store (Third database)
Etc.
You can also have a single database, with
multiple schemas
DBMS Diagram
Query Languages
Query Language = A language used to extract
data from a database
Domain-Specific Language
Only for databases
History
Codd, Alpha (1971)
Chamberlin and Boyce, SEQUEL (1974)
Eventually renamed to SQL
QL Examples
Alpha (Codd)
SEQUEL (Chamberlin, Boyce)
SQL
Stands for Structured Query Language
A non-procedural, domain-specific language (not like
C or C++)
An open ANSI standard
Supported by most major DBMS
Some variations in implementations
Used by programmers, managers, and database
administrators
SQL Functions
View information from relational databases
Single and multiple table selections
Calculation and analysis
Manipulate information in relational databases
Insert and delete records
Update records
Create relational databases
Create databases, tables, constraints, ...
Other Interesting Facts
Question: Why is a database always represented
by an aluminum can or some type of cylinder?
Other Interesting Facts
Answer: Because that’s what disk storage looked
like back in the old days!
Other Interesting Facts
Since IBM already had a database product called
IMS, which was currently being used in the
Apollo space program (1969 – 1972), Dr. Codd
was asked by IBM not to disparage IMS nor
promote his model over IMS since it might hurt
sales of IMS.
Many people to this day believe that hindered
IBM and Dr. Codd from making all the money
that Larry Ellison of Oracle has made.
Downloading MySQL
Go to CS122 Wiki page and under the Required
Software section, click on the links to
MySQL (client and server program)
MySQL GUI Tools (alternative client program)
MySQL Workbench (schema utility)
Download the versions for Windows, without the
installer
Extract all three to a flash drive (preferably)
Important Notes!!!
You can put MySQL directly onto these
computers, but be aware that other students may
delete your stuff (by accident or otherwise)
Do not leave homework, labs, or programs on our
computers as other students may find and
mishandle your stuff
Always back up your work onto a personal flash
drive before you leave
Don’t forget your flash drives