Database management systems

1. RELATIONSHIPS AND NORMALISATION Database management systems

2. SQL? • Structured Query Language (SQL) is language used to create, update and query Databases. • It is a 4th Generation language, and is designed to resemble English as much as possible. • Access provides an interface (in design view) to simplify the creation of Databases, but behind the scenes, most functions are converted to an SQL string. • SQL is used widely in countless databases throughout the world for a number of reasons, including: • It is standardised (SQL in one product is the same as in another) • SQL implementations are currently VERY fast. • It is easy to read, and thus debug (fix). • SQL is used in Enterprise Databases, Websites, Desktop Applications, Scientific Databases etc.

3. Simple Queries • A simple query can be used to • choose one column to display • choose multiple specific columns to display • limit the data in one column with “Criteria” • limit the data in multiple specific columns

4. Using the Query Wizard for a simple Query (1) Click on the Query Wizard. Select Simple Query Wizard (Default) and click OK.

5. Using the Query Wizard for a simple Query (2) First choose the table(s) Then choose the fields from the tables and click the arrow to select them The fields in Selected Fields will be the fields shown in the query. You can have fields from multiple tables, although you should ensure that all tables used are related, so query knows which data to display

6. Using the Query Wizard for a simple Query (3) Give the Query a name. You can either open the query to see the results, or modify it in Query Design. In Query Design you can sort queries based on specific fields, or filter them by Criteria.

7. Using the Query Wizard for a simple Query (4) Query Design View displays the table(s) the query is based on in the top half of the window, and the fields to display in the bottom half. You can add fields by dragging them from the table into a column in the lower portion of the window (* adds all fields from the table). You can hide fields from display by unselecting Show.

8. Using the Query Wizard for a simple Query (5) To see the query results, right-click the queries tab, and select Datasheet View (same as tables). To get back into Design View, right-click the tab and select Design View.

9. Creating a QueryIn Design View (1) • Clicking Query Design creates a new query in design view. • The “Show Table” window is automatically displayed.

10. Creating a Query In Design View (2) Select the table(s) you want to query You can also add fields from existing queries through the Queries Tab Click on Add, which will add the Query to the Query window. Then click on Close.

11. Creating a Query In Design View (3) Added tables and queries will be displayed here. If you need to open the Show Tables window again, right click the Table area in the Query Design View and select Show Tables.

12. Creating a simple Query in Design View (4)The Query Design Controls Switch between Design and Datasheet View Show Table Window, and modify query columns • Choose the type of Query to create. • NOTE: We will focus on Select Queries exclusively (i.e. select which fields to display). • Other Types include: • Make Table – Output query results to a new Table • Append – Add record(s) to a table • Update – Update (change) fields in a Table • Delete – Delete record(s) from a Table.

13. Creating a simple Query in Design View (5)The Show/Hide Totals Control Clicking (Show) Totals allows you specify calculations based on all records in a field. • Total Types include: • Group By – No totals calculated. Show all entries. • Sum – Add all records together (numerical). • Avg – Find the average of all records (numerical). • Min – Find the smallest record. • Max – Find the largest record. • Count – Count the number of records.

14. Totals Example • Assume we have a table of Students Student Numbers and the degree they are doing. • If we Count the number of Student Numbers, and Group them by Degree, we can find the number of students doing each degree. • If we had counted the number of degrees, grouped by student number, it would have told us the number of degrees each student has, which would have never been higher than 1 (why?).

15. Simple Select Query Example Hide Course Field in Datasheet View. Only show records with CompSci in the course field. Sort Student Number ins Ascending order This query shows the Student Numbers (in Ascending order) of those students doing CompSci.

16. Simple Select Query Example - Results This query is equivalent to the SQL statement (created behind the scenes): SELECT Courses.StudentNumber FROM Courses WHERE (((Courses.Course)="CompSci")) ORDER BY Courses.StudentNumber; This can be viewed by selecting SQL View, instead of Datasheet of Design View

17. Saving Queries • Once you are happy your Query is performing correctly, you can save it by right-clicking the Queries Tab, and selecting Save. • After saving, your query is shown under the table(s) you based in on in the Left-Hand Tables pane. • If You query was based on multiple tables, it will be shown under each of them.

18. Returning to Edit a Query Right-click on the query in the All Tables Pane, and choose Design View.

19. Advanced Filter/Sort • When viewing a Table/Query in Datasheet view, you can apply an advanced filter sort from the Home Tab. • Advanced Filter/Sorts are similar to queries, but significantly less powerful. • You can only sort, filter by criteria, and change the order of fields. • You CANNOT • Calculate totals on columns • Hide columns • Show new tables

20. Advanced Filter Sort - Results • NOTE: • The filter is saved as a separate “View” of the data. After naming and saving the filter, you can open it at any time from the All Tables Pane, in the same way you would a Query.

21. Choosing Selection Criteriain Filters and Queries • We usually use like and not like to compare text • * and ? are wildcard characters • * can stand in for any number of characters (e.g. M* indicates any text starting with M, *p indicates any text ending in p, etc) • ? can stand in for one character (e.g. M? would select any two-letter words starting with M such as Me, My, Mm etc. P?n would select any three-letter words starting with P and ending in n such as Pin, Pen, Pun etc) • Use =, <>, >, <, >=, <= to compare numeric values (these can also be used with text in certain ways – see next slide) • Use Is Null and Is Not Null to select fields that are empty or non-empty

22. Possible criteria

23. Multiple Criteria • You can add more than one criteria per Field by using the AND and OR keywords. • AND • Two separate criteria joined by an AND must both be true for a record to be shown. • E.g. Like “M*” AND Like “*s” will match Machines, but not Mother or Snowflakes. It is the same as Like “M*s”. • OR • Two separate criteria joined by an OR will result in a record being displayed if it matches either of the criteria. • E.g. Like “M*” OR Like “*s” will match Machines, Motherand Snowflakes, but not Popcorn.

24. Criteria: Using “And” Implicitly First Selection Criterion Second Selection Criterion This selects all students whose surnames do not start with Bill AND and who are taking CompSci– the AND is implicit, i.e. you don’t actually have to write AND.

25. Why Wouldn’t This Work? Because no surname can start with BOTH “m” AND “S”!

26. Criteria: Using the “Or” lines These are Equivalent We solve the problem of wanting surnames starting with both m and S by using the OR line.

27. Criteria: Using Not Like Use not likeif you want to exclude certain records from a query. For example Course Not like C* gives all courses whose names don’t start with C

28. Criteria: Using Is Null and Is Not Null You can use Is Null and Is Not Nullas your criterion to identify records where the field has been left blank (is null) or contains some (any) value (is not null)

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

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

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

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

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

34. 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)

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

36. Adding Query Parameters - Results

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