Hi. Last week, we could identify the differences between transactional and analytical information systems. This week, we will learn how to develop an OLTP system through the conceptual data modeling and the entity relationship model. The analysis and design of an OLTP system are analysis of requirements, conceptual design, logical design, and physical design. During the analysis of requirements, the final user establishes requirements in terms of business rules. A user requirements specification is simply a statement of the user needs. The second step corresponds to the conceptual design, where a data analyst should map all these real world needs into technical requirements, in this case, into an entity relationship model. The output of an entity relational model should answer: What are the entities and relationships in the enterprise? What information about these entities and relationships should be stored in a database? What are the integrity constraints or business rules that hold? The logical design is an extra step where the ER diagram obtained from the conceptual design will be mapped into a normalized relational data model. The physical design is the final step, which consists of SQL programming in order to create, populate, and query a relational database. During this session, we will learn the entity relationship model. The entity relationship modeling was available for database design by Peter Chen. An entity represents a thing or object of the real world and is distinguished from others according to their characteristics. An entity set is a collection of similar entities, for instance, a set of all employees, and is represented by a rectangle. Attributes are the properties of entities. These are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity, which is a rectangle. Key is an attribute or collection of attributes that uniquely identifies an entity among an entity set. An attribute that is a key is underlined. For example, the roll number of a student makes him or her identifier among students. A super key is a set of attributes, one or more that collectively identifies an entity in an entity set. A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set. A relationship is an association among two or more entities, for example, John works in a pharmacy department. A relationship set is a collection of similar relationships and is represented by a rhombus. Cardinality specifies how many instances of an entity relate to one instance of another entity. There are four types of binary cardinality, one-to-one, one-to-many, many-to-one, and many-to-many. For example, according to different business rules, two entities can be related in different ways. On the one hand, an employee can work in many departments and many employees work in a department, many-to-many relationship model. On the other hand, an employee can work in only one department, and a department is composed by many employees, many-to-one relationship. The degree of a relationship indicates a number of entities that a relationship associates. We can see on the following diagram, samples of degree one, degree two, or binary and degree three, or ternary. An entity schema is a definition of a structure of an entity. Redundancy corresponds to storage of the same data several times in different places. As we have seen, cardinality indicates a number of entities with which a given entity may be related. In the case of inconsistency, it happens when redundant data are not equal to each other. The primary key uniquely identify each entity. The intention of a database is a set of definitions of the data structures for the particular database, also called database schema. An instance corresponds to the actual content of the database, or let's say the data at a particular instant. Database instances tend to change with time. Now that we have learned all the concepts of the entity relationship model, we need to remember the Peter Chen's notation to draw the ER diagram. Rectangles represent entity types, ellipses represent attributes, diamonds represents a relationship types, lines link attributes to entity types and entity types to relationship types. Primary key attributes are underlying. Well, now it's time to know the steps in designing an entity relationship diagram from the user's requirements. The first place we need to identify entity types, then identify relationships. On the third, identify and associate attributes with entity and relationship types. The fourth place, determine attribute domains. Then we need to determine primary key attributes for entity types. At last, associate the cardinality radius with relationship types. We have learned the entity relationship model and its Peter Chen notation so far. So, we are now ready to start doing some exercises. During this week, we learn the steps of the analysis and design of a database system. We'll explore the entity relationship model and its notation in order to draw an ER diagram. Next, we will learn the second step which corresponds to the logical design. See you then.
