Question

In: Computer Science

1. To keep track of vendors and products they supply, XYZ Corp. uses the table structure...

1. To keep track of vendors and products they supply, XYZ Corp. uses the table structure shown below. Assuming that the sample data are representative, draw a dependency diagram in Visio that shows all functional dependencies including both partial and transitive dependencies. (Hint: Look at the sample values to determine the nature of the relationships.)

PART_CODE

PART_DESC

VEND_NAME

VEND_ADDRESS

VEND_TYPE

PRICE

1234

Logic Chip

Fast Chips

Cupertino

Non-profit organization

25.00

1234

Logic Chip

Smart Chips

Phoenix

Non-profit organization

22.00

5678

Memory Chip

Fast Chips

Cupertino

Profit organization

18.00

5678

Memory Chip

Quality Chips

Austin

Profit organization

15.00

5678

Memory Chip

Smart Chips

Phoenix

Profit organization

19.00

2. Using the initial dependency diagram drawn in question 1, remove all partial dependencies, draw the new dependency diagrams in Visio, and identify the normal forms for each table structure you created.

3. Using the table structures you created in question 2, remove all transitive dependencies, and draw the new dependency diagrams in Visio. Also identify the normal forms for each table structure you created.  If necessary, add or modify attributes to create appropriate determinants or to adhere to the naming conventions.

4. Using the results of question 3, draw the fully labeled Crow's Foot ERD in Visio. The diagram must include all entities, attributes, and relationships. Primary keys and foreign keys must be clearly identified on the diagram.

Solutions

Expert Solution

1. PART_CODE-->PART_DESC, PART_DESC-->VEND_TYPE, VEND_NAME-->VEND_ADDRESS

CANDIDATE KEYS: {PRICE}, {PART_CODE, VEND_ADDRESS}, {PART_DESK, VEND_ADDRESS}, {VEND_ADDRESS, VEND_TYPE }.

All attributes are prime based on the attribute values.

There is no such non-prime attributes, indicating partial dependencies.

Transitive dependencies (based on prime attributes): PART_CODE-->PART_DESC, PART_DESC-->VEND_TYPE

2. Since, there is no partial dependencies and transitive dependencies with respect to non-prime attributes. Hence, the relation is in 3 normal form.

For Boyce codd normal form, individual relation will be created based on each functional dependencies and they should have primary key (PK) and foreign key (FK) relationship in all 2 relations (based on PART_CODE-->PART_DESC,

PART_DESC-->VEND_TYPE).

3. Relation1: (PART_CODE (PK), PART_DESC(FK), VEND_TYPE)

Relation2: (PART_DESC (PK), VEND_TYPE)


Related Solutions

1. XYZ Sporting Corp. manufactures two types of products – Footwear and Apparel. The following table...
1. XYZ Sporting Corp. manufactures two types of products – Footwear and Apparel. The following table shows the estimated annual overhead cost of XYZ for the year of 2019 - Production department: Indirect factory wages $700,000 Factory equipment depreciation $250,000 Factory utilities $130,000 Factory building lease $80,000 $1,160,000 General Administrative Department: Administrative wages & salaries $500,000 Office equipment depreciation $10,000 Administrative building lease $60,000 $570,000 Marketing department: Marketing wages & salaries $350,000 Selling expenses $70,000 $420,000 Total overhead cost $2,150,000...
This is a simple list table of a company trying to keep track of parts that...
This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing those parts (in other words, not a database but a flat one table file). You will design a database for this company so that they won’t be relying on a simple 1 table list system to keep track of their data. Looking at the table below, produce the 3NF of the data. OrderNum OrderDate PartNum Description...
A law firm proposed the following table to keep track the information about cases and the...
A law firm proposed the following table to keep track the information about cases and the lawyers who handle the cases: CASE (caseNumber, caseDescription, lawyerInCharge, caseAssistant, beginningdate, endingDate, lawyerRate, accumulatedHours, clientsName., clientPhone, clientAdress, clientType, laywerPhone, caseResultDescription, clientCurrentPayment, paymentMethod, salary, bonus) Among above attributes, caseNumber, is the ID of the case lawyerInCharge is the name of the lawyer (a single person) who in charge of the case. NOTE : There may be also several staffs in the firm serve as the...
Seco Corp., a wholesale supply company, uses independent sales agents to market the company's products. These...
Seco Corp., a wholesale supply company, uses independent sales agents to market the company's products. These agents currently receive a commission of 20% of sales, but are demanding an increase to 25% of sales. Seco had already prepared its budget for next year before learning of the sales agents' demand for an increase in commissions. That budgeted income statement appears below: SECO CORP. Budgeted Income Statement Sales $10,000,000 Cost of sales 6,000,000 Gross margin 4,000,000 Selling and administrative expenses: Commissions...
Task 2 introduction to software engineering A nursery wants to keep track of all its products,...
Task 2 introduction to software engineering A nursery wants to keep track of all its products, including plants, fountains, garden hardware (wheelbarrow, shovels etc) and also soil and sand which they sell. They buy all stock from the wholesalers. The management wants to know which staff members have been selling what, and from which wholesaler the products were purchased. There are also times when a customer returns a product for a refund, and such information should be available in the...
1. A cosmetic product retailer needs to create a database to keep track of the information...
1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...
The following​ table, contains annual returns for the stocks of ABC Corp. ​(ABC ​) and XYZ...
The following​ table, contains annual returns for the stocks of ABC Corp. ​(ABC ​) and XYZ Corp. ​(XYZ ​). The returns are calculated using​ end-of-year prices​ (adjusted for dividends and stock​ splits). Use the information for ABC Corp. ​(ABC ​) and XYZ Corp. ​(XYZ ​) to create an Excel spreadsheet that calculates the average returns over the​ 10-year period for portfolios comprised of ABC and XYZ using the​ following, respective,​ weightings: (1.0,​ 0.0), (0.9,​ 0.1). The average annual returns over...
Connect Four is a game that alternates player 1 and player 2. You should keep track...
Connect Four is a game that alternates player 1 and player 2. You should keep track of whose turn it is next. For this program we will just be building the structure not the full program. Your assignment: Create a two player Connect Four C language program that follows the requirements below. Create functions: Initialization – print “Setting up the game”. Ask each player their name. Teardown – print “Destroying the game” Accept Input – accept a letter for which...
C program: 1, Make two parallel arrays, one to keep track of food names and another...
C program: 1, Make two parallel arrays, one to keep track of food names and another to keep track of the food prices. There will be a total of 10 items, with a total of about 100 characters for each food name title (i think we can use malloc for the strings and allocate it accordingly) Basically it will be something like a menu: 1, we will enter the food information 2, it will list all the food names and...
Xenon Corp. makes 63,200 units per year of a part it uses in the products it...
Xenon Corp. makes 63,200 units per year of a part it uses in the products it manufactures. The unit product cost of this part is computed as follows: Direct material $21.30 Direct labor $24.60 Variable manufacturing overhead $7.35 Fixed manufacturing overhead $30.60 Unit product cost $83.85 An outside supplier has offered to sell the company all of the 63,200 parts it needs for $78.50 a unit. If the company accepts this offer, the facilities now being used to make the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT