DATA MODELING SECTION 3

1. DATA MODELINGSECTION 3 Data Modeling constructs.

2. Structured Methodologies • The techniques are instructions that are carefully drawn up, often step-by-step, with each step building on the previous step. • Progresses from the highest level of abstraction to the lowest level of detail • General to the specific

3. Methodologies • Include: • structured analysis, structured design, and structured programming Structured Analysis • Widely used top-down approach • Primary tool is?

4. Data Flow Diagrams Graphically illustrates a system’s component processes and the flow of data between them • Four basic symbols 1 2 3 4

5. Data Flow Process Data store Source/Sink

6. Context Diagram Example An overview DFD of the entire system as a single process. Shows major inputs and outputs Employee Benefits statements Employee Changes Beneficiary designation Pension benefits recordkeeping system Earnings Payroll Dept Actuary Actuary factors Hours-worked

7. Zero-Level DFD System Boundary Employee Payroll Dept. Benefits statement Earnings Employee changes Hours worked Beneficiary designation 1.0 Track participation 2.0 Track credited service 3.0 Capture Earnings Credited service Pensionable earnings Eligibility Actuary Actuary factors Benefits master file Survivor option Pension benefit 5.0 Calculate benefit 4.0 Maintain actuary tables Retirement dates Pensionable-earnings history Actuarial formula Actuarial table Actuary formula

8. First-Level DFD Actuarial formula Actuarial file 5.2 Calculate normal retirement benefit Normal retirement benefit 5.3 Calculate early retirement benefit 5.4 Calculate survivor benefit Final average earnings Retirement benefits 5.1 Calculate final average earnings Early retirement option date Normal retirement option date Retirement benefits Actuarial formula Survivor Benefit Benefits master file Pensionable earnings history 5.5 Generate benefits statement Employee details Benefits statement

9. Structured Design • Encompasses a set of design rules and techniques for designing a system from the top down in a hierarchy Structure Chart • consider the main function, then break it down into sub-functions

10. Process payroll Get valid inputs Calculate pay Write outputs Get inputs Validate inputs Calculate gross pay Calculate net pay Update master file Write checks, reports, and output files High-Level Structure Chart

11. Structured Programming • Extends the principles governing structured design to the writing of programs. • Incorporate the concepts of modularity

12. Sequence construct • executes statements in the order in which they appear • Selection construct • tests a condition and executes one of two or more alternatives • Iteration construct • repeats an instruction as lon as the results of a conditional test remain true

13. A Sequence Action A then Action B B S Selection If Condition = S Then do Action C Else do Action D Endif C D Iteration Do While Condition = R Action E Enddo E R Basic Control Structures

14. The Entity-Relationship Model • Introduced by Peter Chen in 1976 • Part of a number of Case tools • Key elements • entities, attributes, identifiers, and relationships

15. Entities • Something that can be identified in the users’ work environment • Entity classes

16. Attributes CUSTOMER CustNumber CustName Address City Province PostalCode ContactName PhoneNumber Primary Key? Two instances of CUSTOMER 12345 Ajax Manufacturing 123 Elm St Toronto Ont J1K6F9 P. Shwartz 223-5567 67890 Jefferson Dance Club 345 Main St East Hamilton Ont L8P1R5 Frita Bellingsley 545-1267 • Describe the entity’s characteristics

17. Identifiers • Attributes that name or identify entity instances • Unique vs. Non-unique • Composite identifiers

18. Relationships SALESPERSON MOTHER FATHER SP-ORDER PARENT ORDER CHILD • The way entities can be associated with one another • Degree of relationship

19. Binary relationships EMPLOYEE STUDENT DORMITORY has has has COMPANY CAR STUDENT CLUB 1 1 M 1 N N • Relationships of degree 2 • Three type of binary relationships • 1:1, 1:N, N:M

20. Cardinality RESIDENCE has STUDENT 1 N (0,400) (1,1)

21. Optional existence STUDENT takes COURSE 1 N Optional Entity

22. STUDENT rooms with • Recursive relationship

23. Showing Attributes in an ERD CustomerNumber CustomerAddress CustomerName CUSTOMER CustomerNumber CustomerAddress CustomerName CUSTOMER • Ellipses or flag

24. Weak Entities EMPLOYEE has DEPENDENT 1 M PATIENT has PRESCRIPTION 1 M • Cannot exist in the database unless another type of entity exists

25. ID-Dependent Entities (special type of weak entity) BUILDING has APPARTMENT 1 M • One in which the identifier of one entity includes the identifier of another entity

26. EXOTIC RV RENTALS Exotic RV Rentals is an agent that rents recreational vehicles to customers for a fee. Exotic does not own any RVs but instead leases or rents them on behalf of the RV owner who wishes to earn income when they are not using their RV. Exotic charges a fee for its service. Exotic specializes in RVs that can be used for multi-day, weekly, or monthly rentals. The smallest RV in their inventory is 20 feet and the largest is 40 feet. Each RV is fully equipped at the time it is rented. Most of the equipment is provided by the owners, but some is added by Exotic. The owner-provided equipment includes what is fixed on the RV, such as radio, stoves, tables, beds, generators, and refrigerators. Other owner-provided equipment is not installed as part of the RV. Such equipment includes dishes, silverware, cooking utensils, bedding, and the like. Exotic provides consumable equipment, which could also be considered supplies, such as maps, soap, dish towels, toilet paper, and similar items. An important part of Exotic’s responsibilities is keeping track of the equipment on the RV. Some of it is expensive, and some of it, particularly what is not attached to the RV, can be easily lost or stolen. Customers are responsible for all equipment during the period of their charter. Exotic likes to keep accurate records of its customers and the rentals, not only for marketing but also for recording the trips that customers have taken. Some trips can have poor weather conditions or interesting places and Exotic like to know which customers have what experience.

27. RVs often need maintenance. Exotic is required by its contracts with the RV owners to keep accurate records of all maintenance activities and costs, including normal activities, such as cleaning or engine-oil changes, and unscheduled repairs. In some cases, repairs are necessary during a rental. An engine, for example, might require maintenance while the RV is far away from Exotic’s facility. In this case, the customers phone the Exotic dispatcher, who determines the best facility to make the repair and phones the repair facility so that they can go to the customers aid. To make these decisions, the dispatchers need information about repair facilities as well as past histories of repair quality and cost.

28. Possible Entities for Exotic • What should one look for? • RENTAL • LEASE • RV • CUSTOMER • OWNER • EQUIPMENT • OWNER-PROVIDED-EQUIPMENT • FIXED-OWNER-EQUIPMENT • REMOVABLE-OWNER-EQUIPMENT • EXOTIC-PROVIDED-EQUIPMENT • ITINERARY/WEATHER • SCHEDULED-MAINTENANCE • UNSCHEDULED-MAINTENANCE • REPAIRS • REPAIR-FACILITY

29. Selected Entities for Exotic for ERD • Some entities may not be needed • RENTAL or LEASE (synonyms) • RV • CUSTOMER • OWNER • EQUIPMENT • ITINERARY/WEATHER • SCHEDULED-MAINTENANCE • REPAIRS or UNSCHEDULED-MAINTENANCE (synonyms) • REPAIR-FACILITY

30. The ERD SCHEDULED- MAINTENANCE OWNER N:M 1:N N:M RV EQUIPMENT REPAIR 1:N 1:N N:M RENTAL 1:1 N:1 WEATHER- ITINERARY Note: Not the only model possibility! CUSTOMER

31. Models • There are a variety of different ways of modeling a particular situation • Evaluating alternatives • Does model accurately reflect the users needs and ways of doing things?

32. REA Model • An Accounting Model • Resources, Events & Agents • Resource? • Event? • Agent?