Notes

Outline

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