The following exercises are based on the Solmaris Condominium Group database shown in Figures 1-21 through 1-25. No computer work is required. 1. Using the types of entities found in the Solmaris Condominium Group database (condo locations, owners, condo units, service categories, and service requests), 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 answer and show how to convert to the higher forms. 2. Determine the functional dependencies that exist in the following table, and then convert this table to an equivalent collection of tables that are in third normal form: Location (LocationNum, LocationName, (UnitNum, SqrFt, Bdrms, Baths, CondoFee)) 3. Determine the functional dependencies that exist in the following table, and then convert this table to an equivalent collection of tables that are in third normal form: CondoUnit (CondoID, LocationNum, UnitNum, SqrFt, Bdrms, Baths, CondoFee, OwnerNum, LastName, FirstName) 4. Solmaris is considering adding tenant information to the database. A tenant is the person occupying a condo; this person may or may not be the condo’s owner. Determine the functional dependencies that exist in the following table, and then convert this table to an equivalent collection of tables that are in third normal form: CondoUnit (CondoID, LocationNum, UnitNum, SqrFt, Bdrms, Baths, CondoFee, OwnerNum, OwLastName, OwFirstName, TenantNum, TnLastName, TnFirstName) 5. What type of relationship exists between tenants and condos? Why? What type of relationship exists between tenants and owners? Why?
Extracted text: Location ServiceRequest ServiceCategory LocationNum LocationName Address State PostalCode Service Condo Category Description ID Spent Next Hours City Status Est CategoryNum CategoryDescription 1 Solmaris Ocean 100 Ocean Ave. Bowton FL 31313 ID Num Hours ServiceDate Plumbing 2 Heating/Air Conditioning 3 Painting Electrical Systems 5 Carpentry 6 Janitorial 1 2 Solmaris Bayside 405 Bayside Blvd. Glander Bay FL 31044 1 Back wall in pantry has mold indicating water seepage. Diagnose and rерair. Service rep has verified the problem. Plumbing contractor has been called. 1 4 2 10/12/2015 FIGURE 1-21 Sample location data for Solmaris Condominium Group 4 CondoUnit CondolD LocationNum UnitNum SqrFt Bdrms Baths CondoFee OwnerNum 2 Air conditioning doesn't cool. Service rep has verified problem. Air conditioning 2 5 1 10/12/2015 1 102 1 201 1 306 1 204 1 405 1 401 1 502 1 503 1 675 1 1 $475 AD057 FIGURE 1-24 Sampla data abɔut sernice calogarics for Sohmaris Condominium Group contractor has been called. 1,030 1,575 2 2 1 $550 EL025 3 3 $625 AN175 6 Hardwood floors must be refinished. 3 4 Service call has been 8 10/16/2015 4 1,164 $575 BL720 scheduled. 5 | 1,575 3 2 $625 FE182 4 1 4 Switches in kitchen and Оpen 1 10/13/2015 $550 KE122 $490 JU092 $670 RO123 6 1,030 2 adjoining dining room are reversed. 7 745 1 1 8 1,680 3 3 5 Molding in pantry must be replaced. 2 Cannot schedule until 2 2 A03 725 1 1 $190 TR222 water leak is corrected. 10 2 A01 $235 NO225 | 1,084 1,084 1 $250 SM072 $190 AN175 11 2 B01 2 2 3 Unit needs to be repainted Scheduled due to previous tenant damage. 6 14 7 10/19/2015 12 2 C01 750 1 13 2 C02 $250 WS032 1,245 | 1,540 2 14 2 C06 3 2 $300 RO123 4 Tenant complained that using microwave caused short circuits on two occasions. Service rep unable to duplicate problem. Tenant to notify condo management if problem 7 11 1 1 FIGURE 1-23 Owner Sample data about condo units for Solmaris Condominium Group OwnerNum LastName FlrstName Bruce and Jean Addross City State PostalCode recurs. AD057 100 Ocean Ave. Adney Anderson Bill Bowton FL 31313 31522 29707 3 Kitchen must be repainted. Scheduled Walls discolored due to AN175 18 Wilcox St. Brunswick GA 8 9 5 10/16/2015 Jack Bill and Sandy Daniel BL720 Blake 2672 Condor St. Mills SC EL025 Elend 100 Ocean Ave. Bowton FL 31313 kitchen fire. Feenstra Juarez Kelly Norton. FE182 7822 Coventry Dr. Rivard FL 31062 JU092 Maria 892 Oak St. Kaleva FL 31521 6 Shampoo all carpets. Оpen 10/19/2015 ΚΕ122 527 Walers St. Alyssa Peter and Caitlin 281 Lakewood Ave. Lawndale Norton MI 49441 NO225 19111 10 5 Repair window sills. Scheduled 4 10/20/2015 PA RO123 Robinson Mike and Jane Smeltz |900 Spring Lake Dr. Springs MI 49456 SM072 Jim and Cathy |Michael Henry and Karen 25 Nichols St. 922 Garland Dr. Lewiston FL 32765 FIGURE 1-25 Sample data about service requests for Solmaris Condominium Group TR222 Trent 105 Bayside Blvd. Glander Bay FL 31044 WS032 Wilson Lewiston FL 32765 FIGURE 1-22 Sample owner data for Solmaris Condominium Group