Information System Analysis- Data Design

Transcript
Data Design: Entity-Relationship Diagrams

Welcome to this Video Learning Session.

When you complete the session, you will be able to create entity-relationship diagrams, also called ERDs.

In this session, you’ll learn: what are ERDs and why are they important, how to create ERDs, and how to use a CASE tool to create ERDs.

An ERD is a graphical model that shows how entities are joined together to form a relational database.

You’ll learn more about relational databases in other learning sessions.

Let’s view some examples of relationships between entities.

The relationship between Zip Code and Area is one to one.

One ZIP code identifies one specific area.

The relationship between Team and player is one to many.

One team can have many players, but a player only plays for one team at a time.

The relationship between Person and Project is many to many.

One person can be assigned to many projects, and each project can have many people assigned to it.

You can draw an ERD freehand, or use software tools.

In these examples, we’ll show entities as rectangles, and relationships as diamond shapes.

In a one to one relationship, one office manager heads one office, one vehicle ID number is assigned to one vehicle, and one social security number is assigned to one person.

In a one to many relationship, one individual can own many automobiles, one customer can place many orders, and one faculty advisor can advise many students.

In a many to many relationship, one entity can match many instances of the other entity.

For example, one student can enroll in many classes – and one class can have many students; one passenger can reserve a seat on many flights – and one flight can have many passengers; one order can list many products – and one product can be included in many different orders.

In a many to many relationship, the entities must be joined by another entity, called an associative entity, which identifies and describes that particular combination.

For example, a registration stores information about a specific student in a specific class, a reservation stores information about a specific passenger on a specific flight, and an order-line stores information about a specific product in a specific order.

Suppose you have a sales system where one sales rep serves many customers, one customer can place many orders, and one warehouse stores many products.

These are one to many relationships.

However, order and product have a many to many relationship, because one order can have many products, and one product can appear in many orders.

So, we need an associative entity called order line that identifies a specific product in a specific order, and stores information about that particular combination.

Here’s how the Sales System might look in Microsoft Access.

Notice the associative entity, ORDER-LINE, that joins the ORDER entity and the PRODUCT entity, and stores information, such as the quantity ordered.

In Part 3, you’ll learn how to use a CASE tool to create ERDs.

A CASE tool, like Visible Analyst, can display the entities and the relationships between them.

This simple example has two entities DOCTOR and PATIENT.

We clicked the entity symbol on the menu bar, placed it on the page, and labeled it DOCTOR.

Next, we created the PATIENT entity.

The relationship between DOCTOR and PATIENT is many to many.

One Doctor can see many patients, and one patient can visit many doctors.

So we need to join Doctor and Patient using an associative entity called APPOINTMENT.

To create the new entity, click the associative entity symbol on the Menu bar, place it on the page, and label it APPOINTMENT.

Notice that the associative entity symbol has a diamond shape inside the rectangle.

Each record in the APPOINTMENT table identifies a specific doctor, a specific patient, and stores related information such as date, time, and scheduled procedures.

However, no additional data, such as the Doctor’s name, or the Patient’s name, address, or insurance information needs to be entered in the APPOINTMENT table – because it already is stored in the DOCTOR and PATIENT tables.

The last step is to add lines that join the entities.

Now we have a relational database that stores data in three separate tables.

The associative entity called Appointment joins the DOCTOR and PATIENT tables in a many to many relationship.

A relational design improves data quality and consistency, because data is entered and updated in only one place.

You’ll learn more about data design in other sessions.

In this session, you learned: What ERDs are and why they are important, how to create ERDs, and how to use a CASE tool to create ERDs.

For more information, you can refer to the textbook or your Student Study Tool.

Now it’s Your Turn to apply your skills and check your work.

For background information, tasks to complete, and sample answers, go to the Management Information Systems CourseMate at www.cengagebrain.com. Select this text and navigate to the Video Learning Sessions.

Thanks for attending this Video Learning Session.