Databases
Class 5
LBSC 690
Information Technology
Agenda
•	Questions
•	Databases
–	Relational database design
–	Implementation using Microsoft Access
•	Programming
–	The mythical person-month
–	Object oriented programming
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”
Project to Keep Two Fields
Restrict to Web Pages
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 :)
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
–	The mythical person-month applies!
•	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
Software
•	Software models aspects of reality
–	Input and output represent the state of the world
–	Software describes how the two are related
•	Examples
–	Ballistic computations
–	Homework 6
–	Alta Vista
–	Microsoft Word
Programming Languages
•	Used to specify every detail of the model
•	Special purpose
–	Able to specify an entire class of models
•	Spreadsheets (Excell, Quatro Pro, ...)
•	Databases (Access, Paradox, ...)
•	General purpose
–	Able to specify any possible model
•	Pascal, C, Java, ...
 The Mythical Person-Month
•	If it would take one person three months, why does it take four people SIX months?
•	Four causes
–	It wouldn’t have taken 3 months anyhow!
–	Partitioning strategy
–	Training time
–	Communications effort
How long will it take?
•	Rules of thumb
–	1/3 specification
–	1/6 coding
–	1/2 test planning, testing, and fixing!
•	Add time for coding to learn as you go, but don’t take time away from the other parts!
–	Reread the section on “gutless estimating” if you are tempted
Training Time
•	Simple Example
–	Full time person = 2,000 hours/year
–	Part time person = 288 hours per year
–	No training    ->  7 part time people
–	With training -> 10 part time people
•	Learning the specification takes lots of time
–	Learning organizational “rules” takes longer
Communications
•	Sort of like continuous training
–	Who needs to know what I just learned?
•	Can be minimized by good partitioning
–	Limit the number of interfaces
•	Can be facilitated by computers
–	Asynchronous communication techniques
•	Email, BBS, voice mail
History of Programming
•	Machine code
–	Zeroes and Ones
•	Assembly language
–	“Assembler” changes names to machine code
•	High-level languages
–	“Compiler” translates math to machine code
•	Independent of machine “architecture”
–	FORTRAN, COBOL
History of Programming
•	Structured Programming
–	Group instructions into meaningful abstractions
–	C, Pascal
•	Object oriented programming
–	Group “data” and “methods” into “objects”
–	C++, Java
Object Models
•	Represent facts about the world as “data”
•	Combine data into “data structures”
–	Data structures model things
•	Represent actions using “operations”
•	Combine operations into “methods”
–	Methods model what can be done to things
•	“Classes” are data structures with methods
–	Classes model aspects of reality
A Simple Example
•	Data:                 Height, Weight, Shoe size
•	Data structure:  All three together
•	Operations:       Multiply, Divide
•	Method:            Shoe size=4*Weight/Height
•	Class:                Method & data structure
Instances
•	Classes actually model kinds of things
–	“person” in the example is a class
•	Objects are instances of a class
–	Object representing me is an instance of person
•	Object Oriented Programming
–	Define classes (data structures and methods)
–	Create objects
–	Perform some methods
Data Types
•	int
–	Like integers, but there is a biggest and smallest
•	float
–	Like real numbers, but there are a finite number
•	char
–	Any character in any language (UNICODE)
•	boolean
–	True or false
Data Structures
•	Arrays
–	Lists of some type of data
–	Every element must be the same type
•	Each is assigned a number (0, 1, 2, ...)
–	An array of type “char” is called a “string”
•	Structures
–	Groups of (possibly) different data types
•	Each must be given a name by the programmer
Operations
•	Same idea as methods
–	But applied to data types rather than structures
•	- int                produces int
•	int + int          produces int
•	int * float       produces float
•	int < int          produces boolean
•	char == char  produces boolean
Making Methods
•	Actions can be operations or methods
•	Three ways to combine actions
–	Sequential (...; ...)
–	Conditional (if...then...else)
–	Loop (do...while, for ...)
•	Every possible model can be built this way!
 Java Objects
•	Represent things in the real world
–	Those things may be fairly abstract, though
•	Encapsulate data and methods
–	Data are the facts you seek to model
–	Methods represent actions
•	Classes are types of objects
–	When instantiated, you get an individual object
 Java Data Types
•	boolean
–	True or false
•	int
–	Like integers, but there is a biggest and smallest
•	float
–	Like real numbers, but there are a finite number
•	char
–	Any character in any language (UNICODE)
Java Data Structures
•	Array
–	A list of things
–	Every element must be the same type
•	Each is assigned a number (0, 1, 2, ...)
•	String
–	An array of characters
•	Methods are provided for printing, etc.
 Java Operations
•	Things you can do to data
•	Examples:
–	int < int          produces boolean
–	char == char  produces boolean
–	int + int          produces int
–	int * float       produces float
–	- int                produces int
Java Statements
•	Assignment
–	numberOfBirds = numberOfHawks + numberOfOrioles;
•	Call to a method
–	airForceOne = new Airplane(“747”);
–	Altitude = aifForceOne.readAltitude();
•	Return a valule from a method
–	return numberOfBirds;
Combining Statements
•	Three ways to combine statements
–	Sequential (...; ...)
•	Separate each with a semicolon (statement1; statement2)
•	Group using braces ({statement1;statement2})
–	Conditional (if ... then ... else or try ... catch ...)
–	Loop (do ... while, for ...)
•	Every possible model can be built with these 3!