Transcript Relational Implementation

Foundations of
Relational Implementation
Implementing Relational
Databases
Relational Data
Manipulation
Relational Algebra
Definitions
Relation structure = table format
Occurrence = structure + data
Relational schema = structure +
constraints
(Logical) key = unique tuple identifier
Physical key (index) = attribute supported
by appropriate data structure
Implementing relational DBs
Define the database structure
Data Definition Language (DDL)
Graphical definition tools
Allocate
media space
Populate
tables
Data Definition Language
CREATE TABLE MOVIE
(
MovieNumber
CHARACTER (5)
Title
CHARACTER VARYING (30) NOT NULL,
Type
CHARACTER VARYING (10) NOT NULL,
YearMade
DATE
CriticRating SMALLINT
)
NOT NULL,
NOT NULL,
NOT NULL,
MPAARating
CHARACTER VARYING (5) NOT NULL,
DirNumber
CHARACTER VARYING (5) NOT NULL,
PRIMARY KEY
( MovieNumber ),
FOREIGN KEY
( DirNumber ) REFERENCES DIRECTOR
Populate database
Fill the database with data via:
importing
data entry
After population, data must be verified for
accuracy
DML interfaces
Forms/reports
Query/update
language
SQL
Stored procedures
DML application interfaces
Subroutine calls to DBMS subroutine library
Data access commands embedded in program
Relational data manipulation
Relational algebra
Set operations
Relational calculus
Non-procedural, theoretical importance
Transform-oriented languages
SQL
Query-by-example, query-by-form
We have seen them in Access
Relational algebra operators
Select
Project
Join
Union
Intersection
Difference
Product
Division
Select
Extracts specified rows
SELECT Sells WHERE bar = “Joe’s”
Project
Extracts specified attributes
Duplicate tuples are eliminated
PROJECT Sells OVER (beer, price)
Sells [beer, price]
Union
Extracts rows that belong to either table
All rows from both A and B without
duplicates
UNION A with B
A + B
A
B
Tables must be union-compatible (same
schema):
same number of attributes
attributes have same domain
Example:
Find the bars that are either on Maple
Street or sell Bud for less than $3
Sells(bar,
beer, price)
Bars(name,
addr)
Example revisited
Find the bars that are either on Maple Street
or sell Bud for less than $3, again
Invent new names for intermediary relations
Renaming of attributes is implicit in schema
of new relation
Sells(bar, beer, price)
Bars(name, addr)
Intersection
Extracts rows that belong to both tables
INTERSECT A WITH B
A  B
Tables must be union-compatible
A
B
Difference
Extracts all the rows that belong to B but
not to A
SUBTRACT A FROM B
B - A
Tables must be union-compatible
A
B
Join
Used to combine two relations on the
basis of a common attribute containing
equal (or <,>,...) values
JOIN Sells Bars
WHERE Sells.Bar = Bars.Name
2 types of join
Equijoin: contains both copies of common
attribute
Natural join:
All attributes with same name are equated
Contains only one copy of common attribute
JOIN Sells(bar,beer,price) Bars(bar,addr)
Product
Cartesian product of two relations
Pairs up every row in A with every row in B
PRODUCT A WITH B
A x B
If A has n rows and B has m rows, then
A x B has n x m rows
Note: join is a combination of product,
selection, projection (in that order!)
Division
Extracts rows from first table which are
matched to all of the rows in the second
table
CUSTOMER
CustNo StockNo
1
1
1
2
2
2
3
1
3
2
4
3
5
2
STOCK
StockNo
1
2
DIVIDE CUSTOMER BY STOCK
CustNo
1
3
Summary of query formats
 SELECT table
WHERE condition(s) GIVING newtable
 PROJECT table OVER (field-list)
GIVING newtable
 JOIN table1 table2
WHERE condition(s) GIVING newtable
 UNION table1 WITH table2 GIVING newtable
 INTERSECT table1 WITH table2 GIVING newtable
 SUBTRACT table1 FROM table2 GIVING newtable
 PRODUCT table1 WITH table2 GIVING newtable
 DIVIDE table1 BY table2 GIVING newtable