Design a set of 3nf tables for your database scenario

Assignment Help Database Management System
Reference no: EM135342

Metropolis Toys is an independent, family-owned manufacturer of wooden toys. The toys are designed by members of the Whittle family, which has owned the business for more than 125 years.

Once a design for a toy has been approved by the entire family, Shavings Whittle details the manufacturing process so the toys can be made by the company's staff. Toys are then manufactured and shipped directly from the company's single location.

Metropolis Toys sells its merchandise in two ways: through toy stores of all sizes and direct to customers from a catalog. Redwood Whittle, the patriarch of the family, is satisfied with the company's current manual system for handling inventory and orders. However, the younger generation (children Shavings, Chip, and Splinter) believe that the company could work more efficiently if they invested in a database.

With the help of matriarch Birch, the Whittle children have convinced their father to purchase a computer and a database management system. They also hire a database designer to design their database and create an application program for the company's staff to use.

The Whittle family comes up with the following list of major attributes that should be stored in the database:

product_number

Unique identifier given to each product

product_name

Name of product

product_description

Description of product

shipping_weight

Shipping weight of product

wholesale_price

Wholesale price of product

suggested_retail_price

Suggested retail price of product

number_in_warehouse

Amount of a specific product currently in the warehouse, ready to be shipped

customer_name

Name of a customer

customer_address

Address of the customer

customer_phone

Phone number of a customer

customer_type

Type of customer (store or individual)

order_date

Date on which an order is placed

quantity_ordered

Quantity of a product included on an order

line_cost

Cost of a single line item on an order (computed by multiplying the wholesale price by the quantity ordered)

order_cost

Total cost of an order (computed by summing the line costs)

order_complete

A Boolean indicating whether all products on an order have been shipped

shipment_date

Date on which a shipment is sent to a customer

quantity_shipped

Quantity of a product included on a shipment

line_weight

Weight of a single line item on a shipment (computed by multiplying the shippinjg weight by the quantity shipped)

total_weight

Total weight of a shipment (comnputer by summing the line weights)

quantity_not_shipped

Quantity of a specific product on an order that has not been shipped (initialized to the quantity ordered and decremented by a programn when itrems are shipped)

It is clear to the database designer that a few attributes will need to be added to provide unique keys and that some of the attributes specified by the Whittles will need to be split into several pieces to provide a good database design. You should therefore feel free to add/change attributes as necessary. Just be sure that you capture all the information specified by the Whittles.

1. Draw an ER diagram for your database scenario.

2. Design a set of 3NF tables for your database scenario.

3. Assume that you wish to answer the following information request from the Metropolis Toys database:

"What are the names and quantities of all products ordered by individual customers on 12/11/00? "

a. Write the relational algebra operations needed to answer the query from the relations you created for question #2.

b. Write a SQL query that will answer the query using the relations you created for question #2.

4. Assume that you wish to answer the following information request from the Metropolis Toys database:

            "Which toys have not been ordered in the past two years?"

a. Write the relational algebra operations needed to answer the query from the relations you created for question #2.

b. Write a SQL query that will answer the query using the relations you created for question #2.

Answer two, and two only, of the following questions. You may use examples to support your discussion, but those examples must not duplicate those presented in the lessons or in your text books.

5. Uncontrolled concurrent use of a database can lead to serious problems. What are these problems? Why do they occur?

6. Assume that you have been asked to look at the database design being used by an organization. When you see the tables, you realize that they are all in first normal form; none are higher. Explain to the organization's management the three types of problems you would expect to find in these relations.

7. Discuss four major threats to database security. For each threat you identify, state at least one solution to that threat.

8. Discuss the characteristics of an organization for which a data warehouse would be suitable. Aside from huge amounts of money, what resources does an organization need to support a data warehouse or data mart? For what purposes is a database warehouse best used?

Reference no: EM135342

Questions Cloud

People believe the difficulties aisian economies : Why did people believe the difficulties Aisian economies were expericing in 1997-1998
How can team building be made into work and interaction : Class, team building exercises can be precisely effective however also take time and money that may not be available. How can team building be made into daily work and interaction
Compare the average behavior of insertion sort : Compare the average behavior of insertion sort for n elements with that of the n insertions into an initially-empty straight array implementation of a priority queue
Determine cost to government of buying firms unsold units : Determine the cost to the government of buying firms unsold units
Design a set of 3nf tables for your database scenario : Draw an ER diagram for your database scenario. Design a set of 3NF tables for your database scenario.
Determine the quantity demand and the quantity supplied : Determine the quantity demanded, the quantity supplied, and the magnitude
Draw an entity relationship diagram for the system : Draw an Entity Relationship diagram for the system and Identify the table design for the database displaying all the fields/attributes. Ensure that all tables are in 3NF. You also need to identify the primary keys and foreign keys, where applicable..
What janelle created on her website : To create even more interest in the business, Janelle created _________________ on her website. She posted pictures of events, as well as close-ups of the food they served.
Definitions of management quoted from authors : Similarities in the definitions of management quoted from authors of management textbooks

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd