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

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 ...

Unlimited pickers is a group of workers who have joined

Unlimited Pickers is a group of workers who have joined together to provide harvesting services to farmers who need to have their crops brought in. The organization has many teams of workers who travel from location to l ...

Database management assignmentyou have been hired by

Database Management Assignment You have been hired by National Bank as their new database administrator. Your first task is to upgrade the bank's outdated computer system and implement a relational database management sy ...

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 ...

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 ...

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 ...

Projectinstructionsfor the purpose of grading the project

Project Instructions: For the purpose of grading the project you are required to perform the following tasks: Instructions 1 Open the start file Day Spa that you recently downloaded then Save the workbook as Lastname_Fir ...

Question - databasesuse database software such as microsoft

Question - Databases Use database software such as Microsoft Access or LibreOffice Base to perform the following actions. (Refer to Workshops 9 & 10 for help.) A. Create a new database. Add tables based on the design of ...

Hospital database determining keysdevelop a microsoft

Hospital Database: Determining Keys Develop a Microsoft Access database based upon the following business scenario. Be sure to include tables, fields, keys, relationships, and test data in your database. Your final submi ...

1 what is the most popular database model2 how are primary

1. What is the most popular database model? 2. How are primary and foreign keys different? 3. What are the five important software components of a database management system? 4 What are the four major types of data-minin ...

  • 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