1 / 78

Data Modelling

Data Modelling. Entities, Attributes and Relationships. Data Modelling. Technique for describing information structures Information models represent: things - entities properties of things - attributes associations between things - relationships. A. Entities.

melchior
Download Presentation

Data Modelling

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 Entities, Attributes and Relationships

  2. Data Modelling • Technique for describing information structures • Information models represent: • things - entities • properties of things - attributes • associations between things - relationships

  3. A Entities • Abstractions of real world things e.g. CUSTOMER does not relate to specific customers ...any distinguishable person, place, thing, event or concept about which information is kept. (Bruce 1992) Specific customer

  4. Attributes • The elements of data belonging to an entity are known as its attributes A/C No. Name Address Tel No. Credit limit Customer

  5. Relationships • Imagine two entities: Lecturer and Student • Lecturers teach students • Teaching is the “relationship” between the two abstract entities

  6. Logical Data Model An Entity Type has a set of attributes e.g. Customer has attributes of Account Number Name Address Telephone Number Credit Limit

  7. C B A Logical Data Model An Entity Type may have a number of occurrences. Each Entity Occurrence has a unique set of values for the attributes. A/C No. Name Address Tel No. Credit limit Customer Entity Type Customer Entity Occurrences

  8. Logical Data Model Customer Entity AttributeValue Account Number BL032 Name Bloggs & Son Address 117 Acacia Rd Birmingham 7 Telephone Number 0121 345678 Credit Limit £2500

  9. Reg. No. Surname Forename 45632 Jones David 45315 James Sarah Jones 44355 Mary 45422 Hill Simon A Table or Relation Each row of the table is unique.

  10. Entities, Tables & Relations • An Entity Type is represented as a Table (Relation) • Each Row (Tuple) of the Table is an Occurrence of the Entity • Each Column (Domain) of the Table contains the Values of one Attribute of the Entity

  11. Physical Data Organisation • An Entity Type is usually implemented as a File in the Physical Storage Medium • Each Entity Occurrence is a Record in the File • The Value of an Attribute of the Entity Occurrence is stored in a Field within the Record

  12. Physical Organisation physical medium file field record

  13. one record repeating fields order no. item item item item order no. customer qty. qty. qty. qty. customer Repeating Attributes / Fields Order File / Entity Are repeating attributes (fields) really attributes of this entity?

  14. One Entity Occurrence / Record only one item but … Order Customer Item Quantity Number 1234 Ace XY345 8 Another Occurrence / Record Order Customer Item 1 Quantity 1 Item 2 Quantity2 ........... Number 2156 Williams CCD3 2 TR805 5 ........... Attributes / Fields of an Order

  15. Entities or Attributes? When is data an Attribute of an Entity and when is it a separate Entity? Can one Entity ever be considered to be simply Attributes of another?

  16. Symbols for entities customer student hard box soft box Diagrammatic Representation

  17. order item Diagrammatic Representation Relationship between entities master one order can be for many items crow’s foot detail

  18. order one to one one to many many to many order order delivery delivery delivery Degrees of Relationship

  19. Customer Sales Order Optional Relationships An Order must be for a Customer but a Customer may not have any orders optional at the customer end

  20. Consultant System • A Client has an Account • Consultants have a Grade and a number of Skills • Consultants are active on various Projects • Each Project is for one Account

  21. Possible Entities for Consultant System

  22. Initial Attempt at Relationships

  23. Resolving One to One Relationships A Client can only have one Account. Client Account The account is an attribute of the client and NOT a separate entity. Simply merge the entities which have a one to one relationship.

  24. One to one relationships resolved

  25. Resolving Many to Many Relationships Difficult to implement so how can they be replaced? What is the nature of the relationship between an Actor and a Scene?

  26. Resolving Many to Many Relationships Create a linking entity which is a detail to both the original entities. Actor and Scene are both masters to the new linking entity of Appearance.

  27. Patient Drug Consultant Skill ? ? Resolving Many to Many Relationships What would make suitable entities and what attributes might they have?

  28. Many to many relationships resolved Avoid crossed relationships Resuscitate dead crows!

  29. Developing a Logical Data Structure • identify possible entities • draw initial entity relationship diagram • resolve 1:1 and many:many relationships & check for further entities and relationships • remove redundant relationships • show optionality

  30. Cross-checking the LDS The LDS is derived using a “Top Down” approach. It can be cross-checked by using a “Bottom Up” approach, building up the entities from their attributes. This technique is called Normalisation which is the subject of the next lecture.

  31. Relational Data Analysis Normalisation With thanks to Codd & Date.

  32. One Entity Occurrence / Record only one item but … Order Customer Item Quantity Number 1234 Ace XY345 8 Another Occurrence / Record Order Customer Item 1 Quantity 1 Item 2 Quantity2 ........... Number 2156 Williams CCD3 2 TR805 5 ........... Attributes / Fields of an Order

  33. Normalisationobjectives • to reduce data redundancy • to hold each data item (attribute) with as few occurrences as possible • to identify and remove any dependencies between data items stored together (in the same table)

  34. unique primary key Reg. No. Surname Forename 45632 Jones David row (tuple) 45315 James Sarah Jones 44355 Mary 45422 Hill Simon column (attribute) A RelationA two-dimensional table

  35. Consultant No. Project No. Time (days) C232 C979 A176 C232 3.0 10.5 5.0 20.0 001 001 003 003 A Compound Key

  36. key attribute underlined Reg. No. Surname Forename Consultant No. Project No. Time (days) compound key Attributes Listed

  37. Consultant Details No. Name Address Grade Salary Scale 004 Mary Wheeler 236 Fore Street D S2 Ivybridge Devon Skills Code Description Qualification SK01 Accounting IMA SK10 CAD / CAM 3 yrs. AutoCAD SK15 SSADM V4 Certificate Un-Normalised Data

  38. Assumptions • Consultant No., Skill Code and Grade are unique. • A consultant can have many skills each identified by a Skill Code. • For each skill only the consultant’s highest Qualification is recorded. • Other consultants may have the same skills (and Skill Code) but not necessarily the same Qualification. • Each Skill Code has one Description. • Each Grade belongs to one Salary Scale.

  39. Consultant No. Name Address Grade Salary Scale Skill Code Description Qualification key should uniquely identify a row in the table Un-Normalised Form (UNF)

  40. UNF Consultant No. Name Address Grade Salary Scale (Skill Code Description Qualification) UNF • list all data attributes • allocate primary key • identify repeating group(s) (optional)

  41. First Normal Form (1 NF)Rule remove repeating data Consultant No. Name Address Grade Salary Scale Skill Code Description Qualification

  42. Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Description Qualification The compound key of the new table which contains the repeating group consists of the original key plus the attribute(s) which uniquely identify a single set of repeating values given a single value of the original key. First Normal Form (1 NF)

  43. UNF 1NF Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Description Qualification Consultant No. Name Address Grade Salary Scale (Skill Code Description Qualification) 1NF • separate repeating group • copy non-repeating group unchanged • add initial primary key to repeating group & identify compound key

  44. Second Normal Form (2 NF)Rule remove part-key dependencies Consultant No. Skill Code Description Qualification

  45. Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Qualification Skill Code Description Second Normal Form (2 NF)

  46. 1NF 2NF Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Qualification Skill Code Description Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Description Qualification 2NF • separate part-key dependencies • all other groups are copied across unchanged • do not omit key only groups

  47. Third Normal Form (3 NF)Rule remove inter-data (and inter-key) dependencies Consultant No. Name Address Grade Salary Scale

  48. Salary Scale Grade A B C D E F G S1 S2 S3 Inter-Data Dependency

  49. Consultant No. Name Address Grade Grade Salary Scale Consultant No. Skill Code Qualification Skill Code Description * * denotes a Foreign Key Third Normal Form (3 NF)

  50. 3NF 2NF Consultant No. Name Address Grade Salary Scale Consultant No. Skill Code Qualification Skill Code Description Consultant No. Name Address Grade Grade Salary Scale Consultant No. Skill Code Qualification Skill Code Description * 3NF • separate inter-data (non-key) dependencies • identify foreign keys

More Related