Diploma of Government (Enterprise Architecture)

1. Diploma of Government (Enterprise Architecture) Block 11 Introduction to Data Modelling

2. Overview • ER diagrams • ER relationships • Connectivity and Cardinality • Relationships with attributes • Weak entities • Supertypes and Subtypes

3. Components of an E-R Model(using Chen’s convention) Rectangles are used to represent entities which are things about which we wish to store data. Ellipses to indicate the attributes, which are the data we wish to store. Diamonds are used to represent the relationships between entities. The number 1 (one), and the letters “M” and “N” are used to indicate the type of relationship; also “crows feet” notation

4. Entities or Entity Sets • An entity is “A person, place, event, or thing for which we intend to collect data” • Normally a database will contain data about groups of similar entities (e.g. students, subjects, licences, aircraft or whatever) • These groups of similar entities are referred to as entity sets but often this is shortened to just “entity”

5. Entities & Entity Sets • If we wish to speak of a particular member of an entity set (e.g. a certain student) then we refer to an entity instance or entity occurrence • Entity sets are represented on entity-relationship diagrams by named rectangles

6. Entity Sets • Entity sets are conventionally named in the singular Systems Architect Most Notations student

7. Entities & Attributes • What particular things do we want to record about the entities in the database? • The name of a student • The id number of a subject • The type of an aircraft • Generally there will be many such “things” or attributesthat we want to record about a particular kind of entity

8. Attributes • In most notations attributes are represented on ER diagrams as ellipses attached to the relevant entity set symbol • Systems Architect uses a form similar to UML Class Diagrams, and also similar to Microsoft Access Relationship Diagrams

9. Attributes DOB Name Address Sex Number student

10. Workbook Exercise 1 • On a scrap of paper sketch out the diagram for the entity “car” • Use your own understanding of what attributes you would want to store

12. Relationships • A relationship is an association between entity sets • Relationships are represented by diamond shaped symbols on ER diagrams • A descriptive name is placed inside the relationship symbol

13. Relationships enrols in student subject

14. Relationships & Entity Sets • Entity set names are usually nouns • Relationship names are usually, though not always verbs (or verb phrases) • Most relationships are binary (i.e. connect 2 entity sets) - like “enrols in” • Other types of relationships are possible

15. Degree of a Relationship • The degree of a relationship is the number of entity set(s) that it connects • One Unary • Two Binary • Three Ternary • Relationships of degree higher than three are rare

16. A Unary Relationship supervises employee

17. A Binary Relationship enrols in student subject

18. A Ternary Relationship item vendor sale purchaser

19. A Ternary Relationship

20. Ternary & Binary Relationships • Note that, in general, a ternary relationship is not equivalent to three binary relationships 

21. Relationship Connectivity • Relationships can have different connectivities • one-to-one (1:1) • one-to-many (1:N) • many-to-many (M:N) • Indicated on the ER diagram by placing an appropriate symbol on each “leg” of the relationship

22. Relationship Connectivity 1 1 head of staff department 1 N teaches lecturer subject

23. Relationship Connectivity 1 supervisor supervises employee N supervisee M N enrols in student subject

24. Relationship Connectivity • Note that the connectivity specifies how the entity instances may be connected, not how they must be connected • For example: • In general a student may be enrolled in many subjects (and a subject will in general have many students), so the relationship “enrols in” is M:N - but this does not mean that every student must always be enrolled in many subjects

25. Relationship Cardinality • Sometimes minimum and maximum values for connectivity are shown on the ER diagram • 0..* means zero to many (optional) • 0..1 means zero to one (also optional) • 1..* means one to many (mandatory) • 1..1 means one to one (mandatory) • This is often referred to as the cardinality of the relationship • “Cardinality” is also used as a synonym for “connectivity”

26. Relationship Participation • Entity sets connected by a relationship can have two types of “participation” in it • optional • mandatory • “Mandatory” means that every entity instance must be connected (through the relationship) to an instance of the other participating entity set(s) • “Optional” means - not mandatory

27. Relationship Participation • Examples: • Every department must have a staff member who is its head • A staff member need not be a head of department • This means that the participation (in the relationship “head of”) is: • mandatory for the entity set “department” • optional for the entity set “staff”

28. Key Attribute(s) • There will normally be one, or perhaps several, attributes that will be unique for every entity instance • Example: • Every student will have a unique student number • Such an attribute (or combination) is called a key

29. Key Attribute(s) • If the key for an entity set consists of two or more attributes in combination it is called a concatenated key • Key attribute(s) are underlined on the ER diagram (Chen notation), and • Are indicated in a separate area in the SA notation

30. Key Attribute DOB Name Address Sex Number student

31. Relationships and Attributes • A relationship is an association between entity sets • Relationships can also have attributes • An attribute of a relationship is drawn attached to the relationship diamond • Once a relationship has a key attribute SA will change the symbol from the diamond to a “weak entity” (described later)

32. A Unary Relationship With an Attribute supervises Project employee Systems Architect only permits Attributes on “defining relationships”, hence because an employee does not define another employee, you can’t draw this sort of diagram. In reality, this sort of requirement seldom happens.

33. A Binary Relationship with an Attribute enrols in student subject Date of enrolment

34. A Binary Relationship with Attributes and Key attributes enrols in student subject Grade for enrolment Date of enrolment

35. A Ternary Relationship with Attributes item vendor sale purchaser Invoice Number Invoice Date

36. A Ternary Relationship with Attributes

37. Weak Entity Sets • Sometimes the instances of one entity set depend on their relationship to the instances of another entity set for their unique identification Name Number 1 N consists of golf course hole

38. Weak Entity Sets • In order to uniquely identify an instance of a weak entity set, one must specify the (partial) key of the weak entity plus the key of the entity set on which it is dependent • That is - the golf hole number and the golf course name

39. Weak Entity Sets • Weak entity sets are represented on ER diagrams using a doubled rectangle (Chen) Name Number 1 N consists of golf course hole

40. Supertypes & Subtypes • Sometimes notionally different entity sets are really specializations of a more general entity set • Example: • Trucks, cars, motorcycles, buses, taxis are all motor vehicles • Some attributes are common to all, others are specific to one group

43. Supertypes & Subtypes • This kind of situation can be dealt with using a generalization hierarchy (or supertype/subtype hierarchy) • The attribute(s) that are common belong to the supertype • The attributes that are specific are attached to the relevant subtype

44. Supertypes & Subtypes Number of seats Registration motor vehicle G G truck car bus truck attributes car attributes bus attributes

45. Supertypes & Subtypes

46. Supertypes & Subtypes • Sometimes subtypes are disjoint (i.e. if an entity instance belongs to one subtype it cannot belong to any of the others) - e.g. a car is not also a truck or a bus • Sometimes subtypes can overlap (e.g. an employee (supertype) can be both an engineer and a pilot (subtypes))

47. Overlapped Subtypes Employee Name Employee Number Sex Employee Residential Address DOB G G Pilot Engineer Safety Officer Pilot attributes Engineer attributes Safety attributes

48. Steps to Construction • Identify objects • Identify attributes of objects • Identify Keys of objects • Identify relationships between objects • Identify attributes of relationships • Sketch the diagram (on paper) • Draw the diagram (using Systems Architect) • Show stakeholder • Revise as necessary

49. Workbook Exercise 2 • Sketch the ER diagram for the description below • DSTO requires a database to keep track of its research projects which are worked on by many different staff. Each research project has a “Principal Investigator” assigned to it so that any external funding agencies have a single point of contact. Each research project also produces research reports which address different research topics within the research project. Staff may work on more that one project. However, a senior staff member who supervises a junior staff member will do so across all the projects in which the junior is active.