1 / 50

Data Modelling II

Data Modelling II. Plan. Introduction Structured Methods Data Flow Modelling Data Modelling Relational Data Analysis Feasibility Maintenance. Data Model Requirements. Designers, end-users and programmers tend to view data in different ways

chacha
Download Presentation

Data Modelling II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Modelling II

  2. Plan • Introduction • Structured Methods • Data Flow Modelling • Data Modelling • Relational Data Analysis • Feasibility • Maintenance

  3. Data Model Requirements • Designers, end-users and programmers tend to view data in different ways • We need a communications tool that will enable models to be understood by all • Non-technical • Un-ambiguous (Connolly & Begg, 2000)

  4. Entity • A thing that occurs more than once in the system about which we need to store information • …occurs more than once… • …about which we need to store information… (These are NOT the same as external entities in DFDS)

  5. Identifying Entities • One way of identifying possible entities is to find all the nouns in a description of a system • But… • Some of these might be attributes • Some of these might be neither entities or attributes • This may not find all of the entities

  6. Entities and Attributes • Often an entity in one system will be an attribute in another system and might be neither in another system • This depends on the context of the system we are studying • We need to be clear about the system and its data and processing requirements

  7. Student Student Number Name Address Phone Number Course ERD Elements • E.G. In a student enrolment system Entity Name Key Attribute Non-Key Attributes

  8. Student Course Student Number Course Code One-to-Many • Mulchester University Parent/Master Enrols Enrolled on Child/Detail

  9. Examples • 1 to many (1 or more) • 1 (or 0) to 1 • 1 (or 0) to many (1 or more) • Many (1 or more) to Many (1 or more) • Many (0, 1 or more) to 1 (or 0)

  10. Student Course Student Number Course Code One-to-Many • Mulchester University • Each Course MUST enrol one or more students • Each Student MUST be enrolled on one course • Student and Course cannot exist independently Enrols Enrolled on

  11. Student Course Student Number Course Code One-to-Many • Mulchester University • Each Course MAY enrol one or more students • Each Student MUST be enrolled on one course • A course may exist with no students enrolled on it Enrols Enrolled on

  12. Student Course Student Number Course Code One-to-Many • Mulchester University • Each Course MUST enrol one or more students • Each Student MAY be enrolled on one course • A student may exist that is not enrolled on a course Enrols Enrolled on

  13. Student Course Student Number Course Code One-to-Many • Mulchester University • Each Course MAY enrol one or more students • Each Student MAY be enrolled on one course • Both student and course may exist independently Enrols Enrolled on

  14. StaffID S1001 S1003 S0110 Branch B001 B002 Identifying Relationships • One to One • Each member of staff manages one branch • Each branch managed by one member of staff manages (Connolly & Begg, 2000)

  15. Branch B001 B002 StaffID S0001 S0002 S0110 S2345 Identifying Relationships • One to Many • Each Branch has Many employees • Each employee works at one branch has (Connolly & Begg, 2000)

  16. Identifying Relationships • Many to Many • £ach customer has many accounts • £ach account held by many customers CustID C1001 C1002 C5210 AccNo A001 A102 A307 (Connolly & Begg, 2000)

  17. Identifying Relationships • This is a useful exercise but… • Things are not always as they appear at any given time • Need to check with users • Historical facts • Future Requirements • Have/will staff ever worked at multiple branches

  18. Big Jim’s Bikes • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

  19. Identify Entities • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

  20. Bike Stock Number Draw ERD Customer • Each Customer can buy many bikes • Each bike is bought by one customer • Big Jim holds customer details before they buy a bike • Bike details are held before they are bought Customer Number Buys Bought by

  21. Exercise 2 • Big Jim has decided that it is too much work to list every bike. He now lists bikes by model and customers buy one of that model rather than a specific bike. Each bike still has a unique stock number. • Example models in Big Jim’s system are: • Raleigh Chopper • Penny Farthing • Dahon Espresso • Shogun XC200 • How does this effect the data model?

  22. Solution

  23. What do we know? • How to identify Entities and Attributes • Why we need a Key Attribute • How to describe the Relationships between Entities • How to draw an Entity Relationship Diagram

  24. What problems still exist? • There are some problems associated with M:N relationships that need to be resolved • We may need to examine attributes in more detail • We need to check our model against the required transactions • There are additional relationships we could describe

  25. Implementing the model • At some point we will build a database from our data model • It is likely that this will be a relational database • Access • Oracle • SQL Server • MySQL • DB2

  26. Implementing the model • The relational data model Does Not Support many-to-many relationships • These must be resolved

  27. Student Module Student Number Module Code How to resolve M:N • Example 1- Students and Modules Enrolled on Enrols

  28. Student Module Student Number Module Code Student/Module Student Number Module Code How to resolve M:N Remove the M:N relationship Add two new 1:M relationships Note the key attribute is compound Replace it with a “link” entity

  29. Student Module Student Number Module Code Enrolment Student Number Module Code How to resolve M:N We can give the new entity a more meaningful name

  30. Student Module Student Number Module Code Enrolment Student Number Module Code Grade How to resolve M:N It is likely that we will find new attributes that belong to the new entity

  31. Order Stock Items Order Number Stock Code How to resolve M:N • Example 2 – Orders and Stock Items Contains Appears on

  32. Stock Item Stock Code Order Order Number Stock/Order Order Number Stock Code How to resolve M:N

  33. Stock Item Stock Code Order Order Number Order Line Order Number Stock Code How to resolve M:N

  34. Stock Item Stock Code Order Order Number Order Line Order Number Stock Code QtyLine Total How to resolve M:N

  35. Book/Title Author ISBN Title Author Name How to resolve M:N • Example 3 – Books and Authors Written by Author of

  36. Structural Problems • Some structural problems may exist in our model • Fan Traps • Chasm Traps

  37. Car CarNo Staff StaffID Branch BranchNo Fan Traps How do we know which car is allocated to which staff? has Base for Based at at (Connolly & Begg, 2000)

  38. Fan Traps StaffID S1003 S1010 Branch B001 B002 CarNo V001 V002 V003 (Connolly & Begg, 2000)

  39. StaffID S1003 S1010 Branch B001 B002 CarNo V001 V002 V003 Fan Traps (Connolly & Begg, 2000)

  40. Car CarNo Staff StaffID Branch BranchNo Fan Traps has Base for Based at at allocated Allocated to (Connolly & Begg, 2000)

  41. Staff StaffNo Branch BranchNo Car CarNo Chasm Traps How do we know which staff work at a branch? Base for Based at allocated (Connolly & Begg, 2000)

  42. Chasm Traps Branch B001 B002 CarNo V001 V002 StaffID S1003 S1010 (Connolly & Begg, 2000)

  43. Chasm Traps Branch B001 B002 CarNo V001 V002 StaffID S1003 S1010 (Connolly & Begg, 2000)

  44. Staff StaffNo Branch BranchNo Car CarNo Chasm Traps has Base for at Based at allocated (Connolly & Begg, 2000)

  45. Surfboard SurfboardNo Boat BoatID Customer Customer Number Other relationships Customer can either book boats or surfboards but not both a a

  46. What do we know? • How to identify relationships • How to resolve M:N relationships • What chasm and fan traps look like • Other ways of representing relationships

  47. Normalisation • ERDs provide a top-down means of modelling data • Normalisation is a complimentary approach which works from the bottom up • We can also use normalisation to ensure that we have removed redundant data from our ERD

  48. References • Whiteley, D. (2004) Introduction to Information Systems, Palgrave, 2004. • Lejk, M. and D. Deeks (2002) Systems Analysis Techniques, Addison Wesley 2002 • Mason, D. and L. Willcocks (1994), Systems Analysis, Systems Design, Alfred Waller, 1994.

  49. References • Yeates, D. and T. Wakefield (2004) Systems Analysis and Design, FT/Prentice Hall 2004 • Gane, C. and T. Sarson (1979) Structured Systems Analysis, Prentice Hall, 1979 • Eva, M (1994) SSADM Version 4: A users guide, McGraw hill, 1994

  50. References • DeMarco, T. (1979) Structured Analysis and System Specification, Yourdon, 1979 • Royce, W. (1970) Managing the development of large software systems, In: Proceedings of IEEE WESCON, 1970 pp1-9. • Connolly, T. and C. Begg (2000) Database Solutions, Addison-Wesley, 2000

More Related