Chapter 2 EntityRelationship Model Chapter 12 13 in
- Slides: 74
Chapter 2 Entity-Relationship Model Chapter 12 & 13 in Textbook
Database Design Steps in building a database for an application: 1. Understand the 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) ER Model 2
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 ER Model 3
name PROFESSOR number DOB 1 ERD Example 1 1 (0, 4) (0, *) 1 (0, 1) (1, 1) 1 SCHOOL 1 (1, *) dean of operate (0, *) chairs advise (1, 1) teach 1 (1, 1) M DEPARTMENT (1, *) 1 (1, 1) M N STUDENT offer (0, *) (1, 1) M M enroll-in M CLASS (0, *) (1, 1) M 1 belong-to COURSE (0, *) code has hrs 1 (1, *)
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 ER Model COURSE 5
Relationships & Relationship Types • A relationship associates 2 or more entities. • A relationship type is a set of associations between entity types. STUDENT ER Model study COURSE 6
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. ER Model 7
Degree of Relationship Type A relationship of degree two (2 entity types) are binary. STUDENT ER Model study COURSE 8
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. STUDENT register COURSE STAFF ER Model 9
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 ER Model 10
Roles COURSE Role indicates the purpose that each participating entity type plays in a relationship. (e. g. prerequisite, requester) requester prerequisite require ER Model 11
Roles Role can be used when two entities are associated through more than one relationship to classify the purpose of each relationship. Manager manages BRANCH STAFF Staff member ER Model Branch office allocated Branch office 12
Attributes are descriptive properties for an entity type or a relationship type. All entities in one entity type have the same attributes. name DOB St_no Tel_no STUDENT ER Model 13
Attributes of Entities name DOB St_no hours number Tel_no STUDENT ER Model name study COURSE 14
Attributes of Relationships Object Start Employee contract End Company 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. ER Model 15
Simple & Composite Attributes Simple attribute is an attribute that have a single value with an independent existence. e. g. salary, age, gender, . . . ER Model 16
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) LName initial name FName DOB St_no Area_cd no Tel_no EX STUDENT ER Model 17
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, . . . ER Model 18
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 name DOB St_no Area_cd no Tel_no EX STUDENT ER Model 19
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 STUDENT ER Model age 20
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. ER Model 21
Keys Example ELEMENT(symbol, name, atomic_no) ER Model 22
Keys Example Candidate Key ELEMENT(symbol, name, atomic_no) Primary Key ER Model Alternate Keys 23
Choice of PK Choice of Primary Key (PK) is based on: • Attribute length • Number of attributes required • Certainty of uniqueness ER Model 24
Primary Key in ERD LName initial name FName DOB St_no no Tel_no STUDENT ER Model Area_cd EX age 25
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, newspaper. Name, date. Advert) ER Model 26
Composite Key in ERD Section_no name hours crs_code CLASS ER Model 27
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 existencedependent on some other entity type. It does not have a PK. ER Model 28
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 emp_no DOB EMPLOYEE ER Model dep_no has FName DEPENDENT 29
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) – Many to one (M: 1) ER Model – One to many(1: M) – Many to many (M: N) 30
One-To-One Relationship PROFESSOR P 1 P 2 P 3 PROFESSOR 1 chair r 1 DEPARTMENT D 002 r 2 D 001 1 chairs DEPARTMENT A professor chairs at most one department; and a department is chaired by only one professor. ER Model 31
One-To-Many Relationship PROFESSOR P 1 P 2 P 3 PROFESSOR 1 teach r 1 r 2 r 3 teach COURSE C 01 C 02 C 03 C 04 M COURSE A course is taught by at most one professor; a professor teaches many courses. ER Model 32
Many-To-One Relationship CLASS C 1 C 2 C 3 CLASS require r 1 r 2 r 3 M require ROOM R 001 R 002 R 003 R 004 1 ROOM A class requires one room; while a room can be scheduled for many classes. ER Model 33
Many-To-Many Relationship CLASS C 1 C 2 C 3 CLASS enroll r 1 r 2 r 3 r 5 M STUDENT S 1 S 3 S 4 S 5 enroll N STUDENT A class enrolls many students; and each student is enrolled in many classes. ER Model 34
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#) ER Model 35
Multiplicity STAFF SG 1 SG 2 SG 3 STAFF ER Model manage r 1 BRANCH B 002 r 2 B 001 1 (0, 1) manage 1 (1, 1) BRANCH 36
Multiplicity STAFF SG 1 oversee r 1 r 2 r 3 SG 2 SG 3 STAFF ER Model 1 (0, 10) (0, *) oversee PROPERTY P 1 P 2 P 14 P 6 M (0, 1) PROPERTY 37
Multiplicity Newspaper Al-Riyadh advertise PROPERTY r 1 P 1 r 2 P 13 r 4 P 6 P 4 Al-Bilad Al-Madinah Al-Sharq NEWSPAPER ER Model M (0, *) advertise N PROPERTY (0, *) 38
Participation Constraints Participation constraints determine whether all or only some entities participate in a relationship. Two types of participation: - Mandatory (total) - Optional (partial) ER Model 39
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 i. e. CLASS is a total participator in the relation. A weak entity always has a mandatory participation constraints but the opposite not always true. ER Model 40
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 i. e. PROFESSOR is a partial participator in the relation. ER Model 41
ER Case Study Class exercise: Banks Database § 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 and a customer may have at most 5 accounts in the bank. § An account must have only one customer. § A customer may have many accounts in different branches.
ER Case Study Homework: Television Series Database A Television network wishes to create a database to keep track of its TV series. A television series has one or more episode. Television series identified by name and season number, and includes their production company name and Num_of_Episodes ( i. e. total number of episodes in a specific season of a series ). Episode of a specific season of a series is identified by episode number and has a title and a length. No episode can exist without a corresponding television series. Also each episode has only one writer. A writer is identified by name, and also has birth date and a literary agency that represents him or her. An actor appears as a performer in a television series or a guest star on an episode. An actor is identified by name and also has a nationality and birth date. An actor plays a particular character in a television series or episode.
Problems with ER Model Connection Traps Connection traps are problems that occur due to misinterpretation of the meaning of certain relationships. Types of connection traps: – Fan Trap – Chasm Trap ER Model 44
Fan Trap STAFF SG 1 SG 3 SG 2 has r 1 r 2 r 3 has M STAFF ER Model DIVISION D 1 operate r 4 r 5 r 6 D 2 1 DIVISION 1 BRANCH B 002 B 003 B 004 operate M BRANCH 45
Fan Trap DIVISION D 1 D 2 operate r 1 r 2 r 3 operate 1 DIVISION ER Model BRANCH B 002 B 003 B 004 M BRANCH 1 has r 4 r 5 r 6 STAFF SG 1 SG 3 SG 2 has M STAFF 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. ER Model 47
Chasm Trap BRANCH B 001 B 003 B 002 has r 1 r 2 r 3 has STAFF SG 1 SG 2 SG 3 M (1, 1) STAFF oversee r 4 r 6 1 (0, *) oversee M 1 BRANCH ER Model (1, *) PROPERTY P 14 P 5 (0, 1) PROPERTY 48
Chasm Trap BRANCH B 001 B 003 B 002 has r 1 r 2 r 3 STAFF SG 1 SG 2 SG 3 oversee r 4 r 5 PROPERTY P 14 P 5 offer r 6 r 7 r 8 M has (1, 1) 1 STAFF (0, *) oversee M 1 BRANCH ER Model (1, *) (0, 1) 1 offer M PROPERTY 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. ER Model 50
What makes a good DB design? • Faithfulness. Entity types, attributes & relationship types should reflect reality. Hourly-rate BRANCH (a) EMPLOYEE 1 managers-in 1 BRANCH (b) ER Model 51
What makes a good DB design? • Avoid Redundancy. Say everything only once. Biz-name PERSON ER Model name M own N BUSINESS 52
What makes a good DB design? • Simplicity. Avoid introducing more elements into your design than is absolutely necessary. Example 1 PERSON 1 represent 1 OWNER 1 own M BUSINESS ER Model 53
What makes a good DB design? Example 2 own PERSON BUSINESS run ER Model 54
What makes a good DB design? Example 3 EMPLOYEE M work 1 COMPANY M 1 own contract 1 1 ER Model PERSON 55
What makes a good DB design? • Pick the right kind of element. Example 1 dpt_name STUDENT M allocate 1 degrees DEPARTMENT degrees STUDENT dpt_name ER Model 56
What makes a good DB design? Example 2 BUSINESS 1 contract (business, lawyer, supplier) M SUPPLIER 1 LAWYER (a) BUSINESS 1 Biz-make M CONTRACT M Sup-make 1 SUPPLIER M Biz-make (business, contract) Sup-make (supplier, contract) Law-make (lawyer, contract) Law-make (b) 1 LAWYER 57
Enhanced Entity-Relationship Model (EER) EER is an ER model supported with additional semantic concepts. Semantic concepts supported: - Specialization - Generalization - Aggregation ER Model 58
Specialization • Top-down design process; we designate sub-groupings within an entity type that are distinctive from other entities in the set. • These sub-groupings (subclasses) become lower-level entity types that have attributes or participate in relationships that do not apply to the higher-level entity set (superclass). ER Model 59
Specialization/Generalization Superclass STAFF IS_A relationship 1: 1 Subclass ER Model MANAGER SECRETARY SALES PERSONNEL 60
Generalization • A bottom-up design process – combine a number of entity types that share the same features into a higher-level (superclass) entity type. • Specialization and generalization are simple inversions of each other; they are represented in an EER diagram in the same way. ER Model 61
Inheritance A subclass entity type inherits all the attributes and relationship participation of the superclass entity type to which it is linked. ER Model 62
Inheritance shared attributes Superclass Subclass Unshared attributes ER Model name DOB address STAFF SALES PERSONNEL Car allowance Sales area Shared relationship contract COMPANY require CAR Unshared relationship 63
Constraints on Specialization/Generalization Participation constraint determines whether every member in the superclass must participate as a member of a subclass. Two types of participation constraints: - Mandatory (total) - Optional (partial) ER Model 64
Participation Constraints Mandatory (total) participation where every member in the superclass must also be a member of a subclass. STAFF salary ER Model FULL-TIME STAFF PART-TIME STAFF Hourly-rate 65
Participation Constraints Optional (partial) participation where a member in the superclass need not belong to any of its subclasses. STAFF SALES MANAGER SECRETARY PERSONNEL ER Model 66
Constraints on Specialization/Generalization Disjoint constraint describes the relationship between members of the subclasses & indicates whether it is possible for a member of a subclass to be a member of one or more subclasses. Two types of disjoint constraints: - Disjoint - Non-Disjoint ER Model 67
Disjoint Constraints Disjoint constraint when an entity can be a member of only one of the subclasses of the specialization. STAFF d salary ER Model FULL-TIME STAFF PART-TIME STAFF Hourly-rate 68
Disjoint Constraints Non-disjoint constraints: an entity is a member of more than one subclass of specialization. Entity types may overlap. STAFF o SALES MANAGER SECRETARY PERSONNEL ER Model 69
Aggregation • Represents a “part-of” relationship between entity types, where one represents the ‘whole’ and the other the ‘part’. • No inherited attributes; each entity has its own unique set of attributes. ER Model 70
Aggregation TEAM MEMBER ER Model 71
Summary of ERD notations (1) ENTITY ATTRIBUTE KEY ATTRIBUTE WEAK ENTITY MULTI-VALUED RELATIONSHIP COMPOSITE IDENTIFYING RELATIONSHIP ER Model DERIVED 72
Summary of ERD notations (2) 1 (min, max) ER Model M CARDINALITY RATION PARTICIPATION CONSTRAINTS 73
Summary of EERD notations (3) d Disjoint constraint o Non-Disjoint constraint Total Participation Optional Participation ER Model 74
- Multiple nuclei model ap human geography
- Model edu
- Transforming to achieve linearity
- Mapping of er model to relational model
- Quantum mechanical model 1926
- Bohr quantum mechanical model
- What is nativisation
- Teori teori motivasi
- Jenis model pengajaran
- Model pelatihan
- 13 model perilaku konsumen
- Model model etika profesi
- Model dokumentasi sistem komputerisasi
- Rothman model
- Billiard model international relations
- Compare and contrast bohr model to quantum model
- Vector vs raster data
- Bohr rutherford diagram
- Atomic structure graphic organizer
- Heliocentric vs. geocentric
- Osi model vs internet model
- Model-model inovasi pendidikan
- Contoh model dokumentasi keperawatan sor
- Abc-x model
- S-r model of human behaviour
- Analysis model to design model
- Heliocentric vs geocentric model
- Polis model
- Contoh model kuantitatif dalam pengambilan keputusan
- Model model sistem informasi manajemen
- Model model perilaku abnormal
- Organisational behaviour definition
- Advantages and disadvantages of hilda taba model pdf
- Barlunds model of communication example
- 3 model organisasi pengembangan kurikulum
- Model-model komunikasi
- Kelemahan model atom dalton tidak dapat menerangkan
- Davson danielli model vs fluid mosaic model
- Davson danielli model vs fluid mosaic model
- Lean model canvas vs business model canvas
- Interactive communication model
- Electron cloud model vs bohr model
- Cara memanfaatkan rekan sejawat guru dari sekolah lain
- Rumus model baumol
- örtük model alma
- Convert conceptual model to logical model
- Cash management models ppt
- Model model activity
- Who created the hoyt model
- Model model pembelajaran ips
- Model umum organisasi dan model umum perusahaan
- Latin america city model
- Model primal dan model dual program linear
- Model model pembelajaran kurikulum 2013
- A true man of god
- God model chapter 12
- A model advisory chapter 20
- Model's the 1st chapter
- Red tent summary
- Ch 8 great gatsby summary
- Chapter 10 chemical reactions answer key
- Chapter 11 stoichiometry test
- Chapter 9 study guide chemical reactions
- Chapter 7 similarity chapter test form a answer key
- Chapter 6 career readiness
- Chapter 7 ionic and metallic bonding chapter answer key
- Chapter 9 surface water answer key
- Representing motion physics
- Chemistry: the central science chapter 14 answers
- Chapter 7 ionic and metallic bonding chapter answer key
- Population ecology section 1 population dynamics
- Chapter 2 chapter assessment
- Book of philippians background
- Properties of ionic compounds
- 7 ionic and metallic bonding practice problems