Introduction to Database Systems

York University
Winter 2011
Project #1: Scheming a Schema

Develop an entity-relationship model (ERM) for the attached case. Clearly identify the entity sets, relationship sets, multiplicity (i.e., 1-many, many-many etc.), attributes, primary keys, and constraints in your model, using the notations and diagramming rules described in the textbook.

Keep in mind that the case is designed to simulate a real life systems analysis situation; hence the conversations are unstructured and sometimes fuzzy. It is your responsibility, as an analyst/designer, to translate what you heard/read into an ERM.

You are the database designer of an information system for a bank's operations. Base your design on the requirements as expressed below.

  Requirements Meeting

During the requirements gathering meeting, the bank's chief operation officer explained what they wanted.

Our existing system is antiquated. We want to redevelop it so that it is more robust and extendable, and so it has better performance. The current system handles many operations. We want to first redevelop the portion that manages information on accounts, loans, employees, branches and customers. This is the scope of your design.

The most important information for us is, of course, the customers' bank accounts. Each account has an account number, which is unique within a branch. Obviously, we need to know to whom the account belongs, and the account's current balance. Joint accounts are allowed, which means an account can have more than one owner. We need to know the account type; for example, savings, chequing, and loan. We also want to record in which branch the account was opened. With today's information technology, most types of transactions and services can be handled in any branch; but certain services can only be handled by the branch in which the account was opened. We also want to record when the account was created.

Note that at our bank, a loan account acts as a personal line of credit. For a loan account, we also need to record who the loan officer was who authorized it, the amount, the interest rate, and an end date (when the loan account will be closed). For a savings account, we need also to record an interest rate.

For each customer, we need to know his or her full name, the 9-digit social insurance number (SIN) for reporting interests earned to the government (for filing tax returns), a phone number, and an address. Each customer has a customer number, unique across the bank.

We have a large number of branches across the country. Each branch has a unique 5-digit branch number. For each branch, we want to record its address, main phone number, and office hours. The office hours vary from branch to branch, and are usually different for different days of the week. For example, many branches are open from 9am to 4pm on Mondays, Tuesdays and Wednesdays; 9am to 8pm on Thursdays and Fridays; and 10am to 2pm on Saturdays. Hence, you need the record the office hours (start time and close time) for each branch for each day of the week.

In this database, we also want to include employee information. For each employee, we keep track of a unique employee number, name, address, his or her boss, salary, and date of birth. Assume that each employee has one, and only one, boss. For each of the loan officers, who approves loans, we need to record the maximum loan amount that he/she is authorized to approve for each loan. We want to record where each employee is assigned to work. By where, we mean which branch or head office. If it is the head office, record 00000. An employee can be assigned to work in more than one place. We also want to know the start date and end date, if applicable, of the current and past assignments.

Obviously, we need a record of all the account transactions, which include deposits and withdrawals. For a loan account, a payment (payback) is equivalent to a deposit; and further borrowing against the loan is equivalent to a withdrawal from the account. Each transaction has a unique transaction number, and indicates which account (associated with one, and only one, account, of course), transaction type (deposit or withdrawal), amount, and date. For each transaction, we also want to record who authorized it; that is, which customer associated with the account performed the transaction.

We would like to have the computer to manage much more information. But the above are the critical items, so let's focus on them for this design.

  1. In real life, many more data elements than described above are needed to run a bank business. We do not, however, want to turn this project into a study of business operations. So keep in mind that this is a highly simplified case.
  2. As a general approach, if a particular constraint is not explicitly given, then assume the least restricted situation. For example, "many" in ERM is less restricted than "at most one".
  3. You are not required to specify the domains of the attributes in this part of the project.
  4. Make sure that you clearly indicate the constraints. For example, if there is/are ISA situation(s), indicate the overlapping and coverage constraints next to the ISA symbol(s).

Due: 11:59pm Friday 28 January 2011.

Hand in a hardcopy of your project. This should include:

  • ERM
    Your full E-R model for the prescriptions database.
  • Documentation
    Paragraphs explaining details about the design.
    • Any clarifications about your ERM that are not evident in the model itself.
    • Any assumptions you had to make with respect to the requirements. (In the real world, these would then be resolved in a second requirements meeting.)
    • Any constraints (business rules) apparent from the requirements that you are unable to model via your ERM.
    The documentation may be minimal, or even absent, if your model has no such needed clarifications.

Hand-written is fine, but use pen, not pencil. Do not use binders, folders, paper clips etc. Use standard size paper (8.5" x 11"), and have the sheets stapled together.

Have a cover page for submitting your work, filling out your student number, etc., as follows:

Student Number
Sur (Family) Name  
Given Name  

Please drop off your assignment in the CSE-3421 drop-off box in the Computer Science & Engineering Building (attached to CSB #1003). Do not hand it into your instructor in class.