Draw the dependency diagram and identify all dependencies

Assignment Help Database Management System

Reference no: EM131961968

Assignment

Question 1

Sales

CUS_ID	Name	Ord_NO	Ord_Date	Prod_ID	Description	Qty_Ord
C001	Gold	O81	15-Apr	P005	Chisel	6
C001	Gold	O81	15-Apr	P004	Plane	14
C075	Red	O99	16-Apr	P015	Saw	3
C009	Blue	O56	16-Apr	P033	Punch	24
C009	Blue	O56	16-Apr	P004	Plane	9
C001	Gold	O88	17-Apr	P015	Saw	10

Using the above Sales table structure, perform the followings:

Exercise 1:

Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You may assume that an order number references more than one product and that the table does not contain repeating groups.

Exercise 2:

Write the relational schemas after removing all partial dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 3:

Write the relational schemas after removing all transitive dependencies and draw the new dependency diagrams. Identify the normal form for each created table structure.

Exercise 4:

Draw the Entity Relationship Diagram (ERD) according to the result of Exercise 3.

Question 2:

You are given the following table structure with sample data in it.

PET ID	PET NAME	PET TYPEPET AGEOWNERVISIT DATE PROC_ID PROCEDURE
246	ROVER	DOG	12	SAM COOK	JAN 13/2002 01 RABIES VACCINATION
					MAR 27/2002 10 EXAMINE and TREAT WOUND
					APR 02/2002 05 HEART WORM TEST

298	SPOT	DOG	2	TERRY KIM	JAN 21/2002 08 TETANUS VACCINATION
					MAR 10/2002 05 HEART WORM TEST

341	MORRIS	CAT	4	SAM COOK	JAN 23/2001 01 RABIES VACCINATION
					JAN 13/2002 01 RABIES VACCINATION

519	TWEEDY	BIRD	2	TERRY KIM	APR 30/2002 20 ANNUAL CHECK UP
					APR 30/2002 12 EYE WASH

Exercise 1:

Draw the dependency diagram of the table, and normalize the table to ensure all generated tables are in 3NF. Present all tables generated from the normalization.

Exercise 2:

Write SQL code to create the generated tables.

Reference no: EM131961968

Questions Cloud

Growing focus on e-commerce : The past few years. Business-to-consumer (B2C) e-commerce spending surpassed SAR 29.7 billion in 2016.3 Saudi Arabia's young population

Which of fall operating segments would be considered : Which of Fall's operating segments would be considered reporting segments under the "revenue" test and the "asset" test

What is the purpose of martial law : What would have to be the magnitude of an event for the government to decide to initiate martial law? Why?

How the fluctuation of crude oil affect : How the fluctuation of crude oil affect (1) the various aspects of the exchange process (the market, the agents involved, the equilibrium outcome).

Draw the dependency diagram and identify all dependencies : Write the relational schema for the above table, draw the dependency diagram, and identify all dependencies, including all partial and transitive dependencies.

How might you defend your trace evidence : In court, the Defense challenges your findings in this case as circumstantial. How might you defend your trace evidence?

Aargue your opinion for preventing arson dog findings : Arson dogs are useful tools for finding accelerants in locations where human investigators cannot.

Research the findings of the ucr and nibrs : Criminologists use crime data to measure the nature and extent of criminal behavior, as well as the personality, attitudes, and background of criminal offenders

Discuss the threats that stgs pose to correctional agencies : Identify at least two specific issues that correctional administrators are facing as this segment of the inmate population continues to expand.

User Account

All Pages