dATABase management systems

1. COMPLEX QUERIES, NORMALISATION, FORMS AND REPORTS dATABase management systems

2. Complex Query (1) Create a Query in Design View, and add the tables you wish to work with (they should be related).

3. Complex Query (2) • Then just treat it as a normal query. • If it doesn’t work, ensure that the relationships are defined (black joining lines).

4. Complex Query (3) • This Query is equivalent to the SQL statement: i.e. Access makes your life very easy...

5. Modifying Join Type in a Query Right clicking a relationship in the query Design View allows you to modify how the two tables are joined. REMEMBER: You can modify join properties in the Relationship Manager, by clicking Join Type in the Edit Relationship dialog.

6. Types of Join • There are 3 types of join provided by ACCESS Complete Referential Integrity Partial Referential Integrity

7. Using Aliases • You can give a column a different “Alias” or title using a Semicolon. • E.g. Student: StudentNumberrenames the column StudentNumber to Student. • This can be useful as a means to give columns more meaningful names (especially calculated columns)

8. Adding Query Parameters • In Query Design View, you can add “Parameters” which must be entered by the user on opening the Query. • You can then use these parameters in your Query, using the [ and ] characters.

9. Adding Query Parameters - Results

10. Improving Query Performance • When defining a field in a table, choose the smallest data type appropriate for your data • When creating a query, add only those fields you really need.

11. Database Normalisation • Normalization is a systematic way of ensuring that a database structure is: • suitable for general-purpose querying • free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity. • Introduced by Edgar F. Codd (the inventor of the relational model) in 1970. • A Database is often considered normalised once it is in Third Normal Form (3NF).

12. Normalisation continued • Un-normalised databases (0NF): • Cannot be queried efficiently, if at all. • May result in inconsistent data when one field is updated, but another isn’t (Update Anomaly) • When an employees email address is stored in multiple tables/records, and only some are properly updated. • May prevent a record from being inserted (Insertion Anomaly) • i.e. If License-plate numbers were used as Primary Keys in an employee table, but some employees did not have cars. • May result in a loss of important data when deleting records (Deletion Anomaly). • i.e. If Purchasing Records were stored within the employee table, and we deleted an employee record, we would lose the record of the purchase as well.

13. First Normal Form (1NF) • A database is in 1NF if it contains no repeating groups. • E.g. • When one field can contain more than one value, resulting in the need for duplicate fields. • How many fields do you add? Repeating Fields

14. Converting to 1NF • Create 2 separate tables. 1 ∞ • NOTE: Employee Skills Table needs a Composite key because: • An employee might have multiple skills • A skill might be known by multiple employees • Thus neither Employee ID nor Skill ID is guaranteed to be unique over multiple records. • This can be avoided by introducing an artificial primary key, such as an Autonumber.

15. Second Normal Form (2NF) • A Table is in 2NF when: • It is in 1NF • It has no attributes which are only partially dependent on the primary key. • Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF. Description is only Partially Dependent on the Composite Primary Key. It is dependent on Skill ID, but unrelated to Employee ID. 1 ∞

16. Converting to 2NF NOTE: This is the best way to deal with Many-To-Many relationships. Just create an intermediate table with 1-To-Many relationships on either side. • Create another table. 1 ∞ 1 ∞ 1 ∞ Now Skill Description is fully dependent on the primary key.

17. Third Normal Form (3NF) • A Table is in 3NF if: • It is in 2NF • It has no Transitive Dependencies • i.e. No attribute is dependent on another attribute which is not itself a primary key. • NOTE: because the table is already in 2NF, no field can be partially dependent on a primary key, so it is either fully dependent on the primary key, or not dependent on it. 1 ∞ 1 ∞ Department Name is dependent on Department ID, which is not the primary key of the Employee Table.

18. Converting to 3NF • Surprise! Create another table. 1 ∞ 1 ∞ 1 ∞ 1 ∞ ∞ 1 Department Name is dependent on the primary key.

19. Action Replay 1 ∞ ∞ 1 1 ∞ Partial Dependency Transitive Dependency Repeating Groups 0NF 1NF 2NF 3NF

20. Worth Noting • A table which has repeating groups is un-normalised, regardless of whether it has partial or transitive dependencies. (why?) • You do not have to start with un-normalised tables. Create them in 3rd normal form (try paper – it helps). • An un-normalised or partially-normalised database is NOT wrong – sometimes there are good reasons not to normalise (e.g. avoiding unnecessary complication)

21. 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.

22. 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.

23. 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.

24. 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.

25. 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.

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

27. 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.

28. 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.

29. 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.

30. 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.

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

32. 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.