Functional dependencies video lecture

Loading...

Del curso dictado por University of Colorado System

Database Management Essentials

1578 calificaciones

University of Colorado System

Database Management Essentials

1578 calificaciones

Curso 1 de 5 en SpecializationData Warehousing for Business Intelligence

Database Management Essentials provides the foundation you need for a career in database development, data warehousing, or business intelligence, as well as for the entire Data Warehousing for Business Intelligence specialization. In this course, you will create relational databases, write SQL statements to extract information to satisfy business reporting requests, create entity relationship diagrams (ERDs) to design databases, and analyze table designs for excessive redundancy. As you develop these skills, you will use either Oracle or MySQL to execute SQL statements and a database diagramming tool such as the ER Assistant or Visual Paradigm to create ERDs. We’ve designed this course to ensure a common foundation for specialization learners. Everyone taking the course can jump right in with writing SQL statements in Oracle or MySQL.

De la lección

Normalization Concepts and Practice

Module 11 covers normalization, the second part of the logical database design process. Normalization provides tools to remove unwanted redundancy in a table design. You’ll discover the motivation for normalization, constraints to reason about unwanted redundancy, and rules that detect excessive redundancy in a table design. You’ll practice integrating and applying normalization techniques in the final lesson of this course.

Functional dependencies video lecture7:58

Conoce a los instructores

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to Lesson Two of Module 11 on Normalization Concepts and Practice.

I'm going to start with an important practical question,

about falsifying functional dependencies.

What is the practical usage of falsifying functional dependencies in sample tables?

Lesson Two extends your background about normalization concepts and

practice from the motivation covered in Lesson One.

The most important part of the normalization process is to identify or

assert functional dependencies.

This lesson covers important concepts about functional dependencies

including specification and falsification sample rows.

The objectives in this lesson involve obtaining a solid grounding

about asserting functional dependencies for a table.

You should be able to define functional dependency.

Explain the analogy to a unique constraint.

And falsify functional dependencies in sample rows.

A functional dependency is a value neutral constraint similar to primary key and

foreign key constraints.

A value neutral constraint involves a comparison of columns.

A primary key can take any value

as long as it does not match the primary key in an existing row.

A foreign key constraint requires that the value of a column in one table

matches the value of a primary key in another table.

Constraints must be asserted so that understanding business requirements and

rules is necessary.

Constraints are normative, specifying conditions that should normally be true.

As you will see,

specification of functional dependencies drives the normalization process.

Specification of functional dependencies can not be automated

because an understanding of business requirements and rules is necessary.

A functional dependency or FD for short is a constraint about two or

more columns of the table.

X determines Y, X arrow Y.

If there is just at most one value of Y, for every value of X.

The word function comes from mathematics where a function gives one value.

For example, student number determines student city.

In the university database if there's at most one city value for

every student number.

In a functional dependency, X and Y are one or more columns.

The X columns are known as the determinant or left hand side.

LHS for short.

The Y columns are known as the right hand sides, RHS for sort.

You could also think about functional dependencies as unique constraints.

By stating X determines Y, if X and Y were placed together to table without other

columns, X should have unique values.

For example if OfferNo and OffYear are placed in a table without other columns,

an OfferNo that determines OffYear that OfferNo has unique values in a table.

Likewise, if OfferNo, StdNo, EnrGrade, are placed in a table

without other columns, the combination of OfferNo and StdNo is unique.

If the FD OfferNo, StdNo determines EnrGrade.

Columns that are unique in a context with other columns

should be placed together in a table.

Essentially, functional dependencies indicate columns

that should be placed in the same table.

You will see that this simple idea is the essence of normal forms

presented in Lesson Three.

A simple organization of functional dependencies is to list them,

group by left hand side or determinate.

This list contains four FD groups with determinate StdNo,

OfferNo, CourseNo and a combination of StdNo and OfferNo.

As you will see an FD list,

organized by left hand side, facilitates the normalization process.

As an alternative organization,

a functional dependency diagram compactly displays functional dependencies.

In a FD diagram, line height and

position visually show columns sharing the same determinate.

By examining the position in height of lines,

you can see that the combination of StdNo and OfferNo determines the role grade.

It's easier to see that OfferNo alone determines off term off year and CourseNo.

With a large number of functional dependencies,

functional dependency diagrams can be difficult to draw and understand.

Thus, functional dependency lists are preferred to functional dependency

diagrams even though functional dependency lists can be long for

large collections of functional dependencies.

A functional dependency cannot be proven to exist by examining the rows of a table.

However, you can falsify a functional dependency

by examining sample rows of a table.

Falsifying a functional dependency means proving that the functional dependency is

not true.

Practice with falsifying functional dependencies

can help you understand the meaning of the functional dependency concept and

communicate with users about questionable functional dependencies.

A falsification pattern, or example,

involves two rows with the same X value but a different Y value.

To demonstrate the falsification of potential functional dependencies,

let's use the sample table with StdNo as the left hand side.

I will falsify a few functional dependencies.

And leave it as an exercise for you to falsify the remaining

functional dependencies with StdNo as the determinate.

StdNo determines OfferNo is falsified by two pairs of rows.

One and two, three and four.

Rows one and two have the same StdNo value, S1.

But a different OfferNo value, O1 and O2.

StdNo determines EnrGrade is falsified by one pair of row, three and four.

Rows three and four have the same StdNo value,

S2, but different EnrGrade values, 3.1 and 3.4.

StdNo determines student class is not falsified by any pair of rows.

Note that rows two and three do not falsify the functional dependency

because a StdNo values, S1 and S2 are different in the rows.

Now let's wrap up Lesson Two about functional dependencies.

Functional dependencies are important constraints

to remove unwanted redundancy in a table.

A functional dependency is essentially a constraint about the weakness of a column

if used in a table alone with other columns.

You learned about the definition of functional dependency.

Specification of functional dependencies and

falsification of functional dependencies in sample tables.

In answer to the opening question,

falsification of functional dependencies has an important practical usage.

Falsifying FDs using sample rows

can help communicate with users about business rules.

A user could be shown sample rows that falsify an FD.

If a user confirms that the example is valid, the FD can be eliminated.

Falsification is typically used for functional dependencies

in which a database designer is unsure and needs user feedback.

After this background on functional dependencies, you're ready to

use functional dependencies in rules known as normal forms,

to remove unwanted redundancy.

Lesson Three provides details about an important numeral form

based on a simple pattern of functional dependencies to remove redundancy.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play