Data Modeling and Database Design

1. Data ModelingandDatabase Design

2. Overview of Database Design • Capture Users’ “Views” of data • Forms, Screens, Reports • Data Modeling (entity-based ERD) • Add keys, attributes, relationships (key-based, and fully-described ERD) • Consolidate data models – One big ERD • Determine Database Schema (database design)

3. Data Modeling • Data modeling is a technique for organizing and documenting a system’s DATA. Data modeling is sometimes called database modeling because a data model is usually implemented as a database.

5. System Concepts • Systems thinking is the application of formal systems theory and concepts to systems problem solving. • An ERD depicts data in terms of the entities and relationships described by the data.

6. Entities • An entity is something about which we want to store data. • An entity is a classof persons, places, objects, events, or concepts about which we need to capture and store data. • An entity instance is a single occurrence of an entity.

7. Attributes • An attribute is a descriptive property or characteristic of an entity. Synonyms include element, property, and field

8. Attributes - Identification • Every entity must have an identifier or key. • An key is an attribute, or a group of attributes, which assumes a unique value for each entity instance. It is sometimes called an identifier. • Sometimes more than one attribute is required to uniquely identify an instance of an entity. • A group of attributes that uniquely identifies an instance of an entity is called a concatenated key. Synonyms include composite key and compound key.

9. Relationships • A relationship is a natural business association that exists between one or more entities. The relationship may represent an event that links the entities, or merely a logical affinity that exists between the entities. • A connecting line between two entities on an ERD represents a relationship. • A verb phrase describes the relationship. • All relationships are implicitly bidirectional, meaning that they can interpreted in both directions.

11. Cardinality • Each relationship on an ERD also depicts the complexity or degree of each relationship, and this is called cardinality. • Cardinality defines the minimum and maximum number of occurrences of one entity for a single occurrence of the related entity. Because all relationships are bi-directional, cardinality must be defined in both directions for every relationship.

13. Foreign Keys • A relationship implies that instances of one entity are related to instances of another entity. • To be able to identify those instances for any given entity, the primary key of one entity must be migrated into the other entity as a foreign key. • A foreign key is a primary key of one entity that is contributed to (duplicated in) another entity for the purpose of identifying instances of a relationship. A foreign key (always in a child entity) always matches the primary key (in a parent entity).

15. (a) (b)

16. Data Modeling During Systems Analysis • A key-based data model will be drawn first. • A fully attributed data model will be constructed along with the process of analysis and design. • Each attribute is defined in the repository with data types, domains, and defaults.

17. How to Construct Data Models • 1st Step - Entity Discovery • The first task is to discover those fundamental entities in the system. • There are several techniques that may be used to identify entities. • During interviews or JAD sessions with system owners and users, pay attention to key words in their discussion. • During interviews or JAD sessions, specifically ask the system owners and users to identify things about which they would like to capture, store, and produce information. • Study existing forms and files. • Some CASE tools can reverse engineer existing files and databases into physical data models.

18. How to Construct Data Models • 1st Step - Entity Discovery • An entity has multiple instances • Entities should be named withnounsthat describe the person, event, place, or intangible thing about which we want to store data. • Define each entity in business terms.

19. How to Construct Data Models • 2nd Step - The Context Data Model • The second task in data modeling is to construct the context data model. • The context data model includes the fundamental or independent entities that were previously discovered. • An independent entity is one which exists regardless of the existence of any other entity. Its primary key contain no attributes that would make it dependent on the existence of another entity. • Independent entities are almost always the first entities discovered in your conversations with the users. • Relationships should be named with verb phrases that, when combined with the entity names, form simple business sentences or assertions. • If only one-way naming is used, always name the relationship from parent-to-child.

21. How to Construct Data Models • 3rd Step - The Key-Based Data Model • The third task is to identify the keys of each entity. • If you cannot define keys for an entity, it may be that the entity doesn’t really exist—that is, multiple occurrences of the so-called entity do not exist.

23. How to Construct Data Models • 4th Step - Generalized Hierarchies • At this time, it would be useful to identify any generalization hierarchies in a business problem. • 5th Step - The Fully Attributed Data Model • The fifth task is to identify the remaining data attributes. • 6th Step - The Fully Described Model • The last task is to fully describe the data model. • Most CASE tools provide extensive facilities for describing the data types, domains, and defaults for all attributes to the repository.

24. Conventional Files Versus the Database • Files are collections of similar records. • Databases are collections of interrelated files. • Discuss advantages and disadvantages of each • Compare and Contrast

25. Databases • A database management system (DBMS) is specialized computer software available from computer vendors that is used to create, access, control, and manage the database.

27. A Simple Logical Data Model

29. The Database Schema • Data type. Each DBMS supports different data types, and terms for those data types. • Size of the Field. Different DBMSs express precision of real numbers differently. • Required or NOT Required. Must the field have a value before the record can be committed to storage? • Domains. Many DBMSs can automatically edit data to ensure that fields contain legal data. • Default. Many DBMSs allow a default value to be automatically set in the event that a user or programmer submits a record without a value. • Referential Integrity. An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation

30. Other Considerations • Denormalization • Choices of storage formats • File organizations • Backup/Recovery • Security of Data