BIS 310 Week 7 BIS 310 Structured Analysis

BIS 310: Week 7 BIS 310: Structured Analysis and Design Data Modeling and Database Design

BIS 310: Week 7 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) 06. 5. 18

BIS 310: Week 7 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. 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 System Concepts 8 8 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. 06. 5. 18

BIS 310: Week 7 Entities 8 8 8 An entity is something about which we want to store data. An entity is a class of 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. 06. 5. 18

BIS 310: Week 7 Attributes • An attribute is a descriptive property or characteristic of an entity. Synonyms include element, property, and field 06. 5. 18

BIS 310: Week 7 Attributes - Identification 8 8 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 06. 5. 18 compound key.

BIS 310: Week 7 Relationships 8 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. 8 A verb phrase describes the relationship. – All relationships are implicitly bidirectional, meaning that they can interpreted in both directions. 8 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 Cardinality 8 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. 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 Foreign Keys 8 8 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). 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 (a) (b) 06. 5. 18

BIS 310: Week 7 Data Modeling During Systems Analysis 1 2 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. 06. 5. 18

BIS 310: Week 7 How to Construct Data Models : 1 st 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. 8 During interviews or JAD sessions with system owners and users, pay attention to key words in their discussion. 8 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. 8 Study existing forms and files. 8 Some CASE tools can reverse engineer existing files and databases into physical data models.

BIS 310: Week 7 How to Construct Data Models : 1 st Step - Entity Discovery < < < An entity has multiple instances Entities should be named with nouns that describe the person, event, place, or intangible thing about which we want to store data. Define each entity in business terms.

BIS 310: Week 7 How to Construct Data Models : 2 nd Step - The Context Data Model < The second task in data modeling is to construct the context data model. 8 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. 8 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.

BIS 310: Week 7

BIS 310: Week 7 How to Construct Data Models : 3 rd 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.

BIS 310: Week 7

BIS 310: Week 7 How to Construct Data Models : 4 th Step - Generalized Hierarchies < : 5 th Step - The Fully Attributed Data Model < : At this time, it would be useful to identify any generalization hierarchies in a business problem. The fifth task is to identify the remaining data attributes. 6 th 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.

BIS 310: Week 7 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 06. 5. 18

BIS 310: Week 7 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. 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 A Simple Logical Data Model 06. 5. 18

BIS 310: Week 7 06. 5. 18

BIS 310: Week 7 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 06. 5. 18

BIS 310: Week 7 Other Considerations • • • Denormalization Choices of storage formats File organizations Backup/Recovery Security of Data 06. 5. 18