CPS120:%20Introduction%20to%20Computer%20Science

1
CPS120 Introduction to Computer Science

• Information Systems Database Management

Nell Dale John Lewis
2
Session Goals

• Describe the elements of a database management
  system
• Describe the organization of a relational
  database
• Establish relationships among elements in a
  database
• Write basic SQL statements

3
Database Management Systems

• A database can simply be defined as a structured
  set of data
• A database management system (DBMS) is a
  combination of software and data made up of
• Physical databasea collection of files that
  contain the data
• Database enginesoftware that supports access to
  and modification of the database contents
• Database schemaa specification of the logical
  structure of the data stored in the database

4
Database Management Systems
Figure 12.6 The elements of a database
management system
5
The Relational Model

• In a relational DBMS, the data items and the
  relationships among them are organized into
  tables
• A table is a collection of records
• A record is a collection of related fields
• Each field of a database table contains a single
  data value
• Each record in a table contains the same fields

6
A Database Table
Figure 12.7 A database table, made up of records
and fields
7
A Second Table
A database table containing customer data
8
Relationships

• We can use a table to represent a collection of
  relationships between objects

A database table storing current movie rentals
9
Structured Query Language

• The Structured Query Language (SQL) is a
  comprehensive database language for managing
  relational databases

10
What is SQL

• SQL is an ANSI standard language for accessing
  databases
• SQL stands for Structured Query Language
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database

11
Some Definitions

• Database Tables
• Databases contain objects called Tables.
• Records of data are stored in these tables.
  Tables are identified by names (like "Persons",
  "Orders", "Suppliers").
• Tables contain Columns and Rows with data. Rows
  contain records (like one record for each
  person). Columns contain data (like First Name,
  Last Name, Address, and City).

12
The Select Statement

• The SELECT statement selects columns of data from
  a database
• The tabular result is stored in a result table
  (called the result set)

13
SQL Queries

• With SQL, we can Query a database and have a
  Result returned in a tabular form.
• SELECT LastName FROM Persons

14
Coding Select Statements

• To select the columns named "LastName" and
  "FirstName", use a SELECT statement like this
• SELECT LastName,FirstName FROM Persons
• To select all columns from the "Persons" table,
  use a symbol instead of column name like this 
• SELECT FROM Persons

15
The WHERE Clause

• To conditionally select data from a table, a
  WHERE clause can be added to the SELECT statement
  with the following syntax
• SELECT column FROM table WHERE column condition
  value

16
Where-clause Conditions

• Relations that can be used
• Equal
• ltgt Not equal
• gt Greater than
• lt Less than
• gt Greater than or equal
• lt Less than or equal
• BETWEEN Between an inclusive range
• LIKE Wildcard Search

17
The LIKE Condition

• The LIKE condition is used to specify a search
  for a pattern in a column.
• The syntax is like this
• SELECT column FROM table WHERE column LIKE
  pattern
• A "" sign can be used to define wildcards
  (missing letters in the pattern) both before and
  after the pattern.

18
Sample LIKE Statements

• Select Persons with a Name Pattern
• This SQL statement will return persons with a
  first name that start with an 'O'.
• SELECT FROM Persons WHERE FirstName LIKE 'O'
• This SQL statement will return persons with a
  first name that end with an 'a'.
• SELECT FROM Persons WHERE FirstName LIKE 'a'

19
AND and OR

• AND and OR join two or more conditions in a WHERE
  clause.
• The AND operator displays a row if ALL conditions
  listed are true. 
• The OR operator displays a row if ANY of the
  conditions listed are true.
• SELECT FROM Persons
• WHERE FirstName'Paul'AND LastName'Millis'

20
BetweenAnd

• The BETWEEN ... AND operator selects an inclusive
  range of data between two values. These values
  can be numbers, text, or dates.
• SELECT column_name FROM table_name
• WHERE column_name BETWEEN value1 AND value2

21
SQL Select Distinct

• The DISTINCT keyword is used to return only
  distinct (different) values.
• SELECT DISTINCT column-name(s) FROM table-name

22
SQL Order By

• The ORDER BY clause is used to sort the rows.
• Example To display the companies in alphabetical
  order
• SELECT Company, OrderNumber FROM OrdersORDER BY
  Company

23
SQL Data Manipulation

• SQL includes a syntax to update records with
  query and update commands

24
SQL Data Definition

• The Data Definition Language (DDL) part of SQL
  permits database tables to be created or deleted,
  links between tables defined and, and constraints
  imposed between database tables.