Lecture 11 – Data Modelling

1. Lecture 11 – Data Modelling Maria Petridou Data Dictionaries Entity Relationship Diagram for Data Modelling Steps to Construct Entity Relationship Diagrams Validation of Entity Relationships Diagram

2. Data Dictionaries • Itis a main method for analyzing the data flows and data stores of data-oriented computer systems. • The data dictionary is a reference work of data about data (metadata) • It collects, coordinates, and confirms what a specific data term means to different people in the organization. • Data dictionaries are created by making a detailed analysis of the data flows, data stores, and processes in data flow diagrams. Maria Petridou

3. Data Dictionaries • The data dictionary may be used for the following reasons: • Provide documentation • Eliminate redundancy • Validate the data flow diagram for completeness and accuracy • Provide a starting point for developing screens and reports • Determine the contents of data that is stored Maria Petridou

4. Data Dictionaries • Data dictionaries may be created after the DFDs or at the same time than the DFDs using a top-down approach. • Creating a data dictionary is an iterative and evolutionary process that should be carried out in parallel to the systems analysis and design. Maria Petridou

5. Data Dictionaries • Data Repository is a large collection of project information including: • Information about system data • Procedural logic • Screen and report design • Relationships between entries • Project requirements and deliverables • Project management information Maria Petridou

6. Data Dictionaries • Data dictionaries contain: • Data flow Each data flow should be defined with descriptive information and it's compound with structure or elements. • Data structures They are a group of smaller structures and elements. Specific arrangements of data attributed (elements) that define the organization of a single instance of a data flow. • Data Elements The descriptive property or characteristic of an entity. In database terms, this is a "attribute" or a "field." • Data stores A inventory of data. The whole of the data in a small system. A database! Maria Petridou

7. Entity Relationship Diagrams for Data Modelling • Data modelling focuses on data flows and data stores represented in the data flow diagrams. • Data models can be of two types: • Logical data models – describe logical organisation and presentation of data. A technique for logical data modelling is the entity-relationship diagrams. • Physical data models – produced in the design phase, give implementation details about how data will actually be stored in databases and files. • An Entity-Relationship Diagram (ERD) shows how the data that flows in the system is organised and used. Maria Petridou

8. Entity Relationship Diagrams for Data Modelling • The elements of an ERD are the following: • Entity – multiple instances about which the data is collected. • Attributes – specific details or information about an entity. • Relationships – associations between entities. A relationship has: • Cardinality - The number of instances of entity B that can be associated with each instance of entity A. • Minimum Cardinality or Modality - The minimum number of instances of entity B that may be associated with each instance of entity A. • Maximum Cardinality- The maximum number of instances of entity B that may be associated with each instance of entity A Maria Petridou

9. Entity Relationship Diagrams for Data Modelling Relationships • A one-to-one relationship (1:1) exists when the maximum cardinality for each entity in the relationship is 1. • A one-to-many (1:N) relationship exists when the maximum cardinality on one side is 1 and the maximum on the other side is many. • A many-to-many (M:N) relationship exists when the maximum on both sides is many. Maria Petridou

10. Entity Relationship Diagrams for Data Modelling • Types of entity relationships and example of their representation: Maria Petridou

11. Entity Relationship Diagrams for Data Modelling • Implement One-to-One Relationships • In a relational database, one-to-one relationships between entities can be implemented by merging the two tables/entities together. • Implement One-to-Many Relationships • Place the primary key of the entity that can occur only once as a foreign key in the entity that can occur many times. • Implement Many-to-Many Relationships • In a relational database, many-to-many relationships between entities can be implemented by creating a new entity that links the other two entities together. In most cases, the primary key of the new entity consists of the primary keys of the entities that it is relating together. • Spit it into two one-to-many relationships! Maria Petridou

12. Entity Relationship Diagrams for Data Modelling Student Student ID (pk) Student first name Student last name … Module Module ID(pk) Module name Module Credits … Maria Petridou Module Module ID(pk) Module name Module Credits … Student Student ID (pk) Student first name Student last name … Enrollment Module ID (pk) + (fk) Student ID (pk) + (fk) in

13. Entity Relationship Diagrams for Data Modelling Example of an ERD for the doctor’s office appointments system (see DFD in handouts of Lecture 9). Maria Petridou

14. Steps to Construct Entity Relationship Diagrams • Like constructing DFDs and data dictionaries, the construction of ERDs is an iterative and evolutionary process that requires refinement as a better understanding of the system to be developed is achieved. • Identify entities – use DFDs and use cases to identify multiple instance entities. • Add attributes – requires detailed analysis and perhaps use of requirements gathering techniques. • Assign identifiers or Primary keys – Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type. • Foreign Keys - The attributes of a foreign key in one entity are the attributes of a primary key in another entity. • Identify Relationships – involves establishing the cardinality and the modality of each relationship. Maria Petridou

15. Steps to Construct Entity Relationship Diagrams Example: Create a logical data model (based on an ERD) for the University Registration System (see handouts for Lecture 9). • Identify Entities Maria Petridou

16. Steps to Construct Entity Relationship Diagrams 2. Add Attributes Maria Petridou

17. Steps to Construct Entity Relationship Diagrams 3. Assign Identifiers Maria Petridou

18. Steps to Construct Entity Relationship Diagrams 4. Identify Relationships Maria Petridou

19. Steps to Construct Entity Relationship Diagrams • Important guidelines when creating ERDs: • Use consistent labels and names • Avoid unnecessary attributes • Break-down the model if needed • Determine cardinality and modality correctly Maria Petridou

20. Validation of Entity Relationship Diagrams • Two available techniques to validate ERDs are: Normalisation and Balancing DFD/ERD • Normalisation – set of rules applied to the ERD in order to detect problems in the data model. The data model is checked against 1st Normal Form, 2nd Normal Form and 3rd Normal Form. • Balancing DFD/ERD – there should be a balance or correspondence between the data elements in the DFD and the ERD (data stores-entities and data flows-attributes). This may provoke changes to the DFD and the ERD! Maria Petridou

21. Validation of Entity Relationship Diagrams • The DFD and ERD are balanced when: • There is a one-to-one correspondence between data stores in the DFD and entities in the ERD. • Data flows in the DFD are made of data elements corresponding to attributes in the ERD. Reading: (Kendall&Kendall,chapter 9) and (Dennis &Wixom,chapter 7). Maria Petridou