Difference: Normalization (1 vs. 3)

Revision 32011-10-26 - JimSkon

Line: 1 to 1
 
META TOPICPARENT name="CSC3032DatabaseManagement2011"
Changed:
<
<

Database Homework 5

>
>

Database Homework6

 
Normalization Exercise
Due Oct. 7

Revision 22011-09-30 - JimSkon

Line: 1 to 1
 
META TOPICPARENT name="CSC3032DatabaseManagement2011"
Changed:
<
<
<-- @page { margin: 0.79in } P { margin-bottom: 0.08in } -->
>
>

Database Homework 5

Normalization Exercise
Due Oct. 7
 
Changed:
<
<
1. A database is to be designed to keep track of various information about people working for different companies. In particular, each person (P-NAME) lives on a particular street (STREET) in a particular city (P-CITY) . That person may work for several companies (C-NAME) earning a salary (SALARY) at each company. Each company, in turn, is located in one city (C-CITY). Finally there is a certain status (STATUS) associated with different salaries.
>
>
1. A database is to be designed to keep track of various information about people working for different companies. In particular, each person ( P-NAME) lives on a particular street ( STREET) in a particular city ( P-CITY). That person may work for several companies ( C-NAME) earning a salary ( SALARY) at each company. Each company, in turn, is located in one city ( C-CITY). Finally there is a certain status ( STATUS) associated with different salaries. In doing thie exercise use ONLY the attributes already defined, do not add any new ones.
 
Changed:
<
<
This information could be represented in the relation
>
>
This information could be represented in the relation
 
Changed:
<
<
People(P-NAME, STREET, P-CITY, C-NAME, C-CITY, SALARY, STATUS)
>
>
People( P-NAME, STREET, P-CITY, C-NAME, C-CITY, SALARY, STATUS)
 
Changed:
<
<
The functional dependencies are
>
>
The functional dependencies are:
  • NAMESTREET
  • P-NAMEP-CITY
  • C-NAMEC-CITY
  • P-NAME, C-NAMESALARY
  • SALARYSTATUS
a. Explain each of the functional dependencies in English. Do not use the words "determine, depends on or function" in your explanation.
 
Changed:
<
<
P-NAME STREET
>
>
b. What is the key of the relation People?
 
Changed:
<
<
P-NAME P-CITY
>
>
c. Transform the relation People to a set of fifth normal form relations, identifying the key of each of these relations.
 
Changed:
<
<
C-NAME C-CITY
>
>
d. Use Visio to create a ER diagram. Export and paste image into you soilution document.
 
Changed:
<
<
P-NAME, C-NAME SALARY
>
>
2. Consider the following relation:
 
Changed:
<
<
SALARY STATUS
>
>
R = {A, B, C, D, E, F, G, H, I, J}
 
Changed:
<
<
a. Explain each of the functional dependencies in English. Do not use the words "determine, depends on or function" in your explanation.
>
>
And the following functional dependencies
  • A, B → C
  • A → D, E
  • B → F
  • F → G, H
  • D → I, J
a. What is the key for R?
 
Changed:
<
<
b. What is the key of the relation People?
>
>
b. Decompose R into 2NF.
 
Changed:
<
<
c. Transform the relation People to a set of fifth normal form relations, identifying the key of each of these relations. Do the transformation step-by-step, showing each set of relations for 1NF, 2NF, 3BF, BCNF, 4NF and 5NF.
>
>
c. Decompose R into 3NF
 
Deleted:
<
<
2. Consider the following relation:

R = {A, B, C, D, E, F, G, H, I, J}

And the following functional dependencies

A, B C

A D, E

B F

F G, H

D I, J

a. What is the key for R?

b. Decompose R into 2NF.

c. Decompose R into 3NF

3. Consider the following data fields required for Bank Accounts Data.

CustomerNumber

CustomerName

LoanNumber

LoanType

CustomerAddress

AccountNumber

CurrentAccountBalance

AccountType

LoanBalance

MonthlyLoanPayment

InterestRateForAccountType

( Note: AccountType represents the type of account such as Checking, Savings, DailyInterestChequing , etc. )

  1. Define a complete set of appropriate functional dependencies.

  2. Define a complete set of appropriate multi-valued dependencies.

  3. Translate into a BCNF schema.

  4. Draw the ER diagram for the resulting design.

4. Consider the following data fields for a conference database:

GuestSpeaker

Topic

ExpectedTopicLength

RoomNumberForSession

LunchSeatForSpeaker

A/VEquipmentNeededForTopic

ExpectedAttendanceAtSession

Notes:

  1. A speaker may present more than one topic, but a topic is presented by only one speaker.

  2. A topic may be presented many times.

  1. Define a complete set of appropriate functional dependencies.

  2. Define a complete set of appropriate multi-valued dependencies.

  3. Translate into a BCNF schema.

  4. Draw the ER diagram for the resulting design.

5. Why are transitive dependencies and partial dependencies considered bad in a relational schema?

-- JimSkon - 2011-09-28

 \ No newline at end of file
Added:
>
>
d. Use Visio to create a ER diagram. Export and paste image into you soilution document.

Revision 12011-09-28 - JimSkon

Line: 1 to 1
Added:
>
>
META TOPICPARENT name="CSC3032DatabaseManagement2011"
<-- @page { margin: 0.79in } P { margin-bottom: 0.08in } -->

1. A database is to be designed to keep track of various information about people working for different companies. In particular, each person (P-NAME) lives on a particular street (STREET) in a particular city (P-CITY) . That person may work for several companies (C-NAME) earning a salary (SALARY) at each company. Each company, in turn, is located in one city (C-CITY). Finally there is a certain status (STATUS) associated with different salaries.

This information could be represented in the relation

People(P-NAME, STREET, P-CITY, C-NAME, C-CITY, SALARY, STATUS)

The functional dependencies are

P-NAME STREET

P-NAME P-CITY

C-NAME C-CITY

P-NAME, C-NAME SALARY

SALARY STATUS

a. Explain each of the functional dependencies in English. Do not use the words "determine, depends on or function" in your explanation.

b. What is the key of the relation People?

c. Transform the relation People to a set of fifth normal form relations, identifying the key of each of these relations. Do the transformation step-by-step, showing each set of relations for 1NF, 2NF, 3BF, BCNF, 4NF and 5NF.

2. Consider the following relation:

R = {A, B, C, D, E, F, G, H, I, J}

And the following functional dependencies

A, B C

A D, E

B F

F G, H

D I, J

a. What is the key for R?

b. Decompose R into 2NF.

c. Decompose R into 3NF

3. Consider the following data fields required for Bank Accounts Data.

CustomerNumber

CustomerName

LoanNumber

LoanType

CustomerAddress

AccountNumber

CurrentAccountBalance

AccountType

LoanBalance

MonthlyLoanPayment

InterestRateForAccountType

( Note: AccountType represents the type of account such as Checking, Savings, DailyInterestChequing , etc. )

  1. Define a complete set of appropriate functional dependencies.

  2. Define a complete set of appropriate multi-valued dependencies.

  3. Translate into a BCNF schema.

  4. Draw the ER diagram for the resulting design.

4. Consider the following data fields for a conference database:

GuestSpeaker

Topic

ExpectedTopicLength

RoomNumberForSession

LunchSeatForSpeaker

A/VEquipmentNeededForTopic

ExpectedAttendanceAtSession

Notes:

  1. A speaker may present more than one topic, but a topic is presented by only one speaker.

  2. A topic may be presented many times.

  1. Define a complete set of appropriate functional dependencies.

  2. Define a complete set of appropriate multi-valued dependencies.

  3. Translate into a BCNF schema.

  4. Draw the ER diagram for the resulting design.

5. Why are transitive dependencies and partial dependencies considered bad in a relational schema?

-- JimSkon - 2011-09-28

 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback