Conversion rules video lecture

Loading...

Del curso dictado por University of Colorado System

Database Management Essentials

1470 calificaciones

University of Colorado System

Database Management Essentials

1470 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

Schema Conversion

Modules 6 to 9 covered conceptual data modeling, emphasizing precise usage of ERD notation, analysis of narrative problems, and generation of alternative designs. Modules 10 and 11 cover logical database design, the next step in the database development process. In Module 10, we’ll cover schema conversion, the first step in the logical database design phase. You will learn to convert an ERD into a table design that can be implemented on a relational DBMS.

Conversion rules video lecture6:59

Conoce a los instructores

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to lesson two of module ten on schema conversion.

I'm going to start with two practical questions

about the difference between the ERD notation and the relational data model.

What is the most prominent difference between ERD notation and

the relational data model?

What conversion rules involve this difference?

Lesson two clarifies your understanding of the entity relationship model

in a relational data model.

You will learn rules that convert from an ERD to a table design.

Applying these rules will clarify your understanding of the differences

between the models and help you implement ERDs in relational DBMSes.

The objectives in this lesson involve specific concepts and skills for

the first step of logical database design.

You should be able to apply each rule to an ERD and

use the rules in the specified order.

After getting some practice with the rules, you should clarify your

understanding of the differences between the entity relationship model and

the relational data model.

Conversion from an ERD to a table design is important because of industry practice.

Database design tools support varied notations for ERDs.

It is common practice to use a database design tool as

an aid in developing an ERD.

Because most commercial DBMSes use the relational model,

you must convert an ERD to relational tables to implement your database design.

Even if you use a database design tool to perform normalization,

you should still have the basic understanding of the conversion process.

Understanding the conversion rules improves your understanding of the entity

relationship model, and

helps you avoid the common error of using foreign keys in an ERD.

This lesson covers four conversion rules.

The rules should be applied in the order noted in the diagram.

The entity type rule converts each entity type into a table.

The primary key of the entity type,

if not weak, becomes the primary key of the table.

The attributes of the entity type become columns in a table.

This rule should be applied first, before the relationship rules.

The one-to-many relationship rule converts relationships into foreign keys.

A foreign key should be placed into the child table.

The child table corresponds to the entity type near the crow's foot symbol.

The many-to-many relationship rule converts each many-to-many relationship

into a table with a combined primary key.

The attributes of the many-to-many relationship become columns in a table.

The identification dependency rule is the most subtle.

It adds components to the primary key of a table.

Examples in the remainder of this lesson will give you practice

with the rules on small ERDs.

With some practice, the rules will become easy to use.

Lesson three provides practice on larger ERDs.

Let's apply the entity type and

one-to-many relationship rules to the small ERD.

We first apply the entity type rule, and then the one-to-many relationship rule.

Note that the result shows abbreviated CREATE TABLE statements

to emphasize the major points.

The entity type rule converts the course in offering entity types into the course

in offering tables along with the primary key and other columns of each table.

The one-to-many relationship rule converts the has relationship into

the CourseNo FOREIGN KEY column in the offering table.

Note that the offering table is a child table corresponding to the entity type,

nears the crow's foot symbol.

Because the has relationship is mandatory for

offering entities, the CourseNo column should have a NOT NULL constraint.

This small ERD requires the many-to-many relationship rule.

Note that the result shows abbreviated CREATE TABLE statements

to emphasize the major points.

The entity type rule converts the OFFERING and STUDENT entity types into the offering

in Student tables, along with the primary key and other columns of each table.

The many-to-many relationship rule converts the enrolls in relationship

into the Enrollment table.

The Enrollment table is a combined primary key consisting of student number and

offer number.

Both primary key components are also foreign keys.

Note that the name change, enrolls in to enrollment, is optional.

Typically table names are nouns, so renaming was used.

Not null constraints are not used because a primary key constraint

implies that null values are not allowed.

This small ERD requires the identification dependency rule.

The table design is identical to example two, with the many-to-many relationship,

but the derivation is different.

The table design contains three tables, student, offering, and

enrollment, with the same columns and constraints.

The entity type rule is applied three times for

the student, offering and enrollment tables.

The one-to-many relationship rule is applied twice to create foreign keys,

student number and offer number, in the enrollment table.

The identification dependency rule is applied twice to add student number and

offer number as components of the primary key in the enrollment table.

Now, let's wrap up lesson two about conversion rules.

This lesson demonstrated four basic rules converting an ERD into a table design.

You learned about the actions of each rule along with the order of applying

new rules.

Several small examples were shown to depict usage of the rules.

In the answer to the opening question,

the most prominent difference between the entity relationship model and

the relational model is the named relationships versus foreign keys.

Two conversion rules, the one-to-many relationship rule and

the many-to-many relationship rule involve this essential difference.

The one-to-many relationship rule converts a one-to-many relationship

into a foreign key in a child table.

The many-to-many relationship rule converts a many-to-many relationship

into a table with a combined primary key, consisting of two foreign keys.

Lesson three solidifies your understanding of the conversion

rules with larger examples.

You should also work practice and graded problems to master the conversion process.

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