DESIGNING A DATABASE

1. DESIGNING A DATABASE MS ACCESS

2. The Design Process • The design process consists of the following steps: • Determine the purpose of your database • This helps prepare you for the remaining steps. • Find and organize the information required • Gather all of the types of information you might want to record in the database. • Divide the information into tables • Divide your information items into major entities or subjects. Each subject then becomes a table.

3. The Design Process (cont’d) • Turn information items into columns • Decide what information you want to store in each table. Each item becomes a field, and is displayed as a column in the table. • Specify primary keys • Choose each table’s primary key. • Set up the table relationships • Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.

4. The Design Process (cont’d) • Refine your design • Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design, as needed. • Apply the normalization rules • Apply the data normalization rules to see if your tables are structured correctly. Make adjustments to the tables, as needed.

5. Normalisation • Normalisation is the process of breaking up larger relations into many small ones using a set of rules. • The process involves identifying functional dependencies. If they are found to be attributes that are not directly dependent on the PK, these are extracted to form new relations.

6. Normalisation (cont’d) • The process is carried out until all the data in each relation is clearly and uniquely associated with other data in the same table. This reduces redundancy (although it does not eliminate it) and makes the data easier to maintain.

7. Normal Forms • A number of normal forms have been proposed but the first five normal forms have been widely accepted. • The norm forms progress from 1NF, to 2NF, and so on. Data in 2NF implies that it is also in 1NF – i.e., each level of normalisation implies that the previous level has been met. • Other normal forms such as Boyce-Codd (BCNF) which is an extension of 3NF, 4NF and 5NF also exist

8. First Normal Form (1NF) • A relation (table) that contains a repeating group (or multiple entries for a single record) is called an unnormalised relation. • Removing repeating groups is the starting point in the quest to create tables that are as free of problems as possible. Tables without repeating groups are said to be in 1NF • A relation is in 1NF if – and – only if – all domains contain only atomic or single values, i.e., all repeating groups of data are removed.

9. 1NF(cont’d) • In order to convert an unnormalised relation into 1NF, first identify the key attribute(s) involved. Also, identify a key for the whole relation. • Make a separate relation for each group of related attributes • Give each new relation a primary key

10. Second Normal Form (2NF) • A relation is in 2NF if • it is in 1NF • and all non-key attributes are dependent on the whole of the primary key and not part of it. • If an attribute depends on only part of a multi-value key, remove it to a separate table.

11. Third Normal Form (3NF) • A relation is in 3NF if • it is in 2NF • non-key attributes are dependent on the primary key and independent of each other. i.e., non-key attribute must be non-transitively dependent on the primary key. • a non-key attribute is changed, that change should not affect others • Make a separate relation for attributes transitively dependent on the primary key. • Give each new relation a primary key • Original relation will include a foreign key to link to new relation

12. Example • Help At Home is an agency which provides various services such as baby sitting and dog walking. Details of all bookings and a record of the service carried out are kept. Assume only one person(employee) is involved in carrying out each particular service. An hourly rate is charged depending on the type of service required. Note that customers will only be able to make one booking for a particular service per day.

13. Example (cont’d) Book record example 1 Book record example 2

14. Example (cont’d)Relation in un-normalised form

15. Example (cont’d) – 1NF • Identify PK – Cus_no, Booking_date, Service_type • 1NF determinacy diagram

16. Example (cont’d) – 1NF • Relation in 1NF

17. Example (cont’d)- 2NF • 2NF determinacy diagram

18. Example (cont’d) – 2NF • The relation in 2NF

19. Example (cont’d) – 3NF • 3NF determinacy diagram (the service type and customer diagrams remain the same as in 2NF)

20. Example (cont’d) – 3NF • The relation in 3NF (the service type and customer tables remain the same as in 2NF)

21. Developing a database in MS Access • Practical session to be taken in class

22. Query-By-Example (QBE) • QBE is an approach to writing queries that are very visual. • With QBE, users ask their questions by entering column names and other criteria using an on-screen grid, and data appears on the screen in tabular form • In Access 2007 & 2010, queries are created using the Query Wizard or Query Design in the queries groupings under the Create tab • Practical session to be taken in class