The Relational Model � Entity-Relationship (ER) Model Lecture 2 � Entity Sets � Relationship Sets The Entity-Relationship Model and its � Database Design Issues Translation to the Relational Model � Mapping Constraints � Keys � ER Diagrams � Relational Model Chapter 2 (Sections 2.3, 2.4, 2.5, 2.6, 2.8, 2.9) � Translation of ER-model into the Relational Model Database Techniques 2 Database Modeling: Chaos! Entity Sets � A bank is organized into branches. Each branch is located in a city, and is identified by a unique name. The bank monitors the assets of each branch. � A database can be modeled as: � Bank customers are identified by their person-numbers. The bank stores each � a collection of entities, customer’s name, the street and the city in which they live. Customers may � a set of relationships among entities. have accounts and take out loans. A customer may be associated with a particular bank employee, who may act as a loan officer or personal banker. � An entity is an object that exists and is distinguishable from other � Bank employees are identified by person-numbers. The bank administration objects. stores the name and telephone-number of each employee, the names of his dependants, and the person-number of the employee’s manager. The bank also Examples: specific person, company, event, plant keeps track of the employee’s start date and, thus, the length of employment. Accounts can be held by more than one customer, and an account can have � � Entities have attributes more than one account. Each account is assigned a unique account number. The bank maintains a record of each account’s balance, and the most recent Examples: people have names and addresses date on which the account was accessed by the customer. A loan originates in a particular branch and can be held by more than one � � An entity set is a set of entities of the same type that share the customers. A loan is identified by a unique loan number. For each loan, the bank keeps track of the loan amount and loan payments. Although a loan same properties. number does not identify a particular payment uniquely, a payment number identifies a particular payment for a specific loan. The date and amount are Examples: set of all persons, companies, trees, holidays recorded for each payment. Database Techniques 3 Database Techniques 4 Attributes Relationship Sets � An entity is represented by a set of attributes , that is descriptive � A relationship is an association among several entities properties possessed by all members of an entity set. Example: customer = (customer-id, customer-name, depositor Hayes A-102 customer-street, customer-city) customer entity account entity loan = (loan-number, amount) relationship set � Domain – the set of permitted values for each attribute � A relationship set is a mathematical relation among n ≥ 2 entities, each taken from entity sets � Attribute types: {( e 1 , e 2 , … e n ) | e 1 ∈ E 1 , e 2 ∈ E 2 , …, e n ∈ E n } � Simple and composite attributes. where ( e 1 , e 2 , …, e n ) is a relationship � Single-valued and multi-valued attributes � Example: � E.g. multivalued attribute: phone-numbers (Hayes, A-102) ∈ depositor � Derived attributes � Can be computed from other attributes � E.g. age , given date of birth Database Techniques 5 Database Techniques 6 1
Relationship Sets (Cont.) Degree of a Relationship Set � An attribute can also be property of a relationship set. � For instance, the depositor relationship set between entity sets � Refers to number of entity sets that participate in a relationship set. customer and account may have the attribute access-date � Relationship sets that involve two entity sets are binary (or degree two). Generally, most relationship sets in a database system are binary. � Relationship sets may involve more than two entity sets. � E.g. Suppose employees of a bank may have tasks (responsibilities) at multiple branches, with different tasks at different branches. Then there is a ternary relationship set between entity sets employee, task, and branch. Database Techniques 7 Database Techniques 8 Design Issues & Decisions Mapping Cardinalities � Express the number of entities to which another entity can be � Use of entity sets vs. attributes associated via a relationship set. � Choice mainly depends on the structure of the enterprise being modeled, and on the semantics associated with the attribute in question. � Most useful in describing binary relationship sets. � Use of entity sets vs. relationship sets � For a binary relationship set the mapping cardinality must be � Possible guideline is to designate a relationship set to describe an one of the following types: action that occurs between entities � One to one � Binary versus n -ary relationship sets � One to many � Although it is possible to replace any nonbinary ( n -ary, for n > 2) � Many to one relationship set by a number of distinct binary relationship sets, a n -ary � Many to many relationship set shows more clearly that several entities participate in a single relationship. Database Techniques 9 Database Techniques 10 Mapping Cardinalities Mapping Cardinalities One to one One to many Many to one Many to many Some elements in A and B may not be mapped to any element in the other set Some elements in A and B may not be mapped to any element in the other set Database Techniques 11 Database Techniques 12 2
Recommend
More recommend