Database Design Concepts

1. Database Design Concepts With Access

2. Learning Outcomes • Identify and define the information that is needed to design a database • Create conceptual and logical db designs • Build a relational database that provides users with queries, forms, and reports • Understand core terms, concepts, and tools

3. Learning Outcomes • Design and maintain relational db tables • Create Select and Action queries • Create normalized relationships between tables, apply validation rules, and referential integrityprinciples • Design and modify reports and forms

4. Challenges • House analogy • Need a blueprint before you can build • GIGO (garbage in, garbage out)

5. Key DB Design Concepts • Entity • Person, place, thing, event (noun) • Attribute • Property of an entity (adjective, adverb) • Relationship • Association between entities • Usually a verb

6. Key DB Design Concepts • Database • Structure that can house information about multiple types of entities, the attributes of these entities, and the relationships among the entities. • Relational Database • Perceived by users to be a collection of tables; two-dimensional named tables

7. DBMS (software) • Design structure of database • Create data entry forms • Validate data • Sort and manipulate data • Query the database • Produce reports

8. Goals of Database Design • Input • set of user requirements • Output • database structure capable of supporting user requirements

9. Database Design Step 1 • Information-level design • gather user requirements • design a database that meets requirements as cleanly as possible • independent of DBMS

10. Database Design Step 2 • Concerned with characteristics of specific DBMS • Must resolve issues such as • column names • data type • number of columns • data length

11. General Design Guidelines • Identify the tables (entities) • Determine the primary keys (unique attribute) • Determine additional fields (attributes) • Determine relationships among tables

12. General Design Guidelines (cont) • Determine data types for fields • Identify and remove unwanted redundancy • Storing a piece of data in more than one place • Determine a storage location • Determine additional properties for attributes

13. A Database Example Camashaly Design Group provides custom marketing solutions for the service, non-profit, and retail sectors. The company specializes in designing and maintaining Web sites and using social networking Web sites for online marketing. Camashaly uses business analysts to work collaboratively with clients. Camashaly would like to organize the data on clients and business analysts in to a database managed by Access 2010.

14. Applying the DatabaseDesign Guidelines to Camashaly Design Group

15. Word table (Clients.doc)

16. Excel workbook

17. Word table (Business Analysts)

18. Excel workbook

19. Your Turn! • Look at the user documents provided • Clients Word document • Business analysts Word document • Business analyst financial data Excel workbook • Client financial data Excel workbook • Design a database for Camashaly Design Group

20. Import Excel Files • Open Access • On the External Data tab click Excel in the Import group • Import the two Excel workbooks • DO NOT remove any columns

21. Create Tables for Data in Word Documents • Create a table for Clients and one for Business Analysts • Hint: the Client Number and Business Analyst number should be primary key fields • Are there columns that have been duplicated between tables? If so, remove the column from one table. • Hint: Client name is contained in both the Clients and Client Financial Data tables. Normalization requires that the name is stored in the Clients table. Remove the column in the Financial Data table.

22. Set Up Relationships • Which tables have a column in common? • Close the tables (do not close Access) • Click the Database Tools tab • Click relationships • Set up a one to many relationship • Hint: Look for the common columns. Client and Business Analyst are the tables that are on the one side.

23. Create a Query • Create a Simple Query using the Wizard • Include the first name, last name and incentive YTD for the business analysts • Specify a Detail query, click Finish

24. Create a Second Query • Create a second query using Query Design that will list all clients that owe more than $1,000 • From the Show Tables dialog box, add the Clients and Client Financial Data tables • The fields to display are client name, postal code, and current due • Sort in ascending order on postal code • Criteria for the current due is > 1000 • Change to Datasheet View to see the results

25. Create a Form • Create a form to input new clients • Hint: Open the clients table and click on Create/Form

26. Create a Report • We want a report that will • Use the report wizard • Choose the clients table; fields to display are the client name and postal code • Choose the client financial data; fields to display are the amount paid and current due • View the data by Client • Group by Postal Code

27. Report (continued) • Sort by current due in descending order • No summary options • Keep all the defaults, click finish • If necessary, edit in Design view to make your report attractive.

28. Congratulations! Thank you to Phil Pratt and Mary Last