Database Design and Maintenance

1. Database Design and Maintenance

2. Review • Proper database design ensures that the data is represented properly, tables are joined correctly, and that data can be easily and accurately retrieved.

3. Review Relational Database Terms • Relation = Table • Tuple = Row • Attribute = Field

4. Review Relational Database Characteristics • No two tuples can be exactly the same. • The order of tuples has no significance. • Each attribute must describe the relation, and have a unique name. • Each attribute can have only one value in a tuple. • An attribute must have the same set of possible values (domain) in all tuples.

5. Review Building a Relational Database • Designing tables • Creating tables • Joining tables • Designing and creating other objects

6. Recognizing Table Types • Master tables – contain data about people and things. • Lookup tables – contain data about groups or categories of information. • Bridge or Transaction tables – contain data about transactions and events. Often used to simplify many-to-many joins.

7. Review Identifying a Primary Key • One or more fields that uniquely identify each record • Primary key field must not be blank in any record.

8. Review Data Dependency • Functional dependency - when any attribute determines the value of another attribute. • Transitive dependency – when a non-key attribute determines the value of another attribute. • Partial dependency – when only one field in a multiple-field primary key determines the value of another attribute.

9. Well-Structured Relations What constitutes a well-structured relation? Intuitively, a well-structured relation contains minimal redundancy and allows users to insert, modify, and delete rows in a table without errors or inconsistencies. EMPLOYEE1 Table

10. Well-Structured Relations EMPLOYEE1 is a well-structured relation. Each row of the table contains data describing one employee, and any modification of an employee’s data (such as a change in salary) is confined to one row in the table. EMPLOYEE1 Table

11. Well-Structured Relations In contrast, EMPLOYEE2 is not a well-structured relation. Notice the redundancy. For example, values for EmpID, Name, Dept, and Salary appear in two separate rows for employees 241 and 290. EMPLOYEE2 Table

12. Data Anomalies Redundancies in a table may result in errors or inconsistencies (called anomalies) when a user attempts to update the data in the table. There are three types of data anomalies: • Insertion anomaly – Suppose we need to add a new employee to EMPLOYEE2. Since the primary key is (EmpID, Course), to insert a row both EmpID and Course must be supplied. This is an anomaly, because the user should be able to enter employee data without supplying Course data. • Deletion anomaly – Suppose that the data for employee 241 are deleted. This will result in losing information that this employee completed a course (SPSS) on 5/30/07. • Modification anomaly – Suppose that employee 290 gets a salary increase. We must record the increase in each of the rows for that employee; otherwise the data will be inconsistent. The problem with relation EMPLOYEE2 is that it contains data about two entities: EMPLOYEE and COURSE. We will use normalization techniques to split EMPLOYEE2 into two relations, one for employee data and one for course data.

13. Normalizing Tables On the previous four slides we presented an intuitive discussion of well-structured relations. We need a more formal procedure for designing them. Normalization is the process of successively reducing relations with anomalies to produce smaller, well-structured relations. Some of the goals are: • Minimize data redundancy, thereby avoiding anomalies and conserving storage space. • Simplify the enforcement of referential integrity constraints. • Make it easier to maintain data (insert, delete, update). • Provide a better design that is an improved representation of the real world and a stronger basis for future growth.

14. 1st NORMAL NOT Home Town City State Chicago, IL Chicago IL First Normal Form • All fields describe the entity represented by the table. • All fields contain simplest possible values. • No multivalued attributes (also called repeating groups).

15. Not 1NF A multivalued attribute Another multivalued attribute

16. 1NF - Eliminating multivalued attributes This new table does have only single-valued attributes and so satisfies 1NF. However, as we saw, the table still has some undesirable properties.

17. 2nd NORMAL NOT Student ID* Course#* Grade Student ID* Course#* Name 12345 CIS 101 B 12345 CIS 101 Higgins Determines Determines Second Normal Form • Table is in First Normal Form. • No partial dependencies exist. (No nonkey fields are determined by only part of a multiple-field primary key, i.e., nonkeys are identified by the whole primary key) *Primary key

18. 3rd NORMAL NOT Course# * Textbook Credits Course# * Textbook Book Price CIS 101 Intro to CIS 3 CIS 101 Intro to CIS $45.99 Determines Determines *Primary key Third Normal Form • Table is in Second Normal Form. • No transitive dependencies (no nonkey fields are determined by other nonkey fields, i.e., nonkeys are identified by only the primary key).

19. Fourth and Fifth Normal Form • Fourth Normal Form – Table is 3NF and has at most one multivalued dependency. Can produce records with many blank values. • Fifth Normal Form – the table cannot be split into further tables.

20. Advanced Field Properties • Lookup fields • Multiple-field primary keys • Indexes

21. Lookup Field • Looks up a value in a joined table. • Specify “Lookup wizard” in Data Type list. • Creates an editable query.

22. Using a Lookup Field Looks up data values from another table (or you can create your own list). Order Customer ID 1008 S349 Customer ID Name S349 Smith,Ben Smith,Ben S349 Orders Looks up Name in Customer table Customers

23. Student ID* Course# * Grade 12345 CIS 101 A 12345 CIS 200 B *Primary key Review Multiple-field Primary Keys • Also called compound keys or composite keys • A value in one field in the key can be repeated in multiple records, but not in all fields of the primary key.

24. Index • Field property that increases search speed. • Speeds up sorting and searching in Datasheet view and all database objects.

25. One to One One to Many Many to Many Rec. 1 Rec. 1 Rec. 2 Rec. 1 Table 1 Rec. 2 Rec. 2 Rec. 1 Rec. 1 Rec. 1 Table 2 Joining Tables • One-to-Many join is the most common. • Other join types: • One-to-One • Many-to-Many

26. Join Types • Inner Join • Left Outer Join • Right Outer Join Join types are discussed in the slides to follow, but also see the discussion at Join-queries.htm.

27. Inner Join The default type - includes records with corresponding values in both tables. LASTNAME FIRSTNAME DEPARTMENT Gray Eric CIS Gray Nadine HRS Cedarman Yvonne HRS Malderer Kevin HRS Nale Rusty CIS HRS HRS HRS Only red records are included in join.  No department for Nale DEPARTMENT CODE NAME CIS Computer Information Systems HRS Human Resources WHS Warehouse 1 WHS No Warehouse employees

28. Left Outer Join Includes all records from One table and corresponding records from Many table Rusty Nale not included – no department assigned. LASTNAME FIRSTNAME DEPARTMENT Gray Eric CIS Gray Nadine HRS Cedarman Yvonne HRS Malderer Kevin HRS Nale Rusty  DEPARTMENT CODE NAME CIS Computer Information Systems HRS Human Resources WHS Warehouse 1

29. How to Change the Join Properties in Query Design View Double-click on the join line that connects the tables. Click the bullet next to the join property desired. For example, in the 240students.mdb database, suppose we want all students and the pets they own. All students should be listed regardless of whether they own a pet or not. This is perfect for a Left Outer Join, because it will select a student from tblStudents (the “One” table) even if there isn’t a match (between ID and OwnerID) in tblPets (the “Many” table).

30. Here’s the SQL and the result of running the query SELECT ID, FirstName, Name, Breed FROM tblStudents LEFT JOIN tblPets ON tblStudents.ID = tblPets.OwnerID • The result of running this query (in the 240students.mdb database) Left-Join.htm.

31. Right Outer Join Includes all records from Many table and corresponding records from One table. Warehouse dept. not included – no employees assigned. LASTNAME FIRSTNAME DEPARTMENT Gray Eric CIS Gray Nadine HRS Cedarman Yvonne HRS Malderer Kevin HRS Nale Rusty  DEPARTMENT CODE NAME CIS Computer Information Systems HRS Human Resources WHS Warehouse 1

32. Join Types A A A B B B Inner Join A B Left Outer Join A is “one” table Right Outer Join B is “many” table U Joins displayed as Venn Diagrams A and B are tables Green striped area is join dynaset.

33. Join Types Selected by double clicking on join line between two tables in Relationship window, and clicking Join Type button.

34. Review Referential Integrity • Referential integrity keeps the relationships between tables valid. • All foreign keys have values that correspond to records in the referenced table • Maintain referential integrity by: • Updating and deleting records when matching records in a joined table are updated and deleted. • Eliminating unmatched and duplicated records in joined tables.

35. Enforcing Referential Integrity • Normalize tables • Set field properties • Use lookup fields • Select specific join type settings • Create and run Find Duplicate and Find Unmatched queries

36. One table Find Duplicate Records Query • Locates records with duplicate values in Many table. Duplicates Many table

37. Find Unmatched Records Query Locates records in Many table not associated with record in One table No match One table Many table

38. Maintaining Databases • Older versions of Access can be converted to newer versions, and vice versa. • Databases can be compacted and repaired using the Tools, Database Utilities command. • Databases can be split into two databases: data and objects (back and front end). • Databases can be documented using the Tools, Analyze, Documenter command. • Database performance can be analyzed using the Tools, Analyze, Performance command.

39. Object Groups • Named set of shortcuts that point to database objects • Grouped objects are listed together in a single window.

40. Modifying Access Environment • Tools, Options command allows changes to the behavior of Access. • Access standard toolbars can be modified.