IM 310 Week 2 Conceptual and Logical Data Models Example

Clients, Pets and Visits: Building Conceptual and Logical Data Models for a Veterinary Clinic Group

[Student Name]

University of Phoenix

IM/310: Data Analytics & Modeling

Week 2 Assignment

[Instructor Name]

[Date]

Lone Star Pet Health, its clinics, data and figures are composites written for a model paper.

Week 1 found that Lone Star Pet Health runs three practice management systems that describe the same things differently and recommended an integrated data store. Before data can be combined, the group needs to agree on what its data mean. This paper builds conceptual and logical models for that purpose.

Why Models Come First

Hoffer et al. (2019) describe data modeling as a process that moves from conceptual models, which capture business meaning, to logical models, which add detail such as keys and attributes, to physical designs for a specific database. They emphasize that models should be built from business rules, statements that define or constrain some aspect of the business, because rules make the model's assumptions explicit and testable. At Lone Star, each acquired clinic's system embodies different, unstated rules, which is why its data do not match.

Gathering Business Rules

Interviews with six front desk staff, four veterinarians, two practice managers and the operations director produced rules, including these:

A client may own one or more pets; a pet may have more than one owner, such as two members of a household or a divorced couple sharing custody.

A pet is registered at one home clinic but may be seen at any clinic.

A visit occurs at one clinic, for one pet, on one date and may include several services.

Each service on a visit is performed by one staff member; a visit may involve several staff.

A visit produces one invoice, which may be paid by one or more clients.

A wellness plan covers one pet for one year and includes a defined set of services.

The Conceptual Model

Chen (1976) introduced the entity-relationship model as a way to represent data in terms of entities, the things about which data are kept, and relationships among them, giving designers a view of data close to how people think about the business. From the rules, the conceptual model identifies eight entities: client, pet, clinic, staff member, visit, service, invoice and wellness plan.

Key relationships and their cardinalities follow the rules. Client and pet are many-to-many, since a client may have many pets and a pet may have several owners. Pet and visit are one-to-many: each visit is for one pet. Clinic and visit are one-to-many. Visit and service are many-to-many, since a visit includes several services and each service type appears on many visits. Visit and invoice are one-to-one. Pet and wellness plan are one-to-many over time, with at most one active plan.

The rule that a pet can have two owners sounds trivial until a divorced couple both call to book the same dog's dental cleaning.

Resolving Hard Cases

Many-to-many relationships must be resolved for a workable logical model. Client and pet are linked through an associative entity, pet ownership, which records the relationship's start date and whether the client is the primary contact. Visit and service are linked through a visit line, which records the service performed, the staff member, quantity and price. Invoice payments by several clients are handled through a payment entity linking invoice and client.

Changing data is another hard case. Pets change names, clients move and service prices change. The model keeps price on the visit line, so historical invoices remain accurate when the price list changes.

Attributes and Definitions

Every attribute in the model carries a short definition agreed with staff, because many disagreements in the old systems came from words that meant different things. "Visit date" means the date the pet was seen, not the date the appointment was booked. "Client" means a person or household financially responsible for at least one pet, not anyone who has called the clinic. "Active patient" means a pet seen within the past 18 months. Writing these definitions down took two meetings, but it removed arguments that had been running for years about whose numbers were right.

Identifiers Across Systems

Matching the same client across three systems is a modeling question as well as a technical one. The model gives each client a group-wide identifier and keeps a cross-reference table linking it to the old identifiers in each source system. Matching uses phone number, email and address, with a manual review queue for uncertain matches. About 7 percent of clients were found in more than one system, mostly families who visited clinics in different towns.

The Logical Model

The logical model adds primary keys, foreign keys and attributes with definitions. Each client receives a group-wide client identifier, replacing the three systems' separate numbers, with attributes such as name, phone, email, address and preferred clinic. Each pet receives a pet identifier with species, breed, birth date and home clinic. Visits carry visit identifier, pet identifier, clinic identifier, date and visit type. Visit lines carry visit identifier, line number, service code, staff identifier, quantity and price.

A common service code list replaces the three systems' codes. A working group of veterinarians mapped 1,140 codes from the three systems to 410 common services; for example, "DENT-CLN," "Dental Prophy" and two codes from the third system all map to one dental cleaning code.

Checking Model Quality

Moody and Shanks (2003) developed and tested a framework for evaluating data model quality, with factors such as completeness, integrity, flexibility, understandability, correctness, simplicity, integration and implementability, and found in empirical tests that applying the framework improved the quality of models. Lone Star's model was reviewed against these criteria. Completeness was checked by tracing ten typical questions, such as dental revenue by clinic, through the model. Understandability was tested by walking front desk staff through the diagram; they caught one missing rule, that some pets are boarded without a medical visit, which led to a boarding stay entity. Simplicity was protected by leaving inventory, which is managed separately, out of the first version.

How the Model Guides Integration

The logical model becomes the target for the integration layer described in Week 1. Each source system's fields will be mapped to the model's attributes, client records matched to group-wide identifiers and service codes translated to the common list. Week 3 will turn the logical model into relational tables and test them against normalization rules.

Conclusion

Lone Star's data do not match because each system embodies different, unstated rules. Gathering business rules, building a conceptual entity-relationship model, resolving many-to-many relationships and creating a logical model with shared identifiers and a common service code list gives the group a shared definition of its data, checked with users and ready to guide integration.

References

Chen, P. P.-S. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36. https://doi.org/10.1145/320434.320440

Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.

Moody, D. L., & Shanks, G. G. (2003). Improving the quality of data models: Empirical validation of a quality management framework. Information Systems, 28(6), 619-650. https://doi.org/10.1016/S0306-4379(02)00043-1