Transcript A Very Brief Introduction to Relational Databases

A Very Brief Introduction
to Relational Databases
References
Teach Yourself SQL in 24 Hours (Third Edition)
Ryan Stephens and Ron Plew, SAMS, 2003
Oracle SQL by Example (Third Edition)
Alice Rischert, Prentice Hall PTR 2004
1
Database Management Systems


DBMS vs. Files
Files hold data.
Knowledge of the structure is in the programs
that use the files.


Files can hold complex data structures


Example: Addresses.csv
Each program must know how the data is
represented in the file.
Any change to the representation requires
changes to all programs that access the file
2
Database Management Systems

A DBMS holds both data and structure


Decouples programs from the details of how the
data is represented.
Permits programs to say what they want in
more abstract terms.

Simple Example:






Give me all the records with zip codes beginning
with 336
vs
Read each record.
Extract the first three digits of the seventh field.
Skip over the record if not 336.
Much more complex operations are possible.
3
Database Management Systems



Changes to the structure do not
necessarily require changes to the
programs that access the data.
There is a startup cost
Once you have learned the basics,
using a DBMS is much easier than
writing code to process file data
directly.
4
Database Management Systems


Have long been the mainstay of large
scale data processing.
Recently have become readily available
for personal computers


The .NET Framework provides good support
Smooth integration with C# and VB

Even better in .NET 3.0 and 3.5, Visual Studio 2008
5
Relational Database


Specific form of DBMS
Invented by E.F. Codd of IBM
“A Relational Model of Data for Large Shared Data Bank”
Communications of the ACM; 13(6); 1970

First commercial product produced in
1979 by Relational Software, Inc.


Today known as Oracle Corp.
Extensive theoretical underpinnings

Books, Courses, Career Fields
6
Relational Database

Data is organized as tables


Basic concept: Every distinct of piece of
information is held in only one place.


Rows and columns
Entries in different tables are related when they
have the same value for a field used as a key.
Operations permit data from related tables to
be combined to answer complex questions.

Give me the Product_Description from the
Products table entry with Product_ID matching the
Product_ID in each Order_Item in the
Order_Details table having an Order_ID matching
the Order_ID in Current_Invoice.
7
Database Management System

The DBMS is a server

Often on a dedicated computer.

Small version may reside on a PC.

Always a separate entity


Not a part of the operating system
Users and user programs interact with the
database server to retrieve and update
information in the database.
8
SQL


Structured Query Language
Industry standard langauge for
accessing and manipulating relational
databases.


Prototype developed by Codd at IBM
Now an ANSI and ISO standard


Latest version SQL-99 aka SQL-3
Every vendor has its own version


Core language fairly consistent
Have to learn vendor’s extensions
9
SQL

SQL can be used both interactively and by
programs.



Commands called queries.
Run a program for interactive use.
The .NET Framework supports access by
Windows programs.



Programs create query strings to say what they
want.
Same as the strings you might type on a
command line for an interactive program.
Compiler and runtime system have no
understanding of the string.
10
LINQ

Language Integrated Query – LINQ


Permits us to write C# statements to do
queries rather than just composing
strings and sending them to the server.



New in C# 3.0 and Visual Studio 2008
Permits queries against data structures as well as databases.
Adds syntax similar to SQL to the C# language.
Chapter 25 of Visual C# Step by Step
11
Tables

All data in a relational database is in Tables

To create a table:


Specify a name for the table
Define columns


Specify name for the column
Specify data type.

Similar but not identical to types in programming
languages.
12
SQL Sessions

Users can interact with a database system,
using a program running on their local
computer.


Generally must log in with the server



Command line or GUI
User name and password
This creates a session.
May need to specify a particular database, or
one may be determined automatically by user
name.
13
SQL Sessions

Once logged in, you can







Create tables
Add entries to tables
Modify existing data
Retieve information from tables
Delete entries from table
Delete tables
Programs can do these same things
14
Database Connections



Before a user or a program can interact
with a Database system, there must be
a connection into the database.
An interactive session establishes a
connection when logging in.
A program must connect to a database
by passing a connection string string to
the server.



Specifies user name and desired database
May specify password.
Possibly lots of other stuff.
15
Types of SQL Commands

DDL Data Definition Language


DML Data Manipulation Language


Change data in tables
DQL Data Query Langauage


Create and restructure tables
Retrieve information from tables
Others


Database administration
Advanced concepts
16
Data Definition Language









CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
SQL commands are free form
 Not case sensitive
17
Example: CREATE TABLE
Command
CREATE TABLE
Table Name
INSTRUCTOR
(INSTRUCTOR_ID
NUMBER(8,0),
SALUTATION
VARCHAR2(5),
FIRST_NAME
VARCHAR2(25),
LAST_NAME
VARCHAR2(25),
STREET_ADDRESS VARCHAR2(50),
ZIP
VARCHAR2(5),
PHONE
VARCHAR2(15)
)
Column Name
Data Type
18
Data Manipulation Language



INSERT
UPDATE
DELETE
19
INSERT Example
Insert a new row into table book
Table Name
INSERT INTO book
(book_id, title, publisher, publish_date)
Column
Names
VALUES
(1013, 'Oracle SQL', 'Prentice Hall', '12/02')
Values for new row
Column names may be omitted
if a value is provided for every
column (in the right order.)
20
Data Query Language

SELECT

Only one command


Many options and clauses
Primary method of interacting with the
database


Interactive users
Programs
21
Examples: SELECT
Column Names
SELECT title, publisher
FROM book
Table Name
* means “All Columns”
Example from a C# program:
strSQL = "SELECT * FROM Orders" +
“Where” clause
specifies which
rows to return
" WHERE Store_ID = " + Store_ID.ToString() +
" AND Order_ID = " + Order_ID.ToString();
These are local variables.
End of Presentation
22