Relationships 101

1
Relationships 101

• Relational Database Theory

2
What is a Relational DB

• Essentially it is a collection of tables
• The database is designed so that it can be
  understood and operated on by the user
• Usually can give easy to understand, visual
  representations of the data
• The tables (files) are based on common terms that
  the user knows
• Everything is usually based on the organization
  and its functions

3
Why Relational Databases?

• All of the Same Benefits of the database
• Reduced Redundancy
• Searching
• Queries
• Integrity
• Easy to Design

4
How to setup a relational DB?

• Terms you know
• Entities, Attributes, Relationships
• Entities?
• Become our tables (files)
• Attributes?
• Become our columns
• What about relationships?

5
Relationships?

• What makes a relationship?
• How are you related to your family members?
• Common genes
• How are you related to other students?
• Common classes
• This is a rough example but database
  relationships are built almost the same way.

6
Relationships in a database

• Based on logical associations
• How are the following related to each other
• Customers and Orders
• Orders and Products
• Customers and Products???????
• Well we have to establish these connections in
  our database.
• How do we do that?

7
Establishing a Relationship

• We share a common attribute
• What is something that can refer uniquely to
  every customer on my website?
• ID Number
• When you buy something we can reference you by
  your customer number
• We simply share that attribute with the order
  information.

8
Share the Column

• We simply place the same column in each table in
  the relationship.
• Now they can reference each other and they are
  connected for life
• We will see this more later.

9
Relations?

• Entities, Attributes, and Relationships form what
  are called Relations
• Relation A two dimensional table
• Relations have simple rules
• Entries are single valued
• Columns have distinct names
• Values in a column are all the same attribute
• Order is not important
• Each row is unique
• Each name is qualified

10
Relations vs. Relationships

• A relationship is the connection between two or
  more tables
• Defines the way of communication
• A relation is the actual table itself
• 2 Dimensional
• Contains data

11
So what is a Relational DB?

• A Relational Database is a collection of
  relations
• All files in the database follow the rules of
  relations
• Records are all unique
• Other terms for you
• Rows are called Records or Tuples
• Columns are called Attributes

12
How do you keep it unique?

• Qualify the names of everything
• We also use Keys
• Primary Key the column of a table that uniquely
  defines each record
• Foreign Key the primary key of one table
  located in another table
• It is normally our keys that define our
  relationships

13
Habitat for Data

• How to build relational Databases
• Find out your entities
• Define Attributes
• Define Keys
• Define Relationships
• Rows build themselves
• Do it on paper first
• Define it
• Diagram it
• Build it

14
What are the benefits

• Visual Interfaces
• Easy to understand data structures
• Easy management
• Easy manipulation
• Easy to Query?

15

• Confucius Say
• Man who write Query,
• have Questions

16
What is a query?

• A Query is a simple question that is asked to the
  database
• Queries come in many forms
• QBE (Query by Example)
• Simple Queries
• Criteria Based Searching
• Comparison and Relation Operators
• Compound Criteria
• Sorting, Grouping, Joining

17
Back in the Day

• How it all began
• Queries by hand
• Relational Algebra (NOT Math)
• Based on mathematic structure
• Uses Functions and parameters
• Join, Project, Select, Union, Intersect,
  Subtraction, Division, Product
• SQL is based on relational algebra
• We will look at this later

18
Lets do it!