Transcript presentation source

Databases
Week 5
LBSC 690
Information Technology
Agenda
• Questions
• Relational database design
• Implementation using Microsoft Access
Relational Databases
• Tables represent relations
– Name, project
– Name, email address, phone number
• Relations can be “joined”
– Name, project, email address, phone number
• Relations can be “projected”
– Name, email address
• Relations can be “restricted”
– Name = “Doug Oard”
Why use Join?
• Forces consistency
– Doug Oard, project 18, oard@glue, 57590
– Doug Oard, project 22, oard@wam, 57590
• Limits the chance of error
– Doug Oard, project 18, oard@glue, 57590
– Doug Oard, project 19, oard@glue, 57490
• Avoids lots of duplicated entry and updates
• Can save a lot of storage space
Problems with Joins
• Data modeling for joins is complex
– Taught in LBSC 670
• Joins are expensive to compute
– Both in time and storage space
• But it is joins that make databases “relational”
– Projection and restriction also used in flat files
Key Fields
• Primary Key uniquely identifies line to join
– May group several fields to get a unique key
• Social security number
• First and last name
• “Foreign” key must appear in the other table
– But it need not be unique there
• Join makes a new table
– Line specified by foreign key is tacked on
Example of a Join on “Team”
Name
Chris
Chris
Team
A
A
Camile
Eileen
Natalie
A
B
C
David
Tonya
Skip
Team Project
A
Database
B
Web
C
Web
Name
Chris
Chris
Team Project
A Database
A Database
Camile
Eileen
Natalie
A
B
C
Database
Web
Web
B
C
David
Tonya
B
C
Web
Web
C
Skip
C
Web
Project to Keep Two Fields
Name
Chris
Chris
Team
A
A
Team Project
A
Database
B
Web
Camile
Eileen
Natalie
David
A
B
C
B
Tonya
C
Tonya
Web
Skip
C
Skip
Web
C
Web
Name
Chris
Chris
Project
Database
Database
Camile
Eileen
Natalie
David
Database
Web
Web
Web
Restrict to Web Pages
Name
Chris
Chris
Team
A
A
Camile
Eileen
Natalie
David
A
B
C
B
Tonya
C
Skip
C
Team Project
A
Database
B
Web
C
Web
Name
Eileen
Natalie
Project
Web
Web
David
Tonya
Skip
Web
Web
Web
How to Split Tables
• The idea is to remove redundancy
• For simple problems (like the homework)
– Start with “binary relationships”
• Pairs of fields that are related
– Group together wherever possible
– Add keys where necessary
• For more complicated problems
– Entity relationship modeling (LBSC 670)
FlightFinder Exercise
• Design a database to match passengers with
available flights on corporate jets
– Companies phone in available seats
• They want to know about interested passengers
– Passengers call up looking for flights
• They want to know about available flights
– These things happen in no particular order
Exercise Goals
• Identify the tables you will need
– First decide what data you will save
• What questions will be asked?
– Then decide how to group it into tables
• Start with binary relations if that helps
• Design the queries
– Using join, project and restrict
• Add primary and foreign keys where needed
Exercise Logistics
• Work in groups of 3 or 4
• Brainstorm data requirements for 5 minutes
– Do customers care about the price?
– Do companies care what passengers weigh?
• Develop tables and queries for 15 minutes
– Don’t get hung up on one thing too long
• Compare you answers with another group
– Should take about 5 minutes
One Possible Answer
• Surely you didn’t expect this in the notes :)
Tables:
Flight: Flight Number, Origin, Destination, Departure Time,
Arrival Time, Available Seats, Company Name, Price
Passenger: Passenger Number, Name, Address, Phone Number
Company: Company Name, Company Address, Company Phone
Booking: Flight Number, Passenger Number
Queries:
Passenger calls: Join:
Flight, Company
Project: Departure Time, Company Phone
Restrict: Origin, Destination, Available Seats>0
Company calls: Join:
Flight, Passenger, Booking
Project: Flight Number, Name, Phone Number
Restrict: Company Name
Microsoft Access
• Start Access
– Icon in the Microsoft Office folder
• Name your database planes.mdb in M:\
• Click “Table” and then “New”
– Select “New Table”
• Table wizards can be helpful (query wizards are not)
• Enter the fields
Entering Fields
• Field Names must be unique in a table
• Select field type from a menu
– Use date/time for times
– Use text for phone numbers
• Use right mouse button for primary key
• Save the table when you’re done
– That’s when you get to name it
• Exit, then reselect to enter the data
Building Queries
• Copy N:\share\class\flight.* to M:\
• Select “Queries” then “New”
– Skip the query wizard
• Choose two tables
– Flight and Company
• Pick each field you need using the menus
– Unclick the X to “unproject”
– Enter a criteria to “restrict”
• Save, exit, and reselect to run the query
Fun Facts about Queries
• Joins are automatic if field names are same
– Otherwise, drag a line between the fields
• Sort order is easy to specify
– Use the menu
• Queries form the basis for reports
– Reports give good control over layout
– Use the report wizard - the formats are complex
Other Things to Know
• Referential integrity assures joins will work
– Need to specify this when defining tables
• Forms manage input better than raw tables
– Invalid data can be identified when input
– Graphics can be incorporated
Key Ideas
• Databases are a good choice when you have
– Lots of data
– A problem that contains inherent relationships
• Design before you implement
– This is just another type of programming
• Join is the most important concept
– Project and restrict just remove undesired stuff
Database Projects
• Design for large amounts of data
– Must use multiple tables
– Need a plan for long-term maintenance
• How will data be removed?
• Attention to the user interface
– Forms and reports
– Web interfaces are hard
• Enough content to verify proper operation