杜克大学

用MySQL管理大数据

1781 评分

课程 4（共 5 门，Specialization 从 Excel 到 MySQL：商业分析技术）

本课程致力于介绍如何在商业分析中运用关系数据库。你将会学到关系数据库是如何工作的，以及如何运用实体-联系图来展示数据结构。这将会帮助你理解商业环境下数据需要怎样被收集，而且如果你的工作涉及到完成新数据的归类，此课程也会帮助你

从本节课中

Understanding Relational Databases

Welcome to week 1! This week you will learn how relational databases are organized, and practice making and interpreting Entity Relationship (ER) diagrams and relational schemas that describe the structure of data stored in a database. By the end of the week, you will be able to:<ul><li>Describe the fundamental principles of relational database design <li>Interpret Entity Relationship (ER) diagrams and Entity Relationship (ER) schemas, and</li><li>Create your own ER diagrams and relational schemas using a software tool called ERDPlus that you will use to aid your query-writing later in the course.</li></ul>This week’s exercises are donated from a well-known Database Systems textbook, and will help you deepen and strengthen your understanding of how relational databases are organized. This deeper understanding will help you navigate complicated business databases, and allow you to write more efficient queries. At the conclusion of the week, you will test your understanding of database design principles by completing the Week 1 graded quiz. To get started, please begin with the video “Problems with Having a Lot of Data Used by a Lot of People.” As always, if you have any questions, post them to the Discussions. I hope you enjoy this week's materials!

How to Make Relational Schemas using ERDPlus4:43

与讲师见面

Daniel Egger
Executive in Residence and Director, Center for Quantitative Modeling
Pratt School of Engineering, Duke University
Jana Schaich Borg
Assistant Research Professor
Social Science Research Institute

>> In this video, Jana Schaich will show you how to use his tool,

ERDPlus to make a relational schema.

If you need a reminder about how the general structure of the ERDPlus

website is organized, please refer to the last video that

has a demonstration about how to use ERDPlus to make ER diagrams.

And now, here's an ad.

>> This video demonstrates how to use ERDPlus to create a relational scheme out.

Once you're logged into your ERDPlus account, go to the Diagram section.

Before we demonstrate how to create a sample relational scheme up, first, let's

take a look at the previously created ER Diagram that we will use as the basis for

the sample relational scheme up.

Keep this ER Diagram in mind as we now go and create a relational schema.

Click on New, select Relational Schema,

click on the name of the New Relational Schema and

now you are in a drawing surface for creating relational skills.

We start by creating the first stable.

This stable is named location.

First column of this table is LID.

The next column is L main.

We will declare a primary key of this stable by clicking on

the Primary Key button and we will choose LID as the primary key.

The next table is called Project.

Let's start creating columns, PID.

Pete name.

People project would also have a foreign key.

LID, referencing the primary key LID off the location table.

To create a foreign key, click on Connect,

click on the table that provides the foreign key.

Hold the left-click down and drag to the table that gets the foreign key.

This creates a foreign key.

Now let's create a primary key of the project table by clicking on

the primary key button and choosing PID.

In ERDPlus, you can change the order of columns easily.

Let's move the LID column one spot up.

Now, lets move the LID column one spot down.

The next table we will create is the table employee.

The columns are EID and e-name.

Primary key is the EID column.

The final table of this relational schema is a bridge table works on resulting

the many-to-many relationship between employee and project entities.

Click on the table, name it works on and

now we have add two foreign keys to this table.

One coming from table employee and one coming from the table project.

Click on Connect and creates two foreign keys.

In addition, this table has another column, number of hours.

This column indicates how many hours each employee worked, on each project.

We will not declare the primary key of this table.

The primary key is a composite key composed of EID and PID.

Our sample relational schema is now created.

It is also already saved in the diagram section of your ERDPlus account.

In addition, by clicking on the Diagram and clicking on

Export Diagram File, you can save the same diagram on to your computer.

Also, you can save the image of the diagram by clicking on Export Image.

Saving the image allows you later to copy and

paste it in to PowerPoint, Word or any other application.

This concludes the small demonstration of how to create sample relational schema and

the resulting relational database using ERDPlus.