Download
data modelling ii n.
Skip this Video
Loading SlideShow in 5 Seconds..
Data Modelling II PowerPoint Presentation
Download Presentation
Data Modelling II

Data Modelling II

185 Views Download Presentation
Download Presentation

Data Modelling II

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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