DataBase Data Modeling Using the EntityRelationship Model Data Modeling Process of creating a logical representation of the structure of the database The most important task in database development The Data Model A data model is a plan or blueprint for a database design A data model is more generalized and abstract than a database design It is easier to change a data model than it is to change a database design so it is the appropriate place to work through conceptual database problems The Entity Relationship Model The Entity Relationship model is a set of concepts and graphical symbols that can be used to create conceptual schemas Developed by Peter Chen of M I T in 1976 in a landmark paper The Entity Relationship Model Toward a Unified View of Data ACM Transactions on Database Systems Vol 1 No 1 Chen s initial model has been refined over the years Versions Evolution of the E R Model Original E R model by Peter Chen 1976 Extended E R model 1986 added subtypes now the most widely used E R model and what we will use in IS431 Information Engineering IE model also called the Crow s Foot model developed by James Martin in 1990 IDEF1X 1994 national standard by the National Institute of Standards and Technology Additionally Unified Modeling Language UML Style E R Models Semantic Object Data Models SOMs The Four Major Components of the Entity Relationship E R Model Entities really entity sets Attributes Relationships Identifiers An Example of an E R Diagram The notation will be explained An Extended E R Model The Geometric Symbols in an E R Model Entities are represented by rectangles Attributes are represented by ovals that are connected to the entity by a straight line Relationships are represented by diamond shaped symbols The name of the entity class or attribute or relationship is usually placed inside the symbol used for that object Sometimes with relationships the name is placed adjacent Entities and Entity Sets An entity set or class is something that can be identified and the users want to track Entity class is a collection of entities described by the entity format in that class Entity instance is the representation of a particular entity There are usually many instances of an entity in an entity class Consider an entity class STUDENT An entity instance would be a particular student in the entity class for example Matthew Jones might be an entity instance A general notational convention is to CAPITALIZE the entity set s name Example An Entity Set CUSTOMER and Two Entity Instances Representing Entities Entity Sets in an E R Model An entity set class is represented by a rectangle with the name of the entity set class in the rectangle STUDENT Attributes Description of the entity s characteristics All instances of a given entity class have the same attributes Composite attribute attribute consisting of the group of attributes Multi value attributes attribute with more than one possible value Representing Entities and Their Attributes Method 1 Place the entity name in a rectangle and the attributes as ovals ellipses attached to the rectangle Method 2 Place the entity name at the top of a portrait shaped rectangle and place the attributes below the entity name in a rectangle Variations of Displaying Attributes A Student Entity Set With Three Attributes Using Ovals ellipses Student id Lastname Firstname STUDENT Can you describe what the entity set STUDENT would look like if we used Method 2 Review of Attributes Attributes are the properties that define the entity s characteristics The E R model assumes that all instances of a given entity set have the same attributes So for the ER model in the prior slide for STUDENT instances of STUDENT such as Henry Gordon Joyce Johnson Jeffrey Chan etc will each have a Student ID a Lastname and a Firstname attribute Identifiers Identifiers are one or more attributes of entity instances which serve to name or identify the entity instance For the STUDENT entity set identifiers are Student id and the composite identifier Last name First name Composite identifiers are identifiers that consist of two or more attributes Identifiers are either Unique identifies one and only one entity or Non Unique identifies a set of entities We often refer to a Unique Identifier as the Primary Key of a table relation Attributes Domains Attributes will also have a domain The domain is the attribute s set of possible values The domain of the attribute Grade Point Average is a real number between 0 and 4 The domain of the attribute Gender consists of only two possibilities M or F or some other equivalent code Attributes may share a domain The attribute PROFESSOR AGE and STUDENT AGE share the domain of all possible ages Variations on Displaying Entity Sets and Attributes in an Entity Set Depending on the size of our ER model we might display the entity set name and ALL of the attributes or the entity set name and the identifier or just the entity set name Classification of Attributes By Allowable Values A Single Valued Attribute can have only a single value Example Age is a single valued attribute of the entity person because a person can have only one age A composite attribute is a logical grouping of singlevalued attributes Example The composite attribute Address consists of the group Street City State Zip Classification of Attributes By Allowable Values Continued A Multi Valued Attribute can have many values Example PackageExpertise is a multiple valued attribute of the entity person because a person may have expertise in many packages WordPerfect Excel Photoshop Access Powerpoint AutoCad MathCad etc A multi valued attribute may also be composite An example would be assuming we allowed multiple phone numbers for an entity Phone area code number In an E R model Multiple Valued Attributes are shown by a double line or a bold line connecting the attribute to the entity entity A Multi Valued Attribute Color Model Year Manuf Color Car id Primary Key Unique Identifier Engine CAR Translating E R models with Composite Attributes into a Relational DBMS For the original entity create additional new attributes one for each of the original composite attributes components Thus the composite attribute Color will be split into new attributes Topcolor Bodycolor and Trimcolor all linked to the entity class Car Bodycolor Year Model Topcolor Manuf Trimcolor Car id Engine CAR Splitting the Composite Attribute into New Attributes Derived Attributes A Derived Attribute does not physically