Metadata Overiew

Week 4 - Chapter 4

Entity Relationship (ER) Modeling

Week 4 Overview

Week 4 Chapter 3 & 4

Relational Models and ER Modeling

To be successful you should follow these tasks weekly

read the weekly overview
read the lecture notes posted in weekly modules
participate in the weekly discussion forums
review the supplement documents and example code posted in the weekly modules
attempt the weekly practice exercise
complete the weekly assignment
complete the weekly quiz

Relational Database Modeling Overview

Week 4 reinforces the material that we have been studying in the Data Models and specifically the Relational Database Model. In Database Management, Data Modeling is a way of creating a conceptual model of a physical database. This is similar to how an architect creates a blue print for a room or a building that has to be constructed.

This week, we continue to study and practice creating Entity Relationship Diagrams. Our primary tool for creating these diagrams will be diagrams.net. Students may also use alternative tools for ER Modeling. I placed a link to Gen My Model in Canvas. Gen My Model is a free online service that provides the tools to make ER Diagrams using a Web Browser. You can make ER Diagrams from a variety of platforms including IOS, Windows and Linux. All ER Diagrams must be submitted in a PDF file format

This week, I will provide a business problem that we will use ER Modeling to design a database solution. The solution will be a streamlined prototype version of a small functional database design solution to respond to the problem. We will walk through a problem in class. Walking through this process in class should provide you with a framework of handling designing ER Diagrams individually or collaboratively in a team.

In this course we will primarily study the Chen Diagramming. The class will has already received an additional assignment on Crows Foot Diagramming. That will be the limit to our exposure to Crows Foot Diagramming. Since you have been exposed to Crows Foot Diagramming, you may submit any of our remaining diagramming projects in the course in either diagramming formats, Chen or Crows Foot.

This week we explore a different modeling method referred to as the crow’s feet model in the industry.

Within cardinality there is a concept known as existence dependence. This means that an entity instance exists in database only when it is associated with another related entity occurrence. An example of this is that a spouse entry in a spouse entity of employee database cannot exist without the primary employee instance first being entered

Existence independence is when an entity can exist apart from one or more related entities, sometimes such an entity is referred to as a strong or regular entity. This type of entity is

Chapter 3 was a very comprehensive chapter that contained a significant number of concepts and skills that has to be learned. We will continue going over these skills and concepts this week. We will continue exploring relational database modeling in more depth. Once again this week, we will continue fortifying our understanding of writing sing table queries in SQL. We will review correct queries for last week’s information requests on the Agent Table. Over the next several weeks we will begin to add additional modifiers to the single table SELECT query statements to add additional criteria to the selection process when the query is executing. This week we will not have a single query assignment. We will focus on creating ER diagrams and translating them to relational schemas and Meta Data. Your registration into Microsoft Imagine Premium has completed. If you have not received your email welcoming you to Microsoft Imagine Premium, check your junk folder. If it is not in your junk folder, notify me with your CCP email address included in the message. This week, I will continue illustrating the use of Microsoft Visio to create ER Diagrams. I will show the Crows Foot Models this week. Additionally, I will also demonstrate Microsoft Word as a tool to make less sophisticated Chen models if are working from home and for some reason we are not able to get Visio working at home.

Relationships can have degrees. The degree of a relationship indicates number of entities or participants associated with a relationship.

There can be a unary relationship. This is when the association or relationship is maintained within single entity. In lecture it was discussed that if there is an employee entity that a supervisor can supervise other employees in the employee entity.

There can be Binary Relationships. This is when two entities are associated. In lectured it was discussed that there can be a relationship between an employee and the department that the employee is in. The example that a customer can have many accounts was also discussed. This is the relationship between the entity customer and the entity account.

There can be Ternary Relationship. This is when three or more entities are associated. In lecture it was discussed that there can be a relationship between a Project Entity, a Supplier Entity, and a Supplies Entity. In practice however, it is preferred to have many binary relationships

The textbook discusses Associative (Composite) Entities. These are more commonly known as bridge entities or mapping tables. These are used to implement M:N relationships between two entities. Typically the primary key of his entity is composed of the primary keys of each of the
entities to be connected. This is a composite key. Typically, it also contains additional descriptive attributes that play no role in connective process between the two entities.

In conclusion if referential integrity is being enforced in a database system, a primary key row cannot be removed from a table in referential key rows from another tables are associated with the primary key record. Additionally, a foreign key row cannot be inserted into a foreign key table is the primary key row is not inserted into the primary key table first. In the context of our discussions, a customer cannot be deleted from the cleaner’s database while the customer has material not picked up. A person accepting articles of clothing from a customer cannot input the each article of clothing for the customer, if the customer’s information has not been entered first. Design is of little value unless it delivers all specified query and reporting requirements. Some design and implementation problems do not yield “clean” solutions.

Week 4

Relational Database Models

Once again, to be successful you should follow these tasks weekly

read the weekly overview
read the lecture notes posted in weekly modules
participate in the weekly discussion forums
review the supplement documents and example code posted in the weekly modules
attempt the weekly practice exercise
complete the weekly assignment
complete the weekly quiz

Important Reminder

The correct process for responding to discussions is to first post your own reply to the discussion topic. Other participant’s replies will not be visible until you post your first individual reply. Second, you must post a brief reply to at least two other students’ replies. This along with a relevant individual reply will give you full credit in discussion assignments

Assignment Extensions

Highlights This Week

This week continues the pivotal process of understanding the relational model and diagramming databases using ER diagrams. We continue the effort of reinforcing very important concepts in Relational Database Management. We will continue to use the Entity Relationship Modeling Language to illustrate designs for relational databases. In order to create these designs we must understand. Last week we introduced the Chen diagramming model that primarily uses rectangles, diamonds, ovals and lines to convey the design of a relational database. Within the context of that we reviewed some key concepts and terms

Required attribute: must have a value

Optional attribute: may be left empty

Domain of an Attribute: is a set of possible values for that attribute

Unique Identifiers: one or more attributes that uniquely identify each entity instance, this translates to a primary key of a table
Composite Identifier: primary key composed of more than one attribute

Derived Attribute: value may be calculated from other attributes this type of attribute does not have to be physically stored within database

Composite attribute can be subdivided

Simple or atomic attributes cannot be subdivided

Single-value attribute can have only a single value

Multi-valued attributes can have many values

We also introduced the concept of the Relationship in a Database. We defined a relationship as being an association between entities. The participants in the relationship are the entities that participate in the relationship. Relationships between entities always operate in both directions • Relationship can be classified as 1:1, 1:M and M:N.

We also introduced the concept of Cardinality. Cardinality expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity. Cardinality is established by very concise statements known as business rules

This is another pivotal week for us. Many very important concepts in Relational Database Management will be introduced and emphasized this week. It is very important that everyone review the documents posted this week and read the corresponding chapter in the textbook. These documents contain key information relate to concepts that will carry us throughout the remainder of this course.

One key concept we will focus on is Data Modeling. We will use the Entity Relationship Modeling Language to illustrate designs for relational databases. In order to create these designs you must understand a few key concepts in database design and database manage that you began to study last week in the chapter on basic database modeling. We began to study and reviewed many of these concepts last week. This week will reinforce your understanding of these concepts.

Key Concepts that will be introduced and or emphasized this week:

Foreign Key
Cardinality
Functional Dependency
Referential Integrity
ER Diagramming

Chapters 3 and 4 are a very comprehensive chapter that contains a significant number of concepts and skills that has to be learned. It will take us a couple of weeks going over these skills and concepts.

We did a survey of a relational model while examining many concepts in Relational Database Management. This week we will explore relational database modeling in more depth. This is a topic that we will continue to revisit throughout the remainder of the semester. The Entity Relationship (ER) Model will be the cornerstone of building a functional database.

Over the next few weeks we will focus on primarily on two of these models; the Chen Model, the Crows Foot Model. Our textbook combines these two styles in its illustrations of ER Models Once again, we will continue fortifying our understanding of writing sing table queries in SQL. Over the next several weeks we will begin to add additional modifiers to the single table SELECT query statements to add additional criteria to the selection process when the query is executing.

I will be illustrating the use of diagrams.net to create ER Diagrams. I will show how to make both Chen Models, the Crows Foot Models in diagrams.net. Additionally, I will also demonstrate Microsoft Word as a tool to make less sophisticated Chen models if are working from home and for some reason we are not able to get the class registered by this third week of the semester.

This chapters goes back and reviews the fundamental structure of tables as being a grid consisting of rows and columns reflecting the attributes and instances of the table. It describes the concept of functional dependency as being a type of data structure that has a key that determines the rest of the information. This is similar to your account number of a bill determining your profile and billing information. This chapter also talks about the null value representing having nothing or no data in an attribute.

Below are a few key concepts and terms in Relational Database Management that are important to understand for the computer technology professional

Functional Dependency

By definition, a functional dependency is a constraint between two sets of attributes in a relation from a database. The "function" being discussed in "functional dependency" is the function of identification of related information. The determination of functional dependencies is an important part of designing databases in the relational model. A functional dependency occurs when one attribute uniquely determines another attribute in a relation or one attribute in a relation uniquely determines another attribute in another relation. This can be written A -> B which would be the same as stating "B is functionally dependent upon A."

Examples of functional dependency:

In a table listing employee attributes including Social Security Number (SSN) and name, it can be said that name is functionally dependent upon SSN (or SSN -> name) because an employee's name can be uniquely determined from their SSN. However, the reverse statement (name -> SSN) is not true because more than one employee can have the same name but different SSNs. an employee name cannot distinguish the SSN because more than one employee could have the same name.

The very important concept of Referential Integrity is covered in this chapter.

Referential integrity is a property of data in a relational database which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table). This means that when there is a primary and foreign key cross reference system, the primary key record must exist before the foreign key record can be inserted into the foreign key table. We will discuss this during lecture this week.

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. This means that you cannot delete a primary key record while there are still records in the foreign key table point to the primary key row.

Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. When the database deletes the foreign key records automatically when the primary key is removed, the process is called a cascade delete. This is not encouraged for the beginner working with databases. Typically a database will not perform a cascade deleted unless explicitly given the command. Usually the delete will not be executed if the system detects a referential integrity constraint violation.

The Chapter reviews the concept of Union, Intersection, Difference and Join. You should begin to study these concepts now. We will not seriously apply these concepts until after the midterm when we start studying multiple table queries. However, you should begin to understand the concepts conceptually now. We will examine this over the next two weeks.

Cardinality is also covered in this chapter. Here is a brief overview of cardinality that is explained in chapter 3.

In data modeling, the cardinality of one data table with respect to another data table is a critical aspect of database design. Relationships between data tables define cardinality when explaining how each table links to another. In the relational model, tables can be related as any of: many-to-many, many-to-one (rev. one-to-many), or one-to-one. These particular aspects are said to be the cardinality of a given table in relation to another.

Here are a few examples. Example 1, consider a database designed to keep track of hospital records. Such a database could have many tables like:

a Doctor table full of doctor information
a Patient table with patient information
a Department table with an entry for each department of the hospital.

In that model:

There is a many-to-many relationship between the records in the doctor table and records in the patient table (Doctors have many patients, and a patient could have several doctors); In M:N relationships a mapping/translation table is created that maps the primary keys of the two entities involved in the M:M relationship. This creates two 1 to many relationships towards the mapping table.

There is a one-to-many relation between the department table and the doctor table (each doctor works for one department, but one department could have many doctors). . When there is a one to one relationship, one typically leaves it intact. It is the desirable cardinality in a relational database

A one-to-one relationship is mostly used to split a table in two in order to optimize access or limit the visibility of some information. In the hospital example, such a relationship could be used to keep apart doctors' personal or administrative information. In data modeling, collections of data elements are grouped into data tables. Each entity has a collection of data elements. In the stable translation method, the entities and relationships are translated into tables. The data tables contain groups of data known as attributes. Tables are linked by a cross referencing system using key fields. A primary key attribute that uniquely identifies an instance in the table assigns that field's special order to a table: for example, the Doctor ID field might be assigned as the primary key of the Doctor table. Primary Keys are supposed to be unique. People can have same last name. A table can also have a foreign key which indicates that that field is linked to the primary key of another table. When there is a one to one relationship, one of two actions typically occur. The first is to combine the two tables into a single table. The second is to keep the two tables separated. When this action is taken one table becomes the primary key table and the other becomes the foreign key table. The most frequently accessed table is typically assigned as the primary key table in the relationship

Finally, this chapter revisits the two main concepts that we have been covering since chapter 1, the concepts of the primary key and the foreign key.

Primary Keys

We are beginning to start working on the process of creating ER diagrams and translating those diagrams into a relational schema and metadata that will ultimately be used to create a database. In an ER diagram of a data model, one or more unique identifiers may be declared for each data entity to uniquely identify an instance in the entity. Typically, entities in a relational model will become tables in the database.

The set of unique attributes declared for a data entity is often referred to as the candidate keys for that data entity. Thinks like social security number, your driver’s license number, your school identification number are examples of attributes that can uniquely identify an instance of you in a table. These can be considered as candidate keys that may become a primary key. From the set of candidate keys, a single unique key is selected and declared the primary key for that data entity.

Each unique key may be composed from one or more data attributes of that data entity. In an entity relationship diagram, each entity relationship uses a unique key, most often the primary key, of one data entity and copies the unique key data attributes to another data entity to which it relates. This inheritance of the unique key data attributes is referred to as a foreign key and is used to provide data access paths between data entities. Once the data model is instantiated into a database, each data entity usually becomes a database table, unique keys become unique indexes associated with their assigned database tables, and entity relationships become foreign key constraints.

The primary key of a relational table uniquely identifies each record in the table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.

Foreign Keys

We are studying relational database management systems (RDBMS). In the context of relational databases, a foreign key is a referential constraint between two tables. A foreign key is a field in a relational table that matches a unique key of another table. Usually this is the primary key in another table. The foreign key can be used as a bridge to cross-reference tables.

For example, say we have two tables, a CUSTOMER table that includes all customer data for cleaner service, and an ORDER table that includes all customer clothes that they handed over to e processed and cleaned.. The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table. To do this, we will place a foreign key in the ORDER table and have it relate to the primary key of the CUSTOMER table.

The foreign key identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must reference the columns of the primary key or other key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the foreign table cannot contain values that don't exist in the primary table. This way references can be made to link information together when writing and executing queries. This is an essential part of database organization and normalization. Multiple rows in the foreign key table may refer to the same row in the primary key table. Most of the time, it reflects the one (parent table or primary key table) to the many (child table, or foreign table)

Homework Assignments This Week

Readings

Chapter 4 in the Textbook

ER Modeling Lecture Notes

Practice Example Exercise

ER Diagram Practice demonstration and guides Chen Diagram Exercise

Example of a Chen ER Diagram: Upload Guides Practices Assignment

Review Chapter 4 Lecture Slides

Assignment

Practice: Schema and Metadata Pizza Delivery Company Database

Assignment: ER Modeling Homework Database

Discussions for the Week

Week 4 Chapter 4 Discussion 1

What role does the ER diagram play in the design process?

Week 4 Chapter 4 Discussion 2

Identify all of the cardinalities, and describe their characteristics and uses

Database Project

Practice:

Weekly Quiz

Complete Week 4 Chapter 4 Metadata Quiz Online