INF1050- Databases

1. INF1050- Databases In this module you will use Microsoft Access to create digital databases

2. First Steps • Start Menu -> Programs -> Microsoft Office -> Access • Click on Blank database. Click OK • Open the File button in the middle right. This is where it will be saved. Call it lastname.firstname and save it in your Save Space. Click the Create button

3. You should have a screen that looks like this. Make sure that you select the Create tab, which is circled in red. • Click the Table Design button that is right below the circled area.

4. Insert this data into the table as shown. You will also have to change the Field Size number (see arrow pointing to box) • Change Field Sizes, in descending order, to: 5, 30, 30, 2, 30, 20, 2, 5, 2.

5. Next Steps • When you have all of your information, go to File -> Save (not Save As). • Save your table as ‘Employees’. • It will ask you if you want to create a Primary Key. Say no. • Close the table

6. Create a Department Table • Use the previous steps to create a new table • Change the Field Size to 2 and 30, in descending order • Save this Table as ‘Departments’.

7. Open the Employees Table

8. Enter the Data As Shown • Make sure all the data is visible (you may need to stretch the columns) • Save your table • Close your Employees table

9. Open the Department table • Include the following data in this table by switching back to the DataSheet view:

10. Adding a New Category • Go to the Employee table and change to Design View • Right-click on the Hire Date row • Click on the Insert rows icon on the Table Design toolbar and put in this new text: Field name = Telephone Data Type = Text Field Size = 14 Description= Employee Home Telephone Number • Save this change

11. Making sure your cursor is in the Telephone row, click below on Input Mask and click on • The three black dots

12. Follow These Steps… And then hit Finish

13. Go back to Datasheet view and enter:

14. Using the Lookup Wizard • Open the Employees table and change to Design View • Right-click and add in a new category at the top, Emp. Category. Click on Data Type and select Lookup Wizard at the bottom • Sorry, wrong table in picture

15. Follow These Steps Then save

16. Go back to the Datasheet • Notice that your 3 pay choices can be selected from drop-down boxes • from a menu. • Recreate the boxes as shown • If necessary be sure to stretch out your column so that you can • see all the words.

17. Moving Fields • We will move the Last Name field below the Middle Initial field • Click your mouse in the grey box to the left of the Last Name field • Click and drag it down until the line between Middle Initial and Street Address is a solid black. • Let go with the mouse, and it will move below into the order shown here:

18. Sorting Records • Go back to the Employees table in Datasheet view • Click on the drop-down box under Last Name and select Sort A to Z

19. Relationships Between Tables • Create two new tables: a Clients table and a Claims table. Do not worry about the descriptions

20. Define the Primary Key • Open the Clients table in Design view • Select the “Client #” record and click your mouse in the grey box area to the left. It should be highlighted in black • Click on the Primary Key button. A key symbol should now be on your Client# Row

21. Define the Primary Key 3. Save this table 4. Open the Claims table in Design View 5. Select the “#Claims” record and add the primary key icon to it like you had done with the previous table. Save the table.

22. The Relationship • Click on the Relationship icon in the Database Tools tab

23. Add both Clients and Claims

24. Next Steps Click and drag the Client# into The bottom of the Claims box. Check these 3 boxes Then click “Create”. The two boxes will be linked together.

25. Seeing the Relationship (awwww) • Hit the Plus + button to open the linked Claim table • Fill in the data you see here for the two tables: Switch back to the Claims table and this info will now be in there

26. Congratulations! You have now learned how to: • Create tables and fill in data • Create fields • Create a Drop-Down menu using the Wizard • Use Design view and Data view • Show a relationship between two tables • Please hand your activity into the INF1050 folder when you have completed it

27. Your Assignment • You are the owner of a new auto repair shop, Super Mechanics • You will need to develop a database that keeps track of customers, vehicles, services performed and parts. (It can also be any other sort of business you may be interested in) • You could also do another business such as a clothing store or pet store

28. You will need 4 Tables: • Customers- include last and first name, phone number, address, province and city 2. Service- repair description, service technician name, date of repair, total cost • Parts- parts used, cost of parts, serial number • Vehicle- make, model, colour, plate number • If you are doing a different kind of store, use table 1 but make 3 other tables that fit with the kind of business it would be. Keep the customer table, though.

29. Next Steps • You will have to create 2 or more relationships with the tables before you put in any information • Create 15 entries (customers) for this assignment. You make have to do a bit of research if you are going to list certain products, brands and prices in your database