Diploma of Government (Enterprise Architecture)

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

2. Overview • The Data view compared with the process view • Transition from lists, data dictionaries and glossaries to Entity Relationship diagrams • Why use databases? • The Relational Model

3. Modelling Activities records the Processes and the Data used • Processes and Activities can change quite frequently, but the data they use changes less often • OV-5 diagrams • Record Processes which act upon or transform data • Record the data required for, and produced by, each process • OV-2 and OV-3 Identify major Data Stores, Repositories and Flows • Together they model the work flow of the organisation • What they do not show, is the relationships within the data shown on the OV-7 and SV-11

4. The Data Dictionary and the CV2 give further data definitions Completed Invoice = Customer Number + Customer Name + Customer Address + 1{Labour Details}10 + 1{Parts Details}50 + Invoice Number + Invoice Date + (Message to Customer)

5. Entity Relationship Modelling • The E-R model is a Conceptual Model with a high degree of abstraction. • It models the Data in a system, and the Relationships that exist between the data. • Systems Architect will convert E-R models to OV7 Logical Data Model and SV11 Physical Data Model • It is essential to Database Design, because it yields the Conceptual Schema which is the database blueprint. • Systems Architect will also generate the Schema (SQL) for a number of prominent suppliers of Database software

6. So why use Databases? • Historically, Data often existed in independent files • Programs accessed data files directly leading to program/data dependence • Data often duplicated across many different files leading to inconsistency, confusion and difficulties of integration

7. File Based Systems Programs & Users Programs & Users Programs & Users Files Files Files

8. Database Systems • A database is, simplistically, a collection of files that contain related data • These related files are managed by a specialized piece of software known as a database management system (DBMS) • Users or application programs request the DBMS to retrieve/change/insert/delete it for them

9. Database Systems User or program User or program User or program User or program DBMS Database files

10. Advantages of Database Systems • Users and application programs need not know exactly where or how the data is stored in order to access it - program/data independence • Proper database design can reduce or eliminate data redundancy and confusion • Better security facilities are generally offered

11. Advantages of Database Systems • Support for unforeseen (ad hoc) information requests are better supported - better flexibility • Data can be more effectively shared between users and/or application programs • Data can be stored for long term analysis (data warehousing, data mining)

12. Database Technologies • There are four major database technologies or “models” • Hierarchical • Network • Relational • Object-oriented • Of these, hierarchical is mostly out-dated and network technologies are yet to deliver their promised flexibility

13. The Relational Model • Doesn’t have hidden pointers to connect entities - just uses data • Any entity can be connected to any other entity by using data • Is therefore VERY flexible • Data is organised into Tables, which can be accessed in any sorted order • ER diagrams can be “easily” converted into the relational model (in fact Systems Architect will convert them to OV-7 and SV-11 at the click of a button!)

14. Database Design • To reap the potential benefits offered by database technology, databases must be properly designed • Effort spent in design is always rewarded in data quality • There are different (though complementary) approaches to achieving a good design

15. Approaches to Database Design • Entity-Relationship (ER) data modelling • A graphical technique for understanding and organizing the data independently of the eventual database implementation • Normalization • An algorithmic process for evaluating the quality of a database design - most applicable to relational database designs

16. Entity-Relationship Models • Entity-Relationship models are conceptual models • That is, they are hardware & DBMS independent • They represent the semantics (i.e. meaning) of the data to be stored in the database • There are different notations; we will look at two

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

18. Entity-Relationship Basics: • The concepts upon which ER models are built are: • Entities (or, more correctly, entitysets) • Relationships (between entities) • Attributes (of entities and relationships)

19. 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”

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

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

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

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

24. Attributes DOB Name Address Sex Number student

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

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

28. Relationships enrols in student subject

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

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

31. A Unary Relationship supervises employee

32. A Binary Relationship enrols in student subject

33. A Ternary Relationship item vendor sale purchaser

34. A Ternary Relationship

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

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

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

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

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

40. 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”

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

42. 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”

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

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

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

47. Key Attribute DOB Name Address Sex Number student

48. Simple Vs Composite Attributes • An attribute such as “Height” cannot be divided into simpler components - it is a simple attribute • An attribute such as DOB may be so divided - into day, month and year - it is a composite attribute • Composite attributes can be broken down on the ER diagram (if necessary)

49. Single & Multi-valued Attributes • Some attributes can take on only a single value for each entity instance (e.g. DOB) - they are single valued • Other attributes may take on many values for a particular entity instance (e.g. Qualification, since one person may have none, one or many qualifications) - they are multi-valued

50. Multi-valued Attributes • Represented on an ER diagram by using a doubled connecting line to the relevant entity set symbol (can also use a doubled ellipse symbol) Qualification person Systems Architect does not model multi-valued Attributes