A Very Brief Introduction to Relational Databases

1
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

2
Database Management Systems

• DBMS vs. Files
• Files hold data.
• Knowledge of the structure is in the programs
  that use the files.
• Example Addresses.csv
• Files can hold complex data structures
• 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

3
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.

4
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.

5
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

6
Relational Database

• Specific form of DBMS
• Invented by E.F. Codd of IBM
• A Relational Model of Data for Large Shared
  Data BankCommunications 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

7
Relational Database

• Data is organized as tables
• Rows and columns
• Basic concept Every distinct of piece of
  information is held in only one place.
• 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.

8
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.

9
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 vendors extensions

10
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.

11
LINQ

• Language Integrated Query LINQ
• New in C 3.0 and Visual Studio 2008
• Permits us to write C statements to do queries
  rather than just composing strings and sending
  them to the server.
• 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

12
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.

13
SQL Sessions

• Users can interact with a database system, using
  a program running on their local computer.
• Command line or GUI
• Generally must log in with the server
• User name and password
• This creates a session.
• May need to specify a particular database, or one
  may be determined automatically by user name.

14
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

15
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.

16
Types of SQL Commands

• DDL Data Definition Language
• Create and restructure tables
• DML Data Manipulation Language
• Change data in tables
• DQL Data Query Langauage
• Retrieve information from tables
• Others
• Database administration
• Advanced concepts

17
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

18
Example CREATE TABLE
Command
Table Name

• CREATE TABLE 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
19
Data Manipulation Language

• INSERT
• UPDATE
• DELETE

20
INSERT Example

• Insert a new row into table book
• INSERT INTO book
• (book_id, title, publisher, publish_date)
• VALUES
• (1013, 'Oracle SQL', 'Prentice Hall', '12/02')

Table Name
Column Names
Values for new row
Column names may be omitted if a value is
provided for every column (in the right order.)
21
Data Query Language

• SELECT
• Only one command
• Many options and clauses
• Primary method of interacting with the database
• Interactive users
• Programs

22
Examples SELECT

• SELECT title, publisher
• FROM book

Column Names
Table Name
means All Columns
Example from a C program strSQL "SELECT
FROM Orders" " WHERE Store_ID "
Store_ID.ToString() " AND
Order_ID " Order_ID.ToString()
Where clause specifies which rows to return
These are local variables.
End of Presentation