Explore
For Enterprise
Join for Free
Log In

artes y humanidades
Negocios
Ciencias de la Computación
Ciencia de Datos
Tecnología de información
salud
Matemáticas y Lógica
Desarrollo Personal
Ciencias Físicas e Ingeniería
Ciencias sociales
Aprendizaje de un idioma
Grados
Certificados

Explorar todo lo que Coursera tiene para ofrecer

Explorar
Buscar
For Enterprise
Inicia Sesión
Únete de forma gratuita

Conversion rules video lecture

Loading...

Database Management Essentials

Sistema Universitario de Colorado

4.6 (1,656 calificaciones) | 89K estudiantes inscritos

Curso 1 de 5 en Data Warehousing for Business Intelligence Programa Especializado

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.

Destrezas que aprenderás

Database (DB) Design, Entity–Relationship (E-R) Model, Database (DBMS), SQL

Revisiones

4.6 (1,656 calificaciones)

5 stars
1,129 ratings
4 stars
400 ratings
3 stars
75 ratings
2 stars
22 ratings
1 star
30 ratings

PG

Dec 29, 2018

This course is having very knowledgeable content. It was having various factors which developed my thinking ability and how to execute the queries. This was absolutely beneficial for me.

FM

Jun 22, 2017

Of all the database courses I have ever done, this one covers the concepts best and exhaustively. I highly recommend it to all beginners and advanced users who want to review the basics.

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

Impartido por:

Michael Mannino
Associate Professor

Transcripción

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

Coursera
Acerca de
Liderazgo
Empleo
Catálogo
Certificados
Certificados MasterTrack™
Grados
For Enterprise
For Government

Comunidad
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
Youtube
Blog de Tecnología

Más
Términos
Privacidad
Ayuda
Accesibilidad
Prensa
Contacto
Directorio
Afiliados