DATABASE MANAGEMENT CONCEPTS;1. The DBA asks for your help in planning the data archive for the following Premiere Products database;Rep (RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate);Customer (CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum);Orders (OrderNum, OrderDate, CustomerNum);OrderLine (OrderNum, PartNum, NumOrdered, QuotedPrice);Part (PartNum, Description, OnHand, Class, Warehouse, Price);Determine which data from the database to archive, that is, for each table, specify whether data needs to be archived. If it does, specify which data, when it should be archived, and whether it should be archived with data from another table.;2. The DBA denormalized some of the data in the Premiere Products database to improve performance, and one of the resulting tables is the following;Customer (CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit, RepNum, RepName);Which field or fields cause the table to no longer be in third normal form? In which normal form is the denormalized table?;3. Does your school have a formal disaster recovery plan? If it does, describe the general steps in the plan. If it does not, describe the informal steps that would be taken if a disaster occurred.;For the following exercises, you will answer problems and questions from management at Premiere Products. You do not use the Premiere Products database for any of these exercises.;1. Fragment the Customer table so that customers of rep 20 form a fragment named CustomerRep20, customers of rep 35 form a fragment named CustomerRep35, and customers of rep 65 form a fragment named CustomerRep65. (Include all fields from the Customer table in each fragment.) In addition, you need to fragment the Orders table so that orders are distributed and stored with the customers that placed the orders. For example, fragment OrdersRep20 consists of those orders placed by customers of rep 20. Write the SQL-type statements to create these fragments.;2. Create a class diagram for the Premiere Products database, as shown in Chapter 1, Figure 1-5. If you need to make any assumptions in preparing the diagram, document those assumptions.;3. A user queries the Part table in the Premiere Products database over the company intranet. Assume the Part table contains 5,000 rows, each row is 1,000 bits long, the access delay is 2.5 seconds, the transmission rate is 50,000 bits per second, and only 20 of the 5,000 rows in the Part table satisfy the query conditions. Calculate the total communication time required for this query based on retrieving all table rows one row at a time, then calculate the total communication time required based on retrieving the 20 rows that satisfy the query conditions in a single message.
Paper#31521 | Written in 18-Jul-2015Price : $27