In the following exercises, you will use the data in the Solmaris Condominium Group database shown in Figures 1-21 through 1-25. (If you use a computer to complete these exercises, use a copy of the original Solmaris Condominium Group database so your data will not reflect the changes you made in Chapter 3.) If you have access to a DBMS, use the DBMS to perform the tasks and explain the steps you used in the process. If not, explain how you would use SQL to obtain the desired results. Check with your instructor if you are uncertain about which approach to take.
1. Create a view named LargeCondo using the condo ID, location number, unit number, condo fee, and owner number for those condos with square footages of greater than 1,200 square feet. Display the data in the view.
2. Create a view named InitialService using the condo ID, category number, description, and estimated hours for every service request for which the spent hours are zero. Display the data in the view.
3. Create a view named NumberOfBedrooms using the Bdrms column and a count of all condos of each bedroom size. Display the data in the view.
4. Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name.
a. Create an index named OwnerIndex1 on the State field in the Owner table.
b. Create an index named OwnerIndex2 on the LastName field in the Owner table.
c. Create an index named OwnerIndex3 on the State and City fields in the Owner table and list the states in descending order.
5. Drop the OwnerIndex3 index from the Owner table.
6. Assume the CondoUnit table has been created, but there are no integrity constraints. Create the necessary integrity constraints so that the condo fee must be less than $1,000 and the bedrooms must be greater than 0.
7. Ensure that the following are foreign keys (that is, specify referential integrity) in the Solmaris Condominium Group database.
a. LocationNum is a foreign key in the CondoUnit table.
b. OwnerNum is a foreign key in the CondoUnit table.
c. CategoryNum is a foreign key in the ServiceRequest table.
d. CondoID is a foreign key in the ServiceRequest table.
8. Add to the CondoUnit table a new character field named FeePaid that is one character in length. On all records, change the value for the FeePaid field to Y.
9. Change the value in the FeePaid field in the CondoUnit table to N for the condo whose condo ID is 4.
10. Change the length of the LastName field in the Owner table to 30.
11. If you are using Access 2013, complete the following steps.
a. Add a Currency field named TotalFees to the Location table. Create and run a totals query on the CondoUnit table to determine the total fees by location and then manually update the Location table with these values.

b. Create a data macro associated with the After Insert event for the CondoUnit table to add the condo fee to the total fees for the appropriate location when adding a record to the CondoUnit table. Test the data macro by adding a row to the CondoUnit table and ensuring that the TotalFees field for the corresponding location is updated correctly.
c. Create a data macro associated with the After Update event for the CondoUnit table to add the difference between the new condo fee and the old condo fee to the total fees for the appropriate location. Test the data macro by changing the condo fee on a row in the CondoUnit table and ensuring that the TotalFees field for the corresponding location is updated correctly.
d. Create a data macro associated with the After Delete event for the CondoUnit table to subtract the condo fee from the total fees for the appropriate location when deleting a row in the CondoUnit table. Test the data macro by deleting a row from the CondoUnit table and ensuring that the TotalFees field for the corresponding location is updated correctly.
12. The management of Solmaris Condominium Group wants to split service category 2 (Heating/Air Conditioning) into separate categories by changing category 2 to €œHeating€ and adding a new category 7 for €œAir Conditioning.€ Would management encounter any problems in modifying the ServiceCategory table? What additional updates would management need to make to ensure that the data in the database is correct?

Figure 1-21

Transcribed Image Text:

LocationNum LocationName Address 1 Solmaris Ocean 100 Ocean Ave. 2 Solmaris ayside 405 Bayside Blvd. Glander Bay FL State PostalCode City Bowton FL 31313 31044