Lab 7 CIS 275
flcl178
CIS275_Lab_Week_7.docxLab Questions (6 @5 points each: total = 30)
Your answers for each question should fill at least 1/2 page. You may use any drawing application to create the designs and then copy/paste the pictures into this document. If you use Visio, just use the flowchart shapes; you may be able to use the Word flowchart auto shapes. You might need to scan your drawing to a .jpg or .bmp or whatever and then paste it here.
---------------------------------------------------------------------
An international bank has many branches around the globe. Their major needs at this point is creating a database so they can trace their daily activities. Customers can go to any of the branches to deposit, withdraw, or transfer funds. To track the volume of activities of each personal banker, the banker’s ID is stored on each transaction they process. Only customers with special International ID’s would be allowed to transfer funds to the other countries. The bank would like to have the records of local and international transactions.
Each local and international transactions will have rules and specific fees. Transactions will be tracked for each branch.
The customers need to pay for each transaction they make at the bank. Once they pay, the fees are marked as zero Balance. The total transactions ‘fees will be transactions fees collected for each branch.
Q1. Read through the above scenario and determine which entities are involved (identify all the things and decide whether they are entity classes or attributes). Create a simple ERD to show only the entities and the relationships between them (see the first lecture example). Name the relationships (e.g. “customer”, “transactions”, etc) but do not show the relationship cardinalities nor any of the attributes. It is expected and perfectly OK to have at least one many-to-many and/or ternary relationship here, because these will be changed in the next step.
Q2. Expand the ERD from the previous question by adding the minimum and maximum relationship cardinalities to the model; eliminate any ternary and/or many-to-many relationships from the previous ERD by creating an intersection entity between them. Show optional/mandatory cardinalities and use crows-feet notation to display the “many” sides. Include cardinality for all relationships between two entities.
Q3. Taking each pair of related entities at a time, write one sentence describing the relationship cardinalities (minimum and maximum) in each direction. For example, this relationship
...may be stated:
An employee builds zero to many stoves / A stove is built by exactly one employee.
Q4. Turn four of your entities into relations. Select an attribute(s) to represent the primary key; display this first, underlined. Include other attributes you would expect to find for this entity. Lastly, include any foreign keys which reference other entities; display these in italics.
Example from the FiredUp database:
STOVE (SerialNumber, Type, Version, DateOfManufacture, Color, FK_EmpID)
Q5. What assumptions did you make when you were creating the ERD? Turn these assumptions into questions that you would ask the client in order to continue the design process. Include at least five questions. Be sure to address any ambiguities in the scenario that might affect your design.
Example: Can two or more personal bankers both be listed on a transaction? This would create an M:N relationship that would require an intersection entity to resolve.
Q6. Assume the types of the columns in the SKU_DATA_4 table in the following are:
|
Colum Name |
Data Type |
Size |
|
SKU |
NUMERIC(6, 0) |
5 Bytes per row |
|
SKU_Description |
CHAR(128) |
128 Bytes per row |
|
Buyer |
CHAR(64) |
64 Bytes per row |
Assume the bank has 200 products. Ignoring any additional overhead, how large would the SKU_DATA_4 table be in Bytes? Show how you calculated this number.
EMPLOYEESTOVE
�
EMPLOYEE
