Electricite Du Laos (EDL) ER Model Asst. Prof. Dr. Kanda Runapongsa Saikaew (krunapon@kku.ac.th) Dept of Computer Engineering Khon Kaen University Objectives (1/2) Relational Data Model Terminology of relational data model How tables are used to represent data Properties of database relations How to identify candidate, primary, and foreign keys Meaning of entity integrity and referential integrity 2 Objectives (2/2) How to use ER modeling in database design The basic concepts of an ER model A diagrammatic technique for displaying an ER model How to identify and solve problems in an ER model 3 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 1
Electricite Du Laos (EDL) Data Model Integrated collection of concepts for describing data, relationships between data, and constraints on the data Has three components A structural part A manipulative part A set of integrity rules 4 Relational Model Terminology Relation: table with columns and rows Attribute: named column of a relation Domain: set of allowable values for one or more attributes Tuple: a record of a relation Relational database – collection of normalized relations with distinct relation names 5 RM Terminology Samples 6 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 2
Electricite Du Laos (EDL) Attribute Domain Samples 7 Alternative Terminology Relation, table Attribute, column, field Tuple, record, row 8 Properties of Relations (1/2) Table name is distinct from all other table names in the database Each cell of table contains exactly one atomic (single) value Each column has a distinct name Values of a column are all from the same domain 9 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 3
Electricite Du Laos (EDL) Properties of Relations (2/2) Each record is distinct; there are no duplicate records Order of columns has no significance Order of records has no significance 10 Relational Keys (1/2) Superkey A column, or a set of columns, that uniquely identifies a record within a table Candidate Key Superkey (K) such that no proper subset is a superkey within the table In each record, values of K uniquely identify that record (uniqueness) No proper subset of K has the uniqueness property (irreduciability) 11 Relational Keys (2/2) Primary Key Candidate key selected to identify records uniquely within table Alternate Keys Candidate keys that are not selected to be primary key Foreign Key Column, or set of columns, within one table that matches candidate key of some (possibly same) table 12 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 4
Electricite Du Laos (EDL) Relational Integrity (1/2) Null Represents value for a column that is currently unknown or not applicable for record Deals with incomplete or exceptional data Represents the absence of a value and is not the same as zero or spaces, which are values 13 Relational Integrity (2/2) Entity Integrity Every table must have a primary key Column or columns chosen to be the primary key should be unique and not null Referential Integrity If FK exists in a table, FK value must match a candidate key value of some record in its home table 14 Referential Integrity Broken • There is a foreign key (artist_ st_id id) value in the album table that references a non-existent artist • This anomaly came about when the record for an artist called "Aerosmith", with an artist_id t_id of "4", was deleted from the artist table, even though the album "Eat the rich" referred to this artist 15 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 5
Electricite Du Laos (EDL) Referential Integrity Enforced If referential integrity had been enforced The deletion of the main record would have been possible, but its associated record would have been deleted as well Alternatively, the existence of an associated record would not allow the delete operation of the referenced record, and instead return an error code. 16 Objectives (2/2) How to use ER modeling in database design The basic concepts of an ER model A diagrammatic technique for displaying an ER model How to identify and solve problems in an ER model 17 ER Modeling Top-down approach to database design Start by identifying the important data (called entities) and relationships between the data Then add more details Attributes of entities and relationships Constraints on entities, relationships, and attributes 18 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 6
Electricite Du Laos (EDL) Entities Entity A set of objects with the same properties, which are identified by a user or organization as having an independent existence Entity occurrence Each uniquely identifiable object within a set 19 Entities with Physical and Conceptual Existence 20 ER Diagram of Entity Entity Diagram Use a rectangle to represent an entity Insert the entity name inside the rectangle Example Video 21 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 7
Electricite Du Laos (EDL) Attributes Property of an entity or a relationship Hold values that describe each occurrence of an entity or relationship Represent the main source of data stored in the database 22 ER Diagram of Attributes Attribute Diagram Use an oval to represent an attribute Insert the attribute name inside the oval Video price category catalogNo dailyRental title 23 Classification of Attributes Attributes can be classified as being Simple or composite Single-valued or multi-valued Derived 24 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 8
Electricite Du Laos (EDL) Attributes (1/3) Simple attribute Attribute composed of a single component Example: salary Composite attribute Attribute composed of multiple components Example: name (firstname, lastname) 25 Attributes (2/3) Single-valued attribute Attribute that holds a single value for an entity occurrence Example: gender (M, F) Multi-valued attribute Attribute that holds multiple values for an entity occurrence Example: phone:area-code,number (043-362-160) 26 Attributes (3/3) Derived attribute Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity Example: age which can be derived from birthdate 27 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 9
Electricite Du Laos (EDL) Example: Entity Entity set “Staff” ER diagram of an entity “Employees” ssn name lot 111 A 12 222 B 24 name Entity “ 111 A 12 ” is ssn lot similar to Entity “ 222 B 24 ” since both of Staff them are described using the same set The key of this entity of attributes is “ssn” which is underlined 28 Relationships Relationship A set of meaningful associations among entities Relationship occurrence Each uniquely identifiable association within a set 29 ER Diagram of Relationships 30 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 10
Electricite Du Laos (EDL) Degree of a Relationship Degree of a relationship is the number of participating entities in relationship Relationship of degree Two is binary Three is ternary Four is quaternary 31 Example of ternary relationship 32 Recursive relationships Relationship where same entity participates more than once in different roles Relationships may be given role names to indicate purpose that each participating entity plays in a relationship 33 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 11
Electricite Du Laos (EDL) Example of a recursive relationship 34 Example1: Relationships A relationship between “Employees” and “Departments” since name dname ssn lot did budget Works_In Employees Departments “since” is a descriptive attribute of this relationship A relationship must be uniquely identified by the participating entities 35 Example2: Relationships Same entity set could participate in different relationship sets, or in different “roles” in same set. name ssn lot Manages Employees super- Works_In super-visee Employees Departments visor Reports_To 36 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 12
Electricite Du Laos (EDL) Multiplicity constraints on relationships Represents the number of occurrences of one entity that may relate to a single occurrence of an associated entity Represents policies (called business rules) established by user or company 37 Multiplicity constraints The most common degree for relationships is binary Binary relationships are generally referred to as being One-to-one (1:1) One-to-many (1:*) Many-to-many (*:*) 38 1:1 relationship – individual examples 39 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 13
Electricite Du Laos (EDL) 1:1 relationship - multiplicity 40 1:* relationship – individual examples 41 1:* relationship – multiplicity 42 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 14
Electricite Du Laos (EDL) *:* relationship – individual examples 43 *:* relationship - multiplicity 44 Complex relationships – individual examples 45 Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU 15
Recommend
More recommend
