Announcements

If you are in Section AA or AB and you took exam B, bring your test to lab

Designing Databases

Designing a database requires a “needs analysis”

Standard Process

There are guidelines (no algorithm is possible) for creating a database

Needs Analysis

First cut at a physical DB solution

Refinement of first cut … assess/improve

Define relationships, and create tables

Formulate the logical DB solution

Refinement of the logical database

Create the queries and GUIs

Assess

Needs Analysis

“Needs analysis” -- study the activity to determine what kind of DB is needed

Identify who will create information, who will use it

Find out the information gathered

Find out what information is needed to conduct the activity

Find out when the information is created, when it is needed, and how long it must be saved

“First Cut” --Initial Design

Using Entity-Relationship diagrams, create the best physical DB

Design tables for the information created

Limit tables to simple entities

Worry about redundancy

Assess -- does it make sense?

Building First Physical DB

Build a version of the physical DB and create a few records to test work

The design is fluid -- don’t invest much time in building sample files

See if it is possible to follow the creation & use of the information through the system

Eliminating poor designs now saves time

Logical database

To formulate the views users want, a new needs analysis may be needed

Who are the users to be supported by DB

What information does each person need to see, what information do they enter?

Using ER diagrams, formulate the tables needed for each user’s view

Assess and refine

Building Logical DBs

With the design in hand, formulate tables using this strategy :

Catalog what tables will provide the information for a given view table

Create a supertable (probably using join) containing all data in the view table

Decide which fields are needed and in what order

Formulate an SQL query for the table

Implementation

A skeleton implementation is built to test out the design, then proceed...

Try out the full system to assess how it works

Teach it to the users, and let them try

Revise and retest if necessary

Enhance and “bullet proof”

SLAMA

Project 3 illustrates the ideas of database design … we will try it

It is much easier to design “on paper” than with computer software … so work out the whole design before picking up the mouse


	If you are in Section AA or AB and you took exam B, bring your test to lab


	There are guidelines (no algorithm is possible) for creating a database
			Needs Analysis
			First cut at a physical DB solution
			Refinement of first cut … assess/improve
			Define relationships, and create tables
			Formulate the logical DB solution
			Refinement of the logical database
			Create the queries and GUIs
			Assess


	“Needs analysis” -- study the activity to determine what kind of DB is needed
			Identify who will create information, who will use it
			Find out the information gathered
			Find out what information is needed to conduct the activity
			Find out when the information is created, when it is needed, and how long it must be saved


	Using Entity-Relationship diagrams, create the best physical DB
			Design tables for the information created
			Limit tables to simple entities
			Worry about redundancy
			Assess -- does it make sense?


	Build a version of the physical DB and create a few records to test work
			The design is fluid -- don’t invest much time in building sample files
			See if it is possible to follow the creation & use of the information through the system
			Eliminating poor designs now saves time


	To formulate the views users want, a new needs analysis may be needed
			Who are the users to be supported by DB
			What information does each person need to see, what information do they enter?
			Using ER diagrams, formulate the tables needed for each user’s view
			Assess and refine


	With the design in hand, formulate tables using this strategy :
			Catalog what tables will provide the information for a given view table
			Create a supertable (probably using join) containing all data in the view table
			Decide which fields are needed and in what order
			Formulate an SQL query for the table


	A skeleton implementation is built to test out the design, then proceed...
			Try out the full system to assess how it works
			Teach it to the users, and let them try
			Revise and retest if necessary
			Enhance and “bullet proof”


	Project 3 illustrates the ideas of database design … we will try it
			It is much easier to design “on paper” than with computer software … so work out the whole design before picking up the mouse