Entity-Relationship Modelنموذج الكيان والعلاقة

Logical design involves two decisions (slide 4)

• Business decision — what attributes should we record in the database?
• Computer Science decision — what relation schemas should we have and how should the attributes be distributed among the various relation schemas?

Design approaches (slide 6)

• Entity Relationship Model (covered in this chapter) — models an enterprise as a collection of entities and relationships, represented diagrammatically by an entity-relationship diagram.
• Normalization Theory (Chapter 6) — formalize what designs are bad, and test for them.

The data model (slide 8)

• A plan or blueprint for a database design.
• More generalized and abstract than a database design.
• Easier to change than a database design — the place to work through conceptual problems.
• Data modeling operates at a high level, starting with conceptual models that capture the essential data and relationships without worrying about technical implementation.

ER model — database modeling (slide 9)

• The ER data model was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database.
• It is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema; because of this, many database-design tools draw on concepts from the ER model.
• Its diagrammatic representation, the ER diagram, can express the overall logical structure of a database graphically.

• An entity is represented by a set of attributes: instructor = (ID, name, street, city, salary); course = (course_id, title, credits).
• A subset of the attributes forms a primary key, uniquely identifying each member of the set.

• An attribute can be associated with a relationship set: advisor may have a date (when the student started with the advisor).
• Binary relationships involve two entity sets; most relationship sets are binary.
• proj_guide — students work on research projects under an instructor — is a ternary relationship between instructor, student and project.

• Mapping cardinalities are most useful in describing binary relationship sets (slide 16).
• Note: some elements in A and B may not be mapped to any elements in the other set.
• In diagrams: directed line (→) = "one"; undirected line (—) = "many" (slide 37).

Handling a multivalued attribute (slide 20)

• 1) Several new attributes, one for each component — can cause structural problems.
• 2) A new entity composed of the original multivalued attribute's components — the best option.

• Composite: components indented under the attribute (name, address, street).
• Multivalued: in braces — { phone_number }.
• Derived: followed by parentheses — age ( ).
• Primary key: underlined — ID.

Expressing weak entity sets (slide 50)

• A weak entity set is depicted via a double rectangle.
• The discriminator of a weak entity set is underlined with a dashed line.
• The relationship set connecting the weak entity set to the identifying strong entity set is depicted by a double diamond.
• Primary key for section — (course_id, sec_id, semester, year): section is weak, identified through sec_course by course.

• On slide 51, section is a double rectangle (a weak entity set) and its sec_id, semester and year are underlined with a dashed line (the discriminator).

Entity Relationship Diagram (slide 29)

• A graphical representation of entities and their relationships in a database structure.
• Describes the conceptual database as viewed by the end user: entities, attributes (keys and non-key), relationships.

Naming conventions (slide 30)

• Entity names: descriptive, familiar terminology, singular.
• Attribute names: descriptive of the data; proper naming facilitates communication and promotes self-documentation.
• All lowercase letters for the class assignments.

The examples drawn on slides 31–34

• Slide 31: the rectangles instructor (ID, name, salary) and student (ID, name, tot_cred), with ID underlined.
• Slide 33: the diamond advisor joins the two rectangles by lines.
• Slide 34 (relationship sets with attributes): the attribute date is drawn in a box joined to the advisor diamond by a dashed line.

Chen notation l..h (slide 43)

• l = minimum, h = maximum cardinality.
• Minimum 1 → total participation; maximum 1 → at most one relationship; maximum * → no limit.
• Example: an instructor can advise 0 or more students (0..*); a student must have 1 advisor and cannot have multiple advisors (1..1).

Crow's foot (slides 44–45)

• Minimum 0 (optional): an oval next to the optional entity.
• Minimum 1 (mandatory): a vertical hash mark next to the required entity.
• Next to B, the symbol farther from B gives the minimum and the symbol touching B the maximum number of records of "B" that can be associated with one record of "A" (slide 45).

• Slide 47 — CLASS is optional to COURSE: COURSE (1,1) — generates — (0,N) CLASS; the CLASS end shows a circle with a crow's foot.

The invoicing problem (slide 49, Chen and crow's foot ERDs): this model may be read as follows

• each CUSTOMER may generate one or more INVOICEs
• each INVOICE is generated by one CUSTOMER
• each INVOICE contains one or more invoice LINEs
• each invoice LINE is contained in one INVOICE

Reduction to relation schemas (slide 53)

• Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the contents of the database.
• A database which conforms to an E-R diagram can be represented by a collection of schemas.
• For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set.
• Each schema has a number of columns (generally corresponding to attributes), which have unique names.

• Ignoring multivalued attributes, the extended instructor schema is: instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number, city, state, zip_code, date_of_birth) (slide 56).

Placing the foreign key (slides 58–60)

• Many-to-one / one-to-many, total on the many-side: add to the "many" side an attribute holding the primary key of the "one" side — dept_name in instructor instead of a schema for inst_dept.
• One-to-one: either side can act as the "many" side.
• If participation is partial on the "many" side, the extra attribute could result in null values.
• The schema of the relationship linking a weak entity set to its identifying strong entity set is redundant (section already contains sec_course's attributes).

Non-binary relationship sets (slide 62)

• Most relationship sets are binary; there are occasions when it is more convenient to represent relationships as non-binary — an E-R diagram with a ternary relationship: proj_guide joins instructor, student and project.

Cardinality on a ternary relationship (slide 63)

• At most one arrow out of a ternary (or greater degree) relationship.
• An arrow from proj_guide to instructor: each student has at most one guide for a project.
• More than one arrow has two possible meanings, so we outlaw more than one arrow.

Binary vs non-binary (slide 79)

• An n-ary relationship shows more clearly that several entities participate in a single relationship.
• parents (child, father, mother) is best replaced by two binary relationships, father and mother — this allows partial information (e.g., only the mother known).
• Some relationships are naturally non-binary: proj_guide.

Converting non-binary relationships (slide 81)

• Also need to translate constraints: translating all constraints may not be possible; there may be instances in the translated schema that cannot correspond to any instance of R.
• Exercise (slide): add constraints to the relationships RA, RB and RC to ensure that a newly created entity corresponds to exactly one entity in each of entity sets A, B and C.
• We can avoid creating an identifying attribute by making E a weak entity set identified by the three relationship sets.

• Depicted by a triangle labeled IS A (instructor "is a" person).
• Attribute inheritance: a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set.
• Slide 66: overlapping — employee and student; disjoint — instructor and secretary.

• Slide 66 also lists Partial (default) and Total.
• The terms specialization and generalization are used interchangeably (slide 71).

Slides 69–70

• Partial generalization is the default.
• Total generalization is specified by adding the keyword total in the diagram and drawing a dashed line from the keyword to the corresponding hollow arrow-head (for a total generalization), or to the set of hollow arrow-heads (for an overlapping generalization).
• The student generalization is total: all student entities must be either graduate or undergraduate. Because the higher-level entity set arrived at through generalization is generally composed of only those entities in the lower-level entity sets, the completeness constraint for a generalized higher-level entity set is usually total.
• Disjoint subtypes are also known as nonoverlapping subtypes (d) (slide 70).

• Slide 72: without aggregation, eval_for joins instructor, student, project and evaluation, beside proj_guide.
• Aggregation: treat relationship as an abstract entity; allows relationships between relationships; abstraction of relationship into new entity (slide 73).
• Slide 74: proj_guide with instructor, student and project is boxed as one unit, and eval_for joins that box to evaluation. It represents: a student is guided by a particular instructor on a particular project; a student, instructor, project combination may have an associated evaluation.

Schema (slide 75)

• Primary key of the aggregated relationship + primary key of the associated entity set + descriptive attributes.
• eval_for (s_ID, project_id, i_ID, evaluation_id); the schema proj_guide is redundant.

E-R design decisions (slide 82)

• The use of an attribute or entity set to represent an object.
• Whether a real-world concept is best expressed by an entity set or a relationship set.
• The use of a ternary relationship versus a pair of binary relationships.
• The use of a strong or weak entity set.
• The use of specialization/generalization — contributes to modularity in the design.
• The use of aggregation — can treat the aggregate entity set as a single unit without concern for the details of its internal structure.

• Entity vs. attribute: phone as an entity allows extra information about phone numbers, plus multiple numbers.
• Entity vs. relationship set: use a relationship set to describe an action that occurs between entities; e.g., date as an attribute of advisor or of student.
• Specialization/generalization contributes to modularity in the design.
• Aggregation treats the aggregate entity set as a single unit without concern for its internal structure.

The design-issue diagrams (slides 77–78)

• Slide 77: instructor (ID, name, salary, phone_number) versus instructor (ID, name, salary) — inst_phone — phone (phone_number, location).
• Slide 78: section — section_reg — registration — student_reg — student; the slide also names placement of relationship attributes.

UML (slides 87–88)

• UML: Unified Modeling Language, modelling an entire software system.
• UML class diagrams correspond to E-R diagrams, with differences — note the reversal of position in the cardinality constraint depiction.

• Generalization can use merged or separate arrows independent of disjoint/overlapping (slide 89).
• UML has many components to graphically model different aspects of an entire software system (slide 87).