FREE ELECTRONIC LIBRARY - Thesis, dissertations, books

«2. Conceptual Modeling using the Entity-Relationship Model Contents • Basic concepts: entities and entity types, attributes and keys, relationships ...»

ECS-165A WQ’11 15

2. Conceptual Modeling using the

Entity-Relationship Model


• Basic concepts: entities and entity types, attributes and keys,

relationships and relationship types

• Entity-Relationship schema (aka ER diagram)

• Constraints on relationship types

• Design choices

• Enhanced Entity-Relationship model features

• Steps in designing an ER schema

• Translation of an ER schema to tables Dept. of Computer Science UC Davis 2. Entity-Relationship Model ECS-165A WQ’11 16 What does Conceptual Design include?

Relational High-level Relational Ideas −→ −→ database −→ design DBMS schema

• Entity-Relationship model is used in the conceptual design of a database ( conceptual level, conceptual schema)

• Design is independent of all physical considerations (DBMS, OS,... ).

Questions that are addressed during conceptual design:

– What are the entities and relationships of interest (miniworld)?

– What information about entities and relationships among entities needs to be stored in the database?

– What are the constraints (or business rules) that (must) hold for the entities and relationships?

• A database schema in the ER model can be represented pictorially (Entity-Relationship diagram) Dept. of Computer Science UC Davis 2. Entity-Relationship Model ECS-165A WQ’11 17 Entity Types, Entity Sets, Attributes and Keys

• Entity: real-world object or thing with an independent existence and which is distinguishable from other objects.

Examples are a person, car, customer, product, gene, book etc.

• Attributes: an entity is represented by a set of attributes (its descriptive properties), e.g., name, age, salary, price etc.

Attribute values that describe each entity become a major part of the data eventually stored in a database.

• With each attribute a domain is associated, i.e., a set of permitted values for an attribute. Possible domains are integer, string, date, etc.

• Entity Type: Collection of entities that all have the same attributes, e.g., persons, cars, customers etc.

• Entity Set: Collection of entities of a particular entity type at any point in time; entity set is typically referred to using the same name as entity type.

Dept. of Computer Science UC Davis 2. Entity-Relationship Model ECS-165A WQ’11 18 Key attributes of an Entity Type

• Entities of an entity type need to be distinguishable.

• A superkey of an entity type is a set of one or more attributes whose values uniquely determine each entity in an entity set.

• A candidate key of an entity type is a minimal (in terms of number of attributes) superkey.

• For an entity type, several candidate keys may exist. During conceptual design, one of the candidate keys is selected to be the primary key of the entity type.

–  –  –

Relationships, Relationship Types, and Relationship Sets

• Relationship (instance): association among two or more entities, e.g., “customer ’Smith’ orders product ’PC42’ ”

• Relationship Type: collection of similar relationships An n-ary relationship type R links n entity types E1,..., En.

Each relationship in a relationship set R of a relationship type involves entities e1 ∈ E1,..., en ∈ En

–  –  –

• Degree of a relationship: refers to the number of entity types that participate in the relationship type (binary, ternary,... ).

• Roles: The same entity type can participate more than once in a relationship type.

–  –  –

Role labels clarify semantics of a relationship, i.e., the way in which an entity participates in a relationship.

; recursive relationship.

–  –  –

• Relationship Attributes: A relationship type can have attributes describing properties of a relationship.

“customer ’Smith’ ordered product ’PC42’ on January 11, 2005, for $2345”.

These are attributes that cannot be associated with participating entities only, i.e., they make only sense in the context of a relationship.

• Note that a relationship does not have key attributes! The identification of a particular relationship in a relationship set occurs through the keys of participating entities.

–  –  –

Customers-Suppliers-Products Entity-Relationship Diagram

• Rectangles represent entity types

• Ellipses represent attributes

• Diamonds represent relationship types

• Lines link attributes to entity types and entity types to relationship types

• Primary key attributes are underlined

• Empty Circle at the end of a line linking an attribute to an entity type represents an optional (null) attribute (not mentioned in textbook)

Not in the above diagram, but later in examples:

• Double Ellipses represent multi-valued attributes

–  –  –

Constraints on Relationship Types Limit the number of possible combinations of entities that may participate in a relationship set. There are two types of constraints: cardinality ratio and participation constraints Very useful concept in describing binary relationship types. For binary relationships, the cardinality ratio must be one of the

following types:

• Many-To-Many (default)

–  –  –

Constraints on Relationship Types (cont.) A many-one relationship type (and the counterpart one-many) is also often called a functional relationship.

Cardinality ratio of a relationship can affect the placement of a relationship attribute. E.g., in case of a many-one relationship type, one can place a relationship attribute at a participating entity type.

Participation constraint: specifies whether the existence of an entity e ∈ E depends on being related to another entity via the relationship type R.

• total: each entity e ∈ E must participate in a relationship, it cannot exist without that participation (total participation aka existence dependency).

–  –  –

Instead of a cardinality ratio or participation constraint, more precise cardinality limits (aka degree constraints in textbook) can

be associated with relationship types:

–  –  –

Each entity e1 ∈ E1 must participate in relationship set R at least min1 and at most max1 times (analogous for e2 ∈ E2).

Frequently used cardinalities

–  –  –

Design Choices for ER Conceptual Design It is possible to define entities and their relationships in a number of different ways (in the same model!).

• Should a real-world concept be modeled as an entity type, attribute, or relationship type?

Is “Address” an attribute or an entity type? Decision depends upon the use one wants to make of address information. If one is interested in the structure, e.g., (City, Street, Zip-Code), Address must be modeled as an entity type (or as a complex attribute).

• Should a concept be modeled as an entity type or relationship type?

–  –  –

Enhanced ER Modeling Concepts Although most properties of entities and relationships can be expressed using the basic modeling constructs, some of them are costly and difficult to express (and to understand). That’s why there are some extensions to the ER model.

Subclasses, Superclasses, and Inheritance

• In some cases, an entity type has numerous subgroupings of its entities that are meaningful and need to be represented explicitly because of their significance to the DB application.

–  –  –

• Relationships and attributes of superclass are inherited to subclass (in particular primary key attribute(s)); subclass can have additional attributes and relationships

• An entity cannot exist merely by being a member of only a subclass.

–  –  –

HOURLY EMPS is a subclass of EMPLOYEES and thus inherits its attributes and relationships (same for CONTRACT EMPS).


• Reverse process of specialization (bottom-up); identify common features of entity types and generalize them into single superclass (including primary key!) VehicleNo Price LicensePlate

–  –  –

Constraints on Specialization

• disjointness and totality constraints disjoint, total/partial: each entity in the superclass must/can be in exactly one subclass The disjointness constraint is indicated by the word “disjoint” right next to the ISA triangle The totality constraint is indicated by double lines leading from the superclass to the ISA triangle

• overlapping constraints overlapping, total (... must be in at least one subclass) In this case, only double lines leading to the ISA triangle are used.

overlapping, partial (... can be in at least one subclass)

• Note: special rules are required to propagate deletions from superclass/subclass (implemented later).

Note that for generalization, each entity in the superclass must belong to exactly one subclass

–  –  –

Steps in Designing an Entity-Relationship Schema [Step 1] Identify entity types (entity type vs. attribute) [Step 2] Identify relationship types [Step 3] Identify and associate attributes with entity and relationship types [Step 4] Determine attribute domains [Step 5] Determine primary key attributes for entity types

–  –  –

Translation of ER Schema into Tables

• An ER schema can be represented by a collection of tables which represent contents of the database (instance).

• Primary keys allow entity types and relationship types to be expressed uniformly as tables.

• For each entity and relationship type, a unique table can be derived which is assigned the name of the corresponding entity or relationship type.

• Each table has a number of columns that correspond to the attributes and which have unique names. An attribute of a table has the same domain as the attribute in the ER schema.

• Translating an ER schema into a collection of tables is the basis for deriving a relational database schema from an ER diagram.

–  –  –

Translating Entity Types into Tables

• Given an entity type E1 with (atomic) attributes A1,..., An and associated domains D1,..., Dn.

• Translation of the entity type CUSTOMERS into table


–  –  –

Translating Relationship Types into Tables

• A many-many relationship type is represented as a table with columns for the primary key attributes of the participating entity types, and any descriptive attributes of the relationship type.

Example: Relationship type offers

–  –  –

Prodname and SName are the primary key attributes of the entity types SUPPLIERS and PRODUCTS.

• Translation of one-many and many-one (functional) and oneone relationship types into tables can be optimized ; no table for relationship type necessary!

–  –  –

Translating Subclasses/Superclasses into Tables

• Method 1: Form a table for the superclass and form a table for each subclass. Include the primary key attributes of the superclass in each such table.


Employees(SocialSN, Name, Address) Hourly_Emps(SocialSN, Hours, Wages) Contract_Emps(SocialSN, ContractNo)

• Method 2: Form a table for each subclass and include all attributes of the superclass.

Hourly_Emps(SocialSN, Name, Address, Hours, Wages) Contract_Emps(SocialSN, Name, Address, Contractno) Method 2 has no table for the superclass EMPLOYEES.

• Method 3: Use null values Employees(SocialSN, Name, Address, Hours, Wages, ContractNo) Hourly employees will have a null value for ContractNo.

Contract employees will have null values for Hours and Wages.

–  –  –

Summary of Conceptual Design

• Conceptual design follows requirements analysis, yields a high level description of data to be stored (conceptual level).

• ER model is a popular model for conceptual design, constructs are expressive, close to the way people think about applications; supported by many CASE tools.

• Basic constructs are entities, relationships, and attributes

• Some additional constructs: ISA hierarchies, cardinality ratios,...

• There are many variations on ER model constructs.

• Several kinds of integrity constraints can be expressed in the ER model: key constraints, structural constraints, constraints on specializations – Some of them can be expressed in SQL when translating entity and relationship types into tables – Not all constraints can be expressed in the ER model – Constraints play an important role in determining a good database design for an application domain.

• ER design is subjective: There are many ways to model a given scenario! Analyzing alternative schemas is important!

Entity type vs. attribute, entity type vs. relationship type, binary vs. n-ary relationship type, use of IS-A, generalization and specialization,...

• Ensuring a good database design includes analyzing and further refining relational schema obtained through translating ER schema.

Dept. of Computer Science UC Davis 2. Entity-Relationship Model

Similar works:

«THE SCHOOL OF CALVARY or Sharing His Suffering By Dr. J. H. Jowett Reprinted, 1969, by Prairie Bible Institute Three Hills, Alberta, Canada This edition is a revision of the original edition printed by James Clarke And Company, London, In 1911 From Chuck King, April 1997 * * * * * * * HDM Digital Copy Date: 07/30/99 * * * * * * * CONTENTS The School of Calvary Lose to Find The Sufferings of Christ The Neglected Cup Through Gethsemane to Olivet The Supper of the Lord The Morning Glory * * * * *...»

«This text was adapted by The Saylor Foundation under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work’s original creator or licensee. Saylor URL: http://www.saylor.org/books Saylor.org Chapter 1 Introduction to eMarketing 1.1 Introduction There is no doubt about it—the Internet has changed the world we live in. Never before has it been so easy to access information; communicate with people all over the globe; and share...»

«Japan’s Zoomorphic Urge 69 Japan’s Zoomorphic Urge Laura Miller Loyola University of Chicago One of the Japanese conduct books in my collection substitutes cats for humans in its illustrations.1 For instance, in one drawing, an anthropomorphic cat giving a speech at a wedding reception makes the mistake of using the verb for cutting (kiru), and shocks the other feliform guests who are dressed in festive finery. Sometimes Japanese words are categorized as imi kotoba, language that should be...»

«Bitcoins: Made in China Tim Swanson Revised: May 11, 2014 Abstract: The discussion over the actual costs of maintaining a decentralized seigniorage network is a new area of research. In practice it appears that the logistical cost of operating the Bitcoin network rises linearly with its total value. More efficient mining gear does not reduce energy use of the Bitcoin network. It only raises the network difficulty. The proof-of-work method used to mitigate rogue attacks, must expend real work,...»

«1054 E Old Canyon Ct. Flagstaff, AZ 86001 Phone: (928) 774-5197 Fax: (928) 774-5278 www.canyonpet.com Reproductive Q&A Questions about dog breeding are some of the most common that we get at Canyon Pet Hospital. We wanted to try and address the most frequently asked questions here so that it would be available for people to access at all times. Before we begin, please remember that when dealing with reproductive issues, we are dealing with hormones, and there are never any exacts. If you are...»

«A snapshot of complaints received from Servicemembers, Veterans, and their families April 2015 Message from Holly Petraeus Assistant Director for the Office of Servicemember Affairs Hello and welcome from the Office of Servicemember Affairs (OSA) at the Consumer Financial Protection Bureau (CFPB)! This is our third complaint report1 detailing the data and trends surrounding complaints submitted to the CFPB by servicemembers, veterans and their families. As you can see in the report, our...»

«Alternative Meanings Through the World of Virtual Reality Marlyn Kemper Littman I magine sharing visual and auditory perceptive space with a dolphin in a virtual community, building a virtual city model with three dimensional (3-D) objects and images from around the globe, learning about geometric connections in a Post Euclidean walk-about, or participating in virtual interactive games.1 These are but a few of the many new worlds of meaning already available to young adults through virtual...»

«7 sozialpolitik in diskussion sozialpolitik in diskussion ■ Josef Wallner (Hg.) GESTALTUNG UND FINANZIERUNG VON ARBEITSMARKTPOLITIK: EIN INTERNATIONALER VERGLEICH WIEN Jänner 2008 wien.arbeiterkammer.at GESTALTUNG UND FINANZIERUNG VON ARBEITSMARKTPOLITIK: EIN INTERNATIONALER VERGLEICH Hg. von Josef Wallner Jänner 2008 wien.arbeiterkammer.at/www-9905.html INHALTSVERZEICHNIS Vorwort Adolf Buxbaum, Sonja Ertl, Marc Pointecker Neue Wege der Arbeitsmarktpolitik – Lehren aus einem...»

«Un regalo para sus sentidos ABaC SPA. Un Regalo para sus Sentidos. Situado en una zona privada del Hotel ABaC, es el lugar en donde se encuentra el regalo más personalizado para nuestros clientes: Su Bienestar.TODOS LOS TRATAMIENTOS INCLUYEN UN CIRCUITO DE 30 MINUTOS EN SPA. Dese un baño entre las aguas templadas del spa bañado por la luz natural y envuelto de aromas que le permitirán ir abandonando el cuerpo hacia una nueva experiencia. Cuatro sencillos pasos que prepararán su mente y su...»

«M I N U T E SCITY OF BEAUMONT BECKY AMES, MAYOR W. L. Pate, Jr., At Large Gethrel Wright, At -Large Dr. Alan B. Coleman, Mayor Pro Tem CITY COUNCIL MEETING Michael D. Getz, Ward II September 16, 2014 Audwin Samuel, Ward III Jamie Smith, Ward IV Tyrone E. Cooper, City Attorney Kyle Hayes, City Manager Tina Broussard, City Clerk The City Council of the City of Beaumont, Texas, met in a regular session on September 16, 2014, at the City Hall Council Chambers, 801 Main Street, Beaumont, Texas, at...»

«Common Data Set 2007-08 GENERAL INFORMATION A0. Respondent Information (Not for Publication) Name: Connie Peyton Title: Office: Center for Institutional Effectiveness Mailing Address, City/State/Zip/Country: 901 West Franklin St., P.O. Box 842527, Richmond, VA 23284-2527 Phone: (804) 827-0932 Fax: (804) 828-4753 E-mail Address: cwpeyton@vcu.edu Are your responses to the CDS posted for reference on your institution’s Web site? Yes No If yes, please provide the URL of the corresponding Web...»

«The Section 6662(e) Substantial and Gross Valuation Misstatement Penalty A Presentation and Tutorial Guide of the Code and Regulations What Is the Transfer Pricing Penalty? Though we generally refer to a penalty arising from an I.R.C. ' 482 adjustment as a transfer pricing or as an I.R.C. ' 6662(e) penalty, its real name is the substantial and gross valuation misstatement penalty, under I.R.C. ' ' 6662(a), (e) and (h). I.R.C. ' 6662 contains the provisions for the imposition of accuracy-related...»

<<  HOME   |    CONTACTS
2016 www.dis.xlibx.info - Thesis, dissertations, books

Materials of this site are available for review, all rights belong to their respective owners.
If you do not agree with the fact that your material is placed on this site, please, email us, we will within 1-2 business days delete him.