union operator - Personal.kent.edu
Download
Report
Transcript union operator - Personal.kent.edu
Database Processing
Eighth Edition
Foundations of
Relational
Implementation
1
Chapter 8
David M. Kroenke
© 2002 by Prentice Hall
Implementing a Relational
Database
1. Define structure to DBMS
2. Allocate space
3. Create & load data
2
© 2002 by Prentice Hall
Accomplished Via
•
“Smaller” RDBMS
– Graphical User Interface
• “Larger” RDBMS
1. Data Definition Language
2. Data Manipulation Language
3. Data Control Language
3
© 2002 by Prentice Hall
Review Relational Model
Terminology
• Relation is a two-dimensional table
• Attributes are single valued
• Each attribute belongs to a domain
– A domain is a physical and logical
description of permittable values
• No two rows are identical
• Order is unimportant
4
© 2002 by Prentice Hall
Logical Key versus
Physical Key
• The term ‘key’ is ambiguous
• During the design phase, ‘key’ is used to
describe candidate keys
– Design-phase keys are logical keys
• During the implementation phase, ‘key’ is
any column for which there is an index
– Implementation-phase keys are physical
keys
5
© 2002 by Prentice Hall
Indexes
• Overhead data used to:
– improve access
– improve sorting performance
– enforce uniqueness
• Single column or group of columns
6
© 2002 by Prentice Hall
Indexes
7
© 2002 by Prentice Hall
Data Definition Language (DDL)
• In order to create the tables and
structures within a database, the DBMS
must provide (often using SQL) a data
definition language (DDL).
• The DDL is used to define (i.e., Create,
Drop, and Alter) everything in the
database…
– Tables, Attributes, Constraints
– Relationships
– Indexes
8
© 2002 by Prentice Hall
DDL EXAMPLE
SCHEMA for BASEBALL RDB
TEAM (Name, City);
Name, City: Text, cannot be null
Sort capability on Name and/or City
TEAM
Name
9
City
© 2002 by Prentice Hall
DDL EXAMPLE
CREATE SCHEMA BASEBALL;
CREATE TABLE TEAM (
NAME
VARCHAR2(20) NOT NULL,
CITY
VARCHAR2(50) NOT NULL,
CONSTRAINT TEAMNAME_PK PRIMARY KEY(NAME) );
CREATE INDEX CITY_IX ON TEAM(CITY);
GRANT SELECT ON TEAM TO PUBLIC;
10
© 2002 by Prentice Hall
DDL EXAMPLE
• CREATE SCHEMA: Creates a “Name Space”
• Baseball.Team.Name, Baseball.Team.City, etc
• CREATE TABLE, Attributes & Constraints
• CREATE PRIMARY KEY & other INDEX(es)
11
© 2002 by Prentice Hall
Data Control Language (DCL)
• Commands used to control database,
including administering privileges and
committing (saving) data
• Occurs concurrently with DDL & DML
12
© 2002 by Prentice Hall
DCL Example
CREATE SCHEMA BASEBALL;
CREATE TABLE TEAM (
NAME
VARCHAR2(20) NOT NULL,
CITY
VARCHAR2(50) NOT NULL,
CONSTRAINT TEAMNAME_PK PRIMARY KEY(NAME) );
CREATE INDEX CITY_IX ON TEAM(CITY);
GRANT SELECT ON TEAM TO PUBLIC;
13
© 2002 by Prentice Hall
Space Allocation
• May be handled automatically (Access) or
Organizational standards
• If not, requires
– Normalized relations
– Volume estimates
– Attribute definitions
– Usage Analysis
– Data security, backup, recovery, retention
14
© 2002 by Prentice Hall
Space Allocation
Usage Analysis:
– Where, when, how often: data entry,
retrieval, deletion, update
Order
Supplier
Order Line
Part
15
© 2002 by Prentice Hall
Data Manipulation Language (DML)
• When thinking about SQL, most people
think of the Data Manipulation Language
(DML) aspects of the language
• The DML allows users to insert, delete,
modify, and retrieve information
– Select
– Delete
– Insert
– Update
16
© 2002 by Prentice Hall
Create and Load Data
• Goal is to use DML as much as
possible
• But often requires manual keypunch
17
© 2002 by Prentice Hall
DML Alternatives
• Relational Algebra
• Relational Calculus
• Non-Procedural, Transform Oriented
(i.e. SQL)
• QBE / QBF
18
© 2002 by Prentice Hall
Most common DMLs
• Most RDBMSs provide one/all of the
following…
– SQL
– Query-by-Example
– Query-by-Form
19
© 2002 by Prentice Hall
SQL
• Non-Procedural
– Only define result
• Transform-Oriented
SELECT Name, Age
FROM PATIENT
WHERE Physician = ‘Levy’
20
© 2002 by Prentice Hall
Query-by-Example
21
© 2002 by Prentice Hall
Query-by-Example
• Originally developed by IBM for
mainframe (DB2)
• Ported down to PC (Paradox)
• No official standard, but widely used
22
© 2002 by Prentice Hall
Query by Form
23
© 2002 by Prentice Hall
DML Interfaces to DBMS
•
•
•
•
24
Forms
Query Language (i.e. SQL)
Stored Procedures
Application Program Interface
© 2002 by Prentice Hall
Stored Procedures
• Some DML tasks are performed on a
routine or regular basis
• The DML procedure may be saved in the
DBMS and recalled at a later date
• Note… the DML-code is saved, not the
results of the query/update. As such,
when the procedure is retrieved the
results will be refreshed.
25
© 2002 by Prentice Hall
Stored Procedure Example
CREATE PROCEDURE DELETECUSTOMER
(MyCustomerID IN NUMBER) IS
BEGIN
Delete From Customer Where CustomerID
= MyCustomerID;
End DeleteCustomer;
26
© 2002 by Prentice Hall
Trigger
• Special type of stored procedure
associated with a table
• When Trigger Condition for the table is
met, Trigger Body is fired
– Condition: Before, After
• Trigger Statement: Insert, Update,
Delete
27
© 2002 by Prentice Hall
Trigger Example
CREATE TRIGGER DeleteCustomer
BEFORE DELETE ON Customer
FOR EACH ROW
BEGIN
INSERT INTO CustomerHistory
VALUES(CUSTOMERID, LastName, FirstName);
END DeleteCustomer;
28
© 2002 by Prentice Hall
Application Program
Interface (API)
• Some applications provide an
Application Program Interface (API)
• The API is a DML typically used by
programmers
• To retrieve or update data contained
within the application, a programmer
submits requests to the application’s
API.
29
© 2002 by Prentice Hall
API
• Dynamic
– Generates SQL code “on the fly”
– ODBC: allows processing of many
RDBMS with no program changes
• Embedded
– SQL statements “hard coded” into
application
– Requires recompiling/linking
30
© 2002 by Prentice Hall
Relational Algebra
• Relational algebra defines a set of
operators that may work on relations.
• Recall that relations are simply data
sets. As such, relational algebra
deals with set theory.
• The operators in relational algebra
are very similar to traditional algebra
except that they apply to sets.
31
© 2002 by Prentice Hall
Relational Algebra
Operators
• Relational algebra provides several
operators:
– Union
– Difference
– Intersection
– Product
– Projection
– Selection
– Join
32
© 2002 by Prentice Hall
Union Operator
• The union operator adds tuples from
one relation to another relation
• A union operation will result in
combined relation
• This is similar to the logical operator
‘OR’
33
© 2002 by Prentice Hall
Union Operator
JUNIOR and
HONOR-STUDENT
relations and their
union:
(a) Example of JUNIOR
relation
(b) Example HONORSTUDENT relation
(c) Union of JUNIOR
and HONORSTUDENT relations
34
© 2002 by Prentice Hall
Difference Operator
• The difference operator produces a
third relation that contains the
tuples that appear in the first
relation, but not the second
• This is similar to a subtraction
35
© 2002 by Prentice Hall
Difference Operator
JUNIOR
relation
HONORSTUDENT
relation
36
JUNIOR
minus
HONORSTUDENT
relation
© 2002 by Prentice Hall
Intersection Operator
• An intersection operation will produce
a third relation that contains the
tuples that are common to the
relations involved.
• This is similar to the logical operator
‘AND’
37
© 2002 by Prentice Hall
Intersection Operator
JUNIOR
relation
HONORSTUDENT
relation
Intersection
of JUNIOR
and HONORSTUDENT
relations
38
© 2002 by Prentice Hall
Product Operator
• A product operator is a concatenation of
every tuple in one relation with every tuple
in a second relation
• The resulting relation will have n x m
tuples, where…
n = the number of tuples in the first
relation and
m = the number of tuples in the second
relation
• This is similar to multiplication
39
© 2002 by Prentice Hall
Projection Operator
• A projection operation produces a
second relation that is a subset of
the first.
• The subset is in terms of columns, not
tuples
• The resulting relation will contain a
limited number of columns. However,
every tuple will be listed.
40
© 2002 by Prentice Hall
Selection Operator
• The selection operator is similar to
the projection operator. It produces
a second relation that is a subset of
the first.
• However, the selection operator
produces a subset of tuples, not
columns.
• The resulting relation contains all
columns, but only contains a portion
of the tuples.
41
© 2002 by Prentice Hall
Join Operator
• The join operator is a combination of
the product, selection, and projection
operators. There are several
variations of the join operator…
– Equijoin
– Natural join
– Outer join
• Left outer join
• Right outer join
42
© 2002 by Prentice Hall
Data for Join Examples
SID
Name
Major
GradeLevel
123
Jones
History
JR
158
Parks
Math
GR
271
Smith
History
JR
105
Anderson
Management
SN
StudentNumber
ClassName
PositionNumber
123
H350
1
105
BA490
3
123
B490
7
43
© 2002 by Prentice Hall
Join Examples
Equijoin
Natural
Join
Left Outer
Join
44
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
1. What are the names of all students?
STUDENT [Name]
2. What are the student numbers of all
students enrolled in a class?
ENROLLMENT [StudentNumber]
45
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
3. What are the student numbers of all
students not enrolled in a class?
STUDENT [SID] – ENROLLMENT
[StudentNumber]
4. What are the numbers of students
enrolled in the class ‘BD445’?
ENROLLMENT WHERE ClassName
= ‘BD445’[StudentNumber]
46
© 2002 by Prentice Hall
Expressing Queries in
Relational Algebra
5. What are the names of the students
enrolled in class ‘BD445’?
STUDENT JOIN (SID =
StudentNumber) ENROLLMENT
WHERE ClassName =
‘BD445’[STUDENT.Name]
47
© 2002 by Prentice Hall
Summary of Relational
Algebra Operators
48
© 2002 by Prentice Hall
Database Processing
Eighth Edition
Foundations of
Relational
Implementation
49
Chapter 8
David M. Kroenke
© 2002 by Prentice Hall