1 / 73

Chapter 2

Chapter 2. Entity-Relationship Model. Chapter 11 & 12 in Textbook. Database Design. Steps in building a database for an application: 1. Understand real-world domain being captured 2. Specify it using a database conceptual model (E/R,OO)

dmitri
Télécharger la présentation

Chapter 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 2 Entity-Relationship Model Chapter 11 & 12 in Textbook

  2. Database Design Steps in building a database for an application: 1. Understand real-world domain being captured 2. Specify it using a database conceptual model (E/R,OO) 3. Translate specification to model of DBMS (relational) 4. Create schema using DBMS commands (DDL) 5. Load data (DML) Real-world domain Conceptual model DBMS data model Create Schema (DDL) Load data (DML) Lilac Safadi E/R Model

  3. Entity-Relationship Model (E/R) A picture is worth a thousand words The Entity-Relationship model (ER) is a high-level description of the structure of the DB The Entity-Relationship Diagram (ERD) is a graphical model for representing the conceptual model for the data A E/R models the DB using three element types: - Entities - Attributes - Relationships Lilac Safadi E/R Model

  4. SCHOOL ERD Example 1 1 name (0,1) dean of PROFESSOR 1 (1,1) number M 1 operate DOB 1 (0,4) 1 (0,1) assigned M (0,*) (0,*) 1 advise teach chairs DEPARTMENT 1 (1,1) 1 1 (1,*) offer (0,1) M M M (1,1) N M M 1 enroll CLASS generate COURSE STUDENT (0,*) (0,*) (0,*) (1,1) (1,1) M code hrs has Lilac Safadi E/R Model

  5. Entities & Entity Type Entity is an object that exists and is distinguishable from other objects. e.g. person, company, course, university Entity Type is a set of entities of the same type that share the same properties. e.g. set of all persons, companies, trees, courses STUDENT COURSE Lilac Safadi E/R Model

  6. Relationships & RelationshipTypes • A relationship associates 2 or more entities • A relationship type is a set of associations between entity types study STUDENT COURSE Lilac Safadi E/R Model

  7. Degree of Relationship Type Degree of relationship refers to number of participating entity types in a relationship. • A relationship of degree two (2 entity types) are binary. • A relationship of degree three (3 entity types) are ternary. Lilac Safadi E/R Model

  8. Degree of Relationship Type A relationship of degree two (2 entity types) are binary. study STUDENT COURSE Lilac Safadi E/R Model

  9. Degree of Relationship Type A relationship of degree three (3 entity types) are ternary. (e.g.registration of a student in a course by a staff) register STUDENT COURSE STAFF Lilac Safadi E/R Model

  10. Recursive Relationship Recursive relationship is a relationship type where the same entity type participates more than once in a different role. It is a unary relationship. COURSE require Lilac Safadi E/R Model

  11. Roles Role indicates the purpose that each participating entity type plays in a relationship (e.g. prerequisite, requester) COURSE requester prerequisite require Lilac Safadi E/R Model

  12. Roles Role can be used when two entities are associated through more than one relationship to classify the purpose of each relationship manages Manager Branch office BRANCH STAFF Branch office Staff member allocated Lilac Safadi E/R Model

  13. Attributes Attributes are descriptive properties for an entity type or a relationship type . All entities in one entity type have the same attributes DOB name St_no Tel_no STUDENT Lilac Safadi E/R Model

  14. Attributes of Entities name DOB name hours St_no number Tel_no study STUDENT COURSE Lilac Safadi E/R Model

  15. Attributes of Relationships All relationships in one relationship type have the same attributes Relationships can be distinguished not only by their attributes but also by their participating entities contract Start Object End Lilac Safadi E/R Model

  16. Simple & Composite Attributes Simple attribute is an attribute that have a single value with an independent existence e.g. salary, age, gender,... Lilac Safadi E/R Model

  17. Simple & Composite Attributes Composite attribute is an attribute composed of multiple distinct components, each with an independent existence e.g. address (street, area, city, post code) name (First name, initial, Last name) phone no. (area code, number, exchange no) initial FName LName Area_cd DOB name no St_no Tel_no EX STUDENT Lilac Safadi E/R Model

  18. Single-valued & Multi-valued Attributes Single-valued attribute is an attribute that holds a single value for a single entity. It is not necessarily a simple attribute. e.g. student_no, age, gender,... Lilac Safadi E/R Model

  19. Single-valued & Multi-valued Attributes Multi-valued attribute is an attribute that may hold multiple values, of the same type, for a single entity. e.g. tel_no, degrees,… initial FName LName Area_cd DOB name no St_no Tel_no EX STUDENT Lilac Safadi E/R Model

  20. Derived Attributes Derived attribute is an attribute that represents a value that is derived from the value of a related attribute,not necessarily in the same entity type. e.g. age is derived from date_of_birth total_cost is derived from quantity*unit_price name DOB St_no Tel_no age STUDENT Lilac Safadi E/R Model

  21. Keys Candidate key(CK) is the minimal set of attributes that uniquely identifies an entity. It cannot contain null. e.g. student_no, social_security_no, branch_no… Primary Key(PK) is a candidate key that is selected to uniquely identify each entity. Alternate Key(AK) is a candidate key that is NOT selected to be the primary key. Lilac Safadi E/R Model

  22. Keys Example ELEMENT(symbol, name, atomic_no) Lilac Safadi E/R Model

  23. Keys Example Candidate Key ELEMENT(symbol, name, atomic_no) Primary Key Alternate Keys Lilac Safadi E/R Model

  24. Choice of PK Choice of Primary Key (PK) is based on: • Attribute length • Number of attributes required • Certainty of uniqueness Lilac Safadi E/R Model

  25. Primary Key in ERD initial FName LName Area_cd DOB name no St_no Tel_no EX STUDENT age Lilac Safadi E/R Model

  26. Keys A key can be: - simple key is a candidate key of one attribute e.g. student_no, branch_no… - composite key is a candidate key that consists of two or more attributes e.g. STUDENT (Lname,Fname, Init) CLASS (crs_code, section_no) ADVERT (property_no, newspaperName, dateAdvert) Lilac Safadi E/R Model

  27. Composite Key in ERD name Section_no hours crs_code CLASS Lilac Safadi E/R Model

  28. Strong & Weak Entity Types A strong entity type is NOT existence-dependent on some other entity type. It has a PK. A weak entity type is an entity type that is existence-dependent on some other entity type. It does not have a PK. Lilac Safadi E/R Model

  29. Weak Entity Type • The existence of a weak entity type depends on the existence of a strong entity set; it must relate to the strong entity type via a relationship type called identifying relationship. • The PK of a weak entity set is formed by the PK of its strong entity type, plus a weak entity type discriminator attribute. LName FName dep_no FName emp_no DOB has EMPLOYEE DEPENDENT Lilac Safadi E/R Model

  30. Cardinalities Cardinality ratio expresses the number of relationships an entity can participate in. Most useful in describing binary relationship types. For a binary relationship type the mapping cardinality must be one of the following types: – One to one (1:1) – One to many(1:M) – Many to one (M:1) – Many to many (M:N) Lilac Safadi E/R Model

  31. One-To-One Relationship PROFESSOR chair DEPARTMENT P1 r1 D002 P2 P3 r2 D001 A professor chairs at most one department; and a department is chaired by only one professor 1 1 chairs PROFESSOR DEPARTMENT Lilac Safadi E/R Model

  32. One-To-Many Relationship PROFESSOR teach COURSE P1 r1 C01 r2 C02 P2 r3 C03 P3 C04 A course is taught by at most one professor; a professor teaches many courses. 1 M teach PROFESSOR COURSE Lilac Safadi E/R Model

  33. One-To-Many Relationship CLASS require ROOM C1 r1 R001 C2 r2 R002 C3 r3 R003 R004 A class requires one room; while a room can be scheduled for many classes M 1 require CLASS ROOM Lilac Safadi E/R Model

  34. Many-To-Many Relationship CLASS enroll STUDENT C1 r1 S1 C2 r2 S3 C3 r3 S4 C4 r4 S5 S2 A class enrolls many students; and each student is enrolled in many classes. M N enroll CLASS STUDENT Lilac Safadi E/R Model

  35. Multiplicity Multiplicity is the number (range) of possible entities that may relate to a single association through a particular relationship. It is best determined using sample data. Takes the form (min#,max#) Lilac Safadi E/R Model

  36. Multiplicity STAFF manage BRANCH SG1 r1 B002 SG2 SG3 r2 B001 1 1 manage STAFF BRANCH (0,1) (1,1) Lilac Safadi E/R Model

  37. Multiplicity STAFF oversee PROPERTY SG1 r1 P1 r2 P2 SG2 r3 P14 SG3 P6 1 M oversee STAFF PROPERTY (0,10) (0,*) (0,1) Lilac Safadi E/R Model

  38. Multiplicity Newspaper advertise PROPERTY Al-Riyadh r1 P1 r2 P13 Al-Bilad r3 Al-Madinah r4 P6 Al-Sharq P4 M N advertise NEWSPAPER PROPERTY (0,*) (0,*) Lilac Safadi E/R Model

  39. Participation Constraints Participation constraints determine whether all or only some entities participate in a relationship. Two types of participation: - Mandatory (total) - Optional (partial) Lilac Safadi E/R Model

  40. Participation Constraints • Mandatory (total) (1:*): if an entity’s existence requires the existence of an associated entity in a particular relationship (existence-dependent). (e.g. CLASS taught-by PROFESSOR) CLASS is a total participator in the relation A weak entity always has a mandatory participation constraints but the opposite not always true. Lilac Safadi E/R Model

  41. Participation Constraints • Optional (partial) (0:*): if an entity’s existence does not require a corresponding entity in a particular relationship. (Not existence-dependent) (e.g. PROFESSOR teach CLASS) PROFESSOR is a partial participator in the relation Lilac Safadi E/R Model

  42. E/R Case Study Design a database for a bank with the following description: Each bank has a unique name. Each branch has a number, name, address (number, street, city), and set of phones. Customer includes their name, set of address (P.O. Box, city, zip code, country), set of phones, and social security number. Accounts have numbers, types (e.g. saving, checking) and balance. Other branches might use the same designation for accounts. So to name an account uniquely, we need to give both the branch number to which this account belongs to and the account number. Not all bank customers must own accounts. An account must have only one customer. A customer may have many accounts and in different branches. A customer may have at most 5 accounts in the bank. Lilac Safadi E/R Model

  43. Problems with ER ModelConnection Traps Connection traps are problems that occur due to misinterpretation of the meaning of certain relationships. Types of connection traps: • Fan Trap • Chasm Trap Lilac Safadi E/R Model

  44. Fan Trap STAFF has DIVISION operate BRANCH SG1 r1 D1 r4 B002 SG3 r2 r5 B003 SG2 r3 D2 r6 B004 1 1 operate has DIVISION M M BRANCH STAFF Lilac Safadi E/R Model

  45. Fan Trap DIVISION operate BRANCH has STAFF D1 r1 B002 r4 SG1 r2 B003 r5 SG3 D2 r3 B004 r6 SG2 1 M has operate BRANCH M 1 STAFF DIVISION Lilac Safadi E/R Model

  46. Fan Trap Fan trap where a model represents a relationship between entity types, but the pathway between certain entity occurrence is ambiguous. Exists where 2 or more 1:M relationships fan out from the same entity. Lilac Safadi E/R Model

  47. Chasm Trap BRANCH has STAFF oversee PROPERTY B001 r1 SG1 r4 P1 B003 r2 SG2 P14 B002 r3 SG3 r6 P5 1 M oversee has STAFF (1:1) (0:*) M 1 PROPERTY (0:1) (1:*) BRANCH Lilac Safadi E/R Model

  48. Chasm Trap BRANCH has STAFF oversee PROPERTY B001 r1 SG1 r4 P1 B003 r2 SG2 P14 B002 r3 SG3 r5 P5 offer r6 r7 r8 1 M oversee has STAFF (0:*) M (1:1) 1 (0:1) BRANCH (1:*) 1 M PROPERTY offer Lilac Safadi E/R Model

  49. Chasm Trap Chasm trap where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entities. Exists when partial participating entity is part of the pathway between related entities. Lilac Safadi E/R Model

  50. What makes a good DB design? • Faithfulness. Entity types, attributes & relationship types should reflect reality. Hourly-rate BRANCH (a) managers-in 1 1 EMPLOYEE BRANCH (b) Lilac Safadi E/R Model

More Related