week 3 notes Health Data Management

In the example below, the relationship set is modeling which salesperson manages which salespeople.

At the logical level, the manager attribute is added as a foreign key.

Attributes in Relationship Sets

Attributes can be found not only in entity sets but also in relationship sets. For instance, below we have an example showing two entity sets: project and employee. A project is worked on by zero to many employees and an employee works on one-to-many projects. How would we store how many hours were worked per employee and project?

To do so we would add an attribute to the relationship set.

Attributes within relationship sets are known as intersection data and only exist with many-to-many relationships. We represent relationship set attributes with an irregularly shaped pentagon.

Converting relationship sets from the conceptual to the logical level require the introduction of a new type of key called foreign keys

A foreign key is an attribute or set of attributes in one relation that is also the primary or unique key in another (or even same) relation. It is the mechanism used in the relational model to link relations together.

Converting a one-to-one unary relationship is similar to converting a one-to-one binary relationship.

A primary key is added as a foreign key, but here the foreign key is being added to the same relation. For unary relationships, the foreign key must be named something other than the primary key because each attribute within a relation must be unique.

Talking through this diagram would result in the following:

A project is worked on by Employees. An employee works on projects An employee works for a department. A department is worked for by employees An employee has a certificate. A certificate is assigned to an employee

The second main component in ER models is relationships.

A relationship is a link between one or more entities. For instance, a relationship can be between a specific employee and the department they work in.

For example, from left-to-right:

An Employee works for a department; and from right-to-left: a Department is worked in by Employees.

A relationship set is a collection of all relationships of the same type.

For instance, all relationships that exist between all employees and their departments would be a relationship set.

The next example below illustrates optional modality.

For this example, a salesperson works in one and only one office; however, an office is worked in by zero to one salesperson. The optionality defined near the salesperson entity set means we could store offices in the database that have yet to be assigned.

Cardinality and Modality

Defining a relationship between entities of entity sets does not provide a full picture of how those relationships are formed. To do so, we need to define how many and how few entities can be related to other entities within a relationship.

When you design a database,

usually each entity set should have at least one relationship set with another entity set or itself.

For cardinality, we use a

vertical pipe, which represents a maximum of one, or three lines that look like a crow's foot (hence the name of the notation: Crow's Feet notation), which represents many (see below for examples).

The example below shows a many-to-many relationship between salesperson and product.

Here a salesperson can sell many products and a product can be sold by many salespeople.

The figure below shows an example of a unary relationship set.

Here, a salesperson manages other salespeople, and a salesperson is managed by another salesperson.

Shown below is an example of a binary relationship set.

Here, we are able to capture the relationship between what products a salesperson sales, and what products are being sold by what salespeople.

This simply means your design should not have entity sets that are not related to any other entity sets.

However, only create relationships between entities that make sense. There is no need to define relationships between all entity sets. For instance, the image below shows an example of a simple ER diagram. Noticeable, only some entity sets are connected to others through relationship sets. The project entity set does not directly link to Department but does indirectly link via employee.

In the example below, a salesperson sells to zero-to-many customers,

while a customer is sold to by one and only one salesperson. Since salesperson is on the one side, its primary key is added as a foreign key to the customer relation, which is on the many side.

Converting a one-to-one binary relationship from the conceptual to logical level requires

taking the primary key in one of the relations and adding it as the foreign key in the other relation.

Having a primary key allows for

targeted lookups and updates.

The relationship sets represent

the business rules, policies, and regulations that exist within an organization. These rules should be well understood when designing a database so the databases' rules mirror those of the organization.

When creating a logical design,

the conceptual design is converted into a set of logical database structures. For our purposes, we will assume our logical model follows the relational model. Thus, the conversion will follow a series of steps resulting in a valid set of relations.

The final difference is

the diamonds representing relationship sets are missing. Instead, we use a simple line to represent relationships.

A car may be rented zero-to-many times by a customer and a customer rents at least one, but possibly many cars from the agency.

In addition to capturing basic rental data, extra data, such as rental date, return date, and total cost are included as intersection data. Finally, a car may be repaired zero-to-many times and a particular repair is for one and only one car.

An entity's primary key value should not change over time.

In addition, each entity must have a value for the primary key.

For one-to-many binary relationships

the primary key on the one side becomes the foreign key on the many side.

The larger the overlap between the answerable and important queries,

The better the design

Converting from Conceptual to Logical-The process to go from the conceptual to logical level consists of three basic steps:

The first step is to convert each entity set in the ER diagram into a relation. Any primary keys defined in the ER diagram will remain; however, in the situation of weak entity sets the primary key will be completely defined within the relation. The second step is to convert the relationships within the ER diagram. There are two main challenges when converting relationships. First, foreign keys are specified at the logical level, so new attributes must be added to the appropriate relation in order to preserve the one-to-one or one-to-many relationships from the conceptual model. Second, the relational model does not allow many-to-many relationships, thus the conversion of those relationships requires the creation of a new relation, which will be discussed in more detail later on. In the third step, the conversion from the ER diagram to a relational model has been completed. However, the resulting relations may not be structured in the most beneficial way. There may be data redundancies or anomalies that may be able to creep into the database based on the design, once implemented. Thus, the third step is to go through a process known as normalization to reduce data redundancies and anomalies.

The image below illustrates a one-to-one binary relationship between salesperson and office.

Notice the vertical pipes nearest both entity sets. This means an entity of salesperson can be associated with at most one office and vice versa. To read this diagram from left-to-right, we would say: a salesperson works in at most one office. Reading from right-to-left would be: an office is worked in by one salesperson.

The image below shows an example of a logical design that would be developed from the conceptual design.

Noticeable is this view looks very similar to an ER diagram at the conceptual level.

straight line represents "mandatory" (i.e., 1)

O represents "optional" (i.e.. 0).

A relationship set is represented in an ER diagram

with a diamond shape

When you deal with intersection relations between many relations, the composite primary key may become large, thus many times you will see a synthetic primary key being created.

The second transaction relation below shows Trans_Num was added as a new attribute and it is the primary key. The attributes, cust_num, SP_num, and prod_num are still included in the relation, but now are only foreign keys to their respective relations.

The design above is for a simple car rental database at the logical level.

Walking through the design shows we can capture data about car manufacturers, cars in the rental agency's fleet, maintenance events, and customers. Going from left to right, a manufacturer may have one-to-many cars and a car can only have one associated manufacturer.

there are some differences. First, the logical design explicitly lists which attribute or attributes are foreign keys.

Second, the relational model does not allow many-to-many relationships. Thus, when converting from conceptual to logical, we will adjust our design so it fits within the relational model.

Primary keys can be annotated in many different ways on ER diagrams.

You may see the attributes bolded, underlined, or having an annotation of "PK."

Mandatory modality means

an entity in the related entity set must exist for an entity in the other entity set. In the example below, there is a mandatory requirement between salesperson and office and vice-versa. Thus, a salesperson works in one and only one office and an office is worked in by one and only one salesperson.

An entity is

an object or thing that is distinguishable from other objects. For instance, a specific movie, company, or event.

the database can only provide

answers based on the data it has stored.

name of entity example= employee

attributes are simply listed one per line underneath the name of the entity set. for example; employee number, name, date of birth, social security number, and phone number.

A ternary relationship, or even higher degrees, are possible,

but less common than binary. A relationship set where an entity set connects to itself is called a unary relationship

An entity set is a

collection of entities of the same type that share the same attributes. Thus, an entity set called "cars" will contain a collection of individual cars. Each of those car entities would have values for the same set of attributes such as color, make, model, and year.

The cardinality symbol is defined for

each side of the relationship and is placed closest to the entity set. Since cardinality can have one of two values you can have one-to-one, one-to-many, or many-to-many if you have a binary relationship set.

In entity-relationship modeling, the database is modeled using two main components:

entity sets and relationship sets.

The degree of a relationship set is

how many distinct entity sets are involved.

Whenever converting many-to-many relationships there will always be an intersection relation created.

hus, if you have a ternary relationship (a many-to-many-to-many relationship) then there will be four relations, the original three relations created directly from the entity sets and one additional relation for the associative entity set. If you have a many-to-many relationship between N entity sets then converting to the logical level will result in N + 1 relations.

The way to read this ER diagram is first from

left-to-right you state the name of the entity set, descriptive phrase for the relationship set, and finally, the other entity set connected to the relationship set. Then you can read the same relationship set from right-to-left.

Modality-

looks at the minimum number of entity sets. Our options here are one (required) or zero (optional).

If the relationship is symmetric, meaning both sides are mandatory or optional,

then either primary key can be used.

If the primary key uses multiple attributes,

then it is known as a composite primary key.

If two different entity sets are connected

then that would be a binary relationship set, which is the most common relationship type.

If there are three different entity sets involved

then that would be a ternary relationship.

If a many-to-many binary relationship has intersection data where at least one of the attributes is indicated to be part of the primary key, as shown below with Date annotated with an asterisk,

then the intersection relation would include that attribute as part of the primary key. For instance, Sale now has a composite primary key consisting of three attributes: SP_Number from salesperson, Prod_Number from Product, and Sale_Date.

If the relationship is set up so one relation has a mandatory modality and the other is optional

then the primary key from the mandatory side should be used.

The example below shows how we add attributes to the relationship set

to model when an employee was assigned to a project and how many hours that employee worked.

Why is an entity-relationship (ER) model used when designing a database at the conceptual level?

1. First, the entity-relationship approach is helpful modeling "real world" phenomena because the model represents things (i.e., entities) and their relationships. Although very simple conceptually, it allows even highly complex phenomena to be modeled. 2. Second, ER diagrams are a useful tool for communicating the design to others. Because the ER model is conceptually simple the basics can be explained quickly; leaving more time to focus on the actual design being presented. 3. Third, database systems can be complex having large amounts of data tables. Instead of displaying all parts of a design, the ER diagram can hide non-relevant entities, relationships, or entity details. This allows for easier focus on the relevant sections of the design. 4. The final reason is the ER model fits well with the relational model. Although a database model does not need to be selected at the conceptual design phase, many times a relational database is what is being designed. The conversion from the ER model to a logical relational model is a fairly straightforward process.

To design a database, we follow a basic four-step process.

1. The first step is requirements analysis, where the purpose and requirements of the database are discovered. 2. The second step develops a conceptual model of the database. The output is an entity relationship (ER) diagram. 3. The third step takes the conceptual model and converts it to a logical model, which in our case follows the rules of the relational model. 4. The final step is to create a physical model. Here a specific database management system (DBMS) is known and the design is adjusted to work within the confines of that DBMS.

In the example below, we have a many-to-many relationship between salesperson and product.

A salesperson sells one-to-many products and a product is sold by one-to-many salespeople. The conversion to the logical level will result in both salesperson and product being identical in structure as at the conceptual level. To capture the relationship, a new intersection relation is created called sale, which has a composite primary key that contains SP_Number (the primary key from salesperson) and prod_number (the primary key from product). This particular ER diagram also has intersection data on the relationship set. Thus, the intersection relation would add any attributes from the intersection data into its relation too.

When converting this design to the logical model, the product relation will mirror the product entity set from the design.

Because of the many-to-many relationship set we would create a new intersection relation, here called bill of material. The bill of material relation would have a composite primary key which includes product number twice; however, because each attribute must be uniquely named, one attribute is called product_num and the other is called part num. The addition of the quantity attribute is because the ER diagram has quantity listed as an attribute in the intersection data on the relationship set.

Below is an example of a relationship set between Employee and Department.

Inside the diamond is some descriptive text that helps clarify the relationship set. Of note, the text is directional and so will likely only make sense as written in one direction.

The ER diagram below illustrates a one-to-many relationships between salesperson and customer.

Reading this diagram, a salesperson sells to many customers and a customer is sold to by a salesperson.

The example shown below has a one-to-one relationship between salesperson and office, where a salesperson works in one and only one office and an office can be worked in by zero-to-one salespeople.

Shown below the ER diagram are the two relations. You'll note that salesperson has the same attributes (with slightly different names) in the relation, with SP_Number as the primary key. Office also has the same attributes, with Office number specified as the primary key. Because a salesperson must work in an office, the primary key of office is added as an attribute to salesperson. This new attribute in salesperson is a foreign key and allows us to know which office a particular salesperson is assigned to.

Many-to-many unary relationships work in the same manner as many-to-many binary relationships such that a new intersection relation will be created.

That intersection relation will have a composite primary key; however, instead of pulling primary keys from multiple other relations, the composite primary key will be pulled twice from the same relation.

The example shown below illustrates a ternary relationship between salesperson, customer, and product.

The original entity sets would all be converted directly into relations without any changes, other than minor naming differences. A new intersection relation would be created, in this case transaction, that would contain as its composite primary key the primary keys of all the connecting relations. Thus, the composite primary key would be cust_num, sp_num, and prod_num. In addition, the intersection data has date annotated as a primary key. Therefore, transaction would also include date as part of the composite primary key. The quantity attribute would be added as just an attribute to the transaction relation.

Converting a one-to-many unary relationship is the same as a one-to-one unary relationship.

The primary key of the relation will be added as a foreign key in the same relation.

Implicit in many-to-many relationships is something known as an associative entity set, which is a type of weak entity set.

This type of entity set relies on the supporting entity sets to form its primary key. Thus, the primary key for an associative entity set will at least be a composite of all the primary keys of related entity sets. The primary key may also include attributes specified as part of the primary key in intersection data

Each entity within an entity set has attributes that describe the characteristics of the entity.

These attributes are the target of most queries. For instance, the price of a book.

The figure below shows a relationship between entities in the salesperson, customer, and product entity sets, a ternary relationship set.

This design allows us to know which salesperson, sold what product, to what customer. In addition, we could determine all the products sold by a salesperson, regardless of customer. Or which customer buys what products, regardless of salesperson.

For instance, if our business rule is a product can be a set of other products, then we would have a design such as what's shown below.

This design allows us to say a car is a product, and a car is made up of four wheels, a chassis, an engine, etc., which are all other parts. In addition, an engine is made up of a number of other parts.

Many-to-many binary relationships cannot be represented in the relational model.

Thus, an intersection relation is created. This intersection relation has a composite primary key that consists of the primary key of all connecting relations.

For instance, in the example below, we have a salesperson entity set that has a relationship set to itself. This relationship set is modeling that each salesperson has a backup salesperson.

When converting to the logical level, the salesperson relation has the same attributes as before, except for the addition of one additional attribute, which represents the foreign key. SP_Number is the primary key and backup_number is the foreign key (indicated via the "FK" annotation)

Cardinality is

concerned with determining the maximum number of entities one entity can be associated with. Our options are one or many others

A primary key is

defined that uniquely identifies each entity within an entity set.

Like cardinality, there are two possible options for modality:

mandatory, represented by the vertical pipe, or optional, represented by a 0 (see below for examples). These symbols are the inner symbol of a relationship and are closest to the relationship set diamond.

he primary key may be specified using

one or more existing attributes from the entity set, or be an added attribute known as a synthetic primary key.

Which primary key to copy depends on

the modality