Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Er diagram to table conversion ppt

23/11/2021 Client: muhammad11 Deadline: 2 Day

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-*

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Quick Finance Master
Accounting & Finance Mentor
Professional Coursework Help
Homework Tutor
Top Quality Assignments
Professional Accountant
Writer Writer Name Offer Chat
Quick Finance Master

ONLINE

Quick Finance Master

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$50 Chat With Writer
Accounting & Finance Mentor

ONLINE

Accounting & Finance Mentor

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$50 Chat With Writer
Professional Coursework Help

ONLINE

Professional Coursework Help

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$38 Chat With Writer
Homework Tutor

ONLINE

Homework Tutor

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$20 Chat With Writer
Top Quality Assignments

ONLINE

Top Quality Assignments

After reading your project details, I feel myself as the best option for you to fulfill this project with 100 percent perfection.

$37 Chat With Writer
Professional Accountant

ONLINE

Professional Accountant

I will be delighted to work on your project. As an experienced writer, I can provide you top quality, well researched, concise and error-free work within your provided deadline at very reasonable prices.

$50 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

How to access youthsaver account - Research paper - Setting in a doll's house - Essentials of business communication 11th edition pdf - Maxine hong kingston tongue tied - Examples of all six simple machines - Ib psychology command terms - Texas constitution weaknesses - Horrid henry pe teacher - Example of charging by contact - Variable costing approach - Bathroom floor drain requirement nsw - Nmr worksheet with answers - Sameer singh orthopaedic surgeon - Framing paper religious education in australian catholic schools - Managerial Accounting - English Course - tech communication - Goeller scorecard example - Muslim Molvi 7340613399 OnLine No 1 FaMOUs VashIKaraN sPecIaLIsT IN Berhampur - Cadbury liqueur dan murphy's - NEED IN 18 HOURS - Business requirements document template - Rubbish chute hire brisbane - Clinical reasoning cycle essay - Paper - 2/20 patty street mentone - Walmart recruiting store sales forecasting - Communication - Hydrogen and iodine react together in a reversible reaction - Density of water at 25.5 degrees celsius - Fisher 7600 butterfly valve manual - The alamo drafthouse case study solution - Digital capacitance meter using 555 timer - Physiological psychology - Year group leader responsibilities - Psychcentral com quizzes personality start php - 3 cubed minus 2 squared times 5 - Duality between controllability and observability - Multiple schedule of reinforcement - Health Care Delivery 5.2 - Vehicle recovery training costs - Http www apa org pubs databases psycinfo index aspx - How to critique a presentation - James and the giant peach centipede - Sensifast cdna synthesis kit protocol - 26 electrons 26 protons - Mary maxfield they say i say - Positive child guidance 7th edition - Consider today's stock listing for enam telecom shown below - Merck business ethics - Justine flynn buster productions - Teesside university staff email - Debate team line example - Tellam civil products molendinar - Unwritten informal codes of conduct are referred to as - Purdue powerpoint template - Discussion DQ3 - Luzadis company makes furniture using the latest automated technology - NTC/300: Cloud Technologies - Discussion - Leadership styles in nursing essay - Low voltage electrical symbols - Leaders inspire followers to transcend their self interests - Diffusion coefficient of ferrocene in acetonitrile - Teacher key selection criteria - Which sentences contain vague pronouns check all that apply - Julia baker husband - Microsoft threat analysis and modeling tool - Welsh water new connections - Hands on labs microscope - How to end a speech for school captain - Oh now carry me to bethlehem song - Does vinegar conduct electricity - Beta layout design rules - Demographic of super bowl audience - The act math test lasts a total of _____ minutes - Ck 12 chemistry answer key - Ketamine Therapies - Style guide template word - Weighted average process costing method calculate equivalent units - I need this discussion done for week 2 for my Recruit, Develop, Assess, Reward, Retain class - Cobit 5 goals cascade - Order of reaction ppt - Request letter for diploma - Unitarist and pluralist approaches to employment relations - Chapter5 - Argument - Drawing a genogram on microsoft word - The moths by helena maria viramontes analysis - Persuasive techniques and devices - 2019 cal south state cup presidents olders - The campbell company is considering adding a robotic paint - Last chance company offers legal consulting - Zelda and scott fitzgerald were hailed as the - Our father in chinese - Problem 6 1a perpetual alternative cost flows lo p1 - What is the stage gate process for new product development - Calvin cycle mcgraw hill - 17.1 independent practice answer key - Evidenced base - Recruitment