Chapter 7: Entity-Relationship Model Chapter 7: Entity-Relationship Model Design

Chapter 7: Entity-Relationship Model Chapter 7: Entity-Relationship Model Design www.phwiki.com

Chapter 7: Entity-Relationship Model Chapter 7: Entity-Relationship Model Design

Center, Linda, Host has reference to this Academic Journal, PHwiki organized this Journal Chapter 7: Entity-Relationship Model Chapter 7: Entity-Relationship Model Design Process Modeling Constraints E-R Diagram Design Issues Weak Entity Sets Extended E-R Features Design of the Bank Database Reduction to Relation Schemas Database Design UML Design Phases The initial phase of database design is to characterize fully the data needs of the prospective database users. Next, the designer chooses a data model in addition to , by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database. A fully developed conceptual schema also indicates the functional requirements of the enterprise. In a “specification of functional requirements”, users describe the kinds of operations (or transactions) that will be per as long as med on the data.

Brooks Institute of Photography US www.phwiki.com

This Particular University is Related to this Particular Journal

Design Phases (Cont.) Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Business decision – What attributes should we record in the database Computer Science decision – What relation schemas should we have in addition to how should the attributes be distributed among the various relation schemas Physical Design – Deciding on the physical layout of the database The process of moving from an abstract data model to the implementation of the database proceeds in two final design phases. Design Approaches Entity Relationship Model (covered in this chapter) Models an enterprise as a collection of entities in addition to relationships Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects Described by a set of attributes Relationship: an association among several entities Represented diagrammatically by an entity-relationship diagram: Normalization Theory (Chapter 8) Formalize what designs are bad, in addition to test as long as them Outline of the ER Model

ER model – Database Modeling The ER data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database. The ER model is very useful in mapping the meanings in addition to interactions of real-world enterprises onto a conceptual schema. Because of this usefulness, many database-design tools draw on concepts from the ER model. The ER data model employs three basic concepts: entity sets, relationship sets, attributes. The ER model also has an associated diagrammatic representation, the ER diagram, which can express the overall logical structure of a database graphically. Entity Sets An entity is an object that exists in addition to is distinguishable from other objects. Example: specific person, company, event, plant An entity set is a set of entities of the same type that share the same properties. Example: set of all persons, companies, trees, holidays An entity is represented by a set of attributes; i.e., descriptive properties possessed by all members of an entity set. Example: instructor = (ID, name, street, city, salary ) course= (course-id, title, credits) A subset of the attributes as long as m a primary key of the entity set; i.e., uniquely identifiying each member of the set. Entity Sets – instructor in addition to student instructor-ID instructor-name student-ID student-name

Relationship Sets A relationship is an association among several entities Example: 44553 (Peltier) advisor 22222 (Einstein) student entity relationship set instructor entity A relationship set is a mathematical relation among n 2 entities, each taken from entity sets {(e1, e2, en) e1 E1, e2 E2, , en En} where (e1, e2, , en) is a relationship Example: (44553,22222) advisor Relationship Set advisor Relationship Sets (Cont.) An attribute can also be associated with a relationship set. For instance, the advisor relationship set between entity sets instructor in addition to student may have the attribute date which tracks when the student started being associated with the advisor

Degree of a Relationship Set binary relationship involve two entity sets (or degree two). most relationship sets in a database system are binary. Relationships between more than two entity sets are rare. Most relationships are binary. (More on this later.) Example: students work on research projects under the guidance of an instructor. relationship proj-guide is a ternary relationship between instructor, student, in addition to project Mapping Cardinality Constraints Express the number of entities to which another entity can be associated via a relationship set. Most useful in describing binary relationship sets. For a binary relationship set the mapping cardinality must be one of the following types: One to one One to many Many to one Many to many Mapping Cardinalities One to one One to many Note: Some elements in A in addition to B may not be mapped to any elements in the other set

Mapping Cardinalities Many to one Many to many Note: Some elements in A in addition to B may not be mapped to any elements in the other set Complex Attributes Attribute types: Simple in addition to composite attributes. Single-valued in addition to multivalued attributes Example: multivalued attribute: phone-numbers Derived attributes Can be computed from other attributes Example: age, given date-of-birth Domain – the set of permitted values as long as each attribute Composite Attributes

Redundant Attributes Suppose we have entity sets: instructor, with attributes: ID, name, dept-name, salary department, with attributes: dept-name, building, budget We model the fact that each instructor has an associated department using a relationship set inst-dept The attribute dept-name appears in both entity sets. Since it is the primary key as long as the entity set department, it replicates in as long as mation present in the relationship in addition to is there as long as e redundant in the entity set instructor in addition to needs to be removed. BUT: when converting back to tables, in some cases the attribute gets reintroduced, as we will see later. Weak Entity Sets Consider a section entity, which is uniquely identified by a course-id, semester, year, in addition to sec-id. Clearly, section entities are related to course entities. Suppose we create a relationship set sec-course between entity sets section in addition to course. Note that the in as long as mation in sec-course is redundant, since section already has an attribute course-id, which identifies the course with which the section is related. One option to deal with this redundancy is to get rid of the relationship sec-course; however, by doing so the relationship between section in addition to course becomes implicit in an attribute, which is not desirable. Weak Entity Sets (Cont.) An alternative way to deal with this redundancy is to not store the attribute course-id in the section entity in addition to to only store the remaining attributes section-id, year, in addition to semester. However, the entity set section then does not have enough attributes to identify a particular section entity uniquely; although each section entity is distinct, sections as long as different courses may share the same section-id, year, in addition to semester. To deal with this problem, we treat the relationship sec-course as a special relationship that provides extra in as long as mation, in this case, the course-id, required to identify section entities uniquely. The notion of weak entity set as long as malizes the above intuition. A weak entity set is one whose existence is dependent on another entity, called its identifying entity; instead of associating a primary key with a weak entity, we use the identifying entity, along with extra attributes called discriminator to uniquely identify a weak entity. An entity set that is not a weak entity set is termed a strong entity set.

Weak Entity Sets (Cont.) Every weak entity must be associated with an identifying entity; that is, the weak entity set is said to be existence dependent on the identifying entity set. The identifying entity set is said to own the weak entity set that it identifies. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship. Note that the relational schema we eventually create from the entity set section does have the attribute course-id, as long as reasons that will become clear later, even though we have dropped the attribute course-id from the entity set section. E-R Diagrams Entity Sets Entities can be represented graphically as follows: Rectangles represent entity sets. Attributes listed inside entity rectangle Underline indicates primary key attributes

Center, Linda Open Line - KIKO-AM Host www.phwiki.com

Relationship Sets Diamonds represent relationship sets. Relationship Sets with Attributes Roles Entity sets of a relationship need not be distinct Each occurrence of an entity set plays a “role” in the relationship The labels “course-id” in addition to “prereq-id” are called roles.

Cardinality Constraints We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set in addition to the entity set. One-to-one relationship between an instructor in addition to a student : A student is associated with at most one instructor via the relationship advisor A student is associated with at most one department via stud-dept One-to-Many Relationship one-to-many relationship between an instructor in addition to a student an instructor is associated with several (including 0) students via advisor a student is associated with at most one instructor via advisor, Many-to-One Relationships In a many-to-one relationship between an instructor in addition to a student, an instructor is associated with at most one student via advisor, in addition to a student is associated with several (including 0) instructors via advisor

UML Class Diagrams (Cont.) Binary relationship sets are represented in UML by just drawing a line connecting the entity sets. The relationship set name is written adjacent to the line. The role played by an entity set in a relationship set may also be specified by writing the role name on the line, adjacent to the entity set. The relationship set name may alternatively be written in a box, along with attributes of the relationship set, in addition to the box is connected, using a dotted line, to the line depicting the relationship set. End of Chapter 7

Center, Linda Host

Center, Linda is from United States and they belong to Open Line – KIKO-AM and they are from  Miami, United States got related to this Particular Journal. and Center, Linda deal with the subjects like Public Affairs/Issues; Regional Interest

Journal Ratings by Brooks Institute of Photography

This Particular Journal got reviewed and rated by Brooks Institute of Photography and short form of this particular Institution is US and gave this Journal an Excellent Rating.