Entity Relationship (ER) Model

Entity identifier (Key) I

An Entity identifier or Key is an attribute(s) for one entity set such that no two entities in this set agree on all the attributes of the key. It is allowed for two entities to agree on some, but not all, of the key attributes. We must designate a key for every entity type. One or more of the attributes that can uniquely identify each instance of a given entity type Example: -Employee Employee No -Project Project No Relationship identifier: -A means of identifying each relationship instance. -Usually a composite identifier consisting of the identifiers of the two entity types that it relates Example: -Works(Employee No, Project No) -Underline the key attribute(s).

Attributes II

An attribute is a data value held by the instances in a Entity. e.g. lastName Attributes describe characteristics of the instances in the Entity Entities have specific data values for each attribute e.g. lastName = "Armour", lastName = "Dickson" Typical database built-in Domains include Char, integer, Boolean, Date etc. e.g. lastName: String Domains can be user defined (eg ZipCodes, StateCodes, StatusCodes etc)

When is it an Entity versus when is it an Attribute?

An entity (Entity Type) is more than the name of something, rather it should have at least one nonkey attribute, or It is the "many" in a many-one or many-many relationship.

Attribute properties

An instance of an attribute is a value, drawn from given domain, which presents the set of possible values of the attribute. Types: -Single vs multivalued Single: Social insurance number Multi: students in a course -Simple vs composite -Composite: Address consisting of Street number, City, state Zip -Stored vs derived attribute Stored: Individual grade of a student Derived: Average grade in a class -Key attribute - identifier

One-to-One relationship

At most one instance of a entity A is associated with one instance of entity B. "for each employee there exists a unique office and for each office there exists a unique employee" E.g.: A customer is associated with at most one loan via the relationship borrower -A loan is associated with at most one customer via borrower

Attributes I

Attributes describe the entity of which they are associated. A particular instance of an attributes is a value. For example, "Peter" is one value of the attribute First_Name. A domain is the collection of all possible or permitted values an attribute can have. For example: The domain of Name is a character string, the Domain of ZipCode is all valid ZipCodes Example: CUSTOMER = (CustID, CustName,Street, City, State, Zip) LOAN = (LoanNumber, LoanAmount)

Relationship Constraints

Cardinality ratio: Limits the number of relationship instances an entity can participate in, eg. 1 to 1, 1 to M, M to M Participation constraint: If each entity of an entity type is required to participate in some instance of a relationship type, then there must be at least one entity instance in the relationship; Specify that an entity must participate in at least min and at most max relationship instances.

Existence

Denotes whether the existence of an entity instance is dependent upon the existence of another, related, entity instance. The existence of an entity in a relationship is defined as either mandatory or optional. If an instance of an entity must always occur for an entity to be included in a relationship, then it is mandatory. (Strong Entity Type) An example of mandatory existence is the statement "every project must be managed by a single department". If the instance of the entity is not required, it is optional. E.g. A Car is always owned by a owner (Weak Entity Type) An example of optional existence is the statement, "employees may be assigned to work on projects".

Why ER Models?

ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects. Understanding user data requirements and database is Critical to database success The Users knows they want a database, but they don't know what they want in it. Diagramming the data and the key components is an way to Communicate and validate the information that will be maintained in the database.

Direction of Relationship

Indicates the originating entity of a binary relationship. The entity from which a relationship originates is the parent entity; The entity where the relationship terminates is the child entity. The direction of a relationship is determined by its connectivity. In a one-to-one relationship the direction is from the independent entity to a dependent entity. If both entities are independent, the direction is arbitrary. With one-to-many relationships, the entity occurring once is the parent. The direction of many-to-many relationships is arbitrary.

ER Modeling Relationship Notation

Many different modeling notations for representing Cardinalities at the relationship end points A popular method is called "Crows feet" Notation

Uses of ER model

Maps well to the relational model -Constructs used in the ER model transformed into relational tables. Can be used to by the database designer to communicate the design to the end user. Model can be used as a design plan

Relationships

Represents an association between two or more entities. An example of a relationship would be: -employees are assigned to projects -projects have activities -Departments manage one or more projects The degree n (cardinality) of a relationship type is the number of participating entity types. In the ER model relationships are explicitly represented

Entity Roles

Sometimes, entities in a relationship are not distinct For example, Manager and Staff are called Roles as they specify how employee entities interact via the Manages relationship set. Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. Role are optional, and are used when it is helps to clarify or understand the semantics of the relationship

Ternary Relationship

When three entities participate in the relationship Generally represents more information than 3 binary relationships

Remember, E-R Model is as important to the database designer as a blueprint is to an architect and builder.

Completed E-R diagram is the actual blueprint of the database. Its composition must reflect a businesses operations accurately if the database is to meet that organization's data requirements. It forms the basis for a final check on whether the included entities are appropriate and sufficient, on the attributes found within those entities, and on the relationships between those entities. Used as cross-check against the proposed data dictionary entries. Let the designer communicate more precisely with the stakeholders Finally, the completed E-R Model serves as the implementation guide to those who create the actual database.

Entities

Entities are usually recognizable concepts, either concrete or abstract, such as person, places, things, or events which have relevance to the database Examples of entities are EMPLOYEES, PROJECTS, INVOICES An entity is analogous to a table in the relational model. Sometimes referred to as Entity Type or Entity Set. An Entity is a concept, abstraction or thing with crisp boundaries and meaning for the Business Domain that is being modeled A Entity Type describes a group of Entities with similar properties (attributes) relationships with other Entities An Entity instance is an individual instance of an entity. An Entity instance is analogous to a row in the relational table Simple Entity Example: Entity: EMPLOYEE Attributes: EmployeeID, First Name, Last Name

Basic Elements of E-R Modeling I

Entities: are the principal data object about which information is to be collected. (Sometimes referred to as Entity Type or Entity Set.) Relationships: represents an association between two or more entities. An Attribute: of an entity can have a value from a domain (Value Set) A key: is an attribute(s) of an entity is one whose value uniquely identifies an entity of that type

ER Notation

Entities: represented by labeled rectangles. The label is the name of the entity. Entity names should be singular nouns. Relationships: represented by a solid line connecting two entities with a diamond in the middle. The name of the relationship is written in the Diamond. Relationship names should be verbs. Attributes: Attributes are represented by [Within Entity [or by oval] Each attributes contains the name of the information it represents.. Attributes which are identifiers are underlined. Attribute names should be singular nouns. Cardinality of relationships: is represented by a line ending with O I, II, or Crows Feet (*get more ) Optional existence: is shown by placing a circle next to the entity that is optional.

Entity Relationship Modeling Wrap Up

Entity Type - an abstraction of similar things, e.g. cars, students -An entity Type contains many entities Attributes: common properties of the entities in a entity sets (including Key) Relationship - specify the relations among entities from two or more entity Types -One to one -One to Many -Many to Many

One-to-Many relationships

For one instance of entity A, there are zero, one, or many instances of entity B However for one instance of entity B, there is only one instance of entity A. Examples of a 1 to Many relationships include: -A department has many employees; -Each employee is assigned to one department.

Many-to-Many relationships

For one instance of entity A, there are zero, one, or many instances of entity B and For one instance of entity B there are zero, one, or many instances of entity A. An example is: -Employees can be assigned to no more than two projects at the same time -Projects must have assigned at least three employees Cannot be directly translated to relational tables , Must be transformed into two or more one-to-many relationships using Composite (associative) entities.

Generalization (Inheritance) I

Specifies that two or more entities that share common attributes can be generalized into a higher level entity type called a supertype or generic entity. (called superclass in object modeling) The lower-level of entities become the subtype, or categories, to the supertype. Subtypes are dependent entities. (called subclass in object modeling) Generalization occurs when two or more entities represent categories of the same real-world object. For example, Salaried Employees and Hourly Employees represent categories of the same entity, Employees. In this example, Employees would be the supertype; Salaried Employees and Hourly Employees would be the subtypes.

Generalization II

Subtypes are distinctive from other entities in the set. Subtypes become lower-level entity that have attributes or participate in relationships that do not apply to the higher-level entity set. Subtypes inherit all the attributes and relationship participation of the higher-level entity to which it is linked. Can be depicted by a triangle component labeled IS-A (E.g. employee "is a" person).

Purpose of ER Model

The ER model Analysts/Design to model database designs (sometimes called schemas). Includes some constraints, but not operations. Convert E/R designs to relational DB designs.

Entity-Relationship(ER) Model (实体关系模型)

The ER model is a high-level conceptual data model Used for database Modeling and communicating the database design. The ER model was introduced by Peter Chen in 1976 Now the most widely used conceptual data model.

Cardinality

The actual number of related instances for each of the two entities. The basic types of Cardinality for relations are: one-to-one, one-to-many, and many-to-many.