Identify the functional dependencies

Assignment Help Database Management System

Reference no: EM13811191

PART 1:

Given the following table, identify the functional dependencies:

StdSSN	StdCity	StdClass	OfferNo	OffTerm	OffYear	EnrGrade	CourseNo	CrsDesc
S1	SEATTLE	JUN	01	SPRING	2009	3.5	C1	DB
S1	SEATTLE	JUN	02	FALL	2009	3.3	C2	VB
S2	DENVER	JUN	03	SPRING	2010	3.1	C3	OO
S2	DENVER	JUN	02	FALL	2009	3.4	C2	VB

StdSSN - StdCity, StdClass

OfferNo - OffTerm, OffYear, CourseNo, CrsDesc

CourseNo - CrsDesc

StdSSN, OfferNo - EnrGrad

PART 2:

In the table provided below, fill in the following data such that it is completely un-normalized. That is, it is not even in 1NF.

222-33-44444 John Doe Web Design, Database, Intro to MIS, C# Programming

555-66-7777 Jane Doe Prin. Of Accounting, Auditing I, Statistical Analysis

SSN	StudentName	Courses
222-33-44444	Jane Doe	Web Design Database Intro to Mis C# Programming
555-666-7777	John Doe	Prin. OF Accounting Auditing I Statistical Analysis

PART 3:

Using your un-normalized table above, fill in the table below with the data in 1NF.

SSN	StudentName	Courses

PART 4:

Given the following table and functional dependencies, construct tables below that will satisfy 2NF and identify the primary key of each new table. Note that the primary key of this table is StdSSN + OfferNo.

StdSSN	StdCity	StdClass	OfferNo	OffTerm	OffYear	EnrGrade	CourseNo	CrsDesc
S1	SEATTLE	JUN	01	SPRING	2009	3.5	C1	DB
S1	SEATTLE	JUN	02	FALL	2009	3.3	C2	VB
S2	DENVER	JUN	03	SPRING	2010	3.1	C3	OO
S2	DENVER	JUN	02	FALL	2009	3.4	C2	VB

Functional dependencies:

StdSSN → StdCity, StdClass

OfferNo → OffTerm, OffYear, CourseNo, CrsDesc

StdSSN, OfferNo → EnrGrade

PART 5:

Now, assume an additional functional dependency:

CourseNo → CrsDesc

What table above needs to be modified to accommodate this new functional dependency?

PART 6:

Given this new functional dependency, create tables below that will put this table in 3NF.

PART 7:

Identify any foreign keys in Table of part 6.

Attachment:- erd1.erd

Reference no: EM13811191

Questions Cloud

Qualified national or international standards body : What is the certification when a qualified national or international standards body or similar certifying agency audits and certifies that a company is ISO 9000-compliant?

Essential features of a project plan : Which project progress technique includes the essential features of a project plan, budgeted cost of work scheduled and budgeted cost of work performed?

Perpetual system for inventory management : 1. Which of the following is an input file necessary to run an MRP program? 2. Which of the following is a perpetual system for inventory management?

Self-service approach to service design : What happens to production efficiency as the customer exerts more influence on the system? Why do many customers like the self-service approach to service design?

Identify the functional dependencies : Given the following table, identify the functional dependencies: In the table provided below, fill in the following data such that it is completely un-normalized. That is, it is not even in 1NF

Question regarding the major process flow structure : 1. Which of the following is considered a major process flow structure? 2. ISO audits result in certifications that are done by first, second, or third parties. Which of the following result in the best certification of a firm?

What practical implications do you think it could have : Suppose that you were convinced that only objective time is real. Would this reduce your anxieties about aging, dying, or losing what you love? Why or why not?

Essential component of a high-performing organization : Employee morale is an essential component of a high-performing organization. Employees who are not happy with the work environment become, at best, distracted and, at worst, destructive to productivity.

What information from your daily spending records could hel : 1. What information from your daily spending records could help you achieve your financial goals? 2. based on your observations of our society and the economy , what types of stocks might you consider for investing now or in the near future?

User Account

All Pages