I have homework on Normalization and I need help to do it

profilefzdaham
3-normalization.zip
Home>Information Systems homework help>I have homework on Normalization and I need help to do it

3-Normalization.doc

Normalization

Please type and print your work. Points: 6,5,5,4 for the following four questions, respectively.

In ALL questions,

- Indicate the types of FDs (i.e., Full, Partial, or Transitive) – -2 if not done;

- Indicate referential integrity after normalization (“curvy arrows” FK ( PK) - -2 if not done

All questions have multiple parts. Please answer ALL parts of the questions, clearly marked.

Please start every question on a NEW page. Thank you!!!

Problems 1 and 2 : Chapter 4 – P. 196, #4-37; P. 198, #4-42. Answer ALL parts of the questions.

In ERDs for these two problems, please do include all the fields (attributes).

Problem 3: Supplementary question 1

Given the following table:

a) Identify FDs and name each FD (in a relational schema – graphical presentation);

b) Perform normalization to 3NF (remember to indicate referential integrity!)

Proj

Code

Proj

Name

ProjMgr

Proj

S_Date

Emp

Num

Emp

Name

Dept

Num

Dept

Name

Hrly

Rate

PC20

E-commerce

Lin

11/11/2013

S111

Hsu

D13

Database

$60

PC20

E-commerce

Lin

11/11/2013

S141

Brin

D12

Network

$50

PC20

E-commerce

Lin

11/11/2013

S411

Asimov

D11

HelpCntr

$48

PC20

E-commerce

Lin

11/11/2013

S151

Hong

D11

HelpCntr

$40

PC31

HR system

Lesky

2/22/2014

S211

Roberts

D11

HelpCntr

$44

PC31

HR system

Lesky

2/22/2014

S221

Zhang

D12

Network

$55

PC31

HR system

Lesky

2/22/2014

S311

Torrence

D13

Database

$66

PC42

Cust contact

Lopez

4/14/2014

S321

Gonzalez

D13

Database

$70

PC42

Cust contact

Lopez

4/14/2014

S111

Hsu

D13

Database

$65

PC42

Cust contact

Lopez

4/14/2014

S411

Asimov

D11

HelpCntr

$45

PC42

Cust contact

Lopez

4/14/2014

S441

Neumann

D12

Network

$72

Problem 4: Supplementary question 2

Analyze the following relation,

a) write the types of functional dependencies; b) point out which normal forms it violates, and

c) perform the normalization to 2NF; then d) normalization to 3NF.

State

GvnrID

GvnrName

St_Flower

City

MayorID

MayorName

CityHallAddr

MayorPhone

Functional dependencies:

State ( Governor ID number; State ( Governor name; State ( State flower

State, City ( Mayor ID number; State, City (Mayor name; State, City (City hall addr

State, City (Mayor telephone – remove! ( Note 2015: Ignore the FD on the left.

Mayor ID number ( Mayor name; Mayor ID number ( Mayor telephone

Notes and helps:

When examining the given tables for functional dependencies, please apply the “thought process” we have demonstrated in class:

· Roughly identify the ”themes” in the table (each will later become independent relations/tables);

· Ask yourself “what kind of relationship there might be” for each functional dependency, such as determining whether a price depends on a product, or a vendor, or both ; quantity-sold depends on an invoice, or a product, or a vendor, or some combination of the above; etc.

· Ask yourself the relationships among data elements (attributes) within a functional dependency, such as

· “What’s the relationship between invoice-number and sale-date?

· Can an invoice-number be related to multiple dates?

· Can a date have multiple invoice-numbers?

· Does a date go with an invoice-number or with a customer?”

· etc.

W the part numbers a, b…

Field names below are all one-word; they just wrapped next line

STICK TO the GIVEN FDs;

Do NOT create your own

problem 1 p196 4-37.pdf

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 1/3

PRINTED BY: khlmo­[email protected]. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. 4­35. Figure 4­34  (page 197 ) shows an EER diagram for a restaurant, its tables, and the waiters and waiting staff managers who work at the restaurant. Your assignment is to:

Figure 4­34 EER diagram for a restaurant

a. Develop a relational schema. b. Show the functional dependencies. c. Develop a set of 3NF relations using an enterprise key.

4­36. Table 4­4  shows a relation called GRADE REPORT for a university. Your assignment is as follows:

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 2/3

Table 4­4 Grade Report Relation

Grade Report

StudentID StudentName CampusAddress Major CourseID CourseTitle Instructor Name

Instructor Location

Grade

168300458 Williams 208 Brooks IS IS 350 Database Mgt

Codd B 104 A

168300458 Williams 208 Brooks IS IS 465 Systems Analysis

Parsons B 317 B

543291073 Baker 104 Phillips Acctg IS 350 Database Mgt

Codd B 104 C

543291073 Baker 104 Phillips Acctg Acct 201 Fund Acctg Miller H 310 B

543291073 Baker 104 Phillips Acctg Mkgt 300 Intro Mktg Bennett B 212 A

a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose GRADE REPORT into a set of 3NF relations. d. Draw a relational schema for your 3NF relations and show the referential integrity constraints. e. Draw your answer to part d using Microsoft Visio (or any other tool specified by your instructor).

4­37. Table 4­5  shows a shipping manifest. Your assignment is as follows:

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 3/3

Table 4­5 Shipping Manifest

Shipment ID: 00­0001 Shipment Date: 01/10/2015

Origin: Boston Expected Arrival: 01/14/2015

Destination: Brazil

Ship Number: 39 Captain: 002­15

Henry Moore

Item Number Type Description Weight Quantity TOTALWEIGHT

3223 BM Concrete 500   100  50,000

Form

3297 BM Steel  87 2,000 174,000

Beam

Shipment Total: 224,000

a. Draw a relational schema and diagram the functional dependencies in the relation. b. In what normal form is this relation? c. Decompose MANIFEST into a set of 3NF relations. d. Draw a relational schema for your 3NF relations and show the referential integrity constraints. e. Draw your answer to part d using Microsoft Visio (or any other tool specified by your instructor).

4­38. Transform the relational schema developed in Problem and Exercise 4­37  into an EER diagram. State any assumptions that you have made.

4­39. For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form.

a. Chapter 3 , Problem and Exercise 3­32 b. Chapter 3 , Problem and Exercise 3­37

 4­40. Transform Figure 2­15a , attribute version, to 3NF relations. Transform Figure 2­15b , relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4­10 . What observations and conclusions do you reach by comparing these different sets of 3NF relations?

problem 2 p198 4-42.pdf

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 1/6

PRINTED BY: khlmo­[email protected]. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. 4­40. Transform Figure 2­15a , attribute version, to 3NF relations. Transform  Figure 2­15b , relationship version, to 3NF relations. Compare these two sets of 3NF relations with those in Figure 4­10 . What observations and conclusions do you reach by comparing these different sets of 3NF relations?

4­41. The Public Safety office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on the campus. Table 4­6  shows a portion of this list for the fall semester. (Attribute names are abbreviated to  conserve space.)

Table 4­6 Parking Tickets at Millennium College

Parking Ticket Table

St ID L Name

F Name Phone No

St Lic

Lic No

Ticket #

Date Code Fine

38249 Brown Thomas 111­ 7804

FL BRY 123

15634 10/17/2015 2 $25

16017 11/13/2015 1 $15

82453 Green Sally 391­ 1689

AL TRE 141

14987 10/05/2015 3 $100

16293 11/18/2015 1 $15

17892 12/13/2015 2 $25

a. Convert this table to a relation in first normal form by entering appropriate data in the table. What are the  determinants in this relation?

b. Draw a dependency diagram that shows all functional dependencies in the relation, based on the sample data shown.

c. Give an example of one or more anomalies that can  result in using this relation.

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 2/6

d. Develop a set of relations in third normal form. Include a new column with the heading Violation in the app ropriate table to explain the reason for each ticket. Values in this column are: expired parking meter (ticket code 1), no parking permit (ticket code 2), and handicap  violation (ticket code 3).

e. Develop an E­R diagram with the appropriate cardinality notations.

4­42. The materials manager at Pine Valley Furniture Company maintains a list of suppliers for each of the material items purchased by the company from outside vendors.  Table 4­7  shows the essential data required for this application.

Table 4­7 Pine Valley Furniture Company Purchasing Data

Attribute Name Sample Value

Material ID 3792

Material Name Hinges 3” locking

Unit of Measure each

Standard Cost $5.00

Vendor ID V300

Vendor Name Apex Hardware

Unit Price $4.75

Terms Code 1

Terms COD

a. Draw a dependency diagram for this data. You may  assume the following: Each material item has one or more suppliers. Each supplier may supply one or more items or may not supply any items. The unit price for a material item may vary from one vendor to another.

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 3/6

The terms code for a supplier uniquely  identifies the terms of the sale (e.g., code 2 means 10  percent net 30 days). The terms for a supplier are the same for all material items ordered from that supplier.

b. Decompose this diagram into a set of diagrams in 3NF. c. Draw an E­R diagram for this situation.

4­43. Table 4­8  shows a portion of a shipment table for a large manufacturing company. Each shipment (identified by Shipment#) uniquely identifies the shipment Origin, Destination, and Distance. The shipment Origin and Destination pair also uniquely identifies the Distance.

Table 4­8 Shipment Relation

Shipment# Origin Destination Distance

409 Seattle Denver 1,537

618 Chicago Dallas 1,058

723 Boston Atlanta 1,214

824 Denver Los Angeles   975

629 Seattle Denver 1,537

a. Develop a diagram that shows the functional dependencies in the SHIPMENT relation.

b. In what normal form is SHIPMENT? Why? c. Convert SHIPMENT to third normal form if  necessary. Show the resulting table(s) with the sample data  presented in SHIPMENT.

4­44. Figure 4­35  shows an EER diagram for Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties.

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 4/6

Figure 4­35  EER diagram for Vacation Property Rentals

a. Transform the EER diagram to a set of relations and  develop a relational schema. b. Diagram the functional dependencies and determine the normal form for each relation.

c. Convert all relations to third normal form, if necessary, and draw a revised relational schema.

d. Suggest an integrity constraint that would ensure that no property is rented twice during the same time interval.

 4­45. For your answers to Problem and Exercise 3­33  from Chapter 3 , transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form.

4­46. Figure 4­36  includes an EER diagram describing a car racing league. Transform the diagram into a relational schema that shows referential integrity constraints (see  Figure 4­5  for an example of such a schema). In addition, verify that the resulting relations are in 3NF.

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 5/6

Figure 4­36 EER diagram for a car racing league

4­47. Figure 4­37  includes an EER diagram describing a publisher specializing in large edited works. Transform the diagram into a relational schema that shows referential  integrity constraints (see Figure 4­5  for an example of such a schema). In addition, verify that the resulting relations are in 3NF.

3/30/2016 Bookshelf Online

https://bookshelf.vitalsource.com/#/books/9780134402109/cfi/6/106!/4/2/8/4/18/2/4/2/2/2/[email protected]:0 6/6

Figure 4­37  EER diagram for a publisher

4­48. Figure 4­38  includes an EER diagram for a medium­size software vendor. Transform the diagram into a relational schema that shows referential integrity constraints (see Figure 4­5  for an example of such a schema). In  addition, verify that the resulting relations are in 3NF.

4­49. Examine the set of relations in Figure 4­39 . What normal form are these in? How do you know this? If they are in 3NF, convert the relations into an EER diagram. What  ­ assumptions did you have to make to answer these questions?