Database Design Activities

1. Database Design Activities

2. Activity • Identify the entities and attributes based upon the excerpt from an interview of the manager of an appliance repair shop described on the next several slides. • Create the resulting tables.

3. Activity (cont’d) “We’re in a bit of trouble here. This is the third week in a row that we have lost a customer’s TV set. Somehow we’ve got to keep better track of who brought in which appliance, and when. And when the appliance is picked up by the customer. We also need to know which of our technicians worked on which appliance, and how long it took. We are also having problems with repair equipment loss. Some equipment has gone missing and I’m afraid it is due to employee theft. The equipment is numbered but we can never remember where we put it. We need to have each employee check each piece out when he needs it, and check it back in when he’s done with it – and only one piece at a time. That way the last guy to check it out pays for it if it’s missing. As you can see this is a big shop and each technician is assigned to a bench but I can’t keep track of which employee is assigned to which bench. Sometimes I wish we hadn’t gotten so big…

4. Activity (cont’d) I had this idea – we’d stamp each appliance the customer brought in, with a number. Then we’d put it on the shelf, and when a technician needed work he’d go to the shelf and sign it out. That way, we’d know who had it last. If the technician can’t fix it, he’d put it back and sign it back in. But only after he wrote down how long he had worked on it. Well it was a great idea. I had these cards printed up and everybody wrote all the information on the card and it worked great. Then we ran out of cards and the print shop was closed and the system fell apart. Here’s another problem – the other day a customer walked in with a whole pile of appliances – TVs, radios, a microwave, - you name it. We had to write his name and phone number on 15 of those little slips. And along the same lines – there was a customer who came in the same day who got rather agitated when she had to give her name and phone number. She seemed to think we should have remembered it because she was just in a week before. So you can see we have some major problems and we hope youcan design a better system for us.”

5. Activity • Draw a simple ERD containing the Order, Customer, and Product entities. Show attributes and PKs and FKs. • Choose appropriate relationship names using your common knowledge of interactions between customers, products and orders. • Extend the ERD to include the Employee entity. • Choose appropriate relationship names using your common knowledge of interactions between employees and orders. • The next slide describes the attributes for each entity.

6. Activity (cont’d) • Customer attributes:CustNo , CustFirstName, CustLastName,, CustStreet, CustCity, CustState, CustZip,CustBal (balance.) • Order attributes:OrdNo, OrdDate, OrdName, OrdStreet, OrdCity, OrdState, OrdZip. • Product attributes:ProdNo, ProdName, ProdQOH, ProdPrice, ProdNextShipDate. • Employee attributes:EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpEmail, EmpCommRate (commission rate), EmpDeptName.

7. Activity Define an ERD for database for managing the task assignments on a work order. • A work order records the set of tasks requested by a customer at a specified location. • A customer has a unique customer identifier, a name, and a billing address (street, city, state, zip). •  A work order has a unique work order number, creation date, date required, a completion date, an optional supervising employee, a work address (street, city, state, zip), and a set of tasks. • Each task has a unique task identifier, a task name, an hourly rate, and estimated hours. Tasks are standardized across work orders so that the same task may be performed on many work orders. • Each task on a work order has a status (not started, in progress, or completed), actual hours, and a completion date. The completion date is not entered until the status changes to complete. • A work order cannot be created unless at least 1 task is known.

8. Normalization Exercise • For the following description of a database to support physical plant operations, identify functional dependencies and construct normalized tables. Using the simple synthesis procedure, design a collection of tables in 3NF. • Design a database to assist physical plant personnel in managing key cards for access to buildings and rooms. The primary purpose of the database is to ensure proper accounting for all key cards.

9. Room Key Normalization Exercise 9 A building has a unique building number, a unique name, and a location within the campus. A room has a unique room number, a size (physical dimensions), a capacity, a number of entrances, and a description of equipment in the room. Each room is located in exactly one building. The room number includes a building identification and followed by an integer number. For example, room number KC100 identifies room 100 in the King Center (KC) building. An employee has a unique employee number, a name, a position, a unique email address, a phone, and an optional room number in which the employee works. Magnetically encoded key cards are designed to open one or more rooms. A key card has a unique card number, a date encoded, a list of room numbers that the key card opens, and the number of the employee authorizing the key card. A room my have one or more key cards that open it. A key type must be authorized before it is created.

10. ERD Activity: Seminar Registration System Recall our work with the proposed new online system to automate seminar registration for a company that offers seminars at multiple sites and on multiple dates. Here are some features of the proposed system that were gathered at an initial one-hour meeting with the customer. Seminar registration is now handled by mail or by phone, based on seminar brochures sent out in the mail. The customer wishes to implement an online (web-based) enrollment system. A potential seminar enrollee should be able to go the new web site, select a specific seminar and then pay for and enroll in it if space is available. Payment would be made by an online credit card transaction. Currently all seminar information (title, instructor, location, date, time, current enrollment, and maximum enrollment allowed) is held in a Excel spreadsheet. A separate Excel spreadsheet contains some information (name, address, phone, email, credit card info) about previous seminar attendees. The seminar manager requested a new daily report showing the current status of enrollment for all seminars. 10

11. ERD Activity: Seminar Registration System Recall that we refined the initial set of objectives given to us by the customer by identifying the underlying primary business needs, and by constructing and refining a Level 0 DFD for the proposed system, which is reproduced on the next slide. In this activity, using the Level 0 DFD and the understanding that we have gained about the proposed system, to create an Entity Relationship Diagram (ERD) that models the major entities and their relationships (including cardinalities). Once you complete the ERD, identify (or create) primary keys for each entity, and decide what foreign keys will be required to connect the various entities. Finally, identify additional attributes that would be important to store for each entity. 11

12. Level 0 DFD Attendee Info* seminar_cancellation Email Sys seminar_cancelled Class Admin registration_decline completion_rosters confirmation_of_cancellation request_for_transcript class_rosters Proposed System catalog_search_req payment_info Corporate Financial System registration_req_w_cc credit Seminar Attendee registration_req_w_corp_pmt action cancellation seminar_cancellation confirmation_of_seat daily_report confirmation_of_cc_pmt new_seminar transcript Seminar Mgr certificate search_results Seminar Info* * Customer will convert current data

13. Activity For the following tables of a financial planning database, identify possible uses of denormalization and derived data to improve performance. In addition, identify denormalization and derived data already appearing in the tables. The tables track financial assets held and trades made by customers. A trade involves a purchase or sale of a specified quantity of an asset by a customer. Assets include stocks and bonds. The Holding table contains the net quantity of each asset held by a customer. For example, if a customer has purchased 10,000 shares of IBM and sold 4,000, the Holding table shows a net quantity of 6,000. A frequent query is to list the most recent valuation for each asset held by a customer. The most recent valuation is the net quantity of the asset times the most recent price.

14. Activity continued • Customer(CustNo, CustName, CustAddress, CustCity, CustState, CustZip, CustPhone) • Asset(AssetNo, SecName, LastClose) • Stock(AssetNo, OutShares, IssShares) • Bond(AssetNo, BondRating, FacValue) • PriceHistory(AssetNo,PHistDate, PHistPrice) • FOREIGN KEY AssetNo REFERENCES Asset • Holding(CustNo, AssetNo, NetQty) • FOREIGN KEY CustNo REFERENCES Customer • FOREIGN KEY AssetNo REFERENCES Asset • Trade(TradeNo, CustNo, AssetNo, TrdQty, TrdPrice, TrdDate, TrdType, TrdStatus) • FOREIGN KEY CustNo REFERENCES Customer • FOREIGN KEY AssetNo REFERENCES Asset

15. Customer Database for Municipal Water Utility • The database should support recording of water usage and billing for water usage. To support these functions, the database should contain data about customers, rates, water usage, and bills. Other functions such as payment processing and customer service are omitted from this description for brevity. The following list describes the data requirements in more detail. • Customer data include a unique customer number, a name, a billing address, a type (commercial or residential), an applicable rate, and a collection (one or more) of meters. • Meter data include a unique meter number, and address, a size, and a model. A meter is associated with one customer at a time. • An employee periodically reads each meter on a scheduled date. When a meter is read, a meter-reading document is created containing a unique meter reading number, and employee number, a meter number, a time stamp (includes date and time), and a consumption level. When a meter is first placed in service, there are no associated readings for it.

16. Customer Database for Municipal Water Utility • A rate includes a unique rate code, a description, a fixed dollar amount, a consumption threshold, and a variable amount (dollars per cubic foot). Consumption up to the threshold is billed at the fixed amount. Consumption greater than the threshold is billed at the variable amount. Customers are assigned rates using a number of factors such as customer type, address, and adjustment factors. Many customers can be assigned the same rate. Rates are typically proposed months before approved and associated with customers. • The water utility bills are based on customers’ most recent meter readings and applicable rates. A bill consists of a heading part and a list of detail lines. The heading part contains a customer number a preparation date, a payment due date, a date range for the consumption period. Each detail line contains a meter number, a water consumption level, and an amount. The water consumption level is computed by subtracting the consumption levels in the two most recent meter readings. The amount is computed by multiplying the consumption level by the customer’s rate.

17. CAPSTONE Activity 1 Define an ERD for the following narrative. Show the attributes and the PKs and FKs. Refine the ERD with a generalization hierarchy to depict similarities between buyers and owners. Strive for 3NF

18. CAPSTONE Activity (cont’d) • The database tracks homes, owners, buyers, agents and buyer offers made. • A home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, bathrooms, and square feet. A home is either owner occupied or rented. An owner has a social security number, a name, an optional spouse name, a profession, and an optional spouse profession. An owner can possess one or more homes. Each home has only one owner. • Agents represent owners. An agent can list many homes but only one agent can list a home. An agent has a unique agent identifier, a name, an office identifier, and a phone number. An office may have more than one agent working in the office. When an owner agrees to list a home with an agent, a commission (percentage of the sales price) and a selling price are determined. • A buyer has a social security number, a name, a phone, preferences for the number of bedrooms and bathrooms, and a price range. An agent can work with many buyers, but a buyer works with only one agent. • A buyer makes an offer on a home for a specified sales price. The offer starts on the submission date and time and expires on the specified date and time. A unique offer number identifies an offer. A buyer may submit multiple offers for the same home.

19. CAPSTONE Activity 2 Define an ERD for the narrative on the following slide. Show the attributes and the PKs and FKs. Refine your ERD to add a generalization hierarchy to distinguish between nurse and physician providers. A nurse has a pay grade and a title. A physician has a residence hospital, email address, and a certification. The other attributes of provider apply to both physicians and nurses. A visit involves a physician provider while a visit detail may involve a nurse provider. Strive for 3NF.

20. CAPSTONE Activity 2 (cont’d) • A patient may be treated by one or more health care providers through a visit. Patient information includes name, address, phone and health plan. • A health care provider has a unique provider number, a first name, a last name, a phone, a specialty, a hospital name in which the provide practices, an email address, a certification, a pay grade, and a title. A provider is required for a patient visit. New providers do not have associated visits. • A visit includes a visit number, visit date, visit pay method, and charge for the visit. • A visit detail includes a detail number, a detail charge, an optional provider number, an item number. The combination of the visit number and visit detail number is unique for a visit detail. An item includes a unique item number, an item description, an item price, and an item type. An item can be related to multiple visits. New items may not be related to any visit details. A provider can be related to multiple visit details. Some providers may not be associated to any visit details.

21. ERD Activity: Seminar Registration System Recall our work with the proposed new online system to automate seminar registration for a company that offers seminars at multiple sites and on multiple dates. Here are some features of the proposed system that were gathered at an initial one-hour meeting with the customer. Seminar registration is now handled by mail or by phone, based on seminar brochures sent out in the mail. The customer wishes to implement an online (web-based) enrollment system. A potential seminar enrollee should be able to go the new web site, select a specific seminar and then pay for and enroll in it if space is available. Payment would be made by an online credit card transaction. Currently all seminar information (title, instructor, location, date, time, current enrollment, and maximum enrollment allowed) is held in a Excel spreadsheet. A separate Excel spreadsheet contains some information (name, address, phone, email, credit card info) about previous seminar attendees. The seminar manager requested a new daily report showing the current status of enrollment for all seminars. 21

22. ERD Activity: Seminar Registration System Recall that we refined the initial set of objectives given to us by the customer by identifying the underlying primary business needs, and by constructing and refining a Level 0 DFD for the proposed system, which is reproduced on the next slide. In this activity, using the Level 0 DFD and the understanding that we have gained about the proposed system, to create an Entity Relationship Diagram (ERD) that models the major entities and their relationships (including cardinalities). Once you complete the ERD, identify (or create) primary keys for each entity, and decide what foreign keys will be required to connect the various entities. Finally, identify additional attributes that would be important to store for each entity.

23. Level 0 DFD Attendee Info* seminar_cancellation Email Sys seminar_cancelled Class Admin registration_decline completion_rosters confirmation_of_cancellation request_for_transcript class_rosters Proposed System catalog_search_req payment_info Corporate Financial System registration_req_w_cc credit Seminar Attendee registration_req_w_corp_pmt action cancellation seminar_cancellation confirmation_of_seat daily_report confirmation_of_cc_pmt new_seminar transcript Seminar Mgr certificate search_results Seminar Info* * Customer will convert current data