Modeling the SmallBank DB (graded individual assignment 1)
GOAL: train you on data modeling
TASK: Create an ERD diagram using Draw io for the business described below. Print it out and hand it in in class as a single sheet of paper.
CRITERIA: Use the notations demonstrated in class to provide:
- Tables/Entities
- Primary keys (with "PK" or underlined)
- Attributes
- Foreign keys, shown both as relationships (lines) and as attributes (with "FK" or some other symbol)
- Relationships, labeled with text to clarify their meaning
- Upper and lower cardinalities.
Make it neat and legible. Remember to write your name. Make a copy for yourself.
THE SMALLBANK DB
"We will start simple, with the core of the DB that will support our operations. We at SmallBank have only two products that we sell
to our customers: personal loans and personal insurance. For now, let’s plan to keep track of the principal, the rate, and the date
due of each loan. There is obviously a lot more, and we will worry about that later. Each loan is managed by a loan officer, who is an
employee of the Bank. We store their IDs, first and last names, and one phone number.
We assign a customer id (CID) to our customers. We need to know their first and last names, their street address, city, and state.
Some customers might have more than one loan with us. We do not keep track of their motivations at this point, but for example they might
have a loan to purchase a car and another loan for home renovations. Furthermore, sometimes a loan is given jointly to more than one
customer.
The personal insurance is sold to individual customers to want to protect themselves in case they cannot temporarily meet their obligations
with us (do not model these obligations for now). This insurance is optional, and customers can only purchase one insurance contract that
covers all their loans with us. To start, let’s only model who bought the insurance, the amount of coverage, and the amount of premium.”
Tips for success
The descriptions for the assignment are somewhat vague. You will experience some discomfort because of that. That feeling is actually a
common experience in data modeling. You will have to think hard about what data is needed. As a general recommendation, do not overdo.
Be reasonable in your assumptions and keep your model simple. Handle the problems that are described in the text. Do not handle all the
conceivable problems of the company.