Basic Concepts Part 2

1
Basic Concepts (Part 2)
2
Today's Agenda

• Alternative DB Models
• Hierarchical
• Network
• Relational
• Terminology
• Tables, records, fields, keys, etc
• 11, 1N and MN
• Unary, Binary and N-ary

3
Database Model

• Logical design or organization of data
• Reflects basic structure
• Parent - child
• Belongs to
• Ownership
• May translate into physical file structure

4
Hierarchical Database Model

• Inverted tree (with single root)
• Parent - child

College
Department
Staff
Instructor
Staff
Course/Section
Student/Reg
5
Network Database Model

• Multiple inverted trees (with shared branches)

College
Staff
Department
Instructor
Staff
Student
Course
Section
Registration
6
HDM and NDM

• Weaknesses
• User must know design
• Redundant data
• Does not support odd queries
• May not grow well
• Strengths
• Very strong referential integrity
• Size (low overhead if redundancy controlled)
• Speed (for good queries)

7
Relational Database Model

• Edgar F. Ted Codd (1923-2003)
• A Relational Model for Large Shared Databases
• Journal of the ACM, June 1970
• Mathematical / non-structural approach
• Tables are basic building blocks
• All connections handled by keys

8
Relational Database Model
College
Department
Staff
Instructor
Student
Registration
Course
Section
9
Basic Terminology

• Table or Entity or Relation
• Fields / Attributes / Columns
• Records / Tuples / Rows
• Virtual Table or View or Query
• Filtering / Aggregation
• Computation / Combination
• Access Control / Security

10
Basic Terminology

• Table or Entity or Relation
• Fields / Attributes / Columns
• Records / Tuples / Rows
• Virtual Table or View or Query
• Key Fields
• Primary
• Simple Single Field
• Composite Multiple Fields

11
Basic Terminology

• Table or Entity or Relation
• Fields / Attributes / Columns
• Records / Tuples / Rows
• Virtual Table or View or Query
• Key Fields
• Primary
• Foreign
• Secondary Indices

12
Basic Terminology

• Relationships
• 11 one-to-one
• 1N one-to-many
• MN many-to-many
• Degree
• Binary Relationship - 2 tables
• Unary Relationship - 1 table
• N-ary Relationship - N tables

Women
Men
Girls
Boys
13
Basic Terminology

• Relationships
• 11 one-to-one
• 1N one-to-many
• MN many-to-many
• Participation
• Mandatory or Total
• Optional or Partial

Women
Men
Girls
Boys