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

The use of an access database has become very common over

The use of an Access database has become very common over the last 10 years. Access is the preferred desktop database because server databases such as structured query language (SQL) and others are too complex and are be ...

Relational database systems comp 1005objective this is an

Relational Database Systems (COMP 1005) Objective: This is an individual assignment aimed to give the student exposure in understanding, designing, building and analyzing database systems for a given real-time based scen ...

Create a database to keep inventory of something you own

Create a database to keep inventory of something you own, such as books, CDs, movies, collectibles, etc. You can make these up if you want. (You could use this in real life to keep track of what other people have borrowe ...

Lab modifying a database design in visiothis assignment

Lab: Modifying a Database Design in Visio This assignment contains two Sections: Visio Diagram and Design Summary. You must submit both sections as separate files in order to complete this assignment. Use "Appendix A: De ...

Homeworkgiven the relations below answer the following

HOMEWORK Given the relations below, answer the following queries using SQL. Note that the tables contain sample records only and do not represent all the possible records that could be stored in these tables. MEMB ERS ME ...

Data analysis projectyou have contracted with a local

Data Analysis Project You have contracted with a local school district to help them decide whether to use an interactive computer program or a standard chapter from a textbook to teach students to use fractions. The scho ...

Assignment1write a query that retrieves studentlastname

Assignment 1. Write a query that retrieves student.last_name, student.first_name and total number of enrollments from the student and enrollment tables. Your query should also include students that do not have any enroll ...

Analytical assignment individual or group of max two

Analytical Assignment (Individual or Group of Max two students) What is Research article? Review articles provides summary of current state of the research on a particular topic. Ideally, the writer searches for everythi ...

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

Warehouse management system projectproject deliverablesthe

Warehouse management system project Project Deliverables The project requires students to perform three phases: (a) requirements analysis, (b) system and database design, and (c) a project plan. Note that in the phase 3, ...

  • 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