Management of Information Systems: 45-870

1
Management of Information Systems 45-870
Mini-3 Spring 2000
2
Agenda

• Announcements
• Technology Byte Presentations
• Designing and Creating Databases (Contd)
• Brief Review of Database Design Principles
• Using MS Access 2000 to Create Relational
  Databases
• Writing Queries for Relational Databases
• Using MS Access 2000 to Write Queries for
  Relational Databases

3
Announcements

• Technology Byte Presentations
• Today (2/1)
• Section A DeathSpiral.Com, E-Sixpack
• Section B Jerees Kids, Hot TaMaLes
• Thursday (2/3)
• Section A Digital Agents, Hyperlinks
• Section B Alwin the Chipmunks, Cash Money
  Brothers
• IT Exercise I Graded/Returned
• Comments

4
Announcements

• Schedule modifications
• There will be NO handout on Internet, Intranets,
  Extranets
• Today we will continue with Database topics
• We will discuss Chapter 6 on Thursday (2/3)
• IT Exercise II due no later than midnight this
  Friday, 2/4/00
• Submit one set of answers for your team
• You can email to me as an MS Word attachment or
  submit as hard copy

5
Announcements

• Technology Byte Report
• Due on no later than midnight this Friday, 2/4/00
• Submit as a web page linked to your teams home
  page that you had created in the first IT exercise

6
Technology Byte Presentations

• Section A
• DeathSpiral.Com
• E-Sixpack
• Section B
• Jerees Kids
• Hot TaMaLes

7
Creating Relational DatabasesBrief Review

• What is a relational database
• Going from a flat file to a relational
  database..

8
A Traditional Flat File
9
Relational DataBase
Faculty Table
Dept Table
Course Table
10
How to get from a flat file to a relational
database?

• Identify Entities (Tables)
• Identify Attributes (Fields)
• Normalize Entities
• Identify Relationships between Normalized
  Entities
• Identify Primary and Foreign Keys

11
EXAMPLE 1 Department DB

• Data include
• Department Name
• Department Chair
• Department Address
• Department Phone
• Faculty ID
• Faculty Name
• Faculty Rank

• Course Name
• Course
• Course Credits
• Course Enrollment Capacity

12
Step1 Pull out repeating groups (First Normal
Form)
FROM Department (Dept-Name, Dept-Chair,
Dept-Addr, Dept-Phone, Fac-ID, Fac-Name,
Fac-Rank, Course-Name, Course, Course-Cred,
Course-Cap) TO Department (Dept-Name,
Dept-Chair, Dept-Addr, Dept-Phone) Faculty-Cour
se (Fac-ID, Fac-Name, Fac-Rank,
Course-Name, Course, Course-Cred, Course-Cap)
13
Step2 Pull out facts about a portion of the
key (Second Normal Form)
Doesnt apply here?
14
Step3 Pull out facts about a non-key
attribute (Third Normal Form)
FROM Faculty-Course (Fac-ID, Fac-Name, Fac-Rank,
Course-Name, Course, Course-Cred,
Course-Cap) TO Faculty (Fac-ID, Fac-Name,
Fac-Rank) Course (Course-Name, Course,
Course-Cred, Course-Cap)
15
Normalized Relations for this Data
Department (Dept-Name, Dept-Chair, Dept-Addr,
Dept-Phone) Faculty (Fac-ID, Fac-Name, Fac-Rank,
Fac-Dept) Course (Course-Name, Course,
Course-Cred, Course-Cap, Course-Fac)
16
From Normalized Relations to an Entity
Relationship Diagram

• each normalized relation becomes an entity
• identifier of each normalized relation becomes
  the primary key
• secondary identifier of each normalized relation
  becomes a foreign key
• Characteristics of each normalized relation
  determine relationship between entities

17
Entity Relationship Diagram Example 1
(Dept-Name, Dept-Chair, Dept-Addr, Dept-Phone)
Department
Faculty
(Course, Course-Name, Course-Cred, Course-Cap, C
ourse-Fac)
Course
(Fac-ID, Fac-Name, Fac-Rank, Fac-Dept)
18
From an ERD to a Database in MS Access 2000

• Adding Tables, Relationships and Data
• By hand
• Importing from spreadsheet
• Important!
• Must use normalized tables, relationships and
  data when creating the database in MS Access
• Illustration for Example 1
• See Data for IT Exercise II and MS Access Tips 1

19
Retrieving Data from Databases

• Selecting Relations
• Joining Relations
• Projecting columns (fields) from the joined
  relation
• Selecting rows (records) from the joined relation

20
Retrieving Data from Databases (Contd)

• Deriving new attributes
• Indexing/sorting rows (records)
• Calculating totals
• Presenting data

21
Writing Queries in MS Access 2000

• See MS Access Tips 2
• Simple queries
• Grouping, summing sorting fields
• Selecting data
• Calculated fields
• Displaying/not displaying fields
• Make vs. Select Table queries
• Illustrative Examples

22
Designing DatabasesAnother Example

• Normalizing and creating an ERD for the Sales
  Report Data

23
EXAMPLE 2 Sales Report Data

• Data includes
• Sales Person
• Sales Person Name
• Sales Area
• Customer Name
• Customer Number
• Warehouse Number
• Warehouse Location
• Sales Amount

24
Step1 Pull out repeating groups (First Normal
Form)
FROM SalesReport (SalesPerson,
SalesPerson-Name, Sales-Area, Customer,
Customer-Name, Warehouse, Warehouse-Location,
Sales-Amount) TO SalesPerson (SalesPerson,
SalesPerson-Name, Sales-Area) SalesPerson-Custom
er (SalesPerson, Customer,
Customer-Name, Warehouse,
Warehouse-Location, Sales-Amount)
25
Step2 Pull out facts about a portion of the
key (Second Normal Form)
FROM SalesPerson-Customer (SalesPerson,
Customer, Customer-Name, Warehouse,
Warehouse-Location, Sales-Amount) TO Sales
(SalesPerson, Customer, Sales-Amount) Customer-
Warehouse (Customer, Customer-Name,
Warehouse, Warehouse-Location)
26
Step3 Pull out facts about a non-key
attribute (Third Normal Form)
FROM Customer-Warehouse (Customer,
Customer-Name, Warehouse,
Warehouse-Location) TO Customer (Customer,
Customer-Name) Warehouse (Warehouse,
Warehouse-Location)
27
Normalized Relations for this Data
Sales (SalesPerson, Customer,
Sales-Amount) SalesPerson (SalesPerson,
SalesPerson-Name, Sales-Area) Customer
(Customer, Customer-Name, Warehouse) Warehouse
(Warehouse, Warehouse-Location)
28
Entity Relationship Diagram Example
(SalesPerson, SalesPerson-Name, Sales-Area)
SalesPerson
Sales
(Customer, Customer-Name, Warehouse)
Customer
(SalesPerson, Customer, Sales-Amount)
Warehouse
(Warehouse, Warehouse-Location)