introduction to relational databases and sql l.
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction to Relational Databases and SQL PowerPoint Presentation
Download Presentation
Introduction to Relational Databases and SQL

Loading in 2 Seconds...

play fullscreen
1 / 107

Introduction to Relational Databases and SQL - PowerPoint PPT Presentation

  • Uploaded on

Introduction to Relational Databases and SQL. Brian Panulla Web 2004 Pre-Conference. Administrivia. Objectives Agenda Prerequisites Schedule Logistics Presenter Introduction Attendee Introductions. Objectives.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Introduction to Relational Databases and SQL' - shamus

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction to relational databases and sql
Introduction to Relational Databases and SQL

Brian Panulla

Web 2004 Pre-Conference

  • Objectives
  • Agenda
  • Prerequisites
  • Schedule
  • Logistics
  • Presenter Introduction
  • Attendee Introductions
  • The primary objective of this session is to teach the basics of working with common Relational Databases, including data modeling and database design techniques.
  • Participants will also receive an introduction to query building using Standard or Structured Query Language (SQL).
  • To that end, upon completion of the session you should be able to:
    • Understand database concepts and terminology
    • Design and create tables
    • Use SQL to retrieve and analyze information
    • Use SQL to enter and manipulate data
  • Lesson 1: Database Concepts
  • Lesson 2: Entity-Relationship Modeling
  • Lesson 3: Creating Tables
  • Lesson 4: Using SELECT queries
  • Lesson 5: Using INSERT queries
  • Lesson 6: Using UPDATE queries
  • Lesson 7: Using Delete queries
  • Lesson 8: Database Project
  • General experience in Web site development or management
  • General experience in a scripting language (ColdFusion, PHP, ASP) or full programming language (C/C++, Java)
schedule half day session
Schedule(Half-Day Session)

Start 1:00 PM

Break (approximate) 3:00 PM

Adjourn 5:00 pm


Drinking fountains, refreshments, snacks




Emergency measures


Brian Panulla

B.S. Science, PSU (2000)

Chief Information Officer

Campus Data Group, LLC


  • Your name
  • Organization name
  • Current position
  • Background in databases
  • Expectations
  • Ask away!
lesson 1 objectives
Lesson 1 Objectives
  • Discuss database concepts and terminology
  • Learn database design principles
database terminology
Database Terminology
  • Database
  • Table (or relation, entity)
  • Row (or record, tuple)
  • Column (or field, attribute)
  • Data value
what is a database
What is a database?
  • Common databases
  • Database tasks:
    • Retrieving
    • Sorting
    • Summarizing
    • Inserting
    • Updating
    • Deleting
  • Common DB software
what is a table
What is a table?
  • Tables are the fundamental component of any relational database.
  • A table stores the data corresponding to a specific type of object
    • For example:
      • A Students table would store student information
      • An Orders table would store customer order information
what is a table17
What is a table?
  • Tables are made up of rows and columns
  • The columns of a table describe the characteristics of the information stored in that table
  • The data in each row belongs to a given instance of the type of data stored in a particular table
what is a table18
What is a table?
  • Each row contains one data value per column.
  • The range of values that can go into a particular column of a row is called the domain of that column, and is generally restricted to data of a specific type (integers, character data, dates, etc.)
what is a relational database
What is a“Relational Database?”
  • The term “relational database” comes from the mathematical definition of a relation, or set. All objects in a relation must have the same properties or characteristics
  • The point? Tables group similar data or objects, with use one table per set of objects
  • Q: Is Excel a relational database system?
database design
Database Design
  • An art unto itself, database design skills are crucial in the development of efficient, stable Web applications
  • Planning your database design should generally be one of the first tasks in a development project. Too often, however, the database is developed “as we go along,” creating problem after problem
entity relationship modeling
Entity – Relationship Modeling
  • Entity-Relationship (or E-R) Modeling is the name given to one particular method of relational database design
  • The data to be stored in a database is categorized into entities. An entity is an example of one type of object to be modeled by the database.
  • Entity names are typically nouns.
some basic entities
Some Basic Entities




  • Discuss some of the entities that would exist in databases for:
    • the participants in a youth sporting league (baseball, softball, soccer, etc.)
    • the inventory of an independent gift shop or other store
e r modeling attributes
E-R Modeling: Attributes
  • Each entity has one or more attributes that further describe that entity or relationship.
an entity with attributes
An Entity with Attributes







  • Discuss some of the attributes that would describe the following entities:
    • the Teams entity in the youth sporting league database
    • the Products entity in the gift shop inventory database
e r modeling relationships
E-R Modeling: Relationships
  • A given entity can be correlated with other entities by way of relationships
  • A relationship is typically named with a verb phrase:
    • A Personis aStudent
    • A Bookis published by aPublisher
  • There may also be attributes that truly belong to the relationship and not to an entity
















  • Devise the relationships that connect the entities in the databases discussed in one of the previous exercises:
    • The youth sporting league database
    • The gift shop inventory database
lesson 3 objectives
Lesson 3 Objectives
  • Convert an E-R Diagram to a relational schema
  • Identify improvements to the design tables
  • Use the CREATE TABLE SQL statement to create tables in a database
creating tables
Creating Tables
  • With an E-R Model in hand, the actual creation of a database is very straightforward
  • Each entity will generally become a table
  • Any relationships with one or more attributes will also become a table
  • Relationships without attributes may sometimes be modeled as a table
ground rules for relational database design
Ground Rules for Relational Database Design
  • No multi-part or multi-value fields
  • Eliminate redundant information
  • Avoid designs that call for the addition of columns to store new data
  • Avoid “anomalies”:
    • Update
    • Delete
    • Insert
sample database design37
Sample Database Design
  • No multi-part or multi-value fields
sample database design38
Sample Database Design
  • Eliminate redundant information
sample database design39
Sample Database Design
  • Avoid designs that call for the addition of columns to store new data
sample database design40
Sample Database Design
  • Avoid anomalies: Update Anomaly
  • A key is a field or set of fields that can be used to uniquely identify a particular record in a database table.
    • Examples:
      • Name, Department
      • SSN
  • A Primary Key is a field or set of fields chosen by the database designer to be used to define relationships between tables
primary keys
Primary Keys
  • Primary Key fields:
    • Must contain unique, non-duplicated values (or sets of values in the case of multi-field keys
    • Cannot be NULL
  • In the event that no one column in a table is an appropriate Primary Key, an artificial primary key column is usually generated
foreign keys
Foreign Keys
  • When the values of a Primary Key column in a table are shared by a common column in a child table, that column is called the Foreign Key of the relationship.
  • Foreign Key fields must have the same data type and size as their related Primary Key field.
foreign keys46
Foreign Keys
  • Foreign Keys are used in other tables in order to maintain relationships between data values.
  • By allowing us to split data into multiple related tables, foreign keys can help eliminate the anomalies described previously
  • Develop a relational schema for one of the databases from the previous lesson
an introduction to relationships
An Introduction to Relationships
  • Cardinality
  • One-to-Many Relationships
  • One-to-One Relationships
  • Many-to-Many Relationships
  • Cardinality is the term used to describe the character of a relationship
  • Three types of cardinality exist:
    • One to Many
    • Many to Many
    • One to One
one to many relationships
One-to-Many Relationships

Each row in Employees relates to only one row in Offices, while each row in Offices may relate to many rows in Employees

many to many relationships
Many-to-Many Relationships

Each row in Employees may relates many rows in Departments, and vice versa.

many to many relationships54
Many-to-Many Relationships

A Many-to-Many Relationship can usually be modeled by adding an additional “join table,” creating two One-to-Many Relationships instead.

one to one relationships
One-to-One Relationships

A One-to-One Relationship is usually created for security and/or performance reasons. Technically, the two tables involved in a One-to-One relationship could be combined into one table with no loss of data

  • Describe the cardinality of the relationships in your relational schema
data types
Data Types
  • Each column of a table must have a specified data type supported by the database software.
  • The database will enforce the requirement that data entered into a field must comply with the field’s data type
  • Choose the smallest data type appropriate for each column, leaving room for future expansion (No Y2K-like bugs!)
data types58

FLOAT (floating point numbers)

NUMERIC (real numbers)






Data Types
creating tables59
Creating Tables
  • Choose good field and table names
    • No spaces or special characters (other than the underscore “_” )
    • Use meaningful names
    • The Leszynski Naming Convention (or LNC) is a scheme for helping you create more meaningful table and field names.
lnc naming
LNC Naming
  • Originally suggested by Stan Leszynski and Greg Reddick in a paper entitled "The Leszinski/Reddick Guidelines for Access 1.x, 2.x" it has become the convention used by many developers.
lnc naming61
LNC Naming
  • This naming convention suggests that you precede object names with three letter "tags" to help developers work with unfamiliar database objects.
lnc prefixes
Data Type:

n (integer or numeric)

f (float)

c (char)

vc (varchar)

dt (datetime or timestamp


pk (primary key)

fk (foreign key)


tbl (table)

vw (view)

sp (stored procedure)

LNC Prefixes
create table
  • You can build a table in a SQL-compatible database with the CREATE TABLE statement

create table Offices (

OfficeID integer primary key,

Address varchar(32),

Locality varchar(32),

State char(2),

Zip varchar(10),

Phone varchar(12)


null values
NULL Values
  • Relational databases have a special value, called the NULL, that can be used as a placeholder when information is unknown, missing, or to be filled in later.
  • NULL values are different than
    • Zero
    • Empty Strings
  • A column of any data type can be NULL, though PRIMARY KEY columns can never be NULL
default values
Default values
  • Each column in a table may have a default value specified.
  • The database will set the column to that value if no value is passed during insert
  • If no default value is specified, the column will be set to NULL if possible.
create table with defaults
CREATE TABLE with defaults
  • Our CREATE TABLE from the previous step with NULL preferences and defaults:

create table Offices (

OfficeID integer NOT NULL primary key,

Address varchar(32) NULL,

Locality varchar(32) NULL DEFAULT ‘State College’,

State char(2) NOT NULL DEFAULT ‘PA’,

Zip varchar(10) NULL,

Phone varchar(12) NULL


lesson 3 review
Lesson 3 Review
  • Why is the planning step important when creating a database?
  • Why is separating data into multiple tables a good idea?
  • Practice making tables in your database by building on the schema you developed in previous exercises.
  • SQL (Structured or Standard Query Language) is defined by several ANSI standards
  • Despite being “standardized,” every DB platform may implement some of the language syntax differently
sql select
SQL: Select
  • A SELECT query is the most common type of SQL query:

SELECT * FROM employees

  • This query will retrieve all the columns of all of the rows from the table employees.
sql select73
SQL: Select
  • To only retrieve only some of the columns of a table, list them after SELECT:

SELECT empID, lastname, firstname

FROM employees

sql select74
SQL: Select
  • A WHERE clause may be added to selectively retrieve only those rows in a table that match a given constraint:

SELECT * FROM employees

WHERE lastname = ‘smith’

  • Only employee rows matching the last name of “smith” will be returned. Normal equality or inequality operators (< > =) may be used.
sql select75
SQL: Select
  • Multiple conditions may be added to the WHERE clause, using AND, OR, and NOT as operators and parentheses as appropriate:

SELECT * FROM employees

WHERE department=‘sales’ AND

(lastname = ‘smith’ OR lastname=‘jones’)

  • Using a WHERE clause
sql select77
SQL: Select
  • Add an ORDER BY clause to sort the records:

SELECT empID, lastname, firstname

FROM employees

WHERE department = “sales”

ORDER BY lastname

  • The keyword DESC can be used to order from highest to lowest values
  • Sorting a Query
  • Table joins alIow the database developer to combine information from related tables into a single, coherent record set and return those records to the program
  • While the standard type of join, the inner join, can often provide the information needed, additional join types are available to control how the database combines the data in two tables
inner joins
Inner Joins
  • Inner Joins are the typical join types encountered in SQL. In a two table inner join, fields from records from the first table that can be correlated to records in the second table appear “side-by-side” with fields from the second table in the resulting record set
inner joins86
Inner Joins
  • Two versions of the join syntax exist, corresponding to two different versions of the ANSI SQL Standard.
  • Traditional syntax:


tblAccounts A, tblUsers U

WHERE A.npkAccountID = U.nfkAccountID

AND dtCreated > ‘1/1/2003’

  • Instructions on how to join the tables listed in the FROM clause are placed in the WHERE clause
inner joins87
Inner Joins
  • The newer ANSI SQL 92 syntax makes it easier to write other join types:


tblAccounts A INNER JOIN tblUsers U

ON A.npkAccountID = U.nfkAccountID

WHERE dtCreated > ‘1/1/2003’

  • Instructions on how to join the tables listed in the FROM clause are placed in a new ON clause, leaving the WHERE clause for filtering records
inner join syntax comparison
Inner Join syntax comparison


tblAccounts A, tblUsers U

WHERE A.npkAccountID = U.nfkAccountID

AND dtCreated > ‘1/1/2003’


tblAccounts A INNER JOIN tblUsers U

ON A.npkAccountID = U.nfkAccountID

WHERE dtCreated > ‘1/1/2003’

inner joins more tables
Inner Joins - more tables


(tblAccounts A INNER JOIN tblUsers U

ON A.npkAccountID = U.nfkAccountID)

INNER JOIN tblInstitutions I

ON A.nfkInstitutionID = I.npkInstiutionID

WHERE dtCreated > ‘1/1/2003’

outer joins
Outer Joins
  • Outer Joins are an extremely powerful query feature available in SQL. Outer joins are supported in nearly all major RDBMSs
  • In an outer join, all records from one table will be included in the resulting recordset, along with any records in the second table that can be correlated to records in the first table.
  • As with inner joins, fields from both tables appear “side-by-side” in the resulting record set, but fields from records that have no counterpart in the second table receive NULL values.
outer joins92
Outer Joins
  • Outer joins are directional. An outer join may either be a left outer join or a right outer join.
  • By specifying the direction of the join, you tell your database from which table it should include all records:


tblInstitutions I LEFT OUTER JOIN tblAccounts A

ON I.npkInstitutionID = A.nfkInstitutionID

will include all records from the table tblInstitutions.

outer joins95
Outer Joins
  • Often, the table pointed to by the direction of an outer join will be the parent table in a relationship.

tblAccounts A LEFT OUTER JOIN tblUsers U

makes much more sense than:

tblUsers U LEFT OUTER JOIN tblAccounts A

as tblUsers shouldn’t contain any rows that don’t relate to an Account, but an Account may have no users defined.

aggregate functions
Aggregate Functions
  • Nearly all RDBMSs have support for basic aggregate functions (also called set functions) - functions that operate on a set of values and return a single value.
    • Examples:
      • Count(*)
      • Sum(*)
      • Max(*)
      • Min(*)
aggregate functions collapse your recordset into a single record
Aggregate functions collapse your recordset into a single record
  • For example:

SELECT count(*) as RecordCount,

max(nRevenue) as MaxRevenue,

min(nRevenue) as MinRevenue,

avg(nRevenue) as AvgRevenue

FROM tblModuleData_FoodSvc

grouping records
Grouping Records
  • You may split your recordset into two or more groups according to the values of one of the columns in your query:

SELECT nfkAccountID,

count(*) as RecordCount,

max(nRevenue) as MaxRevenue,

min(nRevenue) as MinRevenue,

avg(nRevenue) as AvgRevenue

FROM tblModuleData_FoodSvc M INNER JOIN tblDemographics D

ON M.nfkDemogID = D.npkDemogID

GROUP BY nfkAccountID

  • Many RDBMSs allow you to nest queries into a single operation.
  • The two most common subquery examples are:
    • “Virtual Tables”
    • “IN-clause” subqueries
  • “Virtual table” subqueries allow you to use the result of a query as a temporary table in a second query:


(SELECT nfkAccountID, count(*)

FROM tblUsers U

GROUP BY nfkAccountID) U ON

A.npkAccountID = U.nfkAccountID

this can be used to reduce the number of fields in your GROUP BY statement in the outer query

  • “IN clause” subqueries allow you filter your records according to the values obtained by a subquery

SELECT * FROM tblUsers U

WHERE nfkAccountID IN

(SELECT npkAccountID

FROM tblAccounts

WHERE count(*) > 2

GROUP BY nfkAccountRepID)

subquery example
Subquery Example
  • EduSoft’s portal Web site is meant to be used by multiple individuals at a given institution.
  • If multiple users haven’t been created within three months of account creation, chances are the main user at the institution is not making good use of the account and may need help.
  • Find all Accounts opened before 1/1/03 for which only one user login has been established, and pass that information onto our Customer Service manager
subquery example104
Subquery Example

SELECT npkAccountID, dtCreated

FROM tblAccounts

WHERE npkAccountID IN

(SELECT nfkAccountID FROM tblUsers

WHERE count(*) = 1

GROUP BY nfkAccountID)

AND dtCreated < ‘1/1/2003’

stored procedures
Stored Procedures
  • Stored Procedures allow you to pre-compile complicated queries on the database server
  • Most RDBMSs that support SPs provide you with a fairly functional programming language in which to write them:
    • Transact-SQL (T-SQL) is MS SQL Server
    • PL/SQL in Oracle
stored procedures107
Stored Procedures
  • SPs are excellent components for two main reasons:
    • Database logic encapsulation - have all queries coded into SPs by your DBA, and made available to developers
    • Performance - the SQL does not need to be interpreted and compiled on each execution, saving processor time
    • Big uses for SPs include Ad Banner or click tracking, autonumber tables for DBMSs without sequences