Basic Concepts of Referential Integrity and Normalization

Microsoft Access Handout 4 and Demo 4: Basic Concepts of Referential Integrity and Normalization Referential Integrity 1. One-to-one relationship EMPLOYEE (Emp_ID, Last_name, First_name, M_initial, DOB) SPOUSE (Emp_ID, S_FN, S_M_ini, S_DOB) 2. One-to-many relationship EMPLOYEE (Emp_ID, Last_name, First_name, M_initial, DOB, Dept_ID) DEPARTMENT (Dept_ID, Dept_name, Dept_location) - a department has many employees - Dept_ID is the foreign key in the table EMPLOYEE (in dashed underline) What is the key in the table DEPARTMENT – Dept_ID – is an attribute in the table EMPLOYEE. This kind of attribute is called a foreign key. A foreign key is a key in the “one” side and an attribute in the “many” side of a one-to-many relationship. In this example, one department (any one department) has many employees, so the “one” side is the DEPARTMENT and the “many” side is the EMPLOYEE. Therefore, the field Dept_ID, which is the key in DEPARTMENT – the “one” side – and an attribute in EMPLOYEE – the “many” side – where it is a foreign key. 3. Many-to-many relationship Students can take many courses, while a course can have many students enrolled. Therefore, the ENROLLMENT relationship is many-to-many. If we simply use the following table: STUDENT (S-ID, L-NAME, F-NAME, COURSE-1, GRADE-1, COURSE-2, GRADE-2, COURSE-3, GRADE-3, COURSE-4, GRADE-4, COURSE-5, GRADE-5) (1) A WRONG relationship: as if the courses are determined by a student taking it, which is not true. (2) If we use the above table, we would have to repeat many courses many times because they are taken by many students – data redundancy. Please also note that GRADE should determined by S-ID and COURSE together – neither one of the two can determine a student’s grade (think about it). Solution: COURSE is an independent entity that needs to have its own table. STUDENT also has its own table. Since they have a many-to-many relationship, a new table (called intersection) must be created: STUDENT (S-ID, L-NAME, F-NAME) COURSE (C-ID, C_TITLE, UNITS) ENROLLMENT (S-ID, C-ID, GRADE) – new table which is the “intersection”. In fact, the “decomposition” of a many-to-many relationship results in the creation of - TWO one-to-many relationships, and - ONE intersection relation (table). In this case, these TWO one-to-many relationships are created: STUDENT and ENROLLMENT (one student, many course enrollments – which is the foreign key here?); COURSE and ENROLLMENT (one course, many enrolled students – which is the foreign key here?). The intersection relation (table) created is: ENROLLMENT (S-ID, C-ID, GRADE) STUDENT COURSE STUDENT ENROLLMENT + ENROLLMENT COURSE A many-to-many relationship (STUDENT-COURSE here) can be broken down to two one-to-many relationships (STUDENT-ENROLLMENT and COURSEENROLLMENT): STUDENT ENROLLMENT COURSE 4. Referential integrity “Many” side: EMPLOYEE (EMPLOYEE-ID, LAST-NAME, FIRST-NAME, DEPT-ID) “One” side: DEPARTMENT (DEPT-ID, DEPT-NAME, DEPT-LOCATION) Referential integrity rules and examples: Referential integrity rules If there are records on the “many” side that refer to a record on the “one” side, the record on the “one” side should not be deleted. If there is not a particular record on the “one” side, no record on the “many” side referencing this record should be added. You are encouraged to… Example 1 (“Department”) Example 2 (“Restaurant”) If there are employees in the “IT” department (in EMPLOYEE table whose DEPT is “IT”), this department (“IT”) should not be deleted from the DEPARTMENT table. If there is not a department called “Production” (in DEPARTMENT table), no employee (in EMPLOYEE table) should have his/her department being “Production”. think about your own… If there are restaurants (“many” side) owned by franchisee number “F001” (“one” side), this franchisee (“F001”) should not be deleted. (Demo with the “Peppy’s Relational” DB) If there is not a franchisee numbered “F014” (“one” side), no restaurant (“many” side) should be owned by franchisee “F014”. example related to DB proj Normalization Purpose: In the design of a database, problems such as update anomaly, deletion anomaly, and insertion anomaly can arise through careless design [See the other posted document “Examples of anomalies”]. These problems can be avoided by following a set of principles called normalization. - Normalization is a process for assigning attributes to entities (assigning fields to tables). - Normalization reduces data redundancy and helps eliminate the data anomalies. Basic Terminology: Normal forms: Normalization works through a series of stages called normal forms. For most business database design purposes, third normal form (3NF) is sufficient. Functional dependency (FD): relationships between attributes of an entity. Example: Student ID  Student Name (S-ID determines S-Name) Stock Code  Stock Name (S-CODE determines S-Name) Product Name  Unit Price (P-NAME determines UNIT-PRICE) The following is a highly simplified or “abridged” description of normalization principles and processes. 1. First normal form (1NF): there should be no multiple values for an attribute. Example: EMPLOYEE (EMPLOYEE-ID, LAST-NAME, FIRST-NAME, CHILDREN) 930-215 Smith Jane Amy, Eric - violates 1NF Problem: (1) it is impossible to list any one of the children without listing all others; (2) it is also impossible to relate them one by one to their respective data such as DOB, etc. Solution: create new attributes – CHILD1, CHILD2, CHILD3, CHILD4 EMPLOYEE (EMPLOYEE-ID, LAST-NAME, FIRST-NAME, CHILD1, CHILD2, CHILD3, CHILD4) 930-215 Smith Jane Amy Eric (blank) (blank) Every attribute now has only one value. [Note: There are better ways of handling this situation, such as creating a DEPENDENT table, with EMPLOYEE-ID as the foreign key (see “one-to-many relationship” under “Referential Integrity” below) for each record in this table. But here we only want to illustrate the conversion of a field from multi-value to single-value] 2. Second normal form (2NF): there should be no attribute depending on only a portion of the primary key – “no partial dependency”. [1. This situation arises ONLY when there is a COMPOSITE KEY] [2. From “1” above, this situation arises ONLY when there is a many-to-many relationship] Example: ENROLLMENT (STUDENT-ID, COURSE-ID, LAST-NAME, FIRST-NAME, COURSE-TITLE, CLASSROOM, GRADE) 930-215 IS302 Smith Jane Info Sys BB2212 A- STUDENT-ID COURSE-ID LAST-NAME FIRST-NAME COURSETITLE CLASSROOM GRADE - violates 2NF, since “BB2212” is determined by“IS302” which is only a portion of the primary key STUDENT-ID, COURSE-ID (the 2 fields TOGETHER form a composite key). Problem: since many students would enroll in one course, the course information would need to be repeated many times – data redundancy. Further notes: there are three functional dependencies (FD) here – Functional Dependency (FD) Left-hand side Right-hand side (Determiner) (Determinee) (1) STUDENT-ID, COURSE-ID  (2) STUDENT-ID  (3) COURSE-ID  GRADE LAST-NAME, FIRST-NAME COURSE-TITLE, CLASSROOM Remarks Legitimate, because GRADE is determined by the PRIMARY KEY, which is STUDENTID, COURSE-ID combined. Violation of 2NF, because STUDENT-ID is NOT a key (only “half” of the key) Violation of 2NF, because COURSE-ID is NOT a key (only “half” of the key) Solution: create separate relations (tables) for STUDENT and COURSE, keeping the original relation ENROLLMENT with the “right-hand side” of unwanted FDs (functional dependencies) removed: R1: R2: R3: ENROLLMENT (STUDENT-ID, COURSE-ID, GRADE) STUDENT (STUDENT-ID, LAST-NAME, FIRST-NAME) COURSE (COURSE-ID, COURSE-TITLE, CLASSROOM) R1: STUDENT-ID COURSE-ID GRADE R2: STUDENT-ID LAST-NAME FIRST-NAME R3: COURSE-ID COURSE-TITLE CLASSROOM Now COURSE-TITLE and CLASSROOM only depend on COURSE-ID in the new COURSE relation, and LAST-NAME and FIRST-NAME only depend on STUDENT-ID – no more “partial dependency”. Now course info only appears once in table COURSE. 3. Third normal form (3NF): if there is a dependency, the determinant must be a primary key – “no transitive dependency”. [A non-key field should not determine another field; or: a field should not depend on a nonkey field] Example: STUDENT (STUDENT-ID, LAST-NAME, FIRST-NAME, MAJOR, HOUR-REQUIRED) HOUR-REQUIRED is dependent on MAJOR, which is NOT a key here. Problem: since many students would be of a certain major, the HOUR-REQUIRED would have to be repeated many times. STUDENT-ID (Left-hand side) LAST-NAME (Right-hand side) FIRST-NAME (R-hand side) MAJOR (R-H-S) (L-H-S) HOUR-REQUIRED (R-H-S) Solution: 1. create a new relation, with all the attributes involved in the unwanted dependency in the new relation, and 2. from the original relation, remove the “right-hand side” (the one that is determined) of the unwanted dependency (the “violation”). STUDENT (STUDENT-ID, LAST-NAME, FIRST-NAME, MAJOR, HOUR-REQUIRED) becomes: R1: STUDENT (STUDENT-ID, LAST-NAME, FIRST-NAME, MAJOR, HOUR-REQUIRED) [last field removed] [Comment: R1 is derived from the original relation, w “right-hand side” of the unwanted dependency – HOUR-REQUIRED – removed] R2: MAJOR (MAJOR, HOUR-REQUIRED) [the fields involved in the dependency are separated to create a new relation] [Comment: the unwanted dependency is separated to form R2, a new relation – new table] R1: STUDENT-ID R2: LAST-NAME FIRST-NAME MAJOR MAJOR HOUR-REQUIRED