Please draw ERD Diagram. Sample diagram format also attached

profilekushal-kumar
ch07.ppt
Home>Information Systems homework help>Please draw ERD Diagram. Sample diagram format also attached

Chapter 7
Logical Database Design

BLCN-534: Fundamentals of Database Systems

Chapter Objectives

  • Describe the concept of logical database design.
  • Design relational databases by converting entity-relationship diagrams into relational tables.
  • Describe the data normalization process.
  • Perform the data normalization process.
  • Test tables for irregularities using the data normalization process.

7-*

Logical Database Design

  • The process of deciding how to arrange the attributes of the entities in the business environment into database structures, such as the tables of a relational database.
  • The goal is to create well structured tables that properly reflect the company’s business environment.

7-*

Logical Design of Relational Database Systems

  • (1) The conversion of E-R diagrams into relational tables.
  • (2) The data normalization technique.
  • (3) The use of the data normalization technique to test the tables resulting from the E-R diagram conversions.

7-*

Converting E-R Diagrams into Relational Tables

  • Each entity will convert to a table.
  • Each many-to-many relationship or associative entity will convert to a table.
  • During the conversion, certain rules must be followed to ensure that foreign keys appear in their proper places in the tables.

7-*

Converting a Simple Entity

  • The table simply contains the attributes that were specified in the entity box.
  • Salesperson Number is underlined to indicate that it is the unique identifier of the entity and the primary key of the table.

7-*

Converting Entities in Binary Relationships: One-to-One

  • There are three options for designing tables to represent this data.

7-*

One-to-One: Option #1

  • The two entities are combined into one relational table.

7-*

One-to-One: Option #2

Separate tables for the SALESPERSON and OFFICE entities, with Office Number as a foreign key in the SALESPERSON table.

7-*

One-to-One: Option #3

  • Separate tables for the SALESPERSON and OFFICE entities, with Salesperson Number as a foreign key in the OFFICE table.

7-*

Converting Entities in Binary Relationships: One-to-Many

  • The unique identifier of the entity on the “one side” of the one-to-many relationship is placed as a foreign key in the table representing the entity on the “many side.”
  • So, the Salesperson Number attribute is placed in the CUSTOMER table as a foreign key.

7-*

Converting Entities in Binary Relationships: One-to-Many

7-*

Converting Entities in Binary Relationships: Many-to-Many

  • E-R diagram with the many-to-many binary relationship and the equivalent diagram using an associative entity.

7-*

Converting Entities in Binary Relationships: Many-to-Many

  • An E-R diagram with two entities in a many-to-many relationship converts to three relational tables.
  • Each of the two entities converts to a table with its own attributes but with no foreign keys (regarding this relationship).
  • In addition, there must be a third “many-to-many” table for the many-to-many relationship.

7-*

Converting Entities in Binary Relationships: Many-to-Many

  • The primary key of SALE is the combination of the unique identifiers of the two entities in the many-to-many relationship. Additional attributes are the intersection data.

7-*

Converting Entities in Unary Relationships: One-to-One

  • With only one entity type involved and with a one-to-one relationship, the conversion requires only one table.

7-*

Converting Entities in Unary Relationships: One-to-Many

Very similar to the one-to-one unary case.

7-*

Converting Entities in Unary Relationships: Many-to-Many

  • This relationship requires two tables in the conversion.
  • The PRODUCT table has no foreign keys.

7-*

Converting Entities in Unary Relationships: Many-to-Many

  • A second table is created since in the conversion of a many-to-many relationship of any degree — unary, binary, or ternary — the number of tables will be equal to the number of entity types (one, two, or three, respectively) plus one more table for the many-to-many relationship.

7-*

Converting Entities in Ternary Relationships

  • The primary key of the SALE table is the combination of the unique identifiers of the three entities involved, plus the Date attribute.

7-*

The Data Normalization Process

  • A methodology for organizing attributes into tables so that redundancy among the nonkey attributes is eliminated.
  • The output of the data normalization process is a properly structured relational database.

7-*

The Data Normalization Technique

  • Input:
  • all the attributes that must be incorporated into the database
  • a list of all the defining associations between the attributes (i.e., the functional dependencies).
  • a means of expressing that the value of one particular attribute is associated with a single, specific value of another attribute.
  • If we know that one of these attributes has a particular value, then the other attribute must have some other value.

7-*

General Hardware Environment: SALESPERSON and PRODUCT

7-*

Functional Dependence

  • Salesperson Number is the determinant.
  • The value of Salesperson Number determines the value of Salesperson Name.
  • Salesperson Name is functionally dependent on Salesperson Number.

7-*

Salesperson Name

Salesperson Number

Steps in the Data Normalization Process

First Normal Form

Second Normal Form

Third Normal Form

7-*

The Data Normalization Process

  • Once the attributes are arranged in third normal form, the group of tables that they comprise is a well-structured relational database with no data redundancy.
  • A group of tables is said to be in a particular normal form if every table in the group is in that normal form.
  • The data normalization process is progressive.
  • For example, if a group of tables is in second normal form, it is also in first normal form.

7-*

General Hardware Company: First Normal Form

  • The attributes under consideration have been listed in one table, and a primary key has been established.
  • The number of records has been increased so that every attribute of every record has just one value.
  • The multivalued attributes have been eliminated.

7-*

General Hardware Company: First Normal Form

  • First normal form is merely a starting point in the normalization process.
  • First normal form contains a great deal of data redundancy.
  • Three records involve salesperson 137, so there are three places in which his name is listed as Baker, his commission percentage is listed as 10, and so on.
  • Two records involve product 19440 and this product’s name is listed twice as Hammer and its unit price is listed twice as 17.50.

7-*

General Hardware Company: Second Normal Form

  • No Partial Functional Dependencies
  • Every nonkey attribute must be fully functionally dependent on the entire key of that table.
  • A nonkey attribute cannot depend on only part of the key.

7-*

General Hardware Company: Second Normal Form

  • In SALESPERSON, Salesperson Number is the sole primary key attribute. Every nonkey attribute of the table is fully defined just by Salesperson Number.
  • Similar logic for PRODUCT and QUANTITY tables.

7-*

General Hardware Company: Third Normal Form

  • Does not allow transitive dependencies in which one nonkey attribute is functionally dependent on another.
  • Nonkey attributes are not allowed to define other nonkey attributes.

7-*

General Hardware Company: Third Normal Form

  • Important points about the third normal form structure are:
  • It is completely free of data redundancy.
  • All foreign keys appear where needed to logically tie together related tables.
  • It is the same structure that would have been derived from a properly drawn entity-relationship diagram of the same business environment.

7-*

Candidate Keys as Determinants

  • There is one exception to the rule that in third normal form, nonkey attributes are not allowed to define other nonkey attributes.
  • The rule does not hold if the defining nonkey attribute is a candidate key of the table.
  • Candidate keys in a relation may define other nonkey attributes without violating third normal form.

7-*

Data Normalization Check

  • The basic idea in checking the structural worthiness of relational tables, created through E-R diagram conversion, with the data normalization rules is to:
  • Check to see if there are any partial functional dependencies.
  • Check to see if there are any transitive dependencies.

7-*

7-*

CREATE TABLE SALESPERSON

(SPNUM CHAR(3) PRIMARY KEY,

SPNAME CHAR(12)

COMMPERCT DECIMAL(3,0)

YEARHIRE CHAR(4)

OFFNUM CHAR(3) );

Dropping a Table with SQL

Creating a Table with SQL

DROP TABLE SALESPERSON;

7-*

CREATE VIEW EMPLOYEE AS

SELECT SPNUM, SPNAME, YEARHIRE

FROM SLAESPERSON;

Dropping a View with SQL

Creating a View with SQL

DROP VIEW EMPLOYEE ;

7-*

UPDATE SALESPERSON

SET COMMPERCT = 12

WHERE SPNUM = ‘204’;

The SQL Update, Insert, and Delete Commands

INSERT INTO SALESPERSON

VALUES

(‘489’, ‘Quinlan’, 15, ‘2011’, ‘59’);

DELETE FROM SALESPERSON

WHERE SPNUM = ‘186’;

Use Cases and Examples

3-*

Designing the General Hardware Company Database

7-*

Designing the Good Reading Bookstores Database

7-*

Designing the World Music Association Database

7-*

Designing the Lucky Rent-A-Car Database

7-*

General Hardware Company: Unnormalized Data

7-*

  • Records contain multivalued attributes.

General Hardware Company: First Normal Form

7-*

General Hardware Company: Second Normal Form

7-*

General Hardware Company: Functional Dependencies

7-*

General Hardware Company: First Normal Form

7-*

Good Reading Bookstores: Functional Dependencies

7-*

World Music Association: Functional Dependencies

7-*

Lucky Rent-A-Car:
Functional Dependencies

7-*

General Hardware Company: Third Normal Form

7-*

General Hardware Company: Third Normal Form

7-*