Term Project Database Normalization

Content type

User Generated

Subject

Databases

Type

Homework

Uploaded By

583pncrcc

Pages

Rating

Team Project – Week 8

1. Review the existing report, functional dependencies, and sample data (consider field

domains and common knowledge) then document any assumptions you feel are

appropriate (beyond those in the key points) and identify the initial entities. (10%)

ANSWER 1: Based on the available report and the given functional dependencies, we can see

that two main “sustantives” live in our design: blood draw and patient. There are, of course,

acceptable sublayers for each of this categories like Dates or Vials, but since no added

complexities should be considered, I can identify the following entities:

• Draw (DrawNum, Date, Nvials)

• Patient (PatID, fName, lName, DOB)

2. Explain functional dependency and describe each row of the functional

dependencies provided above (use field names and values). (15%)

(you do not need to describe full, partial or transitive dependency in this task)

ANSWER 2: Functional dependencies are a method to establish the existing relationship

between attributes within the design of a database table. Functional dependencies are not

considered between tables, but within them.

Its symbolized with an arrow: A->B, for example means that “B is functionaly

dependent on A” and it satisfies both reflexivity and transitivity.

In layman terms, the explanation of what functional dependency means, is that it

allows us to guarantee that if two sets of elements (called A and B) are related like A->B, by a

functional dependency then it is not possible for an entry to have the same value of A and

different value of B. A classical example is govIdNumber->givenName . Clearly, if two people

have the same government id number, then they must have the same name. In database

design, finding an attribute (or a set of attributes) A which functionaly determine all of the

other attributes in a table helps us to identify candidate primary keys. It also makes it easier to

see where a table should be “split” (or factorized) in two or more different tables.

For the functional dependencies given in our initial report, we can describe them as follows.

DrawNum

→

Date,Nvials,PatID,fName,lName,DOB: This means that our “DrawNum” (an unique

code assigned to each event of drawing blood) functionally determines the date, the number

of vials that have been used, the ID of the patient whose blood was drawn, his full name and

his date of birth. It is not possible for two entries to have the same DrawNum but different

values in any of the mentioned attributes.

PatID

→

fName,lName,DOB: This means that the attribute “ParID” functionally determines the

fName, the lName and the DOB of each patient. It is not possible, thus, to have two patients

with the same ID and different name or date of birth.

As we can see, we have transitive dependencies in this table (PatID is dependent on DrawNum,

but fName, lName and DOB depend on PatID) so addressing that issue will be our first step

towards normalization.

3. Based on multiplicity – in plain English explain the relationship between the Entities

provided in the above description – (either one-to-many, or many-to-many). (15%)

ANSWER 3: The multiplicty of a relationship between entitites represents how many of each of

them can -or must- be related among themselves.

For our example, we can see that one Patient might be related to one or many blood draws, so

the relationship. However, one blood drawn must be related to exactly one patient.

So Patient→Draw is one-to-many; Draw→Patiend is many-to-one .

4. Design: all tables and fields must be specified at each normal form level

a) First Normal Forum (1NF) assessment / action 10%

- Copy the 1NF definition from the text (include quotes and page number)

- Assess the UNF table provided and if necessary, make the changes needed to

conform to the 1NF definition. Explain any action taken and why (use field names and

values).

- Specify all 1NF table(s) using relational schema notation or spreadsheet format (see -

the above example or page 111 Figure 4.2.6 of the text). Ensure primary keys are

identified.

ANSWER 4.a:

From the given textbook:

“1NF: A relation in which the intersection of each row and column contains one and only one

value.“ PAGE 418

The relationship is already in 1NF, since there are no multivalued attributes and there is an

unique denomination for each of them.

Our relational schema is thus:

End of Preview - Want to read all 6 pages?

Access Now

Unformatted Attachment Preview

Team Project – Week 8 1. Review the existing report, functional dependencies, and sample data (consider field domains and common knowledge) then document any assumptions you feel are appropriate (beyond those in the key points) and identify the initial entities. (10%) ANSWER 1: Based on the available report and the given functional dependencies, we can see that two main “sustantives” live in our design: blood draw and patient. There are, of course, acceptable sublayers for each of this categories like Dates or Vials, but since no added complexities should be considered, I can identify the following entities: • • Draw (DrawNum, Date, Nvials) Patient (PatID, fName, lName, DOB) 2. Explain functional dependency and describe each row of the functional dependencies provided above (use field names and values). (15%) (you do not need to describe full, partial or transitive dependency in this task) ANSWER 2: Functional dependencies are a method to establish the existing relationship between attributes within the design of a database table. Functional dependencies are not considered between tables, but within them. Its symbolized with an arrow: A->B, for example means that “B is functionaly dependent on A” and it satisfies both reflexivity and transitivity. In layman terms, the explanation of what functional dependency means, is that it allows us to guarantee that if two sets of elements (called A and B) are related like A->B, by a functional dependency then it is not ...
Purchase document to see full attachment

Tags: sample data FUNCTIONAL DEPENDENCIES Multivalued attributes transitive dependencies relational schema notation