ER Model and Relational Model in Databases

• The Entity Relational Model (ER) is a general modeling tool used for database design
• The first step maps a real-world scenario into an ER model-based design known as ER-1
• The second step translates the ER model (ER-1) into the Relational Model

Core Assumptions of ER Model and Relational Model

• All entities within an entity set share the same attributes
• All relationships within a relationship set share an identical set of attributes
• Both instructor and student entities exhibit the same collection of attributes
• The assumptions align well with the relational model

Roadmap for Designing Database Applications

• Analyzing the real-world scenario involves an analysis phase that results in Design Document ER-1
• Transferring the design document (output of the analysis phase ER-1) to the Relational Model
• Refining the design process entails eliminating redundancy and design anomalies
• The last stage involves developing the database application software

Analyzing the Real-World Scenario

• There are two sides: the real-world application side with domain experts
• The second is the DBMS side with a system analyst
• This process outputs a high-level Design Document

Domain Experts

• Domain experts tell their story and specify their needs
• For use cases like a university or hospital database

System Analyst

• System analysts map domain expert needs to an intermediate model
• This allows database developers to design their own system-dependent database applications
• Modeling languages are used

Common Modeling Languages

• Unified Modeling Language (UML) is used for developmental modeling to visualize a system's design
• ER Model(Entity-Relationship Model)
  • High-level data model
  • Defines the data elements and relationships for a specified system
  • Provides a conceptual design for the database
  • Simple and easy design views of data

Analyzing The Real-World Scenario Output

• Includes a High-level Design Document, Entity Relationship diagrams, mockup screen designs, and workflows of processes

Transferring the Design Document to the Relational Model

• Relies on Design Documents (ER Diagrams)
• Outputs a Relational Schema, which includes tables, attributes, and keys
• Employs DB Design tools or is done manually

Refining the Design: Eliminating Redundancy and Design Anomalies

• Uses Relation Schemas
• Outputs an optimized representation, yielding equivalent, normalized Relational schemas
• Normalizes documents to prevent data replication
• Mathematical foundation: Normalization Theory
• Can be done with DB Design tools or manually

Developing the Database Application Software

• Optimized models like relational or graph schemas, screen designs, and process workflows are inputs
• Outputs the Database Application

Summary of the entity relationship model

• Designing databases
• Entity Relationship Modeling
• Translating the ER Diagram into the Relational data model

Key Elements in Database Design Progress

• Analyzing Real-world Scenario
• Application of the Entity-Relationship Model (ER Model)
• Creation of a Conceptual Design Document
• Identifying Entities and Relationships
• Specifying Attributes and Integrity Constraints
• Creating a pictorial database "schema" (ER Diagram)

ER Model Building Blocks

• Entity
• Attribute
• Relationship
• Entity Set
• Relationship Set
• ER Diagram

ER Model - Entity

• Represented by a rectangle
• Real-world objects of interest
• Examples: Instructor, Student, Course, Classroom, Textbook, Restaurant
• Each entity is described using a collection of attributes

ER Model - Attributes

• Displayed as an Oval
• Describes an entity; each one has a type (domain)
• Example attributes for entity instructor: iid, iName, iRank. salary, iOfficeNumber, iPhoneNumber, iAddress

ER Model - Entity Set

• Can be defined as a group or collection of entities sharing the same attributes
• Each entity set has one or more attributes that serve as its key
• Keys are underlined inside the ovals, iid is the key for the instructor

ER Model - ER Diagram

• Entity Set shown in a Rectangle
• Attributes shown in an Oval
• The key of the Entity set is underlined

Relationship Sets

• A relationship associates two or more entities together
• Relationships can have attributes and can consist of binary as well as n-ary relationship sets
• ER Diagrams show relationships as a diamond shape

Cardinality Constraints in Relationship Sets

• Defined in 1-1, 1-m, m-1 and m-m Relationships

1-1 Relationships

• One-to-one relationship

1-m Relationship

• One-to-many relationship

m-1 Relationship

• Many-to-one relationship

m-m Relationship

• Many-to-many relationship

Total Participation

• Every entity in the entity set participates in the relationship
• The ER Diagram: uses a bold line to indicate total participation

ER Diagram Guidelines

• Uses an arrow at Courses side
• One-to-Many Relationship
• Courses side uses a straight line, many side
• Instructor's side uses an arrow, one side

More Complex Participation Constraints

• Lines on one side of a relationship may be annotated by minimum and maximum cardinalities
• Appears in the form: LowerLimit-UpperLimit
• LowerLimit of 1 means total participation, LowerLimit of 0 describes partial participation
• LowerLimit of m means at least m participations in the relationship
• An UpperLimit of 1 means "at most one relationship"
• An UpperLimit of * means Many (no limit)
• An UpperLimit of n means at most n participations in the relationship

Weak Entity Sets

• Weak entities identified uniquely by considering the primary key of another entity "owner"
• Both owner and weak entity sets must participate in a either one-to-one or one-to-many fashion
• Identifying relationship relates the weak entity set with the identifying entity set
• Weak entity sets must have total participation in the identification
• Weak entity must have a discriminator key, which the entities are associated to

Specializations and Generalizations (IS-A Hierarchies)

• Terms are used interchangeably, the design depends on whether you use a top-down or bottom-up approach
• Specialization means that you Identify a subset within an entity set with commonalities
• Specialized subsets are lower-level entity sets that inherit all the attributes from the higher-level set
• Has additional attributes not shared; can participate in relationships independently

Specializations Constraints

• The overlap constraint defines whether the attributes can be a part of multiple specializations
• Needs to state if the overlap is allowed (Overlap) or disallowed (Disjoint)
• Covering constraints determine whether an instructor is neither a tenured faculty nor an adjunct faculty
• It can have total or partial coverage
• It impacts the underlying database

ER Diagram Guidelines

• To get name and Tenure of Professor, need to access two relations: the one for the super-class (Instructor) and the one for the sub-class (Tenured_Prof)

Aggregation

• Use when you want to create a relationship to other relationships and entities
• Aggregation treats a relationship set as an entity set

Attributes In The ER Model

• Simple (single valued) and each attribute has a Domain with sets of feasible values
• Composite: A hierarchy of any depth will contain component attributes
• Multi-valued attributes like multiple phone numbers
• Double Ovals are used for ER Diagram Notation
• Derivable: It can be derived from existing attributes with dashed oval notations

Design Choices In The ER Model

• ER Modeling is a design process
• Should a concept should be modeled as an entity or attribute
• Composite attributes and multivalued attributes CAN be entities
• If an instructor has multiple phones, create an entity set that is specifically for phones.
• Can Semester be an entity as well as an attribute?
• Address attribute is important, make it an entity with street name, city, state and zip
• Otherwise make it an atomic attribute

Design Decisions

• Whether it is an entity or relationship
• Arity of the relationships
• Aggregation versus Relationship

Translating The ER Diagram to the Relational Model

• Goal
  • Capture all details, preserve data
• Process
  • Entities with attributes
  • Relationships, weak entity sets
  • Specializations and generalizations

Translating ER Entity Sets into the Relational Model

• Each entity must make it a relation, and each entity set must relate their attributes as part of the relation attributes
• All keys for relations must be the same as key for the entity set.

Translating ER Relationships into the Relational Model

• Must map a relationship with attributes into a relation
• Then you add keys from the participating entities and put them as keys in the relationships

Translating ER Weak Entity Sets into the Relational Model

• Weak entity sets turns into tables
• The table needs to include a column for the primary key of all identifying strong entity sets
• Is there a column for attributes price and year
• It must have it different for all dependents, hence its relationship to instructor

Translating ER Specializations into the Relational Model

• Can Adam be under both categories in a overlapping setting
• This relates to total coverage constraints that either covers specializations or disallows them
• Coverage has to be partial as well, needing a schema for the super-class entity
• This relates to attributes of subclass