The Relational Database Model

1
The Relational Database Model
2
Learning Objectives

• Data dictionary and system catalog
• Representing relationships with ERDs
• Handling data redundancy in the relational data
  model
• Indexing

3
Data Dictionary and System Catalog

• Data dictionary
• Provides detailed account of all tables found
  within database, and all their attributes
• Metadata
• Attribute names and characteristics
• Table name
• Attribute name
• Content description
• Data type
• Format
• Range
• Required or not
• Key (PK, FK, SK) and referenced table for FK

4
Data Dictionary and System Catalog

• Rules for choosing names
• Give the same name to attributes representing the
  same data (foreign key) they are synonyms
• Give different names to attributes representing
  different data avoid homonyms
• Typical SQL datatypes
• DECIMAL(m,n) signed numbers, m digits, n decimal
  digits
• INTEGER large (up to 11 digits) positive or
  negative whole numbers
• SMALLINT small (5-6 digits) positive or negative
  whole numbers
• REAL real numbers, scientific notation
• CHAR(n) alphanumeric
• DATE calendar date
• LOGICAL true or false

5
Data Dictionary and System Catalog

• System catalog
• Detailed data dictionary in the DBMS
• System-created database
• Stores database characteristics and contents
• Tables can be queried just like any other tables
• Automatically produces database documentation

6
Data Dictionary and System Catalog
7
Relationships within Relational Database

• Relationship classifications
• 11 one-to-one
• 1M one-to-many
• MN many-to-many
• E-R Model
• ERD Maps E-R model
• Chen
• Crows Feet

8
ERD Symbols

• Rectangles represent entity sets
• Diamonds represent the relationship(s) between
  the entities (in Chen)
• 1 side of relationship
• Number 1 in Chen Model
• Bar crossing line in Crows Feet Model
• Many relationships
• Letter M and N in Chen Model
• Three pronged Crows foot in Crows Feet Model
• Orientation is immaterial
• 1-1 relationships often correspond to ill-defined
  entities could be merged in a single entity set
  An important use is the is-a relationship
• 1-M relationships correspond to two tables where
  the FK links to the 1 side of the relationship
• M-N relationships are represented by two 1-M
  relationships 2 pairs of tables, one of these
  tables is called a bridge entity, or composite
  entity and is represented by a different symbol
  in Chen model.

9
Example 11 Relationship
10
Example 11 Relationship
11
Example 1M Relationship
Figure 2.18
12
Example 1M Relationship
Figure 2.20
13
Example MN Relationship
Figure 2.23
14
Example MN Relationship
Figure 2.24
15
Converting MN Relationship to Two 1M
Relationships
Figure 2.25
16
Converting MN Relationship to Two 1M
Relationships (cont.)
Figure 2.26
17
Converting MN Relationship to Two 1M
Relationships (cont.)
Figure 2.27
18
Converting MN Relationship to Two 1M
Relationships (cont.)
Figure 2.28
Relational schema
19
Data Redundancy Revisited

• Foreign keys can reduce redundancy
• Some redundancy is desirable
• Called controlled redundancy
• Speed
• Information requirements

20
(No Transcript)
21
Indexes

• Points to location
• An index is a set of pairs (key, pointer) -
  pointer may be a record address
• Makes retrieval of data faster

Figure 2.31