2 Database Systems Part IV Logical Database Design

2: Database Systems Part IV: Logical Database Design 1

Logical Database Design n n The process of transforming the conceptual data model (i. e. ERDs) into a logical database model (i. e. relational) A logical database model is a design that conforms to the data model for a class of DBMS 2

Review: Data Models n n Hierarchical Network Relational Object-oriented 3

Overview of Logical Design n Represent entities ¡ n Each entity type in an ERD is represented as a relation Represent relationships ¡ Each relationship in the ERD must be represented in the relational model 4

Overview of Logical Design n Normalize relations ¡ n Relations must be refined to avoid unnecessary redundancies and anomalies Merge relations ¡ Redundant relations must be merged 5

Relational Database Model n n n Data is stored in relations (entities) A relation consists of tuples/rows (instances) and attributes Goal: To store data without unnecessary redundancy and to be able to process information easily 6

Components of Relational Database Model n Data structure ¡ n Data manipulation ¡ n Data are organized in the form of tables Powerful data manipulation operations are used Data integrity ¡ Business rules are included to maintain data integrity 7

Keys n Key ¡ n Minimal set of attributes that uniquely identifies each row in a relation Composite key ¡ A key consisting of more than one attribute 8

Keys n Candidate key ¡ ¡ n Any set of attributes that could be chosen as a key of a relation Should be unique and non-redundant Primary key ¡ The candidate key designated for principal use in uniquely identifying rows in a relation 9

Keys n Foreign key ¡ ¡ A set of attributes in one relation that constitutes a key in some other (possibly the same) relation Used to indicate logical links between relations 10

Foreign Key EMPNO -----7839 7698 7782 7566 7654 7499 7844 7900 7521 7902 7369. . . DEPT ENAME DEPTNO -------KING 10 BLAKE 30 CLARK 10 JONES 20 MARTIN 30 ALLEN 30 TURNER 30 JAMES 30 WARD 30 FORD 20 SMITH 20 Foreign key DEPTNO ------10 20 30. . . DNAME -----ACCOUNTING RESEARCH SALES Primary key LOC -------NEW YORK DALLAS CHICAGO 11

Relations n n A named, two-dimensional table of data Consists of a set of named columns and an arbitrary number of unnamed rows Can be expressed as: RELATION (attribute 1, attribute 2, …) Example ¡ STUDENT (ID_Num, Name, Address, Birthday) 12

Properties of Relations n n n Entries in columns are atomic (singlevalued) Entries in columns are from the same domain Each row is unique (no duplicate rows) The sequence of columns is insignificant The sequence of rows is insignificant 13

Anomalies n n Errors or inconsistencies that may result when manipulating data in a table that contains redundancies Types of anomalies: ¡ ¡ ¡ Insertion anomaly Deletion anomaly Modification anomaly 14

Anomalies: An Example EMPLOYEE COURSE EMPID NAME DEPT SALARY COURSE DATE COMPLETED 100 Dana Scully Marketing 42, 000 Planning 5/6/99 100 Dana Scully Marketing 42, 000 Management 5/27/95 140 Fox Mulder Info Systems 39, 000 C++ 12/28/93 110 Walter Skinner Administration 41, 500 Management 5/27/95 110 Walter Skinner Administration 41, 500 Budgeting 6/6/86 190 Alex Krycek Finance 38, 000 Tax Acct. 10/1/93 15

Well-Structured Relations n n Contains a minimum amount of redundancy and allows users to manipulate data without errors Normalization is used to achieve wellstructured relations 16

Normalization n Process of converting a relation to a standard form Used to derive well-structured relations that are free of anomalies when manipulated Often accomplished in stages or normal forms 17

Normal Form n n State of a relation that can be determined by applying dependency rules to that relation Normal Forms: ¡ ¡ ¡ First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF) Boyce-Codd Normal Form (BCNF) Fourth Normal Form (4 NF) Fifth Normal Form (5 NF) 18

Functional Dependency n n The value of an attribute in a relation determines unique value of another (one or more) attributes in the relation Example ¡ n Std_ID -> Name, Bday, Major Left-side attribute (Stud_ID) is called a determinant which determines unique values of other attributes in the relation 19

Partial Functional Dependency n n One or more non-key attributes are functionally dependent on only part of the primary key Example ¡ ¡ EMPLOYEE COURSE (Emp_ID, Name, Dept, Salary, Course, Date_Completed) Functional dependencies: n n ¡ Emp_ID, Course -> Date_Completed Emp_ID -> Name, Dept, Salary Emp_ID is only part of the primary key 20

Transitive Dependency n n A non-key attribute is functionally dependent on one or more other nonkey attributes Example ¡ ¡ SALES (Cust_No, Name, Salesperson, Region) Functional dependencies: n n ¡ Cust_No -> Name, Salesperson, Region Salesperson -> Region Salesperson is not a primary key! 21

Steps in Normalization n First normal form (1 NF) ¡ Repeating groups have been removed Grade Report STUDENT ID STUDENT NAME CAMPUS ADDRESS MAJOR COURSE ID COURSE TITLE INSTRUCTOR NAME INSTRUCTOR LOCATION GRADE 143 Mulder 101 Cervini MIS CS 122 CS 161 DB Sys. O/S Codd Tannenbaum F 227 F 104 B+ A 434 Scully 304 Eliazo Psy 101 Th 141 En 12 Basic Psy Marriage Basic Eng. Freud Pope John Paul Shakespeare Bel 204 B 102 B 202 A A B+ 22

Steps in Normalization n First normal form (1 NF) ¡ Repeating groups have been removed Grade Report STUDENT ID STUDENT NAME CAMPUS ADDRESS MAJOR COURSE ID COURSE TITLE INSTRUCTOR NAME INSTRUCTOR LOCATION GRADE 143 Mulder 101 Cervini MIS CS 122 DB Sys. Codd F 227 B+ 143 Mulder 101 Cervini MIS CS 161 O/S Tannenbaum F 104 A 434 Scully 304 Eliazo Psy 101 Basic Psy Freud Bel 204 A 434 Scully 304 Eliazo Psy Th 141 Marriage Pope John Paul B 102 A 434 Scully 304 Eliazo Psy CS 161 O/S Tannenbaum F 104 B+ 23

Steps in Normalization n Second normal form (2 NF) ¡ ¡ Partial functional dependencies have been removed Assume n n n Student cannot have multiple majors Student cannot repeat a subject Only one teacher is available per course STUDENT (STUDENT ID, STUDENT NAME, CAMPUS ADDRESS, MAJOR) COURSE INSTRUCTOR (COURSE ID, COURSE TITLE, INSTRUCTOR NAME, INSTRUCTOR LOCATION REGISTRATION (STUDENT ID, COURSE ID, GRADE) 24

Steps in Normalization n Third normal form (3 NF) ¡ ¡ Transitive dependencies have been removed Assume n n Instructor teaches only in one classroom Previous assumptions hold STUDENT (STUDENT ID, STUDENT NAME, CAMPUS ADDRESS, MAJOR) COURSE INSTRUCTOR (COURSE ID, COURSE TITLE, INSTRUCTOR NAME) INSTRUCTOR (INSTRUCTOR NAME, INSTRUCTOR LOCATION) REGISTRATION (STUDENT ID, COURSE ID, GRADE) 25

Steps in Normalization n Boyce-Codd normal form (BCNF) ¡ ¡ ¡ Remaining anomalies from functional dependencies are removed In BCNF if and only if every determinant is a candidate key Example: STUDENT ADVISOR (Student ID, Major, Advisor) n n n For each major a student has only one advisor Each advisor advises only one major Each advisor advises several students in one major Each major has several advisors Each student may major in several subjects Student ID Major Advisor 123 Physics Einstein 123 Music Mozart 456 Biology Darwin 789 Physics Bohr 143 Physics Einstein 26

Steps in Normalization n Fourth normal form (4 NF) ¡ Any multivalued dependencies have been removed 27

Steps in Normalization n Fifth normal form (5 NF) ¡ ¡ Any remaining anomalies (join dependencies) have been removed Join dependency - data in relations broken down cannot be recombined to form the original 28

Steps in Normalization n n Domain-Key Normal Form (DK/NF) Proposed by Fagin in 1981 Showed that any relation in DK/NF is automatically in 5 NF, 4 NF, etc. Does not provide methodology for converting to DK/NF 29

Transforming ERDs to Relations n Represent entities ¡ ¡ ¡ Entity = Relation Primary key of entity = Primary key of relation Convert: n n n Multivalued attributes Composite attributes Weak entities 30

Transforming ERDs to Relations n Represent entities ¡ Converting multivalued attributes Employee_ID Name Employee_ID Address Name Address EMPLOYEE has Skill_Name SKILL convert many-to-many Skill_ID 31

Transforming ERDs to Relations n Represent entities ¡ Converting composite attributes Student_ID Address STUDENT Name MI MI Last First 32

Transforming ERDs to Relations n Represent entities ¡ Converting weak entities Employee_ID Name Address Birthdate EMPLOYEE has DEPENDENT Dep_Name Employee_ID Birthdate DEPENDENT Dep_Name 33

Transforming ERDs to Relations n Represent relationships ¡ Depends on: n n Degree of the relationship Cardinalities of the relationship 34

Transforming Relationships n Binary one-to-many relationship ¡ Primary key attributes of the entity on the oneside of the relationship = foreign key in the relation on the many side DName Dept. No DEPT Loc has EName EMP Emp. No Loc Dept. No EName has EMP Emp. No 35

Transforming Relationships n Binary EMP one-to-many relationship EMPNO ENAME 7839 7698 7782 7566 7654 7499 7844 7900 7521 7902 7369. . . KING BLAKE CLARK JONES MARTIN ALLEN TURNER JAMES WARD FORD SMITH DEPTNO 10 30 10 20 30 30 30 20 20 DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO. . . 36 Foreign key Primary key

Transforming Relationships n Binary one-to-one relationship Similar situation as one-to-many relationship Create foreign key on any side of the relationship ¡ ¡ Employee_ID Name Address EMPLOYEE assigned Employee_ID Description COMPUTER Terminal_ID 37

Transforming Relationships n Binary one-to-one relationship ¡ ¡ ¡ Primary key of relation A = foreign key of relation B Primary key of relation B = foreign key of relation A Both situations apply Name Student_ID Address STUDENT has JPG_Image PICTURE Student_ID 38

Transforming Relationships n Binary many-to-many relationship ¡ ¡ ¡ Create a separate relation Primary key is a composite key consisting of the primary key of each of the two entities Occasionally requires a primary key that includes more than just the primary keys of the two relations 39

Transforming Relationships n Binary many-to-many relationship Name Employee_ID example Employee_ID Address Name EMPLOYEE Address EMPLOYEE is given Employee_ID assigned to PROJECT Project_Name PROJECT_ ASSIGNMENT Role Project_ID Date Assigned refers to Project_Name PROJECT Project_ID Role Project_ID 40

Transforming Relationships n EMPLOYEE Binary many-to-many relationship example PROJECT Employee_ID Name Address 40780 Summers, Buffy Sunnydale 100 Looking for Clues 21295 Grissom, Gil Las Vegas 101 Monsters, Inc. 50666 Kent, Clark Smallville Project_ID Project_Name PROJECT_ASSIGNMENT Employee_ID Project_ID Date Assigned Role 21295 100 27/05/2003 Lead Analyst 50666 100 27/05/2003 Jr. Programmer 40780 101 28/12/2003 Project Manager 50666 100 05/01/2004 Sr. Programmer 41

Transforming Relationships n Unary relationships ¡ Unary one-to-many n A recursive foreign key is added to reference the primary key values of the same relation Employee_ID Name Manager_ID EMPLOYEE manages 42

Transforming Relationships n Unary relationships ¡ Unary one-to-many EMPLOYEE_ID 7839 7698 7782 7566 7654 7499 NAME MANAGER_ID KING BLAKE CLARK JONES MARTIN ALLEN 7839 7698 43

Transforming Relationships n Unary relationships ¡ Unary many-to-many n n Item_No. Create a separate relation to represent the many-tomany relationship Primary key = composite key of the two attributes from the same primary key domain Name Item_No. Name Unit_Cost ITEM consists of refers to Item_No. Comp_No. part of Quantity COMPONENT Quantity 44

Transforming Relationships n Unary relationships ¡ Unary many-to-many ITEM Item_No. COMPONENT Item_No. Comp_No. Quantity 006 500 2 006 101 1 006 999 180 500 999 30 101 999 20 Name Unit_Cost 500 Hard Drive 3, 000 006 Pentium 4 PC 101 Keyboard 400 999 Screw 0. 50 27, 000 45

Transforming Relationships n Subtypes ¡ ¡ Create a separate relation for the supertype and for each subtype Supertype relation consists of attributes common to all of the subtypes Relation for each subtype contains primary key and attributes unique to that subtype Primary keys of type and subtypes are from the same domain 46

Transforming Relationships n Subtypes example Name Emp_ID Emp_Type Address Emp_ID Name Address EMPLOYEE Emp_Type = “H” d may be HOURLY SALARIED CONSULTANT Emp_ID “C” “S” HOURLY SALARIED CONSULTANT Emp_ID Hourly_Rate Monthly_Sal Billing_Rate 47

Transforming Relationships n Subtypes example EMPLOYEE Emp_ID Monthly_Sal Emp_ID Name Address Emp_Type 40780 Summers, Buffy Sunnydale S 40780 12, 000 21295 Grissom, Gil Las Vegas S 21295 30, 000 50666 Kent, Clark Smallville H 15249 20, 000 56466 Bristow, Sidney Washington C SALARIED 97872 Bauer, Jack Washington H 15249 Mulder, Fox Washington S Hourly_Rate 50666 500 Emp_ID 97872 750 56466 HOURLY Billing_Rate 3, 500 CONSULTANT 48

Merge Relations: View Integration n n Merge relations that refer to the same entity to remove redundancy View integration problems ¡ ¡ Synonyms Homonyms Transitive Dependencies Subtypes 49

Synonyms n n Two or more attributes may have different names but the same meaning Choose either of the two attribute names and eliminate the other synonym or use a new attribute name to replace both synonyms 50

Homonyms n n A single attribute may have more than one meaning Create new attribute names 51

Transitive Dependencies n n May result when two 3 NF relations are merged to form a single relation Example ¡ n STUDENT 1 (Student ID, Major) STUDENT 2 (Student ID, Advisor) STUDENT (Student ID, Major, Advisor) Note: Assume only one advisor per major Remove transitive dependencies by creating 3 NF relations 52

Subtypes n n n If there are two or more different types of a relation but they contain some characteristics common to all Create supertype-subtype relationships Example PATIENT 1 (Patient No. , Name, Address) PATIENT 2 (Patient No. , Room No. ) PATIENT (Patient No. , Name, Address) INPATIENT (Patient No. , Room No. ) OUTPATIENT (Patient No. , Date Treated) 53