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

Option simulation preparation paperby the end of module 2

Option : Simulation Preparation Paper By the end of Module 2, you will have received the URL, team name (also known as logon ID), and password for the Littlefield Simulation site from your instructor. This week, you must ...

Oracle databaseusing the schema you developed in homework 1

Oracle Database Using the Schema you developed in Homework 1 for your e-Commerce project, design and implement a Virtual Private Database that limits database access. Your design should be based on two of your columns an ...

Discussionatleast 1 paragraph for each partpart 1 remote

Discussion Atleast 1 paragraph for each part Part 1: "Remote Access" Please respond to the following: • Remote access to corporate resources is becoming commonplace. From an auditing perspective, suggest two or more cont ...

Assignmentproject descriptionextreme h2o is a company that

Assignment Project Description: Extreme H2O is a company that specializes in water sports. It is considering adding some new experiences for its customers. In a nearby community, there is a small river that can be used f ...

Learning outcomes1 manage user access and user profiles

Learning Outcomes 1. Manage user access and user profiles within a complex DBMS 2. Conduct database audits 3. Develop backup and recovery procedures Read the given scenario and perform all tasks. You have to answer all q ...

Database design discusion post and assignmentdiscusion post

Database Design Discusion Post and Assignment Discusion Post: Data as a Corporate Resource and Foreign Keys Please respond to BOTH of the following questions: Question A Discuss how data can be a corporate resource and d ...

Assignmentthis must be your own unique work you cannot have

Assignment THIS MUST BE YOUR OWN UNIQUE WORK: you cannot have the exact same table as another student and you cannot turn in something you find online!Your work will be checked against a plagiarism database. The format m ...

Save a copy of the tal distributors database located within

Save a copy of the TAL Distributors database located within the Shared Files of the course. You will use this copy to create indexed fields in your database. The current TAL Distributors database only has a few customers ...

Assignment database environment proposalas one of the

Assignment: Database Environment Proposal As one of the program outcomes for the Bachelor of Science in Information Technology program, students must design and develop a database using professional principles and standa ...

Listed the five important software components of a dbms the

Listed the five important software components of a DBMS: the DBMS engine, the data definition, data manipulation, application generation, and data administration subsystems. Which of those are most and least important to ...

  • 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

WalMart Identification of theory and critical discussion

Drawing on the prescribed text and/or relevant academic literature, produce a paper which discusses the nature of group

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