INFO 321 Case Study Week 6

User Generated

Yvfun

Business Finance

Description

Assignment Instructions

Using the information in the figure below, answer the following questions:

a) Define functional dependency – you only need to include one-to-one relationships. Use plain English and explain the concept and how it applies to this exercise. You may use the fields in this exercise to aid your explanation.

Using the functional notation shown on pages 412, 413 Examples 14.4, 14.5 identify the functional dependencies that exist between the attributes in the below figure. All attributes should be included. There may be more than one row of functional notation needed.

The sample data in the spreadsheet is not representative of “all” possible data, use your common knowledge of cars and owners to finalize the functional dependencies. Consider the pool of possible values for each field, this is called a domain.

b) Identify a primary key for the table in the figure and indicate whether there are any other candidate keys (for this table) – explain your choices.<o:p>

c) Is the table in 3NF? If not, explain why – (if you reference transitive dependency, you must provide a specific example, using field names and values in the table to demonstrate your understanding). Explain what normal form the existing table is in.

Each car can be owned by only one person; each person can own many cars.


<v:shapetype> <v:stroke> <v:formulas> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:f> <v:path> <o:lock> <v:shape> <v:imagedata>
Include each question with your answer. Submit a Word compatible document. Name your document Last Name_Assignment6 (i.e. Smith_Assignment6).


Please No Plagiarism

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.

Surname1

Name:
Professor:
Course:
Date:
HosiA-Case study INFO 321
1).Review the existing spreadsheet, and their sample data and make any assumptions you
need about the company. Make any comments about the approach that you are going to
follow, as you see fit. (10%)
Immediately after looking at the data that has been presented, I would want to devide the
spreadsheet into two tables. The name of my database is called HosiA-Case study INFO 321.
There are two tables existing within the database i.e, H-ID and H.St
2).Submit “one” complete functional dependency analysis (all fields must be included at
least once), use the functional notation shown on pages 410-413 Examples 14.2, 14.3, 14.4
and 14.5 to document the dependencies. Do not submit functional dependencies for each
normal form – only one set of functional dependencies are required.
A functional dependency is the constraint attributes which are in relation to each other from
within the database
1NF
Degree, H-ID, H-St, LastName
H- City H-Tel
H-St, H-City
H-St

H-City

LastName,NurseID

Surname2

2NF
Degree, H-ID, H-St, LastName
H-St, H-City
H-St,

LastName, NurseID

H-City

H-City→H-Tel
3NF
Degree,H-ID, H-St, LastName
H-St

H-City

H-St

H-Tel

3) Explain the relationship between the tables – consider the multiplicity (one-to-many,
many-to-many). (10%)
I preferred using Microsoft Access as my DB on both tables. Both the table are H-ID-H-City and
H-ID –H-St. The purpose for these tables is to track the residential places for the Nurses.
Another function is to know the qualification of the Nurses

4) Submit a complete set of 1NF, 2NF, and 3NF tables (ensure you label and identify 1NF,
2NF and 3NF tables). Ensure a complete set of tables (and fields) are included for each
normal form (repeat the same table if appropriate, I expect to see a complete set of tables
for each normal form level) (30%)

Surname3

1NF (First Normal Form)

No primary key
NurseID LastName Degree H-ID
N01003

Smith

LPN

H-St

H-Tel

Hos100 South.St 555,878,
1234

N01006

Garcia

CNS

Hos200 North.St 555,888,8765

N01004

Rosenberg

RN

Hos300 East.St

555,656,4567

2NF (Second Normal Form) .part.a
NurseID

LstName

Degree

H-ID

H-St

N01003

Smith

LPN

Hos100

South.St 555,878,1234

N01004

Rosenberg

RN

Hos300

East.St

N01006

Garcia

CNS

Hos200

North.St 555,888,8765

Part.b2NF (Second Normal Form) .part.b
NurseID LstName

Degree H-ID

N01005

Jones

NP

N01007

Rosenberg APN

H-St

H-Tel

Hos100 South.St 555,878,1234
Hos200 North.St 555,888,8765

H-Tel

555,656,4567

Surname4

Table Number 2: H-ID
Primary Key: Degree
3NF (Third Normal Form)
Degree H-ID

H-St

H-Tel

LPN

Hos100 South.St 555,878,1234

RN

Hos300 East.St

CNS

Hos200 North.St 555,888,8765

555,656,4567

User table primary key:NurseID
NurseID

LastName

Degree

H-City

N01003

Smith

LPN

Miami

NO1004

Rosenberg RN

Miami

N01005

Jones

NP

Miami

NO1006

Garcia

CNS

Miami

Surname5

Table: R-US-Sales
PrimaryKey:use...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Similar Content

Related Tags