Chapter 4 - Structuring System Requirements: Conceptual Data Modeling Conceptual Data Modeling Conceptual data model is a detailed model that captures the overall structure of data in an organization. It is independent of any database management system (DBMS) or other implementation considerations. Conceptual data modeling is the representation of organizational data. Its 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. The 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 among process flow, decision logic, and data modeling descriptions. The 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. In the design stage, the conceptual data model is translated into a physical design. Project repository links all design and data modeling steps performed during SDLC. Deliverables and Outcomes The primary deliverable is the entity-relationship diagram or class diagram. As many as 4 E-R or class diagrams are produced and analyzed during conceptual data modeling: E-R diagram that covers data needed in the project’s application E-R diagram for the application being replaced E-R diagram for the whole database from which the new application’s data are extracted E-R diagram for the whole database from which data for the application system being replaced is drawn 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 included in the DFD must appear in the data model and vice versa. Each data store in a process model must relate to business objects represented in the data model.
1|P a g e
[email protected]
Figure: Sample conceptual data model diagrams: Standard E-R notation.
Gathering Information for Conceptual Data Modeling There are two perspectives for gathering information for conceptual modeling: Top-down Data model is derived from an intimate understanding of the business Bottom-up Data model is derived by reviewing specifications and business documents Requirements Determination Questions for Data Modeling What are subjects/objects of the business? Data entities and descriptions What unique characteristics distinguish between subjects/objects of the same type? Primary keys What characteristics describe each subject/object? Attributes and secondary keys How do you use the data? Security controls and user access privileges Over what period of time are you interested in the data?
2|P a g e
[email protected]
Cardinality and time dimensions Are all instances of each object the same? Supertypes, subtypes, and aggregations What events occur that imply associations between objects? Relationships and cardinalities Are there special circumstances that affect the way events are handled? Integrity rules, cardinalities, time dimensions
Introduction to Entity-Relationship Modeling The basis entity-relationship modeling notation uses three main constructs: Data entities, Relationships, and Attributes. Entity-Relationship Data Model E-R data model is a detailed, logical, and graphical representation of the entities, associations and data elements for an organization or business area. Entity-Relationship (E-R) Diagram E-R diagram (or ERD) is a graphical representation of an E-R model. ERD illustrate the logical structure of databases. Peter Chen developed ERDs in 1976. ERD is a picture showing the information created, stored, and used by a business system.
Figure: Example of an ERD
3|P a g e
[email protected]
ERD complements DFD. While DFD focuses on processes and data flow between them, ERD focuses on data and the relationships between them. It helps to organize data used by a system in a disciplined way. It helps to ensure completeness, adaptability and stability of data. It is an effective tool to communicate with senior management (what is the data needed to run the business), data administrators (how to manage and control data), database designers (how to organize data efficiently and remove redundancies). Entities An entity is a person, place, object, event or concept in the user environment about which the organization wishes to maintain data. It is represented by a rectangle in E-R diagrams. An entity is an object or concept about which you want to store information. Entities generally represent people, places, and things of interest to the organization.
Weak Entity A weak entity is an entity that must defined by a foreign key relationship with another entity as it cannot be uniquely identified by its own attributes alone.
Entity Type Entity type is a collection of entities that share common properties or characteristics. Entity Instance Entity instance is a single occurrence of an entity type. Attributes A named property or characteristic of an entity that is of interest to an organization. Ovals (or ellipses) are used to represent attributes. Lines links attributes to entity sets and entity sets to relationship sets. Key attribute (or Identifier Attribute) A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute.
4|P a g e
[email protected]
Candidate Keys Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type. A candidate key is an attribute (or combination of attributes) that uniquely identifies each instance of an entity type. Identifier An identifier is a candidate key that has been selected as the unique identifying characteristic for an entity type. One or more attributes can serve as the entity identifier, uniquely identifying each entity instance Selection rules for an identifier: Choose a candidate key that will not change its value Choose a candidate key that will never be null Avoid using intelligent keys
5|P a g e
[email protected]
Consider substituting single value surrogate keys for large composite keys Multivalued Attribute A multivalued attribute is an attribute that may take on more than one value for each entity instance. For example, an employee entity can have multiple skill values.
It is represented on E-R diagram in two ways: double-lined ellipse weak entity
Figure: Entity, Attributes, and multivalued attributes Derived attribute A derived attribute is based on another attribute. For example, an employee's monthly salary is based on the employee's annual salary.
Relationship A relationship is 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. The first entity in the relationship is the parent entity; the second entity in the relationship is the child entity. Relationships go in both directions. Relationships are always labeled with verb phrases. Diamonds are normally used to represent relationships.
Conceptual Data Modeling and the E-R Model The goal of the conceptual data modeling is to capture as much of the meaning of data as is possible. Degree
6|P a g e
[email protected]
The degree of a relationship is the number of entity types that participate in a relationship. The three most common relationships in E-R models are: Unary Relationship A relationship between the instances of one entity type Also called as recursive relationship
Binary Relationship A relationship between the instances of two entity types Most common type of relationship among all
Ternary Relationship A simultaneous relationship among the instances of three entity types Not the same as three binary relationships
Cardinalities in Relationships The cardinality of a relationship is the number of instances of entity B that can be associated with each instance of entity A. It refers to the number of times instances in one entity can be related to instances in another entity. 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
7|P a g e
[email protected]
8|P a g e
[email protected]
Mandatory vs. Optional Cardinalities Specifies whether an instance must exist or can be absent in the relationship One instance in an entity refers to one and only one instance in the related entity (1:1) One instance in an entity refers to one or more instances in the related entity (1:N) One or more instances in an entity refer to one or more instances in the related entity (M:N)
9|P a g e
[email protected]
Figure: Example of cardinality relationships
Figure: The entity-relationship symbols and their meanings.
10 | P a g e
[email protected]
Modality Modality refers to the minimum number of times that an instance in one entity can be related to an instance in another entity. One means that an instance in the related entity must exist for an instance in another entity to be valid Zero means that no instance in the related entity is necessary for an instance in another entity to be valid Associative Entities An associative entity is an entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances. It is also called as gerund.
A relationship with an attribute in above figure as an associative entity is shown below:
A relationship that itself is related to other entities via another relationship must be represented as an associative entity. Some other examples of associative entities:
11 | P a g e
[email protected]
Figure: The entity-relationship diagram for patient treatment. Attributes can be listed alongside the entities. In each case, the key is underlined.
The Role of CASE in Conceptual Data Modeling CASE tools provide two important functions in conceptual data modeling: Maintain E-R diagrams as a visual depiction of structured data requirements
12 | P a g e
[email protected]
Link objects on E-R diagrams to corresponding descriptions in a repository Case Repository Entry for a Relationship
References: Hoffer, J.A., George, J.F. and Valacich J.S., “Modern Systems Analysis and Design”, 3rd Edition, Pearson Education, 2003. K.E.Kendall and J.E.Kendall, “Systems Analysis and Design”, 5th Edition, Pearson Eduation, 2003. V.Rajaraman, “Analysis and Design of Information Systems”, 2nd Edition, Prentice Hall of India, New Delhi, 2002 E.Yourdon “Modern Structured Analysis”, Prentice Hall of India, 1996. Elias M Awad, “Systems Analysis and Design”, Galgotia. Igor Hawryszkiewycz, “Systems Analysis and Design”, PHI. R.Schultheis and Mary Summer, “Management Information Systems”, Tata McGraw Hill, 1999. R.Elmasri and S.B.Navathe, “Fundamentals of Data Base Systems”, 4th Edition, Pearson Education Asia, New Delhi, 2004. T. Connolly and C. Begg, “Database Systems”, 3rd Edition, Pearson Education, Asia, New Delhi, 2003.
Assignments:
13 | P a g e
[email protected]
(1) (2) (3) (4) (5)
What is an entity? Give examples of entities. List some examples of entities and their attributes. What is a relationship? In what way is it different from an entity? In what way is an attribute different from an entity? What do you understand by a 1 to 1, 1 to many, many to many relationships? Give one example for each. (6) What is the difference between a relation and a relationship? (7) What is a key attribute in a relation? (8) For the following word statement, obtain E-R diagram and relations. Use any reasonable assumptions. "A machine shop produces many parts which it takes on contract. It employs many machinists who operate any of the machines. A part needs working on only one machine. A record is kept on the quantity of material needed for producing each part. The production of each part is tracked by giving a job number, start time and end time and machinist identifications". (9) Draw an E-R diagram showing the cardinality for the following: A bill is sent to a customer. A customer can receive many bills. A clerk works in a bank. The bank has many clerks A part is used in many products and a product uses many parts. Students apply for seats in colleges. Each student can atmost get one seat. A college has many seats. A student can send many applications. A car is owned by a person. The person can own many cars. (10) Develop E-R diagram for the following: Customer withdraws money from his account Students write examinations. Students attend classes Professors write books Driver drives a car
A Gentle Advice: Please go through your text books and reference books for detail study!!! Thank you all.
Notes Compiled By: Bijay Mishra biizay.blogspot.com 9813911076 or 9841695609
14 | P a g e
[email protected]
