type: INTEGERlength UNSIGNED ZEROFILL

1
Variable Types
type INTEGER(length) UNSIGNED ZEROFILL
REAL(length,decimals) UNSIGNED ZEROFILL
DECIMAL(length,decimals) UNSIGNED ZEROFILL
NUMERIC(length,decimals) UNSIGNED ZEROFILL
DATE TIME TIMESTAMP DATETIME
CHAR(length) BINARY ASCII UNICODE
VARCHAR(length) BLOB
2
Mapping - Weak Attributes
CREATE TABLE auto ( Serial_no VARCHAR(8)
NOT NULL, . . . . . PRIMARY KEY (Serial_no) )
CREATE TABLE Wheel ( R_auto VARCHAR(8) NOT
NULL, . . . . . FOREIGN KEY (R_auto) REFERENCES
Automobile(Serial_no) ON DELETE Cascade ON
UPDATE Cascade)
3
Mapping - Multi-valued Attributes
Serial_no
CREATE TABLE owner ( Serial_no VARCHAR(8)
NOT NULL, . . . . . FOREIGN KEY (Serial_no)
REFERENCES Automobile(Serial_no) ON DELETE
Cascade)
Same as weak entity!
4
Mapping - Composite Attributes
auto
Added to Relation
5
Mapping - Superclass-Subclass Entities
bicycle
o
mountain bike
road bike
tandem bike
trick bike
stunt bike
d
cruiser
crit bike
6
Mapping - Superclass-Subclass Entities (Method 1)
Each supertype receives a primary key and each
subtype receives a secondary ket
No_posts
7
Mapping - Superclass-Subclass Entities (Method 1)
stunt_bike
bicycle
?
No_posts
Weight wheel_size cost
8
Mapping - Superclass-Subclass Entities (Method 1)
Each supertype receives a primary key and each
subtype receives a secondary ket
CREATE TABLE bicycle ( model_no
varchar(12) NOT NULL . . . . . PRIMARY KEY
(model_no)
CREATE tandem_bike ( . . . . . FOREIGN KEY
(model_no) REFERENCES bicycle(model_no)
ON DELETE Cascade ON UPDATE Cascade
9
Mapping - Superclass-Subclass Entities (Method 1)
Each supertype receives a primary key and each
subtype receives a secondary ket
CREATE TABLE bicycle ( model_no
varchar(12) NOT NULL, . . . . . PRIMARY KEY
(model_no)
CREATE road_bike (rbm varchar(12) NOT
NULL . . . . . PRIMARY KEY (rbm) FOREIGN KEY
(model_no) REFERENCES bicycle(model_no)
ON DELETE Cascade ON UPDATE Cascade
CREATE crit_bike ( FOREIGN KEY (rbm) REFERENCES
road_bike(rbm) ON DELETE Cascade ON
UPDATE Cascade
10
Mapping - Superclass-Subclass Entities (Method 2)
For total inheritence, each subtype is pre-joined
with its supertype
Must query all subtypes and project to get
bicycle alone
11
Mapping - Superclass-Subclass Entities (Method 3)
The supertype is pre-joined with all of its
subtypes non-joining supertypes are filled out
with NULLS
No_posts
Duplicate keys are eliminated
12
Mapping - Superclass-Subclass Entities
Methods can be combined in multi-hierarchies
Methods 2 and 1
13
Mapping - Superclass-Subclass Entities
Methods can be combined in multi-hierarchies
Methods 3 and 1
No_posts
14
Mapping - Superclass-Subclass Entities
Methods can be combined in multi-hierarchies
Methods 3 and 3
No_posts
15
Mapping - Union Relations and Categories
bicycle
d
mountain bike
road bike
tandem bike
trick bike
stunt bike
d
cruiser
crit bike
u
gears
Category receives surrogate key that relates
union type to others in same category
road_bike
Brake_type br mod
Mountain_bike
Brake_type br mod susp_type
gears
Brand model no_gears front/rear
16
Mapping Implementation Issues
Subtypes consisting of many small tables for
classification purposes
Space time trade-offs, for example in inheritance
structures
Implementation of enumerated types
Use of Domain declarations
17
Grading Criteria Assignment Three
Revision of EER diagram
Consistency of mapping between EER and SQL tables
- consistency
Implementation of Primary and foreign keys
Proper treatment of cardinality or arity mappings
Proper treatment of inheritance mappings
Selection of types and domains