Complete the Bits Corporation exercises 1-6

containing red Advisorricum A of y. nasional and that 1. Comment and **. The following spendencies b pendencies that is a ble to an equivalent ie to an equivalent co the incional dependencies, cont 12. Using your w o w n Gowing th e Ancona dependen of this that are in third normal form activity 2 might be band useum and 13. Again, using your kno that exist in the following able A student can choose to participate activity 3 might be the Det bepro , Deseription using your knowledge of the college environ n i e the functional or Multivalud n de bie Aber determining the functional dipendencies, convert this table collection be that are in th normal form. Actium and Activity Name refer to act e to pa te For sale, act number 1 might be soccer, activity 2 might be Who i gn be me debate s c hoose to participate in multiple activities Course for the activity 14. Assume the same scenario as that Des pon refer to courses the student is taking AEivity an dent Student SOM . Description) Advisorum. LastName and FirstName as shown. A scenar as that given in Question 13 but replace CourseNum and Description rum LastName and First A rnoles to the novor o pomole for the are can be responsible for many activities, but each activity has only one advisor dat studentum, studentName, Activityum, Activity Adviseum. Laat.Name, First Name The requirements shown in Question 11 have changed. The dentist's office would like to add the date of parents fast payment in which relation table would you place this attribute? why One advisor can be responsible for many ac BITS Corporation Exercises The following exercises are based on the BITS database shown in Figure 5.1. 1. Using your knowledge of BITS, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form: Take (Taak D, Description. Category. Price, Order Num, Order Date, client Num, Client Name, Conalt Num, Last Name, Pirst Name, ScheduledDate, QuotedPrice)) 2 List the functional dependencies in the following table that concern invoicing (an application BITS is considering adding to its database), subject to the specified conditions. For a given involce (identified by the InvoiceNumi there will be a single client. The client's number, name, and complete address appear on the invoice, as does me date. Also, there may be several different tasks appearing on the invoice. For each task that appears. Database Design 1: Normalization play the Tasklo, description, category, and price. Assume tha ays the same price. Convert this table to an equivalent t a particular service d e s that are in third normal om nice InvoiceNum. clientum, Laste, Vieste, Street, City state, ZipCode, Date, TaskID. Description. Category. Price its wants to store information about the supervisors including their supervisor number and the relationship consultants. Supervisors can work with multiple consultants but consultants only have one supervisor. dation, supervisors specialize in working with clients in specific task categories Using this information, convert the following unnormalized relation to fourth normal form: Consultant (Conalt Num. LastName, Pirst Name, Street, City. ZipCode. Hours, Rate, SupervisorNum, supervisorName). (Tasks, Description, Category. Price, Supervisor Num)) BITS is considering changing its business model so that many consultants can service one client and one consultant can represent many clients. Using this information convert the following unnormalized relation 10 fourth normal form: client client Num, Client Name, Street, City, State, ZipCode, Balance, CreditLimit, (ConaleNum, Last Name, FirstName, Street, City, State, ZipCode, Hours, Rate) 5. Convert the following unnormalized relation to fourth normal form using the same requirements as in Question 4 many consultants can represent one client, and one consultant can represent many clients) Consultant (Conalt Num, Last Name, FirstName, Street, City, State, ZipCode, Rate, (client.Num, clientName, Street, City, State, ZipCode, Balance, CreditLimit)) 6. Is there any difference between the tables you created in Questions 4 and 57 Why or why not? Colonial Adventure Tours Case The following exercises are based on the Colonial Adventure Tours database shown in Figures 1-15 through 1-19 Chapter 1. No computer work is required. 1. Using the types of entities found in the Colonial Adventure Tours database (trips, guides, clients, and reservations Create an example of a table that is in first normal form but not in second normal form and an example of a table that is in second normal form but not in third normal form. In each case, justify your answers and show how to convert to the higher forms. 2. Colonial Adventure Tours is considering changing the way it handles reservations. Instead of storing the nur of persons associated with one reservation, the company would like to store the name and address of each person associated with each reservation. If Colonial Adventure Tours decides to implement this change the price and other fee amounts for each trip would be dependent on only the trip 10. Determine the multival le the following table, and then convert this table to an equivalent collection of tables that Chapters Many of the examples in this chapter use data from from the BITS database, which is shown in FU this chapter Und Consultant Fun Consi LastName stName Street Christopher 554 De Para City State Zip Code THOTE 3 State Zip Code Balance 51.904.55 S21455 58,354,00 CreditLimit Can $2.500.000 5.000.00 19 $10,000.00 22 39876 57,335,55 $10,000.00 35 Client Clientium Clame Street Hershey. Jarrod Mill Svet son Goduto, Sean 12 Sara Parkway City Two Cutty 97 NOK 787 NC 350 West Sunland Cousins Prichards 501 Parkway P & Pasta Saar, Jason 56473 Cherry Tree Dr. Easton 49 Ave Spot Shop 109 Innod In $2.500.00 35 5900.75 $4,113.40 37.500.00 19 $557.70 $5,000.00 22 FL 34465 $4,053.80 $7,500.00 9565 Ridged Tri City Reciful Boutique Yates, Nick Howler, Laura Harpersburg 35 Sunland 39876 FL $2.523.80 53,658.05 $6,824.55 231 Day Rd. 1365 . 1000 S. 65 rest Blvd. $2,500.00 $5,000.00 $10,000.00 733 826 19 Harpersburg FL 31234 $3,099.00 $5,000.00 Market Point 19 26 let St. 3998 F. Easton Tasks Task Description Category ACS Accessories DAI Data Recovery major DRM DISS Data recovery minor DRM HA63 Hardware major HAM HII Hardware minor HAM LA81 Local area networking (LAN) LAN M019 Mobility MOB OT99 Other work OTH P154 Printing PRI Software major SOM SI7 Software minor SOM S191 Security installer SIR UP38 Upgrades UPG VR19 Virus removal VIR WA W'de area networking (WAN) WAN WC9 Web connectivity WEC Price $80.00 $175.00 $50,00 $225.00 $165.70 510400 565.00 $99.99 350.00 $200.00 $144.00 $126.00 $185.00 $90.00 $110.00 75.00 OrderLine Order Num TaskID ScheduledDate Quoted 67101 S177 9/10/2018 $164.00 67313 LASI 9/12/2018 5104.00 67424 M049 9/14/2018 67424 UP38 9/14/2018 S185.00 6738 LASI 9/20/2018 $100.00 67949 9/21/2018 $50.00 67949 VR 39 921/2018 $88.00 67949 WA33 9/21/2018 $1500 68252 DIN5 9/24/2018 $50.00 68868 SA44 9/24/2018 $200.00 68979 AC65 9/27/2018 $27.00 68979 DAII 9/27/2018 3970.00 WorkOrders Order Num OrderDate Client Num 67101 9 66/2018 733 67313 9/10/2018 322 67838 9/10/2018 67949 9/10/2018 68252 9/12/2018 363 68868 9/14/2018 867 68979 9/17/2018 826 97/2018 458 FIGURE 5-1 Sample data for BITS