1. Real Properties wants to expand its database applications beyond the recording of property services. The company still wants to maintain data on owners, properties, employees, and services, but it wants to include other data as well. Specifically, Real Properties wants to track equipment, how it is used during services, and equipment repairs. In addition, employees need to be trained before they use certain equipment, and management wants to be able to determine who has obtained training on which equipment.

With regard to properties, Real Properties has determined that most of the properties it services are too large and complex to be described in one record. The company wants the database to allow for many subproperty descriptions of a property. Thus, a particular property might have subproperty descriptions such as Front yard, Back Garden, Second-Level Courtyard, and so on. For better accounting to the customers, services are to be related to the subproperties rather than to the overall property.

Draw an E-R data model for the Real Properties database schema. Use the IE Crows Foot E-R model for your E-R diagrams. Justify the decisions you make regarding primary keys, foreign keys and cardinality of entities.

Note that foreign keys are not shown in data models; they are added in the database design phase. Therefore, the foreign keys shown in the Garden Glory database schema in Chapter 3 are not in the E-R diagram! The only exception is where the foreign key must be part of the identifier of an ID-dependent weak entity.

2. The Highway patrol office for Washington State wishes to automate their traffic citations. See Figure below. They wish to collect and store information on the driver, the citation, violation, officer and vehicle. Note that a driver, vehicle and an officer can have many citations. Also, a citation can have many violations.

Draw an E-R data model based on the traffic citation form. Use five entities, and use the date items on the form to specify identifiers and attributes for the entities. Use the IE Crows Foot E-R model for your E-R diagram.

Note that all Correction Notices have as their identifier an identifying number that is not shown in the figure, and have an independent existence whether or not they are filled in. The alternative would be a composite identifier (such as Month, Date, Year, Time). But this is so cumbersome that even without a preexisting NoticeNumber wed use a surrogate identifier.

WASHINGTON STATE PATROL CORRECTION NOTICE NINE Kroenke D ADORESS S053 88 Ave SE om Mecer 1sland STATE Wa 98040 RRST STAIE THDATE L HGT WGT EYES VEHICLES UCENSE STATE COLOR YEAR TYPE 90 S 900 VIN REGSTERED OMTNER AOORESS VIOLATION DATE DIST DETACH AIO LOCATON DAY SR410 OF Wratet while dri' OFFICERS SGNATURE PERSOVHEL NUMBER SScott It is i53-reg, no futer ation ismqutod, Corti pod cn tho roawy is not authottod CORRECT vouATO for DRIMERS SIGHATU WASHINGTON STATE PATROL CORRECTION NOTICE NINE Kroenke D ADORESS S053 88 Ave SE om Mecer 1sland STATE Wa 98040 RRST STAIE THDATE L HGT WGT EYES VEHICLES UCENSE STATE COLOR YEAR TYPE 90 S 900 VIN REGSTERED OMTNER AOORESS VIOLATION DATE DIST DETACH AIO LOCATON DAY SR410 OF Wratet while dri' OFFICERS SGNATURE PERSOVHEL NUMBER SScott It is i53-reg, no futer ation ismqutod, Corti pod cn tho roawy is not authottod CORRECT vouATO for DRIMERS SIGHATU