er model

ER Model Asst. Prof. Dr. Kanda Runapongsa Saikaew - PDF document

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


  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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