Relational Database Model

1
Relational Database Model

• Tables
• By
• Bob Larson

2
Relational Database Model

• Introduced by E. F. Codd in 1970
• A Logical View of Data
• Enables developer to view data logically rather
  than physically
• Greater logical simplicity tends to yield simpler
  and more effective database design methodologies

3
Tables

• Cornerstone of Relational DBMS
• Advantages structural and data independence
• Conceptually Resembles a file
• Note a file is actually a physical structure
• Easier to understand than its hierarchical and
  network database predecessors

4
Table Characteristics
5
Keys Key Attributes (Fields)

• Consists of one or more attributes that determine
  appropriateness of other attributes
• Primary Key (PK) Attribute or a combination of
  attributes that uniquely identifies each entity
  (row)
• Keys role is based on determination
• If you know the value of attribute A, you can
  look up (determine) the value of attribute B

6
Keys Types
7
Keys Primary Foreign
8
Null Values

• No data entry Not the same as zero (0)
• Can represent
• An unknown attribute value
• A known, but missing, attribute value
• A not applicable condition
• Not permitted in primary key
• Should be avoided in other fields (when possible)
• Create problems with math functions such as
  COUNT, AVERAGE, and SUM
• Can create logical problems when relational
  tables are linked

9
Field (Column) Redundancy

• Controlled redundancy
• Makes the relational database work
• Database tables share common attributes only to
  enable the tables to be linked
• Multiple occurrences of a field in a database is
  not redundant when required to make the
  relationship work
• Redundancy exists only when there is unnecessary
  duplication of attribute values

(? - click)
10
Entity Integrity Rules
11
Integrity Rules
12
Database Normalization

• Process of efficiently organizing data in a
  database
• Two goals
• Eliminate redundant data (storing same data in
  multiple tables)
• Reduce the amount of space a database consumes
• Ensure data dependencies make sense (only storing
  related data in a table)
• Ensure that data is logically stored
• Database community developed a guidelines
• Normal forms
• Numbered from one (lowest or 1NF) through five
  (5NF)
• Typical applications use 1NF, 2NF, and 3NF
  (occasional 4NF)
• Fifth normal form is very rarely seen and won't
  be
• Important to point out that they are guidelines
  only
• Occasionally, it becomes necessary to meet
  business requirements

13
Normal Forms (1-4)

• First normal form (1NF)
• Eliminate duplicative columns from the same table
  
• Create separate tables for each group of related
  data
• Identify each row with a unique column or set of
  columns (the primary key)
• Second normal form (2NF)
• Meet all the requirements of the first normal
  form
• Remove subsets of data that apply to multiple
  rows of a table and place them in separate tables
• Create relationships between these new tables and
  their predecessors through foreign keys
• Third normal form (3NF)
• Meet all the requirements of the second normal
  form
• Remove columns that are not dependent upon the
  primary key
• Fourth normal form (4NF)
• Meet all the requirements of the third normal
  form
• Remove columns with multi-valued dependencies
• Guidelines are cumulative
• For a database to be in 2NF, it must meet the
  criteria of a 1NF database

14
Table Normalized
15
Indexes (Index Fields)

• Used to logically access rows in a table
• Index key
• Indexs reference point
• Points to data location identified by the key
• Unique index
• Index in which the index key can have only one
  pointer value (row) associated with it
• Each index is associated with only one table

16
Table Types

• Data (Entities / Nouns)
• Transaction (Events / Verbs)
• Subset
• Validation

17
Data Tables

• Sometimes called inventory tables
• Generally your object (entity) tables
• Important persons, places, or things
• Not the processing of those things
• Clearly relationships between the tables
• But no common (link) fields

18
Transaction Tables

• Sometimes called linking, event or junction
  tables
• Used to link data tables which cant be linked
  directly
• Event tables linked at a point in time when
  something happened

19
Subset Tables
Cruise Example
Security Example

• Includes fields directly related to another data
  table
• Cant be included in the original data table
• For security reasons
• They would represent multiple entries in a single
  field
• Often we discovered as we start entering test data

20
Validation Tables

• Helps maintain data integrity for a field
• Used for field lookups and combo boxes on forms
• Limits user choices to a drop-down list
• Not data tables because they are secondary in
  importance
• We didnt build the database to track states or
  department codes

21
Problem Fields (Donts)

• Calculated field can be computed by
  mathematical calculation or text concatenation
• Waste of storage space (redundant),
• No assurance the calculated value is updated when
  the user changes the input field(s)
• Multipart field contains that should be two or
  more fields
• Extra work when you want to analyze your data
• Multivalue field multiple correct entries for
  the field
• Create a separate subset table with each value in
  its own record.
• Derived field contents of one or more fields
  absolutely predicts the contents of another
• Should be dropped from the table

22
Table Name Guidelines

• Keep it short, simple and descriptive without
  using acronyms, abbreviations or codes
• Do not include object names like File, Records,
  Table and List
• Use the plural form of the name
• Do not use names that refer to or imply more than
  one subject
• Materials and Supplies should be two fields
• Do not use names that unnecessarily restrict the
  scope of your data
• Names that imply a time frame, regional location
  or organizational status Manufacturing Employees

23
Field Name Guidelines

• Keep it unique
• Should be unique in entire DBMS except to link
• Keep it short, simple and descriptive
• Use the singular form of the name
• Do not use names that refer to or imply more than
  one attribute or subject
• Avoid codes and acronyms
• Use abbreviations only if their meaning is
  absolutely clear

24
The Data Dictionary/Catalog

• Data dictionary
• Provides detailed accounting of all tables found
  within the user/designer-created database
• Contains (at least) all the attribute names and
  characteristics for each table in the system
• Contains metadatadata about data
• Sometimes described as the database designers
  database because it records the design decisions
  about tables and their structures
• Terms system catalog and data dictionary are
  often used interchangeably

25
A Sample Data Dictionary
26
Fin