Review Session

Review Session
• What is data, what is information, and give a
real world example to differentiate these two
concepts.
Data versus information
Data
Information
Discrete,
unorganized,
raw facts
The
transformation
of those facts
into meaning
• What is the main differences between a
transactional database and an analytical
database?
Two types of data
Transactional
• Captures data
describing and event
• An exchange between
actors
• Real-time
Analytical
• Captures data to
support analysis and
reporting
• An aggregated view of
the business
• Historical
Components of an information
infrastructure
Transactional
Database
Analytical Data Store
Supports management of
an organization’s data
Supports managerial
decision-making
For everyday
transactions
For periodic analysis
This is what is commonly
thought of as “database
management”
This is the foundation
for business intelligence
Comparing Operational and Analytical
Data Stores
Operational Data Store
Analytical Data Store
Based on Relational
paradigm
Based on Dimensional
paradigm
Storage of real-time
transactional data
Storage of historical
transactional data
Optimized for storage
Optimized for data retrieval
efficiency and data integrity and summarization
Supports day-to-day
operations
Supports periodic and ondemand analysis
Prepare an ERD for the following
scenario
Renting a movie at Blockbuster:
• Each movie is described by an ID, name, genre
(horror, comedy, drama, romantic, and
foreign)
• A movie can be rented by one or many
customers.
• A customer is described by an ID, name,
address, credit card number.
Be sure
•
•
•
•
Identify primary keys
Attribute of relationship
Attribute is conceptual and abstract
Don not overcomplicate the problem
Describe the relationship in this ERD
and convert into tables
FacultyID
Faculty
First name
Last
Name
Semester
Teaches
Course
Number
Course
Course
name
Converting that ERD into a schema
Faculty-Course
Course
FacultyCourseID
Faculty
FacultyID
CourseNumber
FacultyID
CourseNumber
CourseName
FirstName
Semester
LastName
FacultyCourseID
FacultyID
CourseID
Semester
1
5
10
Spring 2011
2
5
12
Fall 2011
3
3
10
Spring 2011
4
3
12
Fall 2011
The Rules
1. Create a table for every entity
2. Create table fields for every entity’s attributes
3. Implement relationships between the tables
1:many
relationships
• Primary key field of “1” table put into
“many” table as foreign key field
many:many
relationships
• Create new table
• 1:many relationships with original tables
1:1
relationships
• Primary key field of one table put into
other table as foreign key field
Query this database (petdb)
Pet
Owner
PetID
INT
OwnerID
INT
OwnerID
INT
Name
VARCHAR(30)
Name
VARCHAR(30)
Street
VARCHAR(55)
Weight
DECIMAL(5,1)
City
VARCHAR(25)
Type (dog,
cat, fish)
VARCHAR(25)
State
VARCHAR(2)
Zip
VARCHAR(10)
1) How much does “Fluffy” weigh?
2) What is the average weight of a cat?
3) What is the name of the owner of “Snuggles”?
1) List all the animal’s names
SELECT name FROM petdb.pet;
2) What is the average weight of a cat?
SELECT AVG(pet.weight) FROM petdb.pet
WHERE pet.type = ‘Cat’
3) What commands do you need to get pet’s name
and owner location
(4) What is the average weight for different
categories of animals?