Fundamentals of Conceptual Data Modeling: Building Effective Entity-Relationship Diagrams

1. Chapter 7

2. Conceptual Data Modeling • Representation of organizational data • Purpose is to show rules about the meaning and interrelationships among data • Entity-Relationship (E-R) diagrams are commonly used to show how data are organized • Main goal of conceptual data modeling is to create accurate E-R diagrams • Methods such as interviewing, questionnaires and JAD are used to collect information • Consistency must be maintained between process flow, decision logic and data modeling descriptions

3. Process of Conceptual Data Modeling • First step is to develop a data model for the system being replaced • Next, a new conceptual data model is built that includes all the requirements of the new system • Project repository links all design and data modeling steps performed during SDLC

4. Deliverables and Outcome • Primary deliverable is the entity-relationship diagram • There may be as many as 4 E-R diagrams produced and analyzed during conceptual data modeling • Covers just data needed in the project’s application • E-R diagram for system being replaced • An E-R diagram for the whole database from which the new application’s data are extracted • An E-R diagram for the whole database from which data for the application system being replaced is drawn

5. Sample conceptual data model diagram

6. Deliverables and Outcome • Second deliverable is a set of entries about data objects to be stored in repository or project dictionary • Repository links data, process and logic models of an information system • Data elements that are included in the DFD must appear in the data model and visa versa • Each data store in a process model must relate to business objects represented in the data model

7. Data Design Concepts • Data Structures • A file or table contains data about people, places, things, or events that interact with the system • File-oriented system • File processing system • Database system

8. Data Design Concepts • Overview of File Processing • Potential problems • Data redundancy • Data integrity • Rigid data structure

9. Data Design Concepts • Overview of File Processing • Uses various types of files • Master file • Table file • Transaction file • Work file – scratch file • Security file • History file

10. Data Design Concepts • Overview of Database Systems • A properly designed database system offers a solution to the problems of file processing • Provides an overall framework that avoids data redundancy and supports a real-time, dynamic environment • Database management system (DBMS) • The main advantage of a DBMS is that it offers timely, interactive, and flexible data access

11. Data Design Concepts • Overview of Database Systems • Advantages • Scalability • Better support for client/server systems • Economy of scale • Flexible data sharing • Enterprise-wide application – database administrator (DBA) • Stronger standards

12. Data Design Concepts • Overview of Database Systems • Advantages • Controlled redundancy • Better security • Increased programmer productivity • Data independence

13. Data Design Concepts • Database Tradeoffs • Because DBMSs are powerful, they require more expensive hardware, software, and data networks capable of supporting a multi-user environment • More complex than a file processing system • Procedures for security, backup, and recovery are more complicated and critical

14. DBMS Components • Interfaces for Users, Database Administrators, and Related Systems • Users • Query language • Query by example (QBE) • SQL (structured query language) • Database Administrators • A DBA is responsible for DBMS management and support

15. DBMS Components • Interfaces for Users, Database Administrators, and Related Systems • Related information systems • A DBMS can support several related information systems that provide input to, and require specific data from, the DBMS • ODBC – open database connectivity • JDBC – Java database connectivity

16. DBMS Components • Data Manipulation Language • A data manipulation language (DML) controls database operations, including storing, retrieving, updating, and deleting data • Schema • The complete definition of a database, including descriptions of all fields, tables, and relationships, is called a schema

17. DBMS Components • Physical Data Repository • The data dictionary is transformed into a physical data repository, which also contains the schema and subschemas • The physical repository might be centralized, or distributed at several locations

18. Web-Based Database Design • Characteristics of Web-Based Design • In a Web-based design, the Internet serves as the front end, or interface, for the database management system • Web-based systems are popular because they offer ease of access, cost-effectiveness, and worldwide connectivity

19. Web-Based Database Design • Data Security • Web-based data must be totally secure, yet easily accessible to authorized users • To achieve this goal, well-designed systems provide security at three levels: the database itself, the Web server, and the telecommunication links that connect the components of the system

20. Data Design Terminology • Definitions • Entity • Table or file • Field • Attribute • Common field • Record • Tuple

21. Data Design Terminology • Key Fields • Primary key • Combination key • Composite key • Concatenated key • Multi-valued key • Candidate key • Nonkey field • Foreign key • Secondary key

22. Data Design Terminology • Referential Integrity • Validity checks can help avoid data input errors

23. Entity-Relationship Diagrams • An entity is a person, place, thing, or event for which data is collected and maintained • Provides an overall view of the system, and a blueprint for creating the physical data structures • Entity-relationship diagram

24. Introduction to Entity-Relationship (E-R) Modeling • Notation uses three main constructs • Data entities • Relationships • Attributes • Entity-Relationship (E-R) Diagram • A detailed, logical representation of the entities, associations and data elements for an organization or businessxxxxxxxx

25. Entity-Relationship (E-R) ModelingKey Terms • Entity • A person, place, object, event or concept in the user environment about which the organization wishes to maintain data • Represented by a rectangle in E-R diagrams • Entity Type • A collection of entities that share common properties or characteristics • Attribute • A named property or characteristic of an entity that is of interest to an organization

26. Entity-Relationship (E-R) ModelingKey Terms • Candidate keys and identifiers • Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type • Candidate key • Attribute (or combination of attributes) that uniquely identifies each instance of an entity type

27. Entity-Relationship (E-R) ModelingKey Terms • Identifier • A candidate key that has been selected as the unique identifying characteristic for an entity type • Selection rules for an identifier • Choose a candidate key that will not change its value • Choose a candidate key that will never be null • Consider substituting single value surrogate keys for large composite keys

28. Entity-Relationship (E-R) ModelingKey Terms • Relationship • An association between the instances of one or more entity types that is of interest to the organization • Association indicates that an event has occurred or that there is a natural link between entity types • Relationships are always labeled with verb phrases

29. Conceptual Data Modeling and the E-R Diagram • Goal • Capture as much of the meaning of the data as possible • Result • A better design that is easier to maintain

30. Degree of Relationship • Degree • Number of entity types that participate in a relationship • Three cases • Unary • A relationship between two instances of one entity type • Binary • A relationship between the instances of two entity types • Ternary • A simultaneous relationship among the instances of three entity types • Not the same as three binary relationships

31. Example relationships of different degrees

32. Cardinality • The number of instances of entity B that can be associated with each instance of entity A • Minimum Cardinality • 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

33. Naming and Defining Relationships • Relationship name is a verb phrase • Avoid vague names • Guidelines for defining relationships • Definition explains what action is being taken and why it is important • Give examples to clarify the action • Optional participation should be explained • Explain reasons for any explicit maximum cardinality

34. Naming and Defining Relationships • Guidelines for defining relationships • Explain any restrictions on participation in the relationship • Explain extent of the history that is kept in the relationship • Explain whether an entity instance involved in a relationship instance can transfer participation to another relationship instancexxxxxxxxx

35. Entity-Relationship Diagrams • Drawing an ERD • The first step is to list the entities that you identified during the fact-finding process and to consider the nature of the relationships that link them

36. Normalization • Table design • Involves four stages: unnormalized design, first normal form, second normal form, and third normal form • Most business-related databases must be designed in third normal form

37. Normalization • Standard Notation Format • Designing tables is easier if you use a standard notation formatto show a table’s structure, fields, and primary key Example: NAME (FIELD 1, FIELD 2, FIELD 3)

38. Normalization • Repeating Groups and Unnormalized Designs • Repeating group • Often occur in manual documents prepared by users • Unnormalized design

39. Normalization • First Normal Form • A table is in first normal form (1NF) if it does not contain a repeating group • To convert, you must expand the table’s primary key to include the primary key of the repeating group • Second Normal Form • To understand second normal form (2NF), you must understand the concept of functional dependence • Functionally dependent

40. Normalization • Second Normal Form • A standard process exists for converting a table from 1NF to 2NF • Create and name a separate table for each field in the existing primary key • Create a new table for each possible combination of the original primary key fields • Study the three tables and place each field with its appropriate primary key

41. Normalization • Second Normal Form • Four kinds of problems are found with 1NF designs that do not exist in 2NF • Consider the work necessary to change a particular product’s description • 1NF tables can contain inconsistent data • Adding a new product is a problem • Deleting a product is a problem

42. Normalization • Third Normal Form • 3NF design avoids redundancy and data integrity problems that still can exist in 2NF designs • A table design is in third normal form (3NF) if it is in 2NF and if no nonkey field is dependent on another nonkey field • To convert the table to 3NF, you must remove all fields from the 2NF table that depend on another nonkey field and place them in a new table that uses the nonkey field as a primary key

43. Normalization • A Normalization Example • To show the normalization process, consider the familiar situation, which depicts several entities in a school advising system: ADVISOR, COURSE, and STUDENT

44. Using Codes During Data Design • Overview of Codes • Because codes often are used to represent data, you encounter them constantly in your everyday life • They save storage space and costs, reduce transmission time, and decrease data entry time • Can reduce data input errors

45. Using Codes During Data Design • Types of Codes • Sequence codes • Block sequence codes • Alphabetic codes • Category codes • Abbreviation codes – mnemonic codes • Significant digit codes • Derivation codes • Cipher codes • Action codes

46. Using Codes During Data Design • Developing a Code • Keep codes concise • Allow for expansion • Keep codes stable • Make codes unique • Use sortable codes • Avoid confusing codes • Make codes meaningful • Use a code for a single purpose • Keep codes consistent

47. Steps in Database Design • Create the initial ERD • Assign all data elements to entities • Create 3NF designs for all tables, taking care to identify all primary, secondary, and foreign keys • Verify all data dictionary entries • After creating your final ERD and normalized table designs, you can transform them into a database

48. Database Models • Relational Databases • The relational model was introduced during the 1970s and became popular because it was flexible and powerful • Because all the tables are linked, a user can request data that meets specific conditions • New entities and attributes can be added at any time without restructuring the entire database

49. Database Models • Object-Oriented Databases • Many systems developers are using object-oriented database (OODB) design as a natural extension of the object-oriented analysis process • Object Data standard • Object Database Management Group (ODMG) • Each object has a unique object identifier

50. Data Storage and Access • Data storage and access involve strategic business tools • Strategic tools for data storage and access • Data warehouse - dimensions