Database - Discussions

Chapter06_accessible.pptx

Home >Computer Science homework help >Database - Discussions

Fundamentals of Database Systems

Seventh Edition

Chapter 6

Basic S Q L

If this PowerPoint presentation contains mathematical equations, you may need to check that your computer has the following installed:

1) MathType Plugin

2) Math Player (free versions available)

3) NVDA Reader (free versions available)

Learning Objectives

6.1 S Q L Data Definition and Data Types

6.2 Specifying Constraints in S Q L

6.3 Basic Retrieval Queries in S Q L

6.4 INSERT, DELETE, and UPDATE Statements in S Q L

6.5 Additional Features of S Q L

Basic S Q L

S Q L language

Considered one of the major reasons for the commercial success of relational databases

S Q L

The origin of S Q L is relational predicate calculus called tuple calculus (see Chapter 8) which was proposed initially as the language SQUARE.

S Q L Actually comes from the word “SEQUEL” which was the original term used in the paper: “SEQUEL TO SQUARE” by Chamberlin and Boyce. I B M could not copyright that term, so they abbreviated to S Q L and copyrighted the term S Q L.

Now popularly known as “Structured Query language”.

S Q L is an informal or practical rendering of the relational data model with syntax

S Q L Data Definition, Data Types, Standards

Terminology:

Table, row, and column used for relational model terms relation, tuple, and attribute

CREATE statement

Main S Q L command for data definition

The language has features for: Data definition, Data Manipulation, Transaction control (Transact-S Q L, Chapter 20), Indexing (Chapter 17), Security specification (Grant and Revoke- see Chapter 30), Active databases (Chapter 26), Multi-media (Chapter 26), Distributed databases (Chapter 23) etc.

S Q L Standards

S Q L has gone through many standards: starting with S Q L-86 or S Q L 1.A. S Q L-92 is referred to as S Q L-2.

Later standards (from S Q L-1999) are divided into core specification and specialized extensions. The extensions are implemented for different applications – such as data mining, data warehousing, multimedia etc.

S Q L-2006 added X M L features (Chapter 13); In 2008 they added Object-oriented features (Chapter 12).

S Q L-3 is the current standard which started with S Q L-1999. It is not fully implemented in any R D B M S.

Schema and Catalog Concepts in S Q L (1 of 2)

We cover the basic standard S Q L syntax – there are variations in existing R D B M S systems

S Q L schema

Identified by a schema name

Includes an authorization identifier and descriptors for each element

Schema elements include

Tables, constraints, views, domains, and other constructs

Each statement in S Q L ends with a semicolon

Schema and Catalog Concepts in S Q L (2 of 2)

CREATE SCHEMA statement

CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;

Catalog

Named collection of schemas in an S Q L environment

S Q L also has the concept of a cluster of catalogs.

The CREATE TABLE Command in S Q L (1 of 3)

Specifying a new relation

Provide name of table

Specify attributes, their types and initial constraints

Can optionally specify schema:

CREATE TABLE COMPANY.EMPLOYEE ...

CREATE TABLE EMPLOYEE ...

The CREATE TABLE Command in S Q L (2 of 3)

Base tables (base relations)

Relation and its tuples are actually created and stored as a file by the D B M S

Virtual relations (views)

Created through the CREATE VIEW statement. Do not correspond to any physical file.

COMPANY Relational Database Schema

Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema.

Figure 5.6 One Possible Database State for the COMPANY Relational Database Schema (1 of 2)

Figure 5.6 One Possible Database State for the COMPANY Relational Database Schema (2 of 2)

Figure 6.1 S Q L CREATE TABLE Data Definition Statements for Defining the Company Schema from Figure 5.7 (1 of 2)

Figure 6.1 S Q L CREATE TABLE Data Definition Statements for Defining the Company Schema from Figure 5.7 (2 of 2)

The CREATE TABLE Command in S Q L (3 of 3)

Some foreign keys may cause errors

Specified either via:

Circular references

Or because they refer to a table that has not yet been created

D B A’s have ways to stop referential integrity enforcement to get around this problem.

Attribute Data Types and Domains in S Q L (1 of 4)

Basic data types

Numeric data types

Integer numbers: INTEGER, INT, and SMALLINT

Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION

Character-string data types

Fixed length: CHAR(n), CHARACTER(n)

Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)

Attribute Data Types and Domains in S Q L (2 of 4)

Bit-string data types

Fixed length: BIT(n)

Varying length: BIT VARYING(n)

Boolean data type

Values of TRUE or FALSE or NULL

DATE data type

Ten positions

Components are YEAR, MONTH, and DAY in the form Y Y Y Y-M M-D D

Multiple mapping functions available in R D B M S s to change date formats

Attribute Data Types and Domains in S Q L (3 of 4)

Additional data types

Timestamp data type

Includes the DATE and TIME fields

Plus a minimum of six positions for decimal fractions of seconds

Optional WITH TIME ZONE qualifier

INTERVAL data type

Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp

DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison.

Attribute Data Types and Domains in S Q L (4 of 4)

Domain

Name used with the attribute specification

Makes it easier to change the data type for a domain that is used by numerous attributes

Improves schema readability

Example:

CREATE DOMAIN S S N_TYPE AS CHAR(9);

TYPE

User Defined Types (U D T s) are supported for object-oriented applications. (See Chapter 12) Uses the command: CREATE TYPE

Specifying Constraints in S Q L

Basic constraints:

Relational Model has 3 basic constraint types that are supported in S Q L:

Key constraint: A primary key value cannot be duplicated

Entity Integrity Constraint: A primary key value cannot be null

Referential integrity constraints: The “foreign key “ must have a value that is already present as a primary key, or may be null.

Specifying Attribute Constraints

Other Restrictions on attribute domains:

Default value of an attribute

DEFAULT <value>

NULL is not permitted for a particular attribute (NOT NULL)

CHECK clause

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

Specifying Key and Referential Integrity Constraints (1 of 2)

PRIMARY KEY clause

Specifies one or more attributes that make up the primary key of a relation

Dnumber INT PRIMARY KEY;

UNIQUE clause

Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model).

Dname VARCHAR(15) UNIQUE;

Specifying Key and Referential Integrity Constraints (2 of 2)

FOREIGN KEY clause

Default operation: reject update on violation

Attach referential triggered action clause

Options include SET NULL, CASCADE, and SET DEFAULT

Action taken by the D B M S for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE

CASCADE option suitable for “relationship” relations

Giving Names to Constraints

Using the Keyword CONSTRAINT

Name a constraint

Useful for later altering

Figure 6.2 Default Attribute Values and Referential Integrity Triggered Action Specification

Specifying Constraints on Tuples Using CHECK

Additional Constraints on individual tuples within a relation are also possible using CHECK

CHECK clauses at the end of a CREATE TABLE statement

Apply to each tuple individually

CHECK (Dept_create_date <= Mgr_start_date);

Basic Retrieval Queries in S Q L

SELECT statement

One basic statement for retrieving information from a database

S Q L allows a table to have two or more tuples that are identical in all their attribute values

Unlike relational model (relational model is strictly set-theory based)

Multiset or bag behavior

Tuple-id may be used as a key

The SELECT-FROM-WHERE Structure of Basic S Q L Queries (1 of 2)

Basic form of the SELECT statement:

where

<attribute list> is a list of attribute names whose values are to be retrieved by the query.

<table list> is a list of the relation names required to process the query.

<condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query.

The SELECT-FROM-WHERE Structure of Basic S Q L Queries (2 of 2)

Logical comparison operators

Projection attributes

Attributes whose values are to be retrieved

Selection condition

Boolean condition that must be true for any retrieved tuple. Selection conditions include join conditions (see Chapter 8) when multiple relations are involved.

Basic Retrieval Queries (1 of 2)

(a)

Bdate	Address
1965-01-09	731Fondren, Houston, TX

(b)

Fname	Lname	Address
John	Smith	731 Fondren, Houston, TX
Franklin	Wong	638 Voss, Houston, TX
Ramesh	Narayan	975 Fire Oak, Humble, TX
Joyce	English	5631 Rice, Houston, TX

Query 0. Retrieve the birth date and address of the employee(s) whose name is ‘John B. Smith’.

Query 1. Retrieve the name and address of all employees who work for the ‘Research’ department.

Basic Retrieval Queries (2 of 2)

(c)

Pnumber	Dnum	Lname	Address	Bdate
10	4	Wallace	291Berry, Bellaire, TX	1941-06-20
30	4	Wallace	291Berry, Bellaire, TX	1941-06-20

Query 2. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date.

Ambiguous Attribute Names

Same name can be used for two (or more) attributes in different relations

As long as the attributes are in different relations

Must qualify the attribute name with the relation name to prevent ambiguity

Aliasing, Renaming and Tuple Variables (1 of 2)

Aliases or tuple variables

Declare alternative relation names E and S to refer to the EMPLOYEE relation twice in a query:

Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.

Recommended practice to abbreviate names and to prefix same or similar attribute from multiple tables.

Aliasing, Renaming and Tuple Variables (2 of 2)

The attribute names can also be renamed

Note that the relation EMPLOYEE now has a variable name E which corresponds to a tuple variable

The “AS” may be dropped in most S Q L implementations

Unspecified WHERE Clause and Use of the Asterisk (1 of 2)

Missing WHERE clause

Indicates no condition on tuple selection

Effect is a CROSS PRODUCT

Result is all possible tuple combinations (or the Algebra operation of Cartesian Product– see Chapter 8) result

Queries 9 and 10. Select all EMPLOYEE S s n s (Q9) and all combinations of EMPLOYEE S s n and DEPARTMENT D name (Q10) in the database.

Unspecified WHERE Clause and Use of the Asterisk (2 of 2)

Specify an asterisk (*)

Retrieve all the attribute values of the selected tuples

The * can be prefixed by the relation name; e.g., EMPLOYEE *

Tables as Sets in S Q L (1 of 2)

S Q L does not automatically eliminate duplicate tuples in query results

For aggregate operations (See sec 7.1.7) duplicates must be accounted for

Use the keyword DISTINCT in the SELECT clause

Only distinct tuples should remain in the result

Query 11. Retrieve the salary of every employee (Q11) and all distinct salary values (Q11A).

Tables as Sets in S Q L (2 of 2)

Set operations

UNION, EXCEPT (difference), INTERSECT

Corresponding multiset operations: UNION ALL, EXCEPT ALL, INTERSECT ALL)

Type compatibility is needed for these operations to be valid

Query 4. Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project.

Substring Pattern Matching and Arithmetic Operators

LIKE comparison operator

Used for string pattern matching

% replaces an arbitrary number of zero or more characters

underscore (_) replaces a single character

Examples: WHERE Address LIKE ‘%Houston,TX%’;

WHERE S s n LIKE ‘_ _ 1_ _ 8901’;

BETWEEN comparison operator

E.g., in Q14 :

WHERE(Salary BETWEEN 30000 AND 40000) AND D n o = 5;

Arithmetic Operations

Standard arithmetic operators:

Addition (+), subtraction (–), multiplication (*), and division (/) may be included as a part of SELECT

Query 13. Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.

Ordering of Query Results

Use ORDER BY clause

Keyword D E S C to see result in a descending order of values

Keyword A S C to specify ascending order explicitly

Typically placed at the end of the query

ORDER BY D.D name D E S C, E.L name A S C, E.F name A S C

Basic S Q L Retrieval Query Block

INSERT, DELETE, and UPDATE Statements in S Q L

Three commands used to modify the database:

INSERT, DELETE, and UPDATE

INSERT typically inserts a tuple (row) in a relation (table)

UPDATE may update a number of tuples (rows) in a relation (table) that satisfy the condition

DELETE may also update a number of tuples (rows) in a relation (table) that satisfy the condition

INSERT

In its simplest form, it is used to add one or more tuples to a relation

Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE command

Constraints on data types are observed automatically

Any integrity constraints as a part of the D D L specification are enforced

The INSERT Command

Specify the relation name and a list of values for the tuple. All values including nulls are supplied.

The variation below inserts multiple tuples where a new table is loaded values from the result of a query.

Bulk Loading of Tables

Another variation of INSERT is used for bulk-loading of several tuples into tables

A new table T NEW can be created with the same attributes as T and using LIKE and DATA in the syntax, it can be loaded with entire data.

EXAMPLE:

DELETE

Removes tuples from a relation

Includes a WHERE-clause to select the tuples to be deleted

Referential integrity should be enforced

Tuples are deleted from only one table at a time (unless CASCADE is specified on a referential integrity constraint)

A missing WHERE-clause specifies that all tuples in the relation are to be deleted; the table then becomes an empty table

The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause

The DELETE Command

Removes tuples from a relation

Includes a WHERE clause to select the tuples to be deleted. The number of tuples deleted will vary.

UPDATE (1 of 3)

Used to modify attribute values of one or more selected tuples

A WHERE-clause selects the tuples to be modified

An additional SET-clause specifies the attributes to be modified and their new values

Each command modifies tuples in the same relation

Referential integrity specified as part of D D L specification is enforced

UPDATE (2 of 3)

Example: Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively

UPDATE (3 of 3)

Example: Give all employees in the ‘Research’ department a 10% raise in salary.

In this request, the modified SALARY value depends on the original SALARY value in each tuple

The reference to the SALARY attribute on the right of = refers to the old SALARY value before modification

The reference to the SALARY attribute on the left of = refers to the new SALARY value after modification

Additional Features of S Q L (1 of 2)

Techniques for specifying complex retrieval queries (see Chapter 7)

Writing programs in various programming languages that include S Q L statements: Embedded and dynamic S Q L, S Q L/C L I (Call Level Interface) and its predecessor O D B C, S Q L/P S M (Persistent Stored Module) (See Chapter 10)

Set of commands for specifying physical database design parameters, file structures for relations, and access paths, e.g., CREATE INDEX

Additional Features of S Q L (2 of 2)

Transaction control commands (Chapter 20)

Specifying the granting and revoking of privileges to users (Chapter 30)

Constructs for creating triggers (Chapter 26)

Enhanced relational systems known as object-relational define relations as classes. Abstract data types (called User Defined Types- U D T s) are supported with CREATE TYPE

New technologies such as X M L (Chapter 13) and O L A P (Chapter 29) are added to versions of S Q L

Summary

S Q L

A Comprehensive language for relational database management

Data definition, queries, updates, constraint specification, and view definition

Covered:

Data definition commands for creating tables

Commands for constraint specification

Simple retrieval queries

Database update commands

, , , , ,and

=<<=>>=<>