Transcript Wk9x - Algonquin College

Introduction to Databases
WEEK 9
Agenda
Introduction to Databases
 Introduction to Database Design
 Scope of Learning
 Database Vocabulary
 Database Characteristics
 Types of Databases

Scope of Learning
Our goal is to have a basic understanding
of databases
 Resources:

◦
◦
◦
◦
Slides
In class activities
No textbook is required
Videos
Optional Reading

Database Design for Mere Mortals
 Hernandez
 1997
 Addison Wesley
 ISBN: 0-201-69471-9
Why Learn Databases?

Stores Information
◦ Information is a valuable commodity
◦ Information is used for:
 Marketing
 Business Intelligence (BI)
◦ Used by all kinds of businesses
◦ Access to good information (if properly done)
Question

What are some of the databases that you
have seen or use?
DATABASE TERMS
Data
According to Dictionary.com, data is “individual
facts, statistics, or other items of information”

Data is never contrived (formulate)
Database
According to Dictionary.com, a database is “a
comprehensive collection of related data
organized for convenient access, generally in a
computer.”
DBMS

Database Management System

Provides tools needed to organize data in a
flexible manner. It includes:
◦ Ability to add, modify or delete data
◦ Ability to ask questions about the data stored
(queries)
◦ Ability to produce reports
Database Models
According to Wikipedia: A database model is
the theoretical foundation of a database and
fundamentally determines in which manner
data can be stored, organized, and
manipulated in a database system.

In our words: defines the infrastructure
offered by a particular database system.
TYPES OF DATABASES
Many Types of Databases

File Maker Pro

Oracle

Microsoft
◦ Excel
◦ Access
Database Model Types

Flat File

Hierarchical

Relational Database
Flat File

Wk9_FlatFile.xlsx

Advantages:
◦ Most software includes free access of data
files (ie google docs etc)

Disadvantages:
◦ Not conducive to complicated search queries
Hierarchical

Looks like a family tree with parent / child
relationships

Each table or entity can be either a parent
or a child

Tables are implicitly joined with each
other
Hierarchical
Plants
Flowers
Annuals
Perennials
Trees
Conifers
Deciduous
Hierarchical
Relational Databases

Most Common

Composed of tables (each constructed
like a flat file)

Wk9_Northwind.accdb
ERD

Entity Relational Diagram (ERD)

Visual display of tables and their
relationships for a database

Used for relational modeling
What are Tables or Entities

Used to represent things in the real world
that is of significant interest to the user

For example:
◦ employees might be an entity in a company
database
Employees

Nouns in a business narrative
Entities Continued

Another example:
◦ A database regarding music
Entities and Their Attributes

Entities represent something of
importance

Entities are made up of several attributes:
ARTISTS
SONGS
ArtistName
SongTitle
Gender
Length
Entities Summarized

Each entity is made up of rows and
columns
◦ Columns are called fields or attributes of a
database
◦ Rows are called records or an instance of an
entity
Steps in Database Design
1.
Complete a needs assessment (note this is a huge
process that is not covered in this course)
2.
Create a business narrative
3.
Identify entities
4.
Identify attributes & keys
5.
Identify relationships
Business Narrative

Vital link between database designer and the
end users

Purpose is to make clear the data
requirements

Example:
◦ Algonquin college employs many instructors to
deliver various programs comprised of many
different courses.
Starting an ERD

Identify the entities
Algonquin college employs many instructors to
deliver various programs comprised of many
different courses.
programs
instructors
courses
When the List of Entities Seems
Complete

Ask about each entity:
◦ Is it significant?
 List only entities that are important to your database users and
that are worth the trouble and expense of computer
tabulation.
◦ Is it generic?
 List only types of things, not individual instances
◦ Is it fundamental?
 List only entities that exist independently and do not need
something else to explain them.
Sample Business Narrative

CD Collection example
◦ I have many cd’s by various artists. I like all
types of music
Sample Business Narrative

Courses example:
◦ A course can have many different learning
objectives and each learning objective has
various examples and a method of testing.
Sample Business Narrative

New Home Builders example:
◦ Bamarack is a builder of new homes in the
Ottawa area. They subcontract all the work
that needs to be completed for the build of a
new home. Each subcontractor has a
specialized trade.
We Build a Business Narrative

Create a business narrative for your shoe
collection at home
Selecting Attributes

Attributes are the fields that describe an entity

Attributes can be:
◦
◦
◦
◦
A Characteristic
A Quality
A Feature
A Fact

An attribute is a non-decomposable piece of information about an
entity

Attribute naming conventions:
◦ Single word
◦ Unique
◦ Can’t be a reserved word
Attributes for Our Shoe Scenario

Identify the attributes for all entities
identified for our shoe inventory database
Attributes

Identify the entity and attributes for the
following business narrative:
◦ ABC Property Management Inc has a building
with 20 units. Some are rented, some are
vacant. Some are 1 bedroom, some are 2
bedrooms. The rent varies. ABC Property
Management Inc keeps track of the tenant and
lease information
RULES DEFINING
TABLES, ROWS, AND
COLUMNS
Rule

Columns must stand alone
◦ The order of columns within a table has no
meaning in the model
◦ The model should still be correct even if the
columns are rearranged
Rule

Columns must have a unique name

Columns values must be unitary
◦ A column can contain only single values, never
lists
 For example if you decide to treat a person’s first
and last names as separate values, the names must
be in separate columns not in a single Name
column
Rule

Each column must contain data of a single
type

Rows must be unique
◦ In every row, some column must contain a
unique value
Rule

Rows must stand alone
◦ Each row of a table is independent and does
not depend on any other row of the same
table.
◦ As a consequence, the order of the rows in a
table is not significant
Review Tables / Entities

Tables (aka Entities) in the relational
model are used to represent “things” in
the real world.

Entities are made up of attributes

Attributes are pieces of data that describe
the entity
DETERMINING KEYS
FOR TABLES
What is a Primary Key?

The primary key of a table is the column whose
values are different in every row

A field in the database that guarantees uniqueness

Speeds data retrieval

RULE: Each record in a table must have a primary
key.

Wk9_FlatFile.xlsx (Relational Tab)
Primary Keys

Some entities have ready-made primary
keys, for example:
◦ Catalog codes

If an entity lacks a reliably unique field
◦ Use a system-assigned (autonumber)
What is a Foreign Key?

A field in a table that is the primary key of
another table.
◦ Wk9_FlatFile.xlsx (Relational Tab)

A foreign key is used to indicate a
relationship between 2 or more tables.
Hybrid / Homework



Watch YouTube Video#1 & YouTube Video #2
Read Flat File vs Relational Database
Watch Lynda.com Relational Databases
Video
 Why relational databases?
 Introduction to data modeling
 Reviewing relationship types

In a word document:
◦ Write a review for each video
◦ Identify entities and attributes in the scenario
provided in the hybrid document
Quiz Time