Ask Question, Ask an Expert

+1-415-315-9853

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

For each of the following problems, give the revised table definition.  You should support your definitions with the explanation of why you made the changes. Explanation should be specific to current problem, not just a general explanation.

For EACH and EVERY table (including intermediate ones) you SHOULD underline the primary key AND list all the FDs.

problem1)

Given the following table definition and FD constraints.

Shipment (ShipNum, ShipperName, ShipperContact, ShipperFax, PackageNum, PackDepartureDate, PackArrivalDate, PackDestinaltionCity, PackShipCost, InsuranceValue, InsurerCompany, InsurerAddress)

FDs = {ShipNum → ShipperName, ShipperContact, ShipperFax
PackageNum → PackDepartureDate, PackArrivalDate, PackDestinationCity, PackShipCost, InsuranceValue, InsurerCompany InsurerCompany → InsurerAddress }

Notes: A shipper can be shipping more than one package but each package has only one shipper.
a) Based on FDs above, what is the primary key of the table?
b) Is the table definition in 1NF? Why or why not? If not, convert the table to 1NF table(s).
c) Is/are the table(s) in 2NF? Why or why not? If not, convert the table(s) to table(s) that are in 2NF. describe why they are now in 2NF?
d) Are the tables in 3NF? Why or why not? If not, convert table to 3NF. describe why they are now in 3NF?
e) List all of the foreign keys in problem 1d. Identify them by table name and state which table(s) and corresponding attribute(s) they relate to

problem2) NIU trucking wants to keep track of all of its truck and their base cities.  Given the following relation, produce a normalized set of relations which are normalized up through 3rd normal form.

TRUCK (TruckNum, TruckType, TypeDesc, TruckMiles, DatePurchased, TruckSerialNum, BaseCity, BaseState, BasePhone, BaseManagerName, ManagerPhone, BasePhone)

Business rules:
•    A truck is based at a single base.
•    A base can be the base for many trucks.

a) List ALL FDs for the table which hold based upon the descriptions of the constraints above.
b) Is the table definition in 1NF?  Why or why not?  If not, convert the table to 1NF table(s).
c) Is/are the table(s) in 2NF?  Why or why not?  If not, convert the table(s) to table(s) that are in 2NF.  describe why they are now in 2NF? 

d) Are the tables in 3NF?  Why or why not?  If not, convert the table to 3NF.  describe why they are now in 3NF?

e) List all of the foreign keys in problem 2d.  Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

problem3) 

Ace Manufacturing constructs products for sale.  Each product is identified by a serial number.  Each product is constructed from many other parts which are purchased from a variety of vendors.  Given the following relation, produce a normalized set of relations which are normalized up through 3rd normal form.  Note: in the following relation the prefix Prod indicates Product, Comp indicates Component and Vend indicates Vendor.

Manufacture(ProdSerialNum, ProdName, ProdType, ProdTypeName, Component(CompSerialNum, CompType, CompName, Vendor(VendCode, VendName, VendAddress, CompPrice)* )*, ProdPrice)

Business Rules:
•    Ace Manufacturing constructs many products.
•    A product is classified as a certain type.
•    A product is made up of components. 
•    Each component can be supplied by many different vendors.
•    A vendor’s components may be found in many different products.
•    Each vendor may charge a different price for the same component.

a) List ALL the FDs for the table that hold based upon the descriptions of the constraints above.
b) Is the table definition in 1NF?  Why or why not?  If not, convert the table to 1NF table(s).
c) Is/are the table(s) in 2NF?  Why or why not?  If not, convert the table(s) to table(s) that are in 2NF.  describe why they are now in 2NF? 
d) Are the tables in 3NF?  Why or why not?  If not, convert the table to 3NF.  describe why they are now in 3NF?
e) List all of the foreign keys in problem 3d.  Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

problem

Given the following table definition and constraints:
R (A, B, C, D, E, F, G, H, I, J)

    FD = {A,B,C,D     → E, F, G, H, I J
             B, C, D       → E, F
             A                → G, H, I, J
             E                → F
             H, I            → J }

a) Based on the FDs above, what is the primary key of the table?
b) Is the table definition in 1NF?  Why or why not?  If not, convert the table to 1NF table(s).
c) Is/are the table(s) in 2NF?  Why or why not?  If not, convert the table(s) to table(s) that are in 2NF.  describe why they are now in 2NF? 
d) Are the tables in 3NF?  Why or why not?  If not, convert the table to 3NF.  describe why they are now in 3NF?
e) List all of the foreign keys in problem 4d.  Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

DBMS, Programming

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

Have any Question? 


Related Questions in DBMS

Database individual assignmentdesign a database entity and

Database: Individual Assignment Design a database (entity and relationship tables in Access) for a small college department library. You should have the entities and relationships you need to support: Purchase books Sell ...

The purpose of the veterinary hospital registration system

The purpose of the Veterinary Hospital Registration system is to handle and record all the information relating to the clients, veterinarians, pets, treatments administered, insurance coverage and appointments (open, clo ...

Database illustrationwritting from scratchdirections be

Database Illustration Writting from scratch. Directions: Be sure to make an electronic copy of your answer before submitting it to Ashworth College for grading. Unless otherwise stated, answer in complete sentences, and ...

Systems and database design assignment database design amp

Systems and Database Design Assignment: Database Design & Implementation (Airline) The assignment consists of two tasks. The first task, Database Design, requires a word processed document in PDF format detailing the des ...

Your assignment should be submitted in a single word doc

Your assignment should be submitted in a single Word doc (.doc or .docx). It should include your scenario diagram for Problem 1, and your Ruby source code for Problems 2 and 3. Contact me or post to our discussion board ...

Database reflectionuse the attached database and associated

Database Reflection Use the attached database and associated sheets to review and to make sure the forms are working. Review your project; make sure that all the forms are working. Write a one page report on how you coul ...

Coit20247 database design and development assignmentyou

COIT20247 Database Design and Development Assignment You must use that sample solution of assignment only for the following tasks: Assessment task- 1. Normalization a) Map the ERD, from the sample solution, into a set of ...

Part aexercise 1 data scienceread the article at

Part A Exercise 1: Data Science Read the article at http://datascience.berkeley.edu/about/what-is-data-science/ and answer the following: What is Data Science? According to IBM estimation, what is the percent of the data ...

Overviewfor this assignment you will implement and query a

Overview For this assignment you will implement and query a database from a supplied ER Diagram and Schema. You will be required to write the SQL statements to create the database structures, to fill the database with da ...

Database design and use group assignmentassignment

Database Design and Use Group Assignment Assignment Description - In this assignment you will use your normalized "City Jail" database you have created in your assignment one. You will form a group (2-3 students) and imp ...

  • 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

A cola-dispensing machine is set to dispense 9 ounces of

A cola-dispensing machine is set to dispense 9 ounces of cola per cup, with a standard deviation of 1.0 ounce. The manuf

What is marketingbullwhat is marketing think back to your

What is Marketing? • "What is marketing"? Think back to your impressions before you started this class versus how you

Question -your client david smith runs a small it

QUESTION - Your client, David Smith runs a small IT consulting business specialising in computer software and techno

Inspection of a random sample of 22 aircraft showed that 15

Inspection of a random sample of 22 aircraft showed that 15 needed repairs to fix a wiring problem that might compromise

Effective hrmquestionhow can an effective hrm system help

Effective HRM Question How can an effective HRM system help facilitate the achievement of an organization's strate