You are given a following schema in 1NF: ( First , Last , Address, Job , Salary,

Question

You are given a following schema in 1NF: (First, Last, Address, Job, Salary, Assistant) ,

the parimary keys are first, last and job. The following set of functional dependencies:

First, Last ->Address

Job -> Salary, Assistant

Sample data:

John, Smith, 111 N. Wabash Avenue, plumber, 40K, NULL

John, Smith, 111 N. Wabash Avenue, bouncer, 35K, NULL

Jane, Doe, 243 S. Wabash Avenue, waitress, 52K, NULL

Jane, Doe, 243 S. Wabash Avenue, accountant, 42K, Yes

Jane, Doe, 243 S. Wabash Avenue, bouncer, 35K, NULL

Mike, Jackson, 1 Michigan Avenue, accountant, 42K, Yes

Mike, Jackson, 1 Michigan Avenue, plumber, 40K, NULL

Mary, Who, 20 S. Michigan Avenue, accountant, 42K, Yes

Mary, Who, 20 S. Michigan Avenue, risk analyst, 80K, Yes

Decompose the schema to make sure it is in Third Normal Form (3NF).

Also we have to write SQL DDL for it...how would you write a create statement for SALARY to add the K in it for example Jobs would be something like this -CREATE TABLE Job ( JOB VARCHAR(20) NOT NULL;

Explanation / Answer

First, Last ->Address

Job -> Salary, Assistant

Tables in 3NF

PERSON(First,Last,Address)

JOBDETAILS(Job,Salary,Assistant,First,Last)

underlined attributes are primary keys and italicised are foreign key attributes

DDL

Create Table PERSON

(

First varchar(30) NOT NULL,

Last varchar(30) NOT NULL,

Address varchar(60) NOT NULL,

Job varchar(20) NOT NULL,

Primary Key(First,Last)

);

Create Table JOBDETAILS

(

Job varchar(20) NOT NULL,

Salary int NOT NULL,

Assistant varchar(3) NOT NULL,

Primary Key(Job),

Foreign Key(First,Last) REFERENCES PERSON(First,Last)

);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.