Normalized Er Diagram Of Employees

Entity-relationship (ER) diagrams are the blueprints for database applications in OLTP systems. The process of creating ER diagrams is well documented and involves:

■

Identifying database entities (tables)

■

Defining entity attributes (columns)

■

Identifying unique row identifiers (keys)

■

Defining relationships between entities

The data model then goes through a process called normalization that includes three primary rules for efficient data storage. For a detailed description of the normalization process, refer to the "Database Normalization" sidebar in this chapter.

The ER diagram in Fig. 4 depicts the various entities and relationships that must be represented in the enterprise database for storing information related to revenue business processes for the illustrative retailing firm scenario. A standard set of conversion rules are applied to deduce the relational tables that should result from the ER diagram. The conversion rules are as follows: (1) a separate table is created for each entity; (2) attributes are created for each entity and the primary key in each entity is identified; (3) for the purpose of conversion, all "optional" relationships are treated as "mandatory many" relationships; (4) the primary key of the entity on the "one" side of a relationship is posted to the table of the entity on the "many" side of a relationship; and (5) a separate table is created for the relationship itself for entities participating in an M:M relationship with the primary key of each table being posted to the new relationship table to form a composite key. Attributes unique to many-to-many relationships are posted as nonkey attributes in the composite key table. Applying the conversion rules and streamlining the resulting tables to eliminate redundancies and inconsistencies, we arrive at the set of tables shown in Fig. 5. Primary keys are underlined and foreign keys are indicated with an asterisk at the end of the field.

The ER diagram in Figure 12-18 produces the following tables:

volunteer (volunteer_numb, first_name, last_name, street, city, state_code, zip, phone)

state (state_code, state_name)

availability (volunteer_numb, day_code, start_time, end_time)

day (day_code, day_name)

skill (skill_numb, skill_description)

skills_known (volunteer_numb, skill_numb)

job (job_numb, job_description, job_date, job_start_time, estimated_duration, numb_volunteers_needed)

job_skill_required (job_numb, skill_numb, numb_volunteers_with_skill)

volunteer_scheduled (volunteer_numb, job_numb, skill_numb)

An ER diagram in which all many-to-many relationships have been transformed into one-to-many relationships through the introduction of composite entities can be translated directly into a set of relations. To do so:

▪

Create one table for each entity.

▪

For each entity that is only at the "one" end of one or more relationships and not at the "many" end of any relationship, create a single-column primary key, using an arbitrary unique identifier if no natural primary key is available.

▪

For each entity that is at the "many" end of one or more relationships, include the primary key of each parent entity (those at the "one" end of the relationships) in the table as foreign keys.

▪

If an entity at the "many" end of one or more relationships has a natural primary key (for example, an order number or an invoice number), use that single column as the primary key. Otherwise, concatenate the primary key of its parent with any other column or columns needed for uniqueness to form the table's primary key.

Following these guidelines, we end up with the following tables for the Antique Opticals database:

Customer (customer_numb, customer_first_name, customer_last_name, customer_street, customer_city, customer_state, customer_zip, customer_phone)

Distributor (distributor_numb, distributor_name, distributor_street, distributor_city, distributor_state, distributor_zip, distributor_phone, distributor_contact_person, contact_person_ext)

Item (item_numb, item_type, title, distributor_numb, retail_price, release_date, genre, quant_in_stock)

Order (order_numb, customer_numb, order_date, credit_card_numb, credit_card_exp_date, order_complete?, pickup_or_ship?)

Order item (order_numb, item_numb, quantity, discount_percent, selling_price, line_cost, shipped?, shipping_date)

Purchase (purchase_date, customer_numb, items_received?, customer_paid?)

Purchase item (purchase_date, customer_numb, item_numb, condition, price_paid)

Actor (actor_numb, actor_name)

Performance (actor_numb, item_numb, role)

Producer (producer_name, studio)

Production (producer_name,item_numb)

Note: You will see these relations reworked a bit throughout the remainder of the first part of this book to help illustrate various aspects of database design. However, the preceding is the design that results from a direct translation of the ER diagram.

The symbol for a simple dis is seen in Fig. 7.3.4.

As a simple example of a dis, consider the dis shown in Fig. 7.3.5.

As for the class diagram, the entity-relationship diagram is easy to use and powerful enough to represent relational structures. It is mainly based on a graphical representation that facilitates its understanding.

The concepts used are:

–

entity-types: a set of entities that share data characteristics (e.g. "student", "courses", "university");

–

relationship-types: a set of relationships between entity-types (e.g. "study", "teach", "is registered with");

–

attributes: a property of an entity-type or a relationship-type;

–

cardinality (of a relationship): the number of relationship instances to which an entity can participate.

•

Customer, Sales Order, SO Line Item, Product Type, etc. are examples of entity types.

•

Note that City, State, and Country are also entity types.

○

But each of these is also what is called a "sub-type of" the entity type Geographic Area.

○

That means that each of the sub-types (for example, City) is, by definition, also a definition of the super-type, Geographic Area.

•

"SO Number", "SO Issued Date", and "SO Completed Date" are attributes of Sales Order.

○

The asterisk (*) means the attribute is mandatory.

○

A circle means it is optional.

○

A hashtag with underline (#) means the attribute is part of the unique identifier for the entity type. The attribute name is also underlined.

•

The lines between pairs of entity types are examples of Relationships.

○

Cardinality and optionality of each relationship are further clarified by the text at each end of the line and can be stated in a sentence.

○

By naming relationships in this way, the resulting sentences must be either true or not true, when presented to someone in the business side of the organization.

•

Examples of relationships with resulting sentences:

○

Each Customer may be buyer in one or more Sales Orders.

▪

Optionality of "may be" is indicated by the dashed line nearest Customer.

▪

Cardinality of "one or more" is indicated by the crow's foot nearest Sales Order. Note: For cardinality, the crow's foot notation indicates "many" by its many "toes." It was invented by Gordon Everest, who originally used the term "inverted arrow" (Everest, 1976).

○

Alternatively, each Sales Order must be sold to one and only one Customer.

▪

The Optionality "must be" is indicated by the solid line nearest the subject entity type (in this case Sales Order).

▪

The Cardinality "one and only one" is indicated by the absence of a crow's foot nearest the object entity type (in this case Customer).

•

Example of relationship that is also part of the unique identifier:

○

A vertical bar next to a crow's foot means that the nearest relationship is also part of the unique identifier.

○

Each instance of a Sales Order is uniquely identified only by SO Number (indicated by #), whereas each instance of an SO Line Item is identified by a combination of the attribute # SO Line Number and the relationship "part of" Sales Order.

1.

emp_id, start_date -> job_title, end_date

2.

emp_id -> emp_name, phone_no, office_no, proj_no, proj_name, dept_no

3.

phone_no -> office_no

4.

proj_no -> proj_name, proj_start_date, proj_end_date

5.

dept_no -> dept_name, mgr_id

6.

mgr_id -> dept_no

If we try to put FDs 1–6 into a single table with the composite candidate key (and primary key) (emp_id, start_date) we violate the 3NF definition, because FDs 2–6 involve left sides of FDs that are not superkeys. Consequently, we need to separate FD 1 from the rest of the FDs. If we then try to combine 2–6 we have many transitivities. Intuitively, we know that 2, 3, 4, and 5 must be separated into different tables because of transitive dependencies. We then must decide whether 5 and 6 can be combined without loss of 3NF; this can be done because mgr_id and dept_no are mutually dependent and both attributes are superkeys in a combined table. Thus, we can define the following tables by appropriate projections from 1–6.

emp_hist: emp_id, start_date -> job_title, end_date

employee: emp_id -> emp_name, phone_no, proj_no, dept_no

phone: phone_no -> office_no

project: proj_no -> proj_name, proj_start_date, proj_end_date

department: dept_no -> dept_name, mgr_id

mgr_id -> dept_no

Alternative designs may involve splitting tables into partitions for volatile (frequently updated) and passive (rarely updated) data, consolidating tables to get better query performance, or duplicating data in different tables to get better query performance without losing integrity. In summary, the measures we use to assess the trade-offs in our design are:

•

Query performance (time).

•

Update performance (time).

•

Storage performance (space).

•

Integrity (avoidance of delete anomalies).