Garden Glory 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, Garden Glory 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 Equipment, Garden Glory wants to track the equipment type, make, model, manufacturer and the date purchased. Garden Glory also wants to track what equipment was used for each property service.

For equipment repairs, the company needs to know the equipment being repaired, date of the repair, a description of the repairs done and the cost of the repair.

For employee equipment training, Garden Glory would like to know the date, time and location of training, employees attending the training and what pieces of equipment the employees are being trained on.

With regard to properties, Garden Glory 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 Garden, 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 a database diagram (ERD) using MySQL Workbenchfor the Garden Glory database schema described below. Use Crows feet notation.

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)

OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour)

EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)

PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate, EmployeeID, HoursWorked) [This table uses a surrogate key instead of composite key]

The referential integrity constraints are:

OwnerID in OWNED_PROPERTY must exist as OwnerID in OWNER

PropertyID in PROPERTY_SERVICE must exist as PropertyID in OWNED_PROPERTY

ServiceID in PROPERTY_SERVICE must exist as ServiceID in GG_SERVICE

EmployeeID in PROPERTY_SERVICE must exist as EmployeeID in EMPLOYEE

Extend and modify the database diagram using MySQL Workbenchto meet Garden Glorys new requirements above. Start by writing out the entities as they are described with the columns/attributes needed in each entity.UseCrows feet notation. Create appropriate identifiers (primary keys) and attributes(columns)for each entity. Here are the relationship rules that need to be shown in the database diagram to meet the new requirements:

One owned property can be comprised of one or more subproperties.

One subproperty can be involved in zero or more property services.

One property service can involve zero or many pieces of equipment.

One piece of equipment can be used for zero or many property services.

One piece of equipment can be the basis of zero or many training courses.

One employee can attend zero or many training courses.

One training course can be attended by zero or many employees

One piece of equipment can have zero or many repairs.

Use MySQL Workbench to create separate database diagrams for Part A and Part B.