7_FD.pdf - Functional Dependencies Normalization for...

This preview shows page 1 - 17 out of 78 pages.

Functional Dependencies & Normalization for Relational DBs Chapter 7
Image of page 1

Subscribe to view the full document.

Contents 2 1 Introduction 2 Functional dependencies 3 Normalization
Image of page 2
Contents 3 1 Introduction 2 Functional dependencies 3 Normalization
Image of page 3

Subscribe to view the full document.

Miniworld APPLICATION PROGRAM DESIGN TRANSACTION IMPLEMENTATION Data requirements Conceptual schema Database schema Functional requirements Internal schema High-level transaction specification LOGICAL DESIGN (DATA MODEL MAPPING) REQUIREMENTS - COLLECTION & ANALYSIS CONCEPTUAL DESIGN PHYSICAL DESIGN FUNCTIONAL ANALYSIS Application program DBMS–independent DBMS–specific Application Design Database Design Overview of Database Design Process 4
Image of page 4
Introduction 5 Each relation schema consists of a number of attributes and the relational database schema consists of a number of relation schemas Attributes are grouped to form a relation schema Need some formal measure of why one grouping of attributes into a relation schema may be better than another
Image of page 5

Subscribe to view the full document.

Introduction 6 “Goodness” measures: Redundant information in tuples Update anomalies: modification, deletion, insertion Reducing the NULL values in tuples Disallowing the possibility of generating spurious tuples
Image of page 6
Introduction 7 Redundant information in tuples: the attribute values pertaining to a particular department (DNUMBER, DNAME, DMGRSSN) are repeated for every employee who works for that department.
Image of page 7

Subscribe to view the full document.

Introduction Update anomalies : modification, deletion, insertion Modification As the manager of a dept. changes we have to update many values according to employees working for that dept. Easy to make the DB inconsistent 8
Image of page 8
Introduction Deletion: if Borg James E. leaves, we delete his tuple and lose the existing of dept. 1, the name of dept. 1, and who is the manager of dept. 1
Image of page 9

Subscribe to view the full document.

Introduction Insertion: How can we create a department before any employees are assigned to it ??
Image of page 10
Introduction 11 Reducing the NULL values in tuples Employees not assigned to any dept.: waste the storage space Other difficulties: aggregation operations (e.g., COUNT, SUM) and joins
Image of page 11

Subscribe to view the full document.

Introduction 12 Disallowing the possibility of generating spurious tuples EMP_PROJ (SSN, PNumber, Hours, EName, PName, PLocation) EMP_LOCS (EName, PLocation) EMP_PROJ1 (SSN, PNumber, Hours, PName, PLocation) Generation of invalid and spurious data during JOINS: PLocation is the attribute that relates EMP_LOCS and EMP_PROJ1, and PLocation is neither a primary key nor a foreign key in either EMP_LOCS or EMP_PROJ1
Image of page 12
Introduction 13 Disallowing the possibility of generating spurious tuples
Image of page 13

Subscribe to view the full document.

Introduction 14 Disallowing the possibility of generating spurious tuples
Image of page 14
Introduction 15 “Goodness” measures: Redundant information in tuples Update anomalies: modification, deletion, insertion Reducing the NULL values in tuples Disallowing the possibility of generating spurious tuples Normalization
Image of page 15

Subscribe to view the full document.

Introduction 16 Normalization helps DB designers determine the best relation schemas
Image of page 16
Image of page 17
  • Spring '17
  • Relational model, Database normalization

{[ snackBarMessage ]}

Get FREE access by uploading your study materials

Upload your study materials now and get free access to over 25 million documents.

Upload now for FREE access Or pay now for instant access
Christopher Reinemann
"Before using Course Hero my grade was at 78%. By the end of the semester my grade was at 90%. I could not have done it without all the class material I found."
— Christopher R., University of Rhode Island '15, Course Hero Intern

Ask a question for free

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern