Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Normalization

1. Determine the set of minimal functional dependencies for the following data.

The local bakery, Happy Cakes, needs your help designing a database that will support a kiosk in the store for customers to make special orders.  Orders are limited to cakes and cannot include cookies, pies, or pastries.  Several flavours each of cake and frosting are available, and they can be made in a number of different sizes and shapes.  Combinations of cake and frosting flavours are considered cake styles, and there will be a photograph of each style.  All photographs will be taken with one piece cut from the cake, in order to make the interior visible. All cakes can be customized with decorations and messages (one type of decoration and one message per cake) which have a main color as requested by the customer. Each order will include customer and cake information as well as the date that the customer needs the cake.

Data:

cakeFlavor: the flavor of the cake
frostingFlavor: the flavor of the frosting
style: one of the typical combinations of cake and frosting flavors
picture: the filename of the picture of the cake style
shape: the shape of the cake
size: the size of the cake (diameter for rounds, length-by-width for rectangles)
layers: the number of layers
decoration: the type of decorations on the cake
color: the main color of the cake’s decorations
message: the message that will be written across the finished cake |
orderNumber: the identification number for an order
orderDate: the date that the cake is needed
customerName: the customer’s name (only one customer allowed per order)

2. Determine the minimal set of functional dependencies for the following data.

This table is part of the OHSU database of extracurricular activities and tracks the DMICE band’s concerts for charities.  The DMICE band performs many concerts each year (they're in high demand!).  For each concert, a faculty, staff, or student member of the department volunteers to direct the organization of the concert, and a different member of the department volunteers to be the featured performer (thus lessening the burden of practicing time and effort on the other members of the band).  Concerts are held both in Portland and its suburbs, and concerts may be given names.There is never more than one concert a day."   Sometimes the concert names are used more than once, but always for concerts held in the same city/suburb (i.e. the "Beaverton Bash" held in Beaverton in 2012 and the "Beaverton Bash" held in Beaverton in 2013). Each concert benefits a charity, and the concert director coordinates with a contact person from that charity.  Charities may be benefitted more than once, and the contact person for a charity may vary with each benefit, but there will be only one for each performance.

Data:

concertName: the name of the concert
concertDate: the date of the concert
concertCity:the Portland suburb where the concert is held  
charityName: the name of the charity benefitted by the concert
charityContact: the name of the contact for the charity for the specific performance
charityPhone: the phone number of the charity contact person
concertDirector: the name of the person organizing the concert 
musicianName: the name of a participating musician 
primaryInstrument: a musician’s primary instrument  
secondaryInstrument: a musician’s secondary instrument   
concertFeaturedMusician: the participant featured in the concert

For problems 3-6, give all candidate keys for the tables with FDs specified.  Composite candidate keys must be put in parentheses, and candidate keys must be separated with commas or otherwise clearly specified.  For ex, "A, B, C"  means that there are three candidate keys, but "(ABC)" means that there is one composite candidate key and "(ABC), E" means that there are two candidate keys, the composite of three colums (A B C) plus the single column E.  Please be very specific for full credit. 

3. What is (are) the candidate key(s) for a table with columns

A B C D E F G H I J
and the following set of functional dependencies? 
A B → J
D → B
J→ C E F G H I

4. What is (are) the candidate key(s) for a table with columns

L M N O P Q
and the following set of functional dependencies? 
L → M N
O → L P Q

5. What is (are) the candidate key(s) for a table with columns

A B C D E F G H I
and the following set of functional dependencies?
A →C D E F
E→G H I

6.What is (are) the candidate key(s) for a table with columns

M N O P Q R S T U

and the following set of functional dependencies?

M →N O P
O P →M
N → Q R S T

In problems 7 – 10, you are given the columns of a table, and a set of functional dependencies.  Determine the normal form of this table.  Remember that the normal form is the HIGHEST normal form for the table (e.g. if a table is in both 1st NF and 2nd NF, then what you report is 2nd NF).  Always start by identifying the candidate keys.

7. What is the highest normal form of the table with columns

A B C D E
and the following functional dependencies?
A → B
B → A C D E

8. What is the highest normal form of the table with columns

M N O P Q R S T
and the following functional dependencies?
N → O
P→ M N Q R
Q→S T

9.  What is the highest normal form of the table with columns

A B C D E F G
and the following functional dependencies?
A E → D
D → C G
E → B F

10. What is the highest normal form of the table with columns

A B C D E F G H
and the following functional dependencies?
A → H
B → A C D E F

11. Decompose the table with the following description and functional dependencies into a set of equivalent tables normalized to 3NF or BCNF.

This database stores records from a (very organized) Crazy Cat Person.  It contains vital statistics and visual identifying information about the cats.  It also contains information about the cats’ vet(s) and any supplements or medications shehas prescribed.  It only tracks current medications and supplements.

Data:

catName: the name of a cat
catBirth: the cat’s date of birth, or an estimate thereof   
catAdoption: the cat’s date of adoption by the Crazy Cat Person
color: the main color of the cat’s fur
furPattern: the pattern of the cat’s fur (i.e. “striped”)           
furType: the type of the cat’s fur (long or short)
vetName: the name of the vet who cares for the cat and prescribes supplements or medications
office: the name of the vet’s office
address: the address of the vet’s office
phone: the vet's phone number
supplement: the name of a prescribed supplement or medication
dose: the prescribed unit dosage of a supplement/medication
sig:the dosing recommendations, i.e. how many of the unit doses, how often, and by what route is the supplement/medication to be given

Functional dependencies:

catName→catBirth, catAdoption, color, furPattern, furType
vetName→office, address, phone  
supplement, catName→dose, sig, vetName

12. Decompose the table with the following description and functional dependencies into a set of equivalent tables normalized to 3NF or BCNF.

A long-standing international treaty among the leaders of the world’s developed countries prevents any and all evidence of extraterrestrial life that has been discovered from being reported to the people of Earth.  Although each country dedicates many federal agencies to furthering the country’s interests, in the case of contact with extraterrestrial life, all relevant agencies of the world have agreed to cooperate.  They have developed a database in which information pertaining to extraterrestrial life encounters is recorded.  Much of the data is the identification information about those involved in an instance of contact.  Multiple humans and multiple aliens (assume these are all of one species in a single encounter) may be involved in a single encounter, and sometimes the same human will be involved in more than one encounter.  Encounters are classified in a standardized manner.  The final outcome(s) of the human participant(s) in the encounter is (are) also recorded.  Students of social psychology around the world whisper that this final outcome is a government-created and enforced disposition of the most sinister sort.

Data:

governmentID: the international identification for a government official who is responsible for information on an encounter
governmentOfficialName: the name of a participating government official       
securityClearanceRating: a government official’s international security clearance  
agency: the federal agency for which the government official works                        
country: a government official’s country of citizenship                
alienID: the identification for an extraterrestrial life form                                      
alienSpeciesName: the name of an extraterrestrial life form’s species             
homePlanet: the planet or solar system + planet from which an extraterrestrial life form comes
communicationAbility: the level at which an extraterrestrial life form can communicate
victimID: the identification assigned to a human who encounters extraterrestrial activity
victimName: the name of the human who encountered extraterrestrial activity
victimDateOfBirth: the birthdate of the human who encountered extraterrestrial activity
victimDisposition: the final outcome of the human after an encounter             
encounter: the identification assigned to any instance of alien-human contact
encounterDate: the date an instance of contact occured
encounterType: the type of alien-human contact which occurred

Functional dependencies:

governmentID→governmentOfficialName, securityClearanceRating, agency, country
alienID→alienSpeciesName, homePlanet, communicationAbility
victimID→victimName, victimDateOfBirth
victimEncounter→victimID, encounter, victimDisposition
alienEncounter→alienID, encounter
encounter→encounterDate, governmentID, encounterType

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M91691

Have any Question? 


Related Questions in DBMS

Assignmentthis assignment consists of two sections a design

Assignment This assignment consists of two sections: a design document and a revised Gantt chart or project plan. You must submit both sections as separate files for the completion of this assignment. Label each file nam ...

Fundamentals of databases assignment 1 -q1 explain the

Fundamentals of Databases Assignment 1 - Q1. Explain the distinction between total and partial constraints. Q2. Explain the difference between a weak and a strong entity set. Q3. Explain the distinction between disjoint ...

Assignmentdeliverablesall files are located in the

Assignment Deliverables All files are located in the subdirectory of the project. The project should function as specified: When you visit the Personnel form page (frmPersonnel.aspx), a record should be saved in the tblU ...

Question 1use the tables below to answer the following

Question 1: Use the tables below to answer the following questions: customerid customerid firstname lastname city state 10101 John Gray Lynden Washington 10298 Leroy Brown Pinetop Arizona 10299 Elroy Keller Snoqualmie Wa ...

Tasks objectives1 manage client-server connections using

Tasks Objectives: 1. Manage client-server connections using RDBMS Net Services 2. Evaluate requirements and select appropriate parameters to ensure effective storage management in a database system 3. Manage Redo log inf ...

Exercise 1 - html and Exercise 1 - HTML and XML ID Name Stars Facilities Address Distance

Exercise 1 - HTML and XML ID Name Stars Facilities Address Distance from center (KM) Available 1 Les Jardins du Marais 3 Internet 74 rue Amelot, Paris, 75011 2 True 2 Golden Tulip Little Palace 4 Internet Gym Parking Res ...

Capstone project purpose of the assessmentthe purpose of

Capstone Project Purpose of the assessment: The purpose of this assignment is to identify new learning, self realisation and plan for improvement. Students will have an opportunity to explore the use of the online tools ...

Data information decision please respond to the

"Data Information Decision" Please respond to the following: • With the obvious pace of current technology development, data continues to grow daily. Imagine that you are a Database Administrator for a large organization ...

Lab creating a database designthis assignment contains two

Lab: Creating a Database Design This assignment contains two Sections: Database Design Diagram and Design Summary. You must submit both sections as separate files in order to complete this assignment. Note: If you are us ...

A task descriptionoverviewyour task is to create and test a

A. Task Description Overview Your task is to create and test a database in MySQL using PhpMyAdmin. You are provided with a scenario and supporting documents, describing the requirements that Southern Cross University may ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Section onea in an atwood machine suppose two objects of

SECTION ONE (a) In an Atwood Machine, suppose two objects of unequal mass are hung vertically over a frictionless

Part 1you work in hr for a company that operates a factory

Part 1: You work in HR for a company that operates a factory manufacturing fiberglass. There are several hundred empl

Details on advanced accounting paperthis paper is intended

DETAILS ON ADVANCED ACCOUNTING PAPER This paper is intended for students to apply the theoretical knowledge around ac

Create a provider database and related reports and queries

Create a provider database and related reports and queries to capture contact information for potential PC component pro

Describe what you learned about the impact of economic

Describe what you learned about the impact of economic, social, and demographic trends affecting the US labor environmen