Basics of relational databases video lecture

Loading...

Del curso dictado por University of Colorado System

Database Management Essentials

1474 calificaciones

University of Colorado System

Database Management Essentials

1474 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

Relational Data Model and the CREATE TABLE Statement

Now that you have the informational context for database features and environments, you’ll start building! In this module, you’ll learn relational data model terminology, integrity rules, and the CREATE TABLE statement. You’ll apply what you’ve learned in practice and graded problems using a database management system (DBMS), either Oracle or MySQL, creating tables using the SQL CREATE TABLE statement and populating your tables using given SQL INSERT statements.

Basics of relational databases video lecture7:13

Conoce a los instructores

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

Welcome to lesson 1 of module 3 on the Relational Data Model.

I'm gonna start with an important practical question.

What I want you to think about throughout this lesson,

why is the relational data model commercially dominant?

Module 3 is a major change in the course.

Module 2 was informational, providing background and context for other modules.

Module 3 emphasizes concrete skills and knowledge about the relational data model.

You will play your knowledge and skills on practice and

greater problems using the DBMS, either Oracle or MySQL.

This lesson provides your initial background in a relational data model, so

the learning objectives are modest.

I want you to be able to identify components in sample tables

using additional rows that you added.

To become familiar discussing relational databases in several contexts,

you should be able to list alternative terminology, uses the basic concepts.

Relational database systems were originally developed

because of familiarity and simplicity.

Because tables are used to communicate ideas in many fields,

the terminology of tables, rows, and columns is familiar to most users.

A relational database consists of a collection of tables.

Each table has a heading, or definition part, and a body, or content part.

The heading part consists the table name and column names.

For example, a student table may have columns for student number,

student address, city, state, zip, class, major, and cumulative grade point average.

The body shows the rows of the table.

Each row in a student table represents a student enrolled at a university.

A student table for major university may have, say,

30 to 50 columns, and more than 30,000 rows, too many to view at one time.

To understand the table, it's also useful to view some of its rows.

A table listing or

data sheet shows the column names in the first row in the body in the other rows.

The sample student table contains three sample rows,

representing university students.

To facilitate communication, the naming convention for

column names uses a table abbreviation std followed by a descriptive name.

Because column names are often used without identifying the associated tables,

the abbreviation supports easy table association.

Mixed case in a name, highlights the different parts of a column name.

It is not enough to understand each table individually.

To understand a relational database, connections or

relationships among tables must also be understood.

The rows in a table are usually related to rows in other tables matching or

identical values indicate relationships between tables.

So consider the sample enrollment table,

in which each row represents a student enrolled in an offering of a course.

The values in the std number column of the enrollment table match the std

number values in the sample student table.

For example, the first and third rows,

the enrollment table had the same std number of value,

123-45-6789, as the first row of the student table.

Likewise, the values in the offer number column of the enrollment table

match the offer number column in the offering table.

This graphical depiction of matching values,

can clarify the concept of relationships among tables.

In the table listings, the matching values are difficult to identify.

This graphical depiction only works on small sample tables as arrows cross and

become difficult to follow with more rows.

In the graphical depiction, you should see that the first student row

123-45-6789 is related to the first and third enrollment rows.

Likewise, the first offering row, 1234, matches to the first two enrollment rows.

The concept of matching values is crucial in relational databases.

As you will see, relational databases typically contain many tables

even a mono sized database can have 10 to 15 tables.

Large databases can have hundreds of tables.

To extract meaningful information,

it is often necessary to combine multiple tables using matching values.

For example, if I'm matching on student dot student number,

enrollment dot student number, you could combine the student and enrollment tables.

Similarly, by matching an enrollment dot offer number and offering dot offer

number, you could combine the enrollment and offering tables.

As you will see in module 4,

the operation of combining tables on matching values is known as adjoin.

Understanding the connections between tables or

columns in which tables can be combined is crucial for extracting useful information.

You should be aware that other terminology is used besides table, row, and column.

The diversions in terminology is due to the different groups that use databases.

The table-oriented terminology originally appealed to end users.

The set-oriented terminology appeals to academic researchers.

And the record-oriented terminology

really was originally appealed to information systems professionals.

In practice, these terms may be mixed, even more confusing.

For example, in the same sentence you might hear both tables and fields.

You should expect to see a mix of terminology in your career.

Let's wrap up this lesson giving you an initial background

on the relational data model.

Relational databases have been the dominant commercial standard

in the database industry for more than 30 years.

The relational data model is commercially dominate for several reasons.

The idea of tables to organize data is simple and familiar.

The relational data model has strong theoretical foundation from academic and

commercial research and many commercial university and

open source products have implemented the relational model over more than 40 years.

In addition, there's an important commercial standard.

The Structured Query Language, SQL, that has evolved over 30 years.

I encourage you to use small sample tables to solidify your understanding of

the relational model.

You should try adding your own sample rows to the table shown in the notes, and

identify the components,

especially matching values among rows in different tables.

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