Assignment 2: Conceptual Data Modeling for Authors, Books, and Clinics

This assignment presents a comprehensive data modeling solution, divided into two parts. Part 1 focuses on the business rules surrounding authors and books, including entity identification (Author, Book, Subject), relationship analysis (Author to Book, Book to Author, Book to Subject, Subject to Book), and the specification of optionality and plurality constraints. A complete list of business rules is then created, followed by a conceptual entity-relationship diagram (ERD) using Crow's Foot notation. Part 2 applies the same process to a more complex scenario involving a veterinary clinic, encompassing entities like Pet, Owner, Veterinarian, Appointment, and Bill. The relationships between these entities are analyzed, constraints are defined, a full set of business rules is developed, and a corresponding ERD is constructed. The solution demonstrates a solid understanding of data modeling principles and ERD creation, addressing business rules and relationships within the context of the given scenarios.

MET CS 669 Database Design and Implementation for Business
Assignment 2
The two parts in this assignment will contribute equally to your grade and will be
evaluated separately.
Part 1: Authors and Books
Review the following business rules regarding authors and books to complete this part.
 An author writes one or more books.
 A book is written by one or more authors.
 A subject applies to at least 20 books.
 A book is published in a year.
The business rules above specify all of the relevant entities; however, some business
rules do not specify both sides of the relationship. You will be completing this list of
business rules.
1. To get started, list the names of all entities provided in the business rules above.
- Author, Book, Subject
2. Identify the entities that are related to each other.
 Author to Book, and Book to Author- each author can have multiple books and
each book can have multiple authors.
 Book to Subject, Subject to Book- one subject applies to at least 20 books, each
book has at least one subject.
Each Book contains a Year as an attribute.
3. Identify the optionality and plurality constraints on both sides of each relationship,
where possible. Indicate where the provided business rules do not provide the
optionality and plurality constraints.
 Author to Book – Mandatory, Plural relationship. Each Author must write one or
more books.
 Book to Author – Mandatory, Plural relationship. Each Book must be written by
one or more Authors
 Book to Subject – Mandatory, Plural relationship. Each Book must correspond to
at least one subject (not stated explicitly, but assumed).
 Subject to Book – Mandatory, Plural relationship. Each Subject must correspond
to at least 20 books.
4. Create a complete list of business rules. The new list should not add additional
entities, but should specify the optionality and plurality constraints for both sides of all
relationships. Make reasonable assumptions to create the list and state your
assumptions. There is no provably “right” list of assumptions.
 An author must write one or more books.
 A book must be written by one or more authors.
 A subject must apply to at least 20 books.
Copyright 2011-2013 Boston University. All Rights Reserved.

 A book must correspond to at least one subject.
5. Create a conceptual entity-relationship diagram using Crow’s Foot notation that
reflects your list of business rules. You may use Microsoft Visio Pro or another similarly
capable drawing application to produce your ERD. Note that cardinalities are not
required in the diagram, though relationship connectivities are required.

Part 2: Veterinary Clinic
In this part, you will be repeating the same steps in Part 1 for a more complex veterinary
clinic scenario. Review the following business rules then complete the steps indicated
below.
 Every pet has an owner-
 Owners have one or more pets.
 A pet may have multiple appointments with multiple veterinarians in the clinic.
 One veterinarian attends each appointment.
 Each appointment results in a bill for the pet owner.
1. List the names of all entities provided in the business rules above.
 Pet
 Owner
 Veterinarians
 Appointment
 Bill
2. Identify the entities that are related to each other.
 Pet to Owner
 Owner to Pet
 Pet to Appointment
 Appointment to Pet
 Veterinarian to Appointment
 Appointment to Veterinarian
 Appointment to Bill
 Bill to Appointment
 Bill to Owner
 Owner to Bill
3. Identify the optionality and plurality constraints on both sides of each relationship,
where possible. Indicate where the provided business rules do not provide the
optionality and plurality constraints.
 Pet to Owner- Plural, Mandatory. Every pet must have one or more owners
(assuming a pet can be linked to different members of a family, say mother and
father are each listed as an owner for their dog).
 Owner to Pet- Plural, Mandatory. Every owner must have one or more pets.
 Pet to Appointment- Singular, Optional. Every pet could have zero or more
appointments scheduled.
 Appointment to Pet- Singular, Mandatory. Every appointment must be for one
pet (assuming that the vets do not treat more than one pet at a time).
 Veterinarian to Appointment- Singular, Optional. Every vet could have 0 or more
appointments scheduled.

 Appointment to Veterinarian – Singular, Mandatory. Every appointment must
have one veterinarian (assuming one vet per appointment).
 Appointment to Bill – Plural, Mandatory. Every appointment must result in one
or more bills (assuming no pro bono work, and customers could enter into a
payment plan resulting in multiple bills).
 Bill to Appointment – Plural, Mandatory. Each bill must correspond to at least
one appointment.
 Bill to Owner- Plural, Mandatory. Each bill must be assigned to one or more
owners (again, assuming a pet can be linked to different members of a family)
 Owner to Bill – Plural, Optional. Each owner could have zero or many
outstanding bills assigned to him / her.
4. Create a complete list of business rules. The new list should not add additional
entities, but should specify the optionality and plurality constraints for both sides of all
relationships. Make reasonable assumptions to create the list and state your
assumptions. There is no provably “right” list of assumptions.
 A pet must have one or more owners
 Each owner can have one or more pets
 A pet can be scheduled for zero or more appointments
 Each appointment must be for only one pet
 Each veterinarian can be scheduled for zero or many appointments
 Each appointment must have only one veterinarian assigned
 Each appointment must result in one or more bills
 Each bill must correspond to one or more appointments
 Each bill must be assigned to one or more owners
 Each owner may have zero or many bills assigned
5. Create a conceptual entity-relationship diagram using Crow’s Foot notation that
reflects your list of business rules. Note that cardinalities are not required in the
diagram, though relationship connectivities are required.

James, you did good work on assignment 2. I did find many issues with your plurality
and optionality descriptions in both sections. You also could have gone into a bit more
detail in the first sections “additional rules”.
Your format was generally good. A few areas could have been clarified a bit better. For
example, in the questions that asks you to add business rules. Highlight the added
rules! Formatting can really aid your assignments…
Nice work on your ERD’s. In both cases they looked solid. I had some problems with
the first one, but not a bad job overall. Solid work, this was not an easy assignment!
Grade Qualities Demonstrated by the Assignment Submission Grade
Assigned
Content (70%)
Measures the
quality of the
content in the
assignment
A+  100
The content demonstrates exceptional understanding of all relevant subject matter and its inter-
relationships. All major relevant issues are thoroughly covered, and all content is very focused
and on-topic. There is no known way to improve the content, and there are absolutely no
technical or coverage errors present.
B+
A  96
The content demonstrates exceptional understanding of all relevant subject matter and its inter-
relationships. All major relevant issues are thoroughly covered, and all content is very focused
and on-topic. At most one insignificant technical or coverage error may be present
A-  92 The content demonstrates deep understanding of all relevant subject matter and its inter-
relationships. All major relevant issues are covered, and all content is on-topic.
B+  89 The content demonstrates understanding of all relevant subject matter and its inter-relationships.
Almost all major relevant issues are covered, and the content is at least reasonably on-topic.
B  86
The content demonstrates understanding of most relevant subject matter and its inter-
relationships. Almost all major relevant issues are covered, and all content is at least reasonably
on-topic.
B-  82
The content demonstrates moderate understanding of much relevant subject matter and its inter-
relationships. There is reasonable coverage of major relevant issues, and the content is at least
reasonably on-topic.
C+  79 The content demonstrates some understanding of relevant subject matter and its inter-
relationships. Some major relevant issues are covered, and at least some content is on-topic.
C  76
The content demonstrates understanding of a small portion of the relevant subject matter and its
inter-relationships. Some major relevant issues are covered, and at least a small portion of the
content is on-topic.
C-  72
The content demonstrates little understanding of and insight into the relevant subject matter and
its inter-relationships. A small portion of the major relevant issues are covered. The focus of the
content may be off topic or on insubstantial or secondary topics
D  66
The content demonstrates almost no understanding of or insight into the relevant subject matter
and its inter-relationships. Almost none of the major relevant issues are covered, and the content
may be almost entirely off-topic.
F  0 The content demonstrates no understanding of or insight into the relevant subject matter and its
inter-relationships. No major relevant issues are covered, and the content is entirely off-topic.
Exposition
(30%)
Measures how
well the content
is expressed
A+  100
The presentation of all ideas and designs is exceptionally clear and persuasive; the entire
submission is exceptionally organized. There is no known way to improve the clarity or
organization of the submission.
A-
A  96
The presentation of all ideas and designs is exceptionally clear and persuasive; the entire
submission is exceptionally organized. There may be at most one insignificant way to improve
the clarity or organization of the submission.
A-  92 The presentation of all ideas and designs is very clear and persuasive; the entire submission is
very organized.

B+  89 The presentation of all ideas and designs is clear and persuasive; the entire submission is
organized.
B  86 The presentation of most ideas and designs is clear and persuasive; most of the submission is
organized.
B-  82 The presentation of most ideas and designs is generally clear; most of the submission is
reasonably organized.
C+  79 Some parts of the submission are hard to understand; some parts are disorganized.
C  76 About half of the submission is hard to understand; about half is disorganized.
C-  72 Most parts of the submission are hard to understand; most parts are disorganized.
D  66 Almost all of the submission is hard to understand and disorganized.
F  0 The entire submission is hard to understand and disorganized.
Overall Assignment Grade: 89.9