Assignment+3

**Overview:**
Congratulations, your stakeholder and requirements analysis for New Gulf Bank were a success and the bank's executive team has decided to move forward with the ATM project. Due to your good work in the analysis stage of the project, you have been asked to continue your work on the project by doing some of the project's data modeling. Specifically, you will build on your requirements and stakeholder analysis by creating data models for two parts of the bank's ATM system - managing ATM customer transactions and recording ATM maintenance history.

As with the previous assignment, the description of the fictional bank and their requirements are intentionally vague. You should use your experience, business judgement, and common sense to fill in the gaps in the scenarios and come up with reasonable data models. It is ok to make assumptions as you are doing this exercise. If the assumptions are not obvious then it is probably a good idea to state them clearly in the documents you submit.

You may work on the assignment individually, or in teams of 2 or 3 people.

**Scenarios and background:** New Gulf Bank (NGB) is planning to begin operations in the Qatar Financial Center starting in Summer 2010. NGB plans to open four branches throughout Doha over the following twelve months and to install 50 Automated Teller Machines (ATM's) throughout Qatar during the same time period. NGB is owned by a private individual in Doha who has raised capital from wealthy individuals in the GCC to start the bank. The bank will target retail customers and small and medium businesses throughout Qatar, focusing initially on Doha and the surrounding area but with plans to eventually put branches in outlying cities as well.

For each of the scenarios described below, your group needs to produce an entity-relationship diagram that captures the data needs for the scenario. You also need to produce a set of relational tables that capture the required data and relationships between the data. If you feel it would be helpful to do so, you may also provide an appendix with a written explanation of any important assumptions you made in completing your models. This written appendix is optional.

Scenario 1: Managing customer ATM transactions
Each ATM will initially support four types of transactions (deposit, withdrawl, account transfer, balance inquiry) for two different types of accounts (savings and current). Prior to conducting any transactions at an ATM, a customer will need to insert his or her ATM card and enter the appropriate Personal Identification Code (PIN). You may assume in this transaction that each customer will have only one ATM card but that each ATM card may be linked to multiple accounts. The customer should be able to do any of the listed transactions on any of the accounts liked to the ATM card. The bank, of course, needs to keep a record of the customer's name, phone, address, ID number, and signature.

Prior to completing a withdrawl transaction, the ATM needs to verify that the customer has at least the amount of requested cash in the account from which the withdrawl has been requested. Once the availability of funds has been confirmed, the cash is delivered and the balance in the account is updated as appropriate.

The ATM can accept cash or checks as deposits. The customer needs to insert the cash and/or checks into an envelope and deposit that envelope into the machine as part of the transaction. The ATM records the transaction information and writes a copy of the deposit details on the outside of the deposit envelope.

The bank needs to keep a history (also known as an audit trail) that records details for every transaction undertaken at each ATM. They need a record that includes: type of transaction, amount debited and/or credited and the corresponding accounts debited or credited, time and date of transaction, specific ATM at which the transaction took place, ATM card used for the transaction, and a picture of the person standing at the ATM doing the transaction (taken by a small camera located on the ATM). The picture is stored electronically in the database in case of a future disagreement about whether the account-holder completed the transaction. Each transaction will generally be identified with a unique Transaction ID that can be used to reference the transaction at a later point.

Scenario 2: Recording ATM maintenance history
The bank needs to keep a reliable history of all maintenance operations on the ATM's, including the daily cash restocking and deposit pickups. To do so, they will need to store information about each ATM that they have in their network - location, type of ATM, network name / address. They will also need to store the current status of each ATM, including whether it is currently operational, broken, out of cash, offline for service, etc. This status should also include the total amount of cash available in the machine (the specific number of 50, 100, and 500 QAR notes), and the number of deposits that are awaiting pickup in the machines.

Whenever a technician performs any service on a machine, the following information needs to be recorded about that service:
 * machine serviced
 * date and time of the service
 * employee or contractor who completed the service (including an indication of whether it was a contractor or employee)
 * type of service undertaken (repair, loading cash, collecting deposits, cleaning, etc.)
 * Amount of cash loaded, in the case of a cash load, broken down into the number of 50, 100, and 500 QAR bills loaded
 * Number of deposits collected, in the case of a collection
 * Notes and description of service (optional - only if the technician decides this information is needed)

Example:
Attached below is a scan of the sheet we used to go over the ER diagram question on the second exam. It should provide an example of the kinds of details required for the ER diagrams in this assignment.

**Deliverables:**
You need to hand in a paper copy of your data models no later than 5:00 on Thursday, November 12th. You may drop the hard-copy off to me in my office. If I am not in my office when you come by, you may slide it under my door.

You may produce the document with a computer or by hand. You are likely to find that it is quicker and easier to draw the ER diagrams by hand than with a computer. Regardless of how you decide to produce the document, please make sure that it is clear, clean, and legible. Documents that are illegible will be graded accordingly.

Your submitted document should contain four distinct data models: ===** The ER diagrams should contain all of the entities and attributes identified in the scenario descriptions, plus any additional entities and/or attributes that you feel need to be included in the model for the bank to be able to make proper use of the database that will be created based on this data model. All appropriate relationships between the entities should also be included in the diagram. Likewise, the relational models should capture and represent all of the data identified in the scenario, and also any additional data that you included in your ER diagram. Your relational model should clearly identify each table's name, the names and data types of each of columns, as well as all primary keys and foreign keys. **===
 * Scenario 1 ER diagram
 * Scenario 1 Relational model (tables)
 * Scenario 2 ER diagram
 * Scenario 2 Relational model (tables)

**Grading Criteria:**
There are 110 points possible on the assignment. I will use the following criteria when grading your submission:
 * Submitting your document on time and according to the submission and deliverables specification is worth 10 points.
 * Scenario #1 is worth 50 points (25 points for each data model)
 * Scenario #2 is worth 50 points (25 points for each data model)

Extra Credit:
There are up to ten points of extra credit available for this assignment. If you choose to do so, you may create Microsoft Access databases for scenario 1 and scenario 2 that capture and implement your relational model. You will need to submit these databases filled with a few rows of example data in each table. There does not need to be a lot of data, just enough to illustrate that you have done a reasonable job implementing the database and that you have chosen appropriate data types, primary keys, and foreign keys if/where necessary.

You can only receive extra credit for the database if you have submitted the hard-copy models on time for both scenarios. The main assignment remains due on Tuesday, November 10th, but you have until 5:00pm on Thursday, November 12 to submit the extra credit databases. To submit the Access databases, please send an e-mail message with the database as an attachment to bmonroe@cmu.edu. Be sure to include all of the names of the team members who worked on the database in your e-mail.

**Statement on Collaboration:**
The document you submit may be done individually, or in teams of two or three people. You may not work in teams larger than three people - if you have a group of more than three people who would like to work together you will need to break the group into separate teams.

You are free to discuss the assignment, the banks needs, stakeholders, and general ideas about the case and assignment with your classmates on other teams. Each team will, however, need to create, write down, and submit their own data models. The document submitted needs to represent the work of the team submitting it - your team needs to synthesize the discussion and come to their own conclusions regarding what should be in the data model.

You should not discuss specific details about your data model with people on other teams, only general ideas. In general, if you find that after discussing the assignment with your classmates you produce very similar data models then you are probably discussing the assignment in too much detail. If you find that you are just copying the ideas from another group then you have probably stepped over the line that separates a constructive discussion about the assignment from cheating. You would be surprised how much variation there will be in the data models that different teams submit, and how much two data models that have been done together, even with minor variations, will stand out when looked at in the context of all of the assignments submitted.

The names of all team members should be on the single document that you submit. If your team has spent so much time discussing the case with another team that it is likely the instructor will be concerned about copying between the teams then you should list the names of the other people in the class with whom you have discussed the assignment as well. These people should be identified as participants in your discussions rather than team members. Those identified on an assignment only as participants will not be graded or get credit for that team's submission but they will also be much less likely to get in trouble for academic integrity problems if their participation is called out clearly in the submission.