Entity-Relationship Diagrams - Lecture 3 - Database Design and ER Modeling

Oct 25, 2026

Lecture 3 explains how database design proceeds and how relational databases rely on structured requirements and modeling steps. The session reviews phases of DB design from requirements analysis and functional analysis through conceptual, logical, and physical design, illustrating the flow of outputs into subsequent phases. It then introduces ER modeling using entity-relationship diagrams, including ERD notation, core concepts of entities, attributes, relationships, constraints, and mapping an ER diagram to a relational schema. Examples and diagram symbols support practical understanding.

LanguageEnglish

CategoryTechnology

FormatPDF

Pages39

Reading Time1 h 38 min

AccessFree to download

Recall: Relational Database Management

Recap: Steps in Database Design

Phases of DB Design

Phases of DBMS Design

ER Modeling

Traditional approach to Conceptual Modeling

ER Model Basics

E-R Diagram as Wallpaper

Entity Relationship Diagram Symbols

Entity-Relationship Diagrams Fall 2017, Lecture 3 There is nothing worse than a sharp image of a fuzzy concept. Ansel Adams 1 Recall: Relational Database Management Relational DataBase Management Systems were invented to let you use one set of data in multiple ways, including ways that are unforeseen at the time the database is built and the 1st applications are written. ( Curt Monash, analyst/blogger) Software to be used in this Chapter… • MySQL Workbench which can be downloaded from [http://www.mysql.com/products/workbench/](http://www.mysql.com/products/workbench/) Recap: Steps in Database Design this lecture 5 Phases of DB Design • Requirements Analysis § Database designers interview prospective users and stakeholders § Data requirements describe what kind of data is needed § Functional requirements describe the operations performed on the data • Functional Analysis § Concentrates on describing high-level user operations and transactions • Does also not contain implementation details • Should be matched versus conceptual model Phases of DB Design • Conceptual Design § Transforms data requirements to conceptual model § Conceptual model describes data entities, relationships, constraints, etc. on high-level • Does not contain any implementation details • Independent of used software and hardware • Logical Design § Maps the conceptual data model to the logical data model used by the DBMS • e.g. relational model, hierarchical model,… • Technology independent conceptual model is adapted to the used DBMS software • Physical Design § Creates internal structures needed to efficiently store/manage data • Table spaces, indexes, access paths,… • Depends on used hardware and DBMS software 7 Phases of DBMS Design • While modeling the data, three design phases have to be completed § The result of a phase serves as input to the next phase § Often, automatic transition is possible with some additional designer feedback Example: DBA for Bank of America • Requirements Specification § Determine the requirements of clients (Database to store information about customers, accounts, loans, branches, transactions,…) • Conceptual Design § Express client requirements in terms of E/R model. § Confirm with clients that requirements are correct. § Specify required data operations • Logical Design § Convert E/R model to relational, object-based, XMLbased, • Physical Design § Specify file organizations, build indexes 9 ER Modeling • Traditional approach to Conceptual Modeling § Entity-Relationship Models (ER-Models) • Also known as Entity-Relationship Diagrams (ERD) • Introduced in 1976 by Peter Chen • Graphical representation • Top-Down-Approach for modeling § Entities and Attributes § Relationships § Constraints • Some derivates became popular § ER Crow’s Foot Notation (Bachman Notation) § ER Baker Notation § Later: Unified Modeling Language (UML) ER Modeling • What are the entities and relationships? • What info about E’s & R’s should be in DB? • What integrity constraints (business rules) hold? • ER diagram is a representation of the `schema’ • Can map an ER diagram into a relational schema. • Conceptual design is where the SW/data engineering begins 11 12 E-R Diagram as Wallpaper • Very common for them to be wall-sized 13 ER Model Basics lot Employees • Entity: § Real-world object, distinguishable from other objects . § Described using a set of attributes . § Has its own identity and represents just one thing • Entity Set: A collection of similar entities . E.g., all employees. § All entities in an entity set have the same set of attributes . (Until we consider hierarchies, anyway!) § Each entity set has a key (underlined). § Each attribute has a domain. ER Model Basics Employees | ssn | name | lot | | --- | --- | --- | | 123-22-3666 231-31-5368 131-24-3650 | A

FAQs

What are the main phases in database design covered in this lecture?

The lecture outlines requirements analysis, functional analysis, conceptual design, logical design, and physical design. Each phase produces outputs that feed the next phase.

What does conceptual design produce in ER modeling terms?

Conceptual design transforms data requirements into a conceptual model describing entities, relationships, constraints, and related high-level structure without implementation details.

How is an ER diagram mapped to a relational schema?

The lecture states that an ER diagram can be mapped into a relational schema, using entities and relationships represented in ERD form to derive the corresponding relational structures.

Oct 25, 2026

Upload by Callum