Relational Data Model: Basic Concepts Part 1

1
Relational Data ModelBasic ConceptsPart 1
2
Contents

• Relational model concepts
• Relational integrity constraints
• Conceptual data model to relational model Mapping
  
• Normalization

3
Relational Model

• Domain D is a set of atomic values.
• Relation schema R, denoted by R(A1, A2,, An), is
  made up of a relation name R and a list of
  attributes A1, A2, An.
• Attribute Ai is the name of a role played by some
  domain D.
• A relation r of the relation schema R(A1, A2,An)
  is a set of n-tuples.
• n-tuple t is an ordered list of n values (v1,
  v2,, vn) such that v1?D1, v2?D2,.....
  vn?Dn.
• Degree of relation r is n.
• Cardinality of relation r is the number of
  n-tuples in r.

4
Relation
5
An Example Relation
6
Relational Integrity Constraints

• Constraints are conditions that must hold on all
  valid relations
• Domain Constraints
• Key constraints
• Entity integrity constraints
• Referential integrity constraints

7
Domain Constraints

• The value of attribute A must be an atomic value
  drawn from the domain for A

8
Key Constraints

• Superkey A set of attributes SK of a relation r
  such that no two tuples in r will have the
  same value for SK
• Key A minimal superkey
• If a relation has several candidate keys, one is
  chosen arbitrarily to be the primary key. The
  primary key attributes are underlined.
• Example
• CAR(State, Reg, SerialNo, Make, Model, Year)

9
Entity Integrity Constraints

• The primary key attributes PK of a relation r
  cannot have null values in any tuple.

10
Referential Integrity

• A constraint involving two relations
• Used to specify a relationship among tuples in
  two relations the referencing relation and the
  referenced relation
• Foreign Key A set of attributes FK of the
  referencing relation r1 that reference the
  primary key attributes PK of the referenced
  relation r2
• Referential Integrity The value of the foreign
  key attributes FK of a tuple t1 in r1 must be
  either
• null or
• the value of the primary key attributes of a
  tuple t2 in r2
• A referential integrity constraint can be
  displayed in a relational database schema
  as a directed arc

11
An Example Relational Data Model
12
Update Operations and Integrity Constraints

• Update operations
• INSERT
• DELETE
• MODIFY
• Integrity constraints should not be violated by
  the update operations
• Domain constraints
• Key constraints
• Entity integrity constraints
• Referential integrity constraints

13
Conceptual Data Model to Relational Model
Mapping

• Entity ? Relation
• Attribute ? Attribute
• Relationship ? Foreign Key/Not represented
• 11 Relationship Either or both relationship
  descriptors are foreign keys
• 1M Relationship Relationship descriptor with
  degree 1 (The descriptor on the M side) is a
  foreign key.
• Identifier ? Primary Key

14
(No Transcript)
15
Supertype/Subtype Structure to
Relational Mapping

• Option 1
• Relation for supertype
• Relation for each subtype with its specific
  attributes plus supertype identifier
• Option 2
• Relation with supertype attributes and specific
  attributes of all subtypes, plus a type attribute