Relational Database - Gonzaga University

Relational Database - Gonzaga University

Logical Database Design and the Relational Database Professor Chen School of Business Administration Gonzaga University Spokane, WA 99258 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-1 6-1 What is a Relation? A relation is a named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows. Not all table are relations Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-2 6-2 Fig. 6-1: EMPLOYEE1 Relation with sample data EMPLOYEE1 Emp_ID Name Dept_Name Salary

100 140 110 190 150 Margaret Simpson Allen Beeton Chris Lucero Lorenzo Davis Susan Martin Marketing Accounting Info. System Finance Marketing 48,000 52,000 43,000 55,000 42,000 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-3 6-3 Relational Definitions Relation Every relation has a unique name. Every attribute value is atomic (singled-value) (Fig. 6-1) Every row is unique.

Attributes in tables have unique names. The order of the columns is irrelevant. The order of the rows is irrelevant. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-4 6-4 Fig. 6-2: Eliminating multi-valued attributes (a) Table with repeating groups (Un-Normalized) Emp_ID Name Dept_Name Salary Course_ Title Date_ Completed 100 Margaret Simpson Marketing 140 110 Allen Beeton Chris Lucero Accounting

Info. System 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 190 150 Lorenzo Davis Susan Martin Finance Marketing 48,000 SPSS Surveys 52,000 Tax Acc 43,000 SPSS C++ 55,000 42,000 SPSS Java Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems 6/16/199X 8/12/199X TM 6-5 6-5 Emp_ID

Name Dept_Name 100 Margaret Simpson Marketing 140 110 Allen Beeton Chris Lucero Accounting Info. System 190 150 Lorenzo Davis Susan Martin Finance Marketing Emp_ID Name Dept_Name 100 100

140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Salary Course_ Date_ Title Completed 48,000 SPSS 6/19/199X Surveys 10/7/199X 52,000 Tax Acc 12/8/199X 43,000 SPSS 1/12/199X

C++ 4/22/199X 55,000 42,000 SPSS 6/16/199X Java 8/12/199X Salary Course_ Title 48,000 SPSS 48,000 Surveys 52,000 Tax Acc 43,000 SPSS 43,000 C++ 55,000 42,000 SPSS 42,000 Java Date_ Completed 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/16/199X 8/12/199X TM 6-6 6-6 Fig. 6-2: Eliminating multi-valued attributes (b) EMPLOYEE2 Relation (Normalized) EMPLOYEE2 Emp_ID Name

Dept_Name Salary Course_ Title Date_ Completed 100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing

48,000 48,000 52,000 43,000 43,000 55,000 42,000 42,000 SPSS Surveys Tax Acc SPSS C++ 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X SPSS Java 6/16/199X 8/12/199X Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-7 6-7 Relational Keys and Structures Primary Key Composite Key Foreign Key

One-to-Many Relationship Many-to-Many Relationship Intersection Data Candidate Key Surrogate Key Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-8 6-8 Fig. 6-3: Schema for four relations (Pine Valley Furniture) Graphical Representation Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-9 6-9 Fig. 6-3: Schema for four relations (Pine Valley Furniture) Graphical and Text Representations CUSTOMER(Customer_ID, Customer_name,Address, City,State,Zip) ORDER(Order_ID, Order_Date,Customer_ID) ORDER_LINE(Order_ID, Product_ID,Quantity) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems PRODUCT(Product_ID, Product_Description, Product_Finish,Unit_Price, On_Hand)

TM 6-10 6-10 Integrity Constraints Domain Constraints Allowable values for an attribute. A domain definition contains: domain name, data type, size, meaning, and allowable values/range (if applicable). Entity Integrity No primary key attribute may be null. Operational Constraints Business rules (see Chapter 4) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-11 6-11 Fig. 6-5: Referential integrity constraints (Pine Valley Furniture) pk fk pk ck/pk fk fk pk Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-12 6-12 Referential Integrity (Addition and Deletion) CUSTOMER Customer_ID Customer_Name Customer_Address 123 345 489 789 John Smith Allen Stone Mary Jones Kent Watson S. 34 Freya, Spokane 45 A St. Cheney E. 21 Trent, Spokane 23 Y Ave. Spokane PK ORDER FK Order_ID

Order_Date Customer_ID OR-002 OR-003 OR-004 OR-004 9/21/1998 9/30/1998 10/1/1998 10/2/1998 123 345 123 489 642 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-13 6-13 Referential Integrity (Summary) Addition - you cant add (insert) an ORDER record if Customer_ID (FK) does not exist (or does not match) a Customer_ID (PK) in the CUSTOMER table. Deletion - you cant delete a CUSTOMER record if there is (are) related Customer_ID in the ORDER table. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-14 6-14 Integrity Constraints Referential Integrity A rule that states that either each foreign key value must match a primary key value in the other relation or else the foreign key value must be null. For example: Delete Rules Restrict Cascade Set-to-Null Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-15 6-15 Referential Integrity (Conclusion) CUSTOMER pk Customer_ID Customer_Name Customer_Address 123 345 489 789

John Smith Allen Stone Mary Jones Kent Watson S. 34 Freya, Spokane 45 A St. Cheney E. 21 Trent, Spokane 23 Y Ave. Spokane com m ORDER on f ield pk fk Order_ID Order_Date Customer_ID OR-002 OR-003 OR-004 OR-004 9/21/1998 9/30/1998

10/1/1998 10/2/1998 123 345 123 489 642 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-16 6-16 Referential Integrity (Conclusion) TABLE-1 (CUSTOMER ) TABLE- (ORDER) RULES: 1. You cant add a record to TABLE- (or the table with fk) unless there is a corresponding record in TABLE-1 (or the table with pk). 2. You cant delete a record in TABLE-1 (or the table with pk) if there is a record in TABLE- (or the table with fk). Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-17 6-17 Referential Integrity (Conclusion)

STUDENT pk SID SLNAME 100 Miller 105 Connoly 9188 FACULTY pk FID 1 5 SPIN 8891 fk FID 1 3 fk FLNAME Cox

Brown LOCATION pk LOCID BLDG_CODE 45 CR 57 LIB Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems LOCID 53 57 ROOM 101 222 FPIN 1181 9899 CAPACITY 150 1

TM 6-18 6-18 Referential Integrity (Conclusion) TABLE-1 (FACULTY) TABLE- (STUDENT) RULES: 1. You cant add a record to TABLE- (or the table with fk, e.g., STUDENT) unless there is a corresponding record in TABLE-1 (or the table with pk). 2. You cant delete a record in TABLE-1 (or the table with pk, e.g., FACULTY) if there is a record in TABLE- (or the table with fk). Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-19 6-19 Order of Entering Data and Referential Integrity STUDENT pk SID SLNAME 100 Miller 105 Connoly

9188 FACULTY pk FID 1 5 SPIN 8891 fk FID 1 3 LOCATION FPIN 1181 9899 FACULTY fk FLNAME Cox Brown

LOCATION pk LOCID BLDG_CODE 45 CR 57 LIB LOCID 53 57 ROOM 101 222 CAPACITY 150 1 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems STUDENT TM 6-20 6-20 STUDENT pk SID SLNAME

100 Miller 105 Connoly 9188 FACULTY pk FID 1 5 fk FID 1 3 fk FLNAME Cox Brown LOCATION pk LOCID BLDG_CODE

45 CR 57 LIB TABLE-1 (FACULTY) RULES: SPIN 8891 LOCID 53 57 ROOM 101 222 FPIN 1181 9899 CAPACITY 150 1

TABLE(STUDENT) 1. You cant add a record to TABLE- (or the table with fk, e.g., STUDENT) unless there is a corresponding record in TABLE-1 (or the table with pk). 2. You cant delete a record in TABLE-1 (or the table with pk, e.g., FACULTY) if there is a record in TABLE- (or the table with fk). Order of entering data into the database: LOCATION FACULTY STUDENT Order of deleting data from the database:STUDENT FACULTY LOCATION Well-Structured Relations A well-structured relation contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies. The following anomalies should be removed for a well-structured relation: Insertion Anomaly Deletion Anomaly Modification Anomaly Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-22 6-22 Is EMPLOYEE2 a WellStructured relation? EMPLOYEE2 Emp_ID Name Dept_Name Salary Course_ Title

Date_ Completed 100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing 48,000 48,000 52,000 43,000 43,000

55,000 42,000 42,000 SPSS Surveys Tax Acc SPSS C++ 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X SPSS Java 6/16/199X 8/12/199X Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-23 6-23 Is EMPLOYEE2 a WellStructured relation? NO! WHY? Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-24 6-24

Insertion Anomaly: Inserting a new row, the user must supply values for both Emp_ID (PK) and Course_Title (CK and FK). This is an (insertion) anomaly, since the user should be able to enter employee data without knowing (supplying) course (title) data. EMPLOYEE2 Emp_ID Name Dept_Name 100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance

Marketing Marketing Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Salary Course_ Title 48,000 SPSS 48,000 Surveys 52,000 Tax Acc 43,000 SPSS 43,000 C++ 55,000 42,000 SPSS 42,000 Java Date_ Completed 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/16/199X 8/12/199X TM 6-25 6-25 Deletion Anomaly: Deleting the employee number 140, it results in losing not only the employees information but also the course had an offering that completed on that date. EMPLOYEE2 Emp_ID Name

Dept_Name 100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Salary Course_ Title 48,000 SPSS 48,000 Surveys

52,000 Tax Acc 43,000 SPSS 43,000 C++ 55,000 42,000 SPSS 42,000 Java Date_ Completed 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/16/199X 8/12/199X TM 6-26 6-26 Modification Anomaly: If the employee number 100 gets a salary increase, we must record the increase in each of the rows for that employee (two occurences); otherwise the data will be inconsistent. EMPLOYEE2 Emp_ID Name Dept_Name 100 100 140 110 110 190

150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Salary Course_ Title 48,000 SPSS 48,000 Surveys 52,000 Tax Acc 43,000 SPSS 43,000 C++ 55,000 42,000 SPSS 42,000 Java Date_ Completed

6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/16/199X 8/12/199X TM 6-27 6-27 Fig. 6-7: Normalized Relations from EMPLOYEE2 EMPLOYEE2 Emp_ID Name Dept_Name 100 100 140 110 110 190 150 150 Margaret Simpson Marketing Margaret Simpson Marketing Allen Beeton Accounting Chris Lucero Info. System Chris Lucero Info. System Lorenzo Davis

Finance Susan Martin Marketing Susan Martin Marketing Salary Course_ Date_ Title Completed 48,000 48,000 52,000 43,000 43,000 55,000 42,000 42,000 SPSS Surveys Tax Acc SPSS C++ 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X SPSS Java 6/16/199X 8/12/199X

EMPLOYEE1 Emp_ID 100 140 110 190 150 Name Margaret Simpson Allen Beet Chris Lucero Lorenzo Davis Sususan Martin EMP_COURSE Dept_Name Salary Marketing Accounting Info. System Finance Marketing 48,000 52,000 43,000 55,000 42,000 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Emp_ID 100 100 140 110

110 150 150 Course_ Title SPSS Surveys Tax Acc SPSS C++ SPSS Java Date_ Completed 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/19/199X 8/12/199X TM 6-28 6-28 Normalization Normalization is the process of decomposing relations with anomalies to produce smaller, wellstructured and stable relations. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-29 6-29 Data Normalization Normalization is a formal process for deciding which attributes should be grouped together in a relation (see Fig.6-7 next). Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-30 6-30 Fig. 6-7: Normalized Relations from EMPLOYEE2 EMPLOYEE2 Emp_ID Name Dept_Name 100 100 140 110 110 190 150 150 Margaret Simpson Marketing Margaret Simpson Marketing

Allen Beeton Accounting Chris Lucero Info. System Chris Lucero Info. System Lorenzo Davis Finance Susan Martin Marketing Susan Martin Marketing Salary Course_ Date_ Title Completed 48,000 48,000 52,000 43,000 43,000 55,000 42,000 42,000 SPSS Surveys Tax Acc SPSS C++ 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X

SPSS Java 6/16/199X 8/12/199X EMPLOYEE1 Emp_ID 100 140 110 190 150 Name Margaret Simpson Allen Beet Chris Lucero Lorenzo Davis Sususan Martin EMP_COURSE Emp_ID Course_ Date_ Title Completed 100 100 140 110 110 150 150

SPSS Surveys Tax Acc SPSS C++ SPSS Java Dept_Name Salary Marketing Accounting Info. System Finance Marketing 48,000 52,000 43,000 55,000 42,000 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/19/199X 8/12/199X TM 6-31 6-31 Functional Dependencies and Keys Functional Dependency: The value of one

attribute (the determinant) determines the value of another attribute. Candidate Key:An attribute, or combination of attributes, that uniquely identifies a row in a relation. A candidate key is always a determinant, while a determinant may or may not be a candidate key. Each non-key field is functionally dependent on every candidate key. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-32 6-32 Fig. 6-22: Steps in normalization Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-33 6-33 First Normal Form Normal form is a state of a relation that results from applying simple rules regarding functional dependencies (or relationships between attributes) to that relation. No multi-valued attributes. Every attribute value is atomic. Fig. 6-2a, b. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-34 6-34

Emp_ID Name Dept_Name 100 Margaret Simpson Marketing 140 110 Allen Beeton Chris Lucero Accounting Info. System 190 150 Lorenzo Davis Susan Martin Finance Marketing Emp_ID Name Dept_Name

100 100 140 110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Salary Course_ Date_ Title Completed 48,000 SPSS 6/19/199X Surveys 10/7/199X 52,000 Tax Acc 12/8/199X

43,000 SPSS 1/12/199X C++ 4/22/199X 55,000 42,000 SPSS 6/16/199X Java 8/12/199X Salary Course_ Title 48,000 SPSS 48,000 Surveys 52,000 Tax Acc 43,000 SPSS 43,000 C++ 55,000 42,000 SPSS 42,000 Java Date_ Completed 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X 6/16/199X 8/12/199X TM 6-35 6-35 Second Normal Form 1NF and every non-key attribute is fully functionally dependent on the primary key. Every non-key attribute must be defined by the

entire key (either a single PK or a CK), not by only part of the key. No partial functional dependencies. Fig. 6-1,6-23a Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-36 6-36 Fig. 6-23: A Process of 1NF to 2NF (EMPLOYEE2 - - 1NF) (b) Functional Dependencies in EMPLOYEE2 Emp_ID Course_Title Name Dept_Name Salary Date_Completed Partial Depend. Emp_ID Name Dept_Name Salary Course_ Title Date_ Completed 100 100 140

110 110 190 150 150 Margaret Simpson Margaret Simpson Allen Beeton Chris Lucero Chris Lucero Lorenzo Davis Susan Martin Susan Martin Marketing Marketing Accounting Info. System Info. System Finance Marketing Marketing 48,000 48,000 52,000 43,000 43,000 55,000 42,000 42,000 SPSS Surveys Tax Acc

SPSS C++ 6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X SPSS Java 6/16/199X 8/12/199X Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-37 6-37 Fig. 6-23: A Process of 1NF to 2NF EMPLOYEE1 (a) Functional dependencies in EMPLOYEE1 (2NF) Emp_ID Name Dept_Name Salary Emp_ID Name Dept_Name Salary 100

140 110 190 150 Margaret Simpson Allen Beeton Chris Lucero Lorenzo Davis Susan Martin Marketing Accounting Info. System Finance Marketing 48,000 52,000 43,000 55,000 42,000 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-38 6-38 EMPLOYEE2 Emp_ID Fig. 6-23: Summary on Normalization Course_Title Name

Dept_Name Salary Date_Completed Partial Depend. EMPLOYEE1 Emp_ID Name Dept_Name Salary EMP_COURSE 2NF 3NF ? Emp_ID Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems Course_Title Date_Completed TM 6-39 6-39 Fig. 6-23: Summary on Normalization EMPLOYEE2 (1NF) Emp_ID Name Dept_Name 100 100 140 110 110

190 150 150 Margaret Simpson Marketing Margaret Simpson Marketing Allen Beeton Accounting Chris Lucero Info. System Chris Lucero Info. System Lorenzo Davis Finance Susan Martin Marketing Susan Martin Marketing Salary Course_ Title 48,000 48,000 52,000 43,000 43,000 55,000 42,000 42,000 SPSS Surveys Tax Acc SPSS C++

6/19/199X 10/7/199X 12/8/199X 1/12/199X 4/22/199X SPSS Java 6/16/199X 8/12/199X EMPLOYEE1 (3NF) Emp_ID 100 140 110 190 150 Name Margaret Simpson Allen Beet Chris Lucero Lorenzo Davis Sususan Martin Date_ Completed EMP_COURSE (3NF) Emp_ID Course_ Date_ Title Completed

100 100 140 110 110 150 150 SPSS 6/19/199X Surveys 10/7/199X Tax Acc 12/8/199X SPSS 1/12/199X C++ 4/22/199X SPSS 6/19/199X Java 8/12/199X Dept_Name Salary Marketing Accounting Info. System Finance Marketing 48,000 52,000 43,000 55,000 42,000 Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-40 6-40 Third Normal Form 2NF and no transitive dependencies (functional dependency between non-key attributes.) Fig. 6-23, 6-24, 6-25. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-41 6-41 Fig. 6-24: Relation with transitive dependency (a) SALES relation with simple data Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-42 6-42 What Anomalies might be in SALES relation? Insertion anomaly ? Deletion anomaly ? Modification anomaly ? Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-43 6-43 Fig. 6-24: (b) Transitive dependency in SALES relation

Cust_ID ---> Name, Salesperson, Region and Salesperson ---> Region therefore ... Cust_ID ---> Region Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-44 6-44 Fig. 6-25: Removing a transitive dependency (a) Decomposing the SALES relation Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-45 6-45 Fig. 6-25: (b) Relations in 3NF Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-46 6-46 Fig. 6-26: Another example with transitive dependencies Snum Origin Destination Distance 409 618 723

824 629 353 Seattle Chicago Boston Denver Minneapolis Seattle Denver Dallas Atlanta Las Angeles St. Louis Denver 1,537 1,058 1,214 1,150 587 1,537 Insertion anomaly? Deletion anomaly? Modification anomaly? Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-47 6-47 Break ! Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-48 6-48 Fig. 6-26: Another example with transitive dependencies SHIPMENT Snum Origin Destination Distance Snum Origin 409 618 723 824 629 353 Seattle Chicago Boston Denver Minneapolis Seattle Destination Denver Dallas Atlanta Las Angeles St. Louis Denver Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

Origin Destination Distance Seattle Chicago Boston Denver Minneapolis Seattle Denver Dallas Atlanta Las Angeles St. Louis Denver 1,537 1,058 1,214 1,150 587 1,537 TM 6-49 6-49 Fig. 6-22: Steps in normalization Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems

TM 6-50 6-50 Merging Relations (View Integration) In a project development process, there may be a number of separate E-R diagrams and user views created and some of them may be redundant. Therefore, some relations should be merged to remove the redundancy. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-51 6-51 Merging Relations (View Integration - A example) EMPLOYEE1( Employee_ID, Name, Address, Phone) EMPLOYEE2(Employee_ID, Name, Address, Jobcode, No_Years) EMPLOYEE(Employee_ID, Name, Address,Phone, Jobcode, No_Years) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-52 6-52 Merging Relations (Problems on View Integration) Synonyms: Different names, same meaning. Homonyms: Same name, different meanings. Transitive Dependencies: e.g. (Stu ID,

Major) (Stu ID, Advisor). Supertype/Subtype. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-53 6-53 Problems on View Integration Synonyms: Different names, same meaning. STUDENT1(Student_ID, Name) STUDENT2(Matriculation_No,Name, Address) STUDENT(SSN, Name, Address) Homonyms: Same name, different meanings. STUDENT1(Student_ID, Name,Address) STUDENT2(Student_ID,Name, Phone_No,Address) STUDENT2(Student_ID,Name, Phone_No, Campus_Address, Permanent_Address) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-54 6-54 Problems on View Integration Synonyms: Different names, same meaning. STUDENT1(Student_ID, Name) STUDENT2(Matriculation_No,Name, Address) STUDENT(SSN, Name, Address) Homonyms: Same name, different meanings. STUDENT1(Student_ID, Name,Address) STUDENT2(Student_ID,Name, Phone_No,Address) STUDENT2(Student_ID,Name, Phone_No, Campus_Address, Permanent_Address)

Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-55 6-55 Problems on View Integration Transitive Dependencies STUDENT1(Student_ID, Major) STUDENT2(Student_ID, Advisor) the result is ... STUDENT(Student_ID, Major, Advisor) and after removing transitive dependency STUDENT(Student_ID, Major) STUDENT(Major, Advisor) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-56 6-56 Problems on View Integration Supertype/Subtype PATIENT1(Patient_ID, Name, Address) PATIENT2(Patient_ID, Room_No) PATIENT(Patient_ID, Name, Address) INPATIENT(Patient_ID, Room_No) OUTPATIENT(Patient_ID, Date_Treated) Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-57 6-57 After learning one of most important database concepts and theories...

WHATS NEXT ? Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-58 6-58 Relational Definitions Tuple Attribute View Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-59 6-59 Relational Concepts Relational Algebra Relational Calculus Relational Operations SELECT PROJECT JOIN Equijoin - Join field appears twice. Natural Join - Join field appears once. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-60 6-60 Logical Database Design You have just learned and completed one of the most important

concepts and theories, integrity constraints and normalization, for developing a quality of database. Copyright Addison Wesley Longman, Inc. & Dr. Chen, Business Database Systems TM 6-61 6-61

Recently Viewed Presentations

  • Principals' Decisions Impact Student Achievement

    Principals' Decisions Impact Student Achievement

    This qualitative inquiry used interview data collected from five principals of south central Louisiana who were selected based on their School Performance Grades (SPG) as issued by the State Department of Education growing from C, D or F to A...
  • Diapositiva 1 - World Bank

    Diapositiva 1 - World Bank

    Supervisory StYle: IMF 2012 opinion. 60. The core supervisory process at the BdEis strong and is supported by an experienced cadre of inspectors, …Regulatory capital and loan-loss provisioning requirements for real estate exposures also have been tightened and further guidance...
  • Into the Wild Chapter Questions - mrsjankowski.com

    Into the Wild Chapter Questions - mrsjankowski.com

    Into the Wild Activities. I can: RN 3.1 Analyze author's choices in structure/ story development. RN 2.1 Cite textual evidence to support my analysis. SL.2.1 Initiate and participate effectively in a range of collaborative discussions on texts, and issues,
  • The heart and science of medicine. Enhancing the

    The heart and science of medicine. Enhancing the

    Chris Holmes MD, PhD. Mary . Cushman MD, MSc . Charles . MacLean MD Neil Zakai . MD, MSc. ... Included internal jugular vein & portal vein thromboses, as well as superficial thrombophlebitis treated with full anticoagulation. Prior history of...
  • Sénèque et la religion romaine

    Sénèque et la religion romaine

    La nature ne livre pas non plus ses vérités sacrées d'un seul coup. Nous nous croyons des initiés ; elle nous arrête dans le vestibule. La connaissance de ses secrets ne s'ouvre pas indistinctement, ni à tout le monde.
  • Menyusun SOP yang Efektif - WordPress.com

    Menyusun SOP yang Efektif - WordPress.com

    Terkait dengan Keputusan, maka dalam penyusunan SOP yang efektif harus merujuk pada struktur organisasi. Kelemahannya adalah tidak fleksible, berubah organisasi, berubah SOP. Terkait dengan fleksibilitas, maka penyusunan SOP yang efektif harus disusun dengan memperhatikan fungsi organisasi atau proses bisnis yang...
  • MQEC Spring Update 2018 Western MASS www.mqec.org dburke@mqec.org

    MQEC Spring Update 2018 Western MASS www.mqec.org [email protected]

    MQEC's Legislative Priorities (cont) Ensure application of inflation to all elements in the fundingformula, particularly special education. Successfully defeat efforts to fund charter schools, tax credits,vouchers, and education savings accounts with public funds at the expense of funds that would...
  • Compounds and Molecules

    Compounds and Molecules

    Some polymers are MAN-MADE: PLASTICS CHAPTER FOUR TEST STUDY THIS: MIXTURE or COMPOUND NETWORK structure or MOLECULE group IONIC bond or COVALENT bond Will a substance conduct electricity? Explain. Chapter vocabulary words ION location on the Periodic Table (notes) 4.1...