Database Practice

Database Practice

During this exercise if you are unsure first try to work it out. Second ask the person next to you if they can show you how to do it. Third put your hand up and ask the teacher.

1. Create New Database called aardvark
2. Create a new table in design view called animal
3. Insert the following fields into the table
4. Id_animaldata type autonumber
5. Namedata type text
6. Furdata type yes/no
7. Livingdata type number
8. Make Id_animal the primary key
9. Change the format of Name so that the maximum text length can be 30 characters.
10. Change the data type of Fur to text
11. Add the validation rule to Fur - "Fur" Or "no fur".
12. Add the default value to Fur – “Fur”
13. Add the Indexing to Fur – yes (Duplicates OK)
14. Switch to data entry view
15. Enter the following data

Fur

1. Close the table.
2. Create a new table in design view called Transaction
3. Insert the following fields into the table
4. Trans_IDdata type autonumber
5. Id_animaldata type number
6. Eating_welldata type yes/no
7. Criticaldata type yes/no
8. Make Trans_ID the primary key
9. Save and close the table
10. Create a form based upon the animal table. Include all fields and accept all defaults. Name this BASIC FORM.
11. Add a new record using the form.
12. Id_animal
13. NamePolar Bear
14. FurYes
15. Living2
16. Search for turtle – ensure you use the search function.
17. Change the search to Living = 10
18. Go to the last record.
19. Do a filter by Form showing all those that have Fur.
20. Apply the filter
21. Cancel filter
22. Create a new filter using filter by example on Living finding all those with 12.
23. Close form
24. Open BASIC FORM in design view
25. Move Fur field heading and field to the last position of the form.
26. Go to Form view
27. Close form and save.
28. Go back to Transaction table.
29. Move Critical column so that it is the second column in the table.
30. Close and save the table.
31. Create a query in design view called Problem.
32. Add the table animal to the query
33. Add all fields from the animal table. Ensure they are in this order.
34. Id_animal
35. Name
36. Fur
37. Living
38. Sort name by alpha
39. Add the criteria <20 to the Living field.
40. Hide the Id_animal field.
41. Run the query
42. Save the query (name Problem)
43. Close query
44. Open query in design view.
45. Change the criteria to <=10
46. Run query
47. Add the criteria that does not include Polar Bear
48. Run Query
49. Close but do not save query
50. Open Help
51. Use the contents tab to find help on Securing a database. More specifically on Protect a database by adding a database password.
52. Close help.
53. Create a report based upon the Query Problem.
54. Group by living
55. Accept all other defaults.
56. Save Report as Problem_Report
57. Go to design view
58. Add a picture of a zoo (find this from the internet) into the right hand side of the header of the Report.
59. Add page numbers (n of m, alignment right) to the Report.
60. Save the report and return back to Print preview
61. Change the page size to A4 portrait.
62. Close report.
63. Open the relationships window. Use help if you do not know how to do this.
64. Add the animal table.
65. Close the show tables window.
66. Add (clue in the word) the Transaction table.
67. Create a relationship between the animal table (Id_animal) and the Transaction table (Id_animal).
68. Close and save relationship
69. Open relationship window.
70. Edit relationship enforcing referential integrity.
71. Delete Relationship, close window and restart from point 63.
72. If you are unsure of anything try and do it again. Create new forms, add data to the tables. Change the format of the tables. Create new forms and Records. Design new Queries.

1 of 4R Stanyer