Extended Learning Module J (Office 2010 Version)

1. Extended Learning Module J (Office 2010 Version) Implementing a Database with Microsoft Access

2. STUDENT LEARNING OUTCOMES • Identify the steps necessary to implement the structure of a relational database using the DDL provided by Microsoft Access. • Demonstrate how to use the data manipulation subsystem in Access to enter and change information in a database and how to query that information. • Explain the use of the application generation subsystem in Access to create reports and data entry screens.

3. MODULE ORGANIZATION • Solomon Enterprises Database • Learning Outcome #1 • Defining Relationships within the Solomon Enterprises Database • Entering Information into the Solomon Database • Learning Outcome #2 • Creating a Simple Query Using One Relation

4. MODULE ORGANIZATION • Creating an Advanced Query Using More than One Relation • Generating a Simple Report • Learning Outcome #3 • Generating a Report with Grouping, Sorting, and Totals • Creating a Data Input Form

5. SOLOMON DATABASE • The steps to creating a database are • Define entity classes and primary keys • Define relationships among the entity classes • Define fields for each relation (file) • Use a data definition language to create the database, which is the focus of this Module

6. SOLOMON DATABASE • In Figure J.1 (p. 474) on the following 3 slides, we’ve recreated the correct database structure from Extended Learning Module C • Revisit Module C if you need a refresher

7. SOLOMON DATABASE

8. SOLOMON DATABASE

9. SOLOMON DATABASE

10. Creating Solomon’s Database • Data dictionary - contains the logical structure for the information • To create the Solomon Enterprise database: • Start Microsoft Access • Click on Blank Database in the upper left corner of the screen • Enter Solomon Enterprises.accdb as the database name • Click on Create

11. Creating Solomon’s Database 1. Create a new database by clicking on Blank Database 2. Enter SolomonEnterprises.accdb

12. Creating Solomon’s Database • Our recommendation: Create a relation (table) in Design view: • Must switch from Datasheet View to Design View • Click on View in the upper left corner and then Design View • Enter a table name • Use the Design View to enter the specifications for the table

13. Creating Solomon’s Database This is Design View – our recommendation for creating the tables in a database To switch to Design View, click on View and then Design View

14. Creating the Raw Material Relation • Enter the four fields of the Raw Material relation • Raw Material ID • Raw Material Name • QOH • Supplier ID • Click on the Raw Material ID row and then the key button to designate Raw Material ID as the primary key

15. Creating the Raw Material Relation Primary key identifier and the Primary key button Integrity constraints for Raw Material ID

16. Creating the Concrete Type Relation The fields for Concrete Type Primary Key Integrity Constraints

17. Creating the Bill of Material Relation • We created (in Extended Learning Module C) the Bill of Material relation to eliminate the many-to-many relationship between the Concrete Type and Raw Material relations

18. Creating the Bill of Material Relation • The Bill of Material relation has a primary key composed of two fields (composite primary key): • Concrete Type • Raw Material ID • Composite primary key - consists of the primary key fields from the two intersecting relations

19. Creating the Bill of Material Relation Composite primary key

20. DEFINING RELATIONSHIPS WITHIN SOLOMON’S DATABASE • The final structural task is to define how all the relations relate to each other • That is, link primary and foreign keys • Foreign key - a primary key of one file (relation) that appears in another file (relation)

21. Primary and Foreign Key Logical Ties

22. Defining Relationships between Relations • To create relationships • Click on Database Tools in the menu area and then click on the Relationships button • Make each relation appear on the palette by highlighting each relation name and clicking on Add • Then click on the Close button

23. Defining Relationships between Relations Select tables and click on Add Relationship palette

24. Defining Relationships between Relations

25. ENTERING INFORMATION INTO SOLOMON’S DATABASE Double-click on any table to begin entering information

26. Entering Information We double-clicked on the Supplier table and can now begin entering information We have completed entering all supplier information

27. Referential Integrity • Referential integrity ensures consistency. For example, that you don’t put a non-existent Supplier ID into the Raw Material relation • The relationships we set up for referential integrity guard against bad information • Integrity constraints – rules that help ensure the quality of the information

28. Enforcing Referential Integrity Because we entered a non-existent Supplier ID (445) that doesn’t exist, Access will not allow us to continue If you try to close the window, Access will allow you to change the information or save the good information without the bad

29. Change the Structure of a Relation You can change the structure of a relation by clicking on View and then DesignView In Design View, you can change the structure of a table

30. CREATING A SIMPLE QUERY USING ONE RELATION • Query-by-example (QBE) tool - helps you graphically design the answer to a question • Suppose we wanted to see a list of raw materials that shows • Raw Material Name • Supplier ID

31. Creating a Simple Query Using the Raw Material Relation First, click on Create and then Query Design Second, select the table, click on Add, and then close the box.

32. Creating a Simple Query Using the Raw Material Relation The result of the query Drag and drop the appropriate field names and then click on the exclamation point (Run).

33. Creating a Simple Query with a Condition Set the condition here

34. CREATING AN ADVANCED QUERY USING MORE THAN ONE RELATION • Suppose we want a query that shows • All order numbers • Date of orders • Where the goods were delivered • The contact person • The truck involved in each delivery • The truck driver in each delivery

35. Steps to Create an Advanced Query • Click on Create in the menu area and then Query Design • In the Show Table dialog box • Select and Add the relation names • Close the Show Table dialog box • Tables linked appear are joined by lines with • 1 beside the table with the primary key • The infinity sign by the table with the foreign key

36. Steps to Create an Advanced Query • Drag and drop the fields that you want from the appropriate relation into the QBE grid in the order that you want • Click on the exclamation point (Run) in the button bar to see the results of the query

37. Steps to Create an Advanced Query From the Show Table box, select the appropriate tables – Customer, Order, Employee and Truck

38. Steps to Create an Advanced Query Drag and drop the appropriate fields

39. Steps to Create an Advanced Query The completed query

40. GENERATING A SIMPLE REPORT • Click on Create in the menu area and then the Report Wizard button • Choose tables and/or queries: Lets you choose which table/query you want • Choose fields: Lets you choose the fields you want

41. Grouping, Sorting, Layout of Report • Grouping: Lets you specify grouping of information (we chose the default) • Sorting: Allows you to specify sorting (we chose the default) • Layout and orientation: Allows you to select layout and page orientation (we chose the default)

42. Style and Header of Report • Report header: Allows us to enter a title for the report. • The Report: Shows all customers and phone numbers.

43. Choose Table/Query and Fields Choose tables and/or queries Click on Create and then Report Wizard

44. Choose Table/Query and Fields We don’t need grouping so click on Next. Select fields by clicking on each and clicking on the > Then click on Next 4. Grouping

45. Sorting and Layout Select a Layout and Orientation We don’t need to sort so click on Next.

46. Style and Title The wizard generated completed report Choose a Style then click on Next. Enter a report headerand click on Finish

47. Changing the Look of the Report Click on View an choose Layout View Click on Close Print Preview

48. Changing the Look of a Report Move the report elements to change the look of the report

49. REPORT WITH GROUPING, SORTING, AND TOTALS • Say we want to create the Supply Chain Management report from Extended Learning Module C • First, create a query • Then, put the query into the report generator

50. Steps for Creating a Report with Grouping, Sorting, and Totals • Click on Create in the menu area and click on the Report Wizard button • Choose tables/queries: Query: Supply Chain Query • Choose fields: Select all fields by clicking on the double greater-than sign (>>)