Objectives 6/11/14

1. Objectives 6/11/14 • Learn introductory concepts and guidelines for designing databases • Differentiate and categorize types of stored data. • Understand the situations where it is appropriate to use a database vs. spreadsheet vs. both. • Define key terms related to database design. • Learn how to design a small database. • Understand how Access creates data structures and relates data in a database.

2. Questions for a systems developer • What am I trying to accomplish by using a computer? Are you: • Keeping track of lots of information for an organization, customer, government, investor? • Making processes more efficient requiring less labor? • Facilitating decision making? • Presenting information in a more readable, attractive format? • What is the best way to accomplish your goals? • Database management system? • Spreadsheet? • Web front end? • Custom developed programs? • Combination?

3. Why do we store data in a database? • To protect the “integrity” of the data. • Make data accurate. • Reduce data redundancy. • To make data more accessible. • Enhance flexibility of data access methods. • Improve speed of data access. • To make an application more adaptable. • Provide more flexibility in application development. • Decrease dependence on a given visualization method.

4. Process for Designing Databases (Database Normalization) • Identify all the fields needed to produce the required information • Divide each piece of data into its smallest useful part Example 1: Break up a name into first, last, initial Example 2: Break up an address into street, city, state, zip • Group related fields into tables • Use an entity-relationship diagram (ERD) to depict the design • Determine each table’s primary key • Identify how the tables are related (or if they are related) • Include a common field in related tables (foreign key) • Determine the properties of each field • Type of data: Text, date, number, etc. • Size of data • Name of data

5. Example: Belmont Landscapes Company provides landscape architecture services including analyzing sites, developing plans and performing construction work. Wants to have a way to keep track of the work that will be done for customers (“contracts”) and then have a way to charge those customers (“invoices”) once the work has been completed.

6. Examine the contract “spreadsheet” Review data content. What is the application? What is the purpose of the stored data? Is any of the data redundant? Why might redundant data be a problem?

8. Contract Data

9. Group (sub-divide) the customer data

10. Group (sub-divide) the invoice data

11. What is a primary key? Definition Examples “Natural” vs. “Surrogate”

12. Primary Keys • Naturalkeys are primary keys having an innate value • Examples • A UPC code • A stock symbol • A book ISBN number • In cases where there is no innate value, we create an artificial key (surrogate key). • The key value is arbitrary • Maybe just some Integer value • Composite key: two or more fields combine to uniquely identify a record. • Sometimes used when joining tables.

13. Define keys and relationships

14. What does it look like in Access?

15. What is a foreign key? Definition Example Depiction on diagrams

16. What is referential integrity? Definition “Constraint” Conceptual example Implementation in Access example

17. Referential Integrity Usage • Primary keys ensure entity integrity • Eliminates duplicate records • Null values are prohibited • Referential integrity ensures that a foreign key in one table matches a primary key in another table • Without referential integrity, orphaned records can exist • A record in the (many) table with a corresponding record in the (one) table

18. Referential Integrity in Access

19. Referential Integrity • Enforce referential integrity means 1-to-many relationships are enforced • Cascade Update means that if primary key changes in master table the corresponding key is updated in the detail table • Cascade Delete means that deleting master record causes corresponding child records to be deleted