Illustrate the process of normalizing the table

Assignment Help Other Subject
Reference no: EM132155163

Question: Evaluate if the table shown on the attachments see if it's normalized, if not, describe and illustrate the process of normalizing the table shown above to third normal form. State any assumptions you make about the data shown in this table.

1. The table below shows all information stored for an order entry system. It keeps track of customer information, order date and item/quantity, as well as MSRP and manufacturer for each item. CustomerNo is the unique identifier for customer.

Business Rules: A customer can place multiple orders, but each order contains at most one item (multiple quantities are allowed), and the customer cannot order the same item again during the same day.

CustomerNo

CustomerName

CustomerCity

CustomerPhone

OrderDate

Item

Quantity

Manufacturer

ManufacturerPhone

MSRP

S1011

S1011

S1024

S1024

S1032

S1032

James Bond

James Bond

Andrew Joe

Andrew Joe

Kevin King

Kevin King

DC

DC

LA

LA

New York

New York

1238888, 1239999

1238888, 1239999 7778888, 7779999

7778888, 7779999

9998888

9998888

1/9/05

1/9/05

1/9/05

2/7/05

3/1/05

3/1/05

ipad

iphone

kindle

kindle

iphone

Pixel 2

1

2

3

5

1

8

Apple

Apple

Amazon

Amazon

Apple

Google

8888999

8888999

7773333

7773333

8888999

3338899

888

999

333

333

999

777

Evaluate if the table shown above is normalized, if not, describe and illustrate the process of normalizing the table shown above to third normal form. State any assumptions you make about the data shown in this table.

I. Is the table in 1NF, 2NF, or 3NF?

II. If not, describe and illustrate the process of normalizing it to 3NF. Identify functional dependencies before each conversion.

III. Show the final tables with data after normalization.

2. Design a database.

John owns a portfolio of rental properties with his parterns. He wants to develop a database to store property information as well rental data. Below are some of the data that need to be kept track of.

A. For each property, the database needs to store property address, size, number of rooms and bathrooms, and purchase date.

B. There are two types of properties - single family house and condo. Need to store yard size for single family house and monthly HOA(Home Owner Association) fee for condo.

C. For current and potential customers, need to store customer name, current address, family size, and contact phone number.

D. A customer needs to submit an application to rent a property. Need to track application submission date, potential start date, application result and explanation.

E. Once approved, a lease will be signed and need to track start date, end date, sign date, montly rent, and deposit amount.

F. Need to track montly payment information including payment amount, pay date, and payment method.

G. A customer can set up different payment methods. Each method includes financial institute name, address, and account number. There are two types of methods - credit card and check. For credit card, need to store expiration date and security code. For check, need to store bank routing number.

Please complete the following tasks to design the database to meet requirements specified. State any relevant assumptions you make, but do not add any additional data requirement unless it is really necessary.

I. Identify entities and attributes. List entity name and attribute names for each entity.

II. Identify relationships among entities. List relationship type in terms of cardinality and specify business rules (e.g. 1:M between Library and Branch: a library has many branches; a branch belongs to one and only one library).

III. For each entity, identify primary key and foreign key if applicable. If you use surrogate key for PK, also specify business unique key (natural key). For FK, also specify parent entity and the matching attribute in parent entity.

IV. Create ERD using crow's foot notation using ER Assistant or Visio. The ERD should incorporate all items mentioned in I, II, and III (NOTE: I, II, and III still need to be answered separately in narrative format).

Reference no: EM132155163

Questions Cloud

Create a comprehensive training proposal for the business : Analyze key elements of training and development geared toward improving the performance of the specific small business for which you are consulting.
Make environmental protection wasted effort : What is the history of the free-use attitude toward the natural world? How can technology make environmental protection a wasted effort?
Individual prefer to have routine and unenriched job : Under what circumstances might an individual prefer to have a routine and unenriched job?
Discuss the difference between premium and luxury : MDM302 LUXURY & DESIGN ASSIGNMENT - Discuss difference between premium and luxury? Use appropriate theories and facts studied in class to support your arguments
Illustrate the process of normalizing the table : Evaluate if the table shown on the attachments see if it's normalized, if not, describe and illustrate the process of normalizing the table shown.
Juvenile justice system and adult criminal case process : Compare and contrast the juvenile justice system and the adult criminal case process. List and explain two similarities and four differences between them.
Reflect on whether you met the project success criteria : Create a lessons-learned PowerPoint presentation that you would incorporate into a closing ceremony for a healthcare project.
Describe the range of presentation aids : Describe the range of presentation aids and materials available to support effective presentations?
The balanced scorecard management control system : In the context of the feedback control model, which of the following statements is true of the balanced scorecard management control system?

Reviews

Write a Review

Other Subject Questions & Answers

  Cross-cultural opportunities and conflicts in canada

Short Paper on Cross-cultural Opportunities and Conflicts in Canada.

  Sociology theory questions

Sociology are very fundamental in nature. Role strain and role constraint speak about the duties and responsibilities of the roles of people in society or in a group. A short theory about Darwin and Moths is also answered.

  A book review on unfaithful angels

This review will help the reader understand the social work profession through different concepts giving the glimpse of why the social work profession might have drifted away from its original purpose of serving the poor.

  Disorder paper: schizophrenia

Schizophrenia does not really have just one single cause. It is a possibility that this disorder could be inherited but not all doctors are sure.

  Individual assignment: two models handout and rubric

Individual Assignment : Two Models Handout and Rubric,    This paper will allow you to understand and evaluate two vastly different organizational models and to effectively communicate their differences.

  Developing strategic intent for toyota

The following report includes the description about the organization, its strategies, industry analysis in which it operates and its position in the industry.

  Gasoline powered passenger vehicles

In this study, we examine how gasoline price volatility and income of the consumers impacts consumer's demand for gasoline.

  An aspect of poverty in canada

Economics thesis undergrad 4th year paper to write. it should be about 22 pages in length, literature review, economic analysis and then data or cost benefit analysis.

  Ngn customer satisfaction qos indicator for 3g services

The paper aims to highlight the global trends in countries and regions where 3G has already been introduced and propose an implementation plan to the telecom operators of developing countries.

  Prepare a power point presentation

Prepare the power point presentation for the case: Santa Fe Independent School District

  Information literacy is important in this environment

Information literacy is critically important in this contemporary environment

  Associative property of multiplication

Write a definition for associative property of multiplication.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd