dATABase management systems

1. FORMS AND REPORTS dATABase management systems

2. Forms and Reports • Forms provide an interface to a database. • They simplify data entry and record navigation. • They can execute user specified operations (macros). • They can open other tables, queries, forms or reports. • Much more... • Reports provide a mechanism for extracting information from data. • Designed to be printable. • Can be based on either tables or queries. • Can include diagrams.

3. NOTE • The topic of Forms and Reports could quite easily fill an entire course in and of themselves. • Due to time limitations, we will only be giving them brief consideration.

4. Types of Forms • Single Form • Form for creating/modifying/viewing individual records. • Split Form • Contains both a Form (top half of page), and the table/query datasheet it is based on (bottom half of page). • Selecting a record/field in the table displays that record/field in the form for editing. • Multiple Items • Form that shows all records at once, in a list. • Pivot Chart • Form for presenting data as a chart. • In the interest of time, we will only look at single forms and multiple item forms.

5. Creating a Form We want to make a forms for entering/reviewing customer data. Click the type of form in the Forms group in the Create Tab. Select the table/query to base the form on from the Tables pane.

6. Creating a Single Form All fields appear, with validation rules enforced. Any One-to-Many relationships will be added as Sub-tables You can navigate to different records using the record navigation controls.

7. Creating a Multiple Item Form Record Selectors Multiple Item Forms look a lot like tables at first glance, but they can be modified significantly.

8. Editing a Form • Right click the Form tab and click Design View. • You can move, resize and remove fields in this view. • Return to Form view to see how it looks. You can also add colour, pictures, formatting and text boxes to make the form look however you want.

9. Example: Multiple Item Form • When you know what you are doing in forms, you can build in substantial automation. • This form can: • Calculate a price from the time spent on a task. • Filter by month, year and task type. • Open a report of the information. • Etc. • While you are not required to do these things in this course, you should know that they are possible.

10. Reports • Reports are easily generated by selecting a table or query, and clicking Report in the Create tab. • This will generate a basic report, in a similar way to that of multi item lists.

11. Reports Wizard (1) • For more control over Report Design, you can click Report Wizard. • First select the fields you wish to display (you can add fields from multiple tables or queries) • Then select how Access should display records. • In the image, DVD rentals are being automatically grouped by customer in the selected view.

12. Reports Wizard (2) • You can then select any extra grouping levels, sort data and adjust the layout of the report.

13. Report Wizard (3) • You then select a theme for the report (similar to Word themes), and give the report a name. • Choose a name for your report, and click finish to preview.

14. Revision

15. Example 1 1 ∞ ∞ 1 1 ∞ Partial Dependency Transitive Dependency Repeating Groups 0NF 1NF 2NF 3NF

16. Example 2: DVD Shop Repeating Groups

17. 1NF Split DVDShop into 2 tables Problem: Customer can only rent a DVD once. Partial Dependency: DVDTitle and DVDRentalCategory only partially dependent on primary key.

18. 2NF: Create DVD table Transitive dependency: DVDRentalPrice dependent on DVDRentalCategory, not the primary key.

19. 3NF: Create RentalCategory table

20. 1 ∞ 1 ∞ ∞ Partial Dependency Repeating Groups 1 Transitive Dependency 0NF 1NF 2NF 3NF

21. Exams – To Clarify • MCQ Exams • 15 questions per section • All lecture slides • Practical Exams • 15 minutes for each section • Practicals and the lectures they referenced

22. Practical Exam Tips • Do each practical again a few times until you are VERY comfortable with them. • Practical exam questions derive directly from the set practicals. • Play around once you’re comfortable • Make things to test your knowledge • This is particularly relevant to the Access exam, as very few people are comfortable with it. • If you get stuck, consult google / other students for help.