Lecture 5

1. Lecture 5 Entity Relationship Modeling

2. Topics • Real things and descriptions • Example for the workshop • Implementation into RDBMS and XML • ER models as abstract models • Modeling as a language task

3. Schema modeling design analysis Descriptions guides Real world Fact base Use Real things and descriptions

4. route departure routeno : char(8) destination: char(20) bay : char(2) * dtime : time The timetable data model As Relational tables: Route ( routeno, destination, bay) Departure ( routeno#, dtime ) SELECT * FROM route NATURAL JOIN departure;

5. As XML • <route> • <routeno>99</routeno> • <destination>Centre via Blackboy Hill</destination> • <bay>2</bay> • <departures> • <dtime>7.00</dtime> • <dtime>7:20</dtime> • … • </departures> • </route>

6. ER models are abstract • Relationships • Datatypes • Stored / derived data • Identity • Constraints • Subtypes

7. employee empno ename job hiredate salariedEmp contractEmp sal comm hourRate contractedHours Types and Subtypes

8. RDBMS implementation • In Oracle 9i and Postgres, we can implement this type/subtype relationship directly, but in SQL-92 DBMS’s we will have to do some work to achieve this. • Generate two tables, one for each of the subtypes: each table will contain the common fields: • salariedEmp (empno, ename, job, mgr, hiredate, sal, comm) • contractEmp (empo, ename, job, mgr, hiredate, hourlyRate, contractHours) • Generate three tables:one for the supertype and one for each of the subtypes • emp (empno, ename, job, mgr, hiredate) • salariedEmp (empno, sal, comm) • contractEmp (empno, contractHours)

9. Modeling and Language • Modeling as Lego • you need the ability to understand how the UoD is structured (analysis), you need knowledge of how all the different kinds of blocks work, what fits with what and then the ingenuity to use the blocks to create the closest possible model • misapprehensions

10. Creativity in naming • Guidelines • names of entities must be singular, not plural – naming the entity type, not the entity set • the name of entity should match the name of the real-world concept - employee in the real world is modeled by employee in the information system, not by employeeRecord, or employeedata, or employeedetails • Names should be based on user terminology

11. What to model • Identify the model boundary • Depends on purpose of system • Reject things outside the boundary • E.g. users, stakeholders, external agencies unless data required to be held • Reject things in the implementation • E.g. database, report • (agile modelling) Model only to the detail immediately required

12. Evaluating models • How do you know how good they are? • Generate instances of the model dept employs emp manages

13. Experienced Modelers • Martin Fowler, ‘Analysis Patterns : reusable object models’ Addison Wesley Longman ,1997 • David C. Hay, ‘ Data Model Patterns : conventions of thought’ Dorset House, New York, 1996 • Models on-line • Pet shop store • Inherent in XML specification

14. Workshop 5 • Two weeks to build a usable site with all Mon-Friday timetables • Consolidation problem – how to get different providers to supply data in the same form • Issues over • Data format • Coding • Versions