Extended Learning Module J Implementing a Database with Microsoft Access

1. Extended Learning Module J Implementing a Database with Microsoft Access A Great Module on Your CD. Management Information Systems for the Information Age

2. Presentation Overview • Implementing The Structure Of Your Employee Database • Defining Relationships Within Your Employee Database • Entering Information Into Your Employee Database • Creating A Simple Query Using One Relation • Creating An Advanced Query Using More Than One Relation • Generating A Report Management Information Systems for the Information Age

3. Introduction • In Figure J.1 on the following slide, we’ve recreated the correct database structure from Extended Learning Module C. • Revisit Module C if you need a refresher. Management Information Systems for the Information Age

4. Introduction Management Information Systems for the Information Age

5. Implementing The Structure Of Your Employee Database • Data dictionary - contains the logical structure for the information. • To create a database using Microsoft Access, perform the following steps: • Start Microsoft Access. • Select Blank Database on the right side of the screen or select File and New from the menu and then Blank Database on the right side of the screen. • Select a folder for the database and enter a file name (we’ll use Employee.mdb). • Click on Create. Management Information Systems for the Information Age

6. Implementing The Structure Of Your Employee Database Create a blank database by clicking on Blank Database. Management Information Systems for the Information Age

7. Implementing The Structure Of Your Employee Database Select a location and provide a database name. Management Information Systems for the Information Age

8. Implementing The Structure Of Your Employee Database • To create a relation (table) using the Design view, make sure the Tables tab is selected and then double-click on Create table in Design view. • Enter a name, data type, and description (the last is optional) for each field in a given relation. • Save that structure and repeat the process until you’ve created the structure for each relation in your database. Management Information Systems for the Information Age

9. Implementing The Structure Of Your Employee Database To define the structure of a relation, first select the Tables tab and then double-click on Create table in Design view. Management Information Systems for the Information Age

10. Implementing The Structure Of Your Employee Database Enter field names and data types here. Management Information Systems for the Information Age

11. Implementing The Employee Relation Structure To identify a primary key, place your cursor in the appropriate row and click on the key icon. The key icon will then appear to the left of the field name. This is the field properties for Employee ID, the primary key. Notice that it does not allow for duplicates. Management Information Systems for the Information Age

12. Implementing The EmployeeRelation Structure • We entered three fields for the Employee relation – Employee ID, Name, and Department Num. • Employee ID = Number • Name =Text • Department Num = Number Management Information Systems for the Information Age

13. Implementing The EmployeeRelation Structure • We identified Employee ID as the primary key by placing a key symbol next to its field name. • You’ll perform this process for each primary key in each relation you create. Management Information Systems for the Information Age

14. Implementing The EmployeeRelation Structure • To save the structure, click on the disk icon (Save As), enter Employee as the table name, and click on OK. • You can then close the Table: Employee box. Management Information Systems for the Information Age

15. Implementing The Job Relation Structure Job Number is the primary key. This is the field properties for Job Name. We have set Allow Zero Length to No which means that you must enter something in this field. Management Information Systems for the Information Age

16. Implementing The Job Relation Structure • We created the Job relation by using the Design view. • We identified Job Number as the primary key by placing the key icon beside its name in the field name list. Management Information Systems for the Information Age

17. Implementing The Job Relation Structure • Let’s take a look at the Field Properties for Job Name. • Since this is a text field, you have some new Field Properties to consider. • Allow Zero Length - if you leave this at Yes, you’re essentially saying that anyone can enter a new job but not specify a name (i.e., blank). Management Information Systems for the Information Age

18. Implementing The Department Relation Structure This is the field properties for Department Name. Notice that we changed the Field Size from the default of 50 to 35. Always make sure text fields are large enough to handle any entry but not so large that you waste space. Management Information Systems for the Information Age

19. Implementing The Department Relation Structure • We created the Department relation using the Design view. • Department Num = Number • Department Name = Text • We also specified that Department Num is the primary key by placing the key icon beside its name in the field name list. Management Information Systems for the Information Age

20. Implementing The Department Relation Structure • Let’s take a look at the Field Properties for Department Name. • Since this is a text field, the default size (Field Size) is 50 characters, which we changed to 35. • The field size for a text field can range from 0 to 255. Management Information Systems for the Information Age

21. Implementing The Job Assignment Relation Structure The Job Assignment relation is a composite (or intersection) relation, meaning that it will have two fields (Employee ID and Job Number) making up the primary key. To identify more than one filed as the primary key, highlight the appropriate fields and click on the key icon. Management Information Systems for the Information Age

22. Implementing The Job Assignment Relation Structure • We entered all the field names • Employee ID = Number • Job Number = Number • Hours =Number • This particular relation is different from the rest because it has a composite primary key. Management Information Systems for the Information Age

23. Implementing The Job Assignment Relation Structure • Composite primary key - consists of the primary key fields from the two intersecting relations. • We created the Job Assignment relation to eliminate the many-to-many relationship that existed between the Employee and Job relations. • The Job Assignment relation has a primary key composed of two fields – the primary key Employee ID that originated in the Employee relation and the primary key Job Number that originated in the Job relation. Management Information Systems for the Information Age

24. Defining Relationships Within Your Employee Database • We have one final structural task to take care of – that of defining how all the relations relate to each other. • Foreign key - a primary key of one file (relation) that appears in another file (relation). Management Information Systems for the Information Age

25. Defining Relationships Within Your Employee Database To define relationships in a database, you must first click on the Relationships button. When you first start this process, the Relationships palette will be blank. As you highlight each relation and click on the Add button in the Show Table box, the relations will appear on the palette. Management Information Systems for the Information Age

26. Defining Relationships Within Your Employee Database • To create these relationships, you click on the Relationships button in the button bar. • To identify the relationships, you must make each relation appear on the palette. • To do this, simply highlight each relation name and click on Add, which is what we did. • We then clicked on the Close button to make the Show Table box disappear. Management Information Systems for the Information Age

27. Defining Relationships Within Your Employee Database Drag and drop primary keys onto foreign keys. This will create a one-to-many relationship. The “many” is denoted by the infinity symbol. Enforce referential integrity by clicking here. Management Information Systems for the Information Age

28. Defining Relationships Within Your Employee Database • Click on and drag each primary key and drop it onto its respective foreign key counterpart. • Once you drop the primary key onto its respective foreign key counterpart, you’ll see the Edit Relationships box. • In that box, you need to click on Enforce Referential Integrity and then Create. Management Information Systems for the Information Age

29. Entering Information Into Your Employee Database • To enter information, you simply highlight the desired relation and click on Open. Management Information Systems for the Information Age

30. Entering Information Into Your Employee Database To enter information, highlight the appropriate relation and click on Open. Management Information Systems for the Information Age

31. Entering Information Into Your Employee Database When you begin entering information, your database will look very much like a spreadsheet with rows and columns. Management Information Systems for the Information Age

32. Creating a Simple Query Using One Relation • Query-by-example (QBE) tool - helps you graphically design the answer to a question. • Suppose, for example, that we wanted to see a list of all employees by Employee ID and by Name. Management Information Systems for the Information Age

33. Creating a Simple Query Using One Relation To create a query, select the Queries tab and double-click on Create query in Design view. Management Information Systems for the Information Age

34. Creating a Simple Query Using One Relation Then, select the appropriate relations by highlighting each of them individually and clicking on the Add button. This is the QBE grid. Management Information Systems for the Information Age

35. Creating a Simple Query Using One Relation Since all the query information we need is in the Employee relation, it’s the only relation we added. Management Information Systems for the Information Age

36. Creating a Simple Query Using One Relation Next, we dragged and dropped Employee ID and Name from the Employee relation into the QBE grid. Management Information Systems for the Information Age

37. Creating a Simple Query Using One Relation After clicking on the exclamation point icon (Run), Access provided us with this result of our query. Management Information Systems for the Information Age

38. Creating a Simple Query Using One Relation • Let’s assume that we wanted to see all employees by ID and name who are in the Residential department (Department Num is 43). • We often refer to this as a conditional query because it only returns results based on some condition. Management Information Systems for the Information Age

39. Creating a Simple Query Using One Relation To create a condition query, we added Department Num from the Employee relation and specified “=43” in the Criteria field. Management Information Systems for the Information Age

40. Creating a Simple Query Using One Relation The result of this query shows only employees in department 43. Management Information Systems for the Information Age

41. Creating An Advanced Query Using More Than One Relation • Suppose we wanted a list of all employees by ID, name, and the department in which they work. However, instead of Department Num as the identifier for the department, we would like to see Department Name. • That query will require the use of two relations because Employee ID and Name are in the Employee relation and Department Name is in the Department relation. Management Information Systems for the Information Age

42. Creating An Advanced Query Using More Than One Relation To show employees by ID, name, and department name, we added both the Employee and Department relations to the query. Management Information Systems for the Information Age

43. Creating An Advanced Query Using More Than One Relation We then dragged and dropped Employee ID and Name from the Employee relation and Department Name from the Department relation into the QBE grid. Management Information Systems for the Information Age

44. Creating An Advanced Query Using More Than One Relation The result of that query show employee information including Department Name, not Department Num. Management Information Systems for the Information Age

45. Creating An Advanced Query Using More Than One Relation • As a final query example, let’s consider that we would like to see all The IDs and names of employees who work in the Residential Department (Department Num is 43) who work more than four (4) hours in Sales (Job Number is 23) and how many hours they work in Sales. Management Information Systems for the Information Age

46. Creating An Advanced Query Using More Than One Relation • To perform this query, we still only need two relations, this time Employee and Job Assignment. • Employee ID from Employee • Name from Employee • Department Num from Employee • Job Number from Job Assignment • Hours from Job Assignment Management Information Systems for the Information Age

47. Creating An Advanced Query Using More Than One Relation To show employees in department 43 who are assigned to job 23 for more than 4 hours, we added both the Employee and Job Assignment relations to this query. Management Information Systems for the Information Age

48. Creating An Advanced Query Using More Than One Relation We then dragged and dropped the necessary information into the QBE grid and specified the appropriate information in the Criteria fields. Management Information Systems for the Information Age

49. Creating An Advanced Query Using More Than One Relation The result shows only two employees. Management Information Systems for the Information Age

50. Generating A Report • To create this report, follow these steps: • Select the Reports tab. • Double click on Create report by using wizard. • Under Tables/Queries, select Table: Employee. • Under Available Fields, select Employee ID and click on the greater than sign (>) to the right and select Name and click on the greater than sign (>) to the right. • Under Tables/Queries, select Table: Department. • Under Available Fields, select Department Name and click on the greater than sign (>) to the right. Management Information Systems for the Information Age