Study guide: ERD, Normalization and Modeling

Course Outline

  1. Database Systems and Their Environment
  2. Database Architecture and Independence
  3. Database Roles and Requirements
  4. Data Models and Relational Concepts
  5. Keys in Relational Databases
  6. ERD Entities and Attributes
  7. ERD Relationships and Cardinality
  8. Enhanced Entity-Relationship Modeling
  9. Mapping EER Models to Schemas
  10. Normalization and Data Anomalies
  11. UML and Use Case Diagrams
  12. UML Classes and Relationships
  13. Data Flow Diagrams
  14. SQL Fundamentals and Commands
  15. SQL Data Retrieval and Manipulation
  16. SQL Aggregation, Subqueries, and Joins
  17. Data Changes and Database Security
  18. Database Interfaces and Wireframes
  19. Database Testing
  20. Software Documentation

1. Database Systems and Their Environment

Key Concepts & Definitions

  • Database: A structured collection of data that enables efficient storage, retrieval, and manipulation.
  • DBMS: An interface between a database and end users or application programs that organizes data and maintains its accessibility, security, and integrity.
  • Data: Raw, unprocessed facts that need to be organized.
  • File-oriented system: Stores data in separate, independently managed files and retrieves information through predefined paths, requiring repetitive coding and substantial storage management.
  • Data dictionary: A catalog of data elements and their properties, with metadata describing data structures, types, and constraints.

★ Must-know

📌 Information is data interpreted and assembled in a meaningful way, such as aggregated and analyzed customer names and purchase amounts that reveal purchasing trends.

  • File-oriented systems can cause data redundancy and inconsistency, limited security, and difficulties with simultaneous access, including data locking and conflicts.

Further detail

  • The five database types listed are:

    • Relational databases
    • NoSQL databases
    • Object-oriented databases
    • Hierarchical databases
    • Network databases
  • The stated database advantages are:

    • Data integrity
    • Data security
    • Reduced redundancy
    • Scalability
    • Improved data access
  • A typical database system comprises:

    • Hardware
    • Software
    • Data
    • Procedures
    • A database access language
    • Users
  • DBMS functions include:

    • Data storage, retrieval, and updating
    • User access control
    • Backup and recovery
    • Data integrity management
    • Data dictionary management

2. Database Architecture and Independence

Key Concepts & Definitions

  • Mapping: Governs relationships between database architecture levels so that changes at one level can be made without affecting others.

★ Must-know

  • The three database architecture levels are:
    • External
    • Conceptual
    • Internal

📌 Logical data independence permits changes to the conceptual schema without changing external schemas or application programs, whereas physical data independence permits changes to storage methods and structures without changing the conceptual schema.

Further detail

  • The external level defines user-tailored views, the conceptual level represents the entire database structure, and the internal level describes physical storage structures and access methods.

Memory Hook

External views serve users, while the internal level governs physical storage.

3. Database Roles and Requirements

Key Concepts & Definitions

  • Requirement: A condition or capability needed by users or systems to solve a problem or achieve an objective.
  • User requirement: Specifies what end users need from a system, including the tasks it must support and features it must provide.

★ Must-know

📌 Functional requirements specify system behavior and services, whereas non-functional requirements specify operational qualities and constraints such as performance, reliability, and usability.

Further detail

  • Database systems support structured storage, retrieval, and management for operations and decisions, including e-commerce transactions, customer relationship management, data mining, and blockchain transaction integrity and transparency.

  • Examples of non-functional requirements include responding to user queries within two seconds and handling at least 10,000 concurrent users without performance degradation.

  • System requirement categories include:

    • Performance requirements
    • Interface requirements
    • Design requirements
    • Security requirements
  • Requirements are prioritized as high when they deliver immediate business value and address critical user needs, medium when essential but not time-sensitive, and low when they are nice-to-have and deferrable without significant impact on initial rollout.

4. Data Models and Relational Concepts

Key Concepts & Definitions

  • Data model: A conceptual representation of the data structures required by a database, defining how data is connected, processed, and stored.

★ Must-know

  • E.F. Codd proposed the relational data model in 1970; it structures data in relations and is based on first-order predicate logic and set theory.

📌 In relational terminology, degree is the number of attributes in a relation, whereas cardinality is the number of tuples.

Further detail

  • Data models typically include entities, attributes, and relationships: entities are objects or concepts with stored data, attributes are their properties, and relationships describe how entities interact.

  • The six data model types listed are:

    • Hierarchical
    • Network
    • Relational
    • Object-oriented
    • Object-relational
    • NoSQL
  • The relational model's fundamental concepts are:

    • Relations
    • Tuples
    • Attributes
    • Domains
    • Integrity constraints
  • A relation has these properties:

    • A unique name within the database
    • A unique name for each attribute
    • Atomic attribute values
    • Irrelevant order of attributes and tuples
    • Unique tuples

5. Keys in Relational Databases

Key Concepts & Definitions

  • Primary key: A candidate key selected to uniquely identify tuples in a relation; it must be unique and cannot contain null values.
  • Foreign key: An attribute in a table that links data between two tables by referring to another table's primary key.
  • Candidate key: An attribute or set of attributes that uniquely identifies a tuple in a table; a table can have multiple candidate keys.
  • Super key: Any set of attributes that uniquely identifies tuples; every candidate key is a super key, but not every super key is a candidate key.
  • Alternate key: A candidate key that was not chosen as the primary key.
  • Composite key: A primary key consisting of two or more attributes.
  • Recursive foreign key: References the primary key in the same table and is useful for representing hierarchical data within one table.

6. ERD Entities and Attributes

Key Concepts & Definitions

  • Entity-Relationship Diagram: An Entity-Relationship Diagram (ERD) visually represents data, entities, and their relationships to model a database's logical structure and requirements.
  • Entity: An object or concept about which data can be stored, typically becoming a table in a relational schema.
  • Attribute: A property or characteristic that describes an entity and represents pertinent data about it.

★ Must-know

  • The five attribute types are:
    • Simple attributes cannot be divided into smaller parts
    • Composite attributes can be subdivided into other attributes
    • Derived attributes obtain their values from other attributes
    • Multivalued attributes can hold multiple values
    • Key attributes uniquely identify an entity

Further detail

  • Entities are typically shown as rectangles in ERDs, while their attributes are shown as ovals connected to them.

  • An Address composite attribute can be divided into Street, City, and Postal Code.

  • Age can be derived from the DateOfBirth attribute.

  • A PhoneNumber attribute can contain multiple numbers for one entity.

7. ERD Relationships and Cardinality

Key Concepts & Definitions

  • Relationship: An association between two or more entities that describes how they interact.
  • Relationship Degree: The number of entities participating in a relationship.
  • Cardinality: How many instances of one entity can or must be associated with each instance of another entity.

★ Must-know

📌 A unary relationship involves one entity relating to itself, a binary relationship involves two entities, and a ternary relationship involves three entities.

Further detail

  • Relationships are typically depicted as diamond shapes linking entity rectangles.

  • In a one-to-one (1:1) relationship, each entity instance is associated with one instance of the other entity.

  • In a one-to-many (1:M) relationship, one entity instance can be associated with multiple instances of another entity, while each instance on the many side can have one instance on the one side.

  • In a many-to-many (M:N) relationship, instances of either entity can be associated with multiple instances of the other.

8. Enhanced Entity-Relationship Modeling

Key Concepts & Definitions

  • EER Model: An extension of the ER Model with constructs such as specialization, generalization, and categories for representing more complex database structures.
  • Supertype: A generic entity type related to one or more more-specialized subtypes.
  • Subtype: A specialized entity type that inherits a supertype's attributes and may add attributes of its own.

★ Must-know

📌 Generalization is a bottom-up process that groups shared characteristics of specialized entity types into a supertype, whereas specialization is a top-down process that divides a supertype into subtypes with specialized characteristics.

📌 Under a disjoint constraint, a supertype instance can belong to only one subtype; under an overlap constraint, it can belong to more than one subtype.

Further detail

  • In a hospital database, Patient can be a supertype with Indoor Patient and Outdoor Patient subtypes; Ward and Clinic are respective subtype-specific attributes.

Memory Hook

Generalization builds upward from subtypes; specialization branches downward from a supertype.

9. Mapping EER Models to Schemas

★ Must-know

  • Mapping an EER model to a relational schema involves mapping entities to tables, translating relationships into foreign keys or associative tables, choosing a subtype strategy, transforming specialization constraints, normalizing tables, and implementing integrity constraints.

Further detail

📌 A single table with columns for all subtypes is suggested when the number of subtypes is small and overlap exists.

📌 Separate tables for each subtype are suggested when the specializations are radically different and involve many unique attributes.

Memory Hook

Entities become tables, relationships become keys or tables, then normalize and enforce constraints.

10. Normalization and Data Anomalies

Key Concepts & Definitions

  • Data Normalization: The organization of relational database fields and tables to reduce redundancy and dependency while maintaining data integrity.
  • Normal Form: A set of criteria that a database table must satisfy to achieve a level of normalization, with higher forms considered more normalized.
  • Data Anomaly: An inconsistency or problem that can occur when database changes are made, often because the database is inadequately normalized.
  • Boyce-Codd Normal Form: Boyce-Codd Normal Form (BCNF) extends 3NF; a table must be in 3NF and every determinant must be a candidate key to satisfy BCNF.

★ Must-know

📌 An update anomaly occurs when redundant instances of data are not all updated, a deletion anomaly occurs when deleting data unintentionally removes other data, and an insertion anomaly occurs when data cannot be added without other data being present.

  • Normalization progresses through 1NF, 2NF, 3NF, and BCNF by applying increasingly stringent criteria to table structure and dependencies.

Further detail

  • In the student-course example, normalization makes values atomic and rows unique in 1NF, separates student and course data with a foreign-key connection in 2NF, and separates instructor data into a linked table in 3NF.

Memory Hook

The anomaly trio: UDI — Update, Deletion, Insertion.

11. UML and Use Case Diagrams

Key Concepts & Definitions

  • Unified Modeling Language: A standardized graphical modeling language used to visualize, specify, construct, and document software-system artifacts.
  • Use Case Diagram: A UML behavioral diagram representing a system's functional requirements and interactions with external actors.

★ Must-know

  • A use case diagram contains actors, use cases, and a system boundary; associations connect actors to use cases, and use case relationships include extend, include, and generalization.

  • Use case relationships include:

    • Include: behavior entirely encapsulated by another use case
    • Extend: conditional or optional behavior extending another use case
    • Generalization: inheritance from a more general to a more specific use case

Further detail

📌 UML structural diagrams represent static aspects of a system, while behavioral diagrams represent its dynamic behavior.

  • Ivar Jacobson first introduced the concept of use case models in 1987 to capture user interactions with systems.

Memory Hook

Include is encapsulated functionality; extend adds conditional or optional behavior.

12. UML Classes and Relationships

Key Concepts & Definitions

  • Class: A representation of objects that share characteristics and behaviors, encapsulating data as attributes and actions as methods.
  • Class Diagram: A static structure diagram showing a system's classes and the relationships among them.

★ Must-know

📌 Aggregation is a whole-part association whose part can exist independently of the whole, whereas composition ties the part's lifecycle to the whole.

Further detail

  • Class names should be intuitive nouns that reflect the class's role or function.

  • A dependency means one class relies on another to function, and changes in the supplier class can affect the dependent class.

  • Generalization is an inheritance relationship in which a subclass inherits attributes and behaviors from a superclass.

  • Association is a structural relationship indicating that objects of one class connect to objects of another class.

  • Creating an effective class diagram involves defining clear class names, listing relevant attributes, outlining behaviors, depicting relationships with appropriate connectors, and reviewing the diagram against the system architecture.

Memory Hook

Aggregation parts can live independently; composition parts depend on the whole.

13. Data Flow Diagrams

Key Concepts & Definitions

  • Data Flow Diagram: A graphical representation of how data moves through a system from input to output via processes, data stores, and data flows.
  • Process: A DFD activity that transforms or handles input data to produce output data and must have at least one input and one output.
  • Context Level DFD: A Level 0 DFD depicting the system as one process with external entities and major data flows, without detailing internal processing.
  • External Entity: A source or destination of data that interacts with a system but exists outside its boundary.
  • Data Store: A place where data is retained within a system for processing or retrieval, such as a database or file.
  • Data Flow: A directional movement of data between processes, data stores, and external entities that should have a descriptive label.
  • Level 1 DFD: A breakdown of the context diagram's high-level process into subprocesses, with more detail about internal processes, data stores, and flows.

★ Must-know

  • A DFD uses processes, data flows, data stores, and external entities; processes are shown as circles or rounded rectangles, data flows as arrows, data stores as open-ended rectangles, and external entities as rectangles.

Further detail

📌 A DFD must not show data moving directly between data stores or between external entities in different levels, and it must not include control elements such as decision symbols.

📌 DFDs should use consistent labels for data stores, processes, and flows, and their flows should connect logically to processes with direction and necessary data stores represented.

14. SQL Fundamentals and Commands

Key Concepts & Definitions

  • SQL: The standard language for communicating with and managing databases, including creating structures and querying or modifying relational database data.
  • Primary key: A primary key uniquely identifies each record in a table, and its constraint enforces unique values in the specified column or columns.

★ Must-know

  • SQL can:
    • Query data
    • Insert data
    • Update records
    • Delete data
    • Create and manage database structures
    • Access data in multiple tables through join operations

📌 DQL queries data, DDL defines database structures, DML manipulates data, and DCL manages rights and permissions.

  • The CREATE TABLE statement defines a table's structure, including its columns, data types, and constraints.

📌 A UNIQUE constraint maintains unique column values and can be used multiple times in a table, whereas a primary key uniquely identifies records.

Further detail

  • SQL was developed in the 1970s and standardized by the American National Standards Institute (ANSI).

  • The CREATE DATABASE statement establishes a new database, as in CREATE DATABASE SchoolDB;.

📌 The DROP DATABASE statement deletes an existing database and its contents, and the action is irreversible, so data should be verified before execution.

  • The Students table example defines StudentID as an INT primary key, FirstName and LastName as VARCHAR(50), and DateOfBirth as DATE.

  • A NOT NULL constraint prevents a column from containing NULL, making a value mandatory when a row is inserted.

  • A DEFAULT constraint assigns a predetermined value during insertion when no value is supplied for the column.

  • A CHECK constraint restricts column values to those satisfying its specified condition, such as a GradeValue between 0 and 100.

  • A FOREIGN KEY constraint establishes a relationship by referencing a primary key in another table.

  • ALTER TABLE modifies an existing table's structure by adding or dropping columns and constraints.

  • AUTO_INCREMENT automatically generates sequential integer values for a column, typically a primary key, when rows are inserted.

15. SQL Data Retrieval and Manipulation

★ Must-know

  • Data Manipulation Language operations include INSERT to add records, UPDATE to modify existing data, DELETE to remove records, and SELECT to retrieve data.

  • A SELECT statement retrieves specified columns from a table using the form SELECT column1, column2 FROM table_name.

  • A WHERE clause filters rows so that a query returns only records satisfying its specified conditions.

Further detail

  • SQL operators include arithmetic operators +, -, *, and /, and logical operators AND, OR, and NOT.

  • SELECT INTO selects data and inserts it into a new table, as in SELECT * INTO backup_table FROM original_table.

  • SELECT DISTINCT removes duplicate rows from a result set so that only unique records are returned.

  • ORDER BY sorts query results by one or more columns in ascending order by default or descending order when specified.

📌 AND returns rows only when all combined conditions are true, whereas OR returns rows when at least one condition is true.

  • The IN clause tests whether a value belongs to a specified set and simplifies multiple OR conditions.

16. SQL Aggregation, Subqueries, and Joins

Key Concepts & Definitions

  • Subquery: A subquery is a query nested inside another query and can appear in SELECT, FROM, WHERE, or HAVING clauses.

★ Must-know

  • Common aggregate functions are:

    • COUNT
    • SUM
    • AVG
    • MIN
    • MAX
  • GROUP BY groups rows with the same values in specified columns into summary rows for use with aggregate functions.

📌 WHERE filters rows, whereas HAVING filters groups based on aggregate values.

  • Join queries combine rows from multiple tables based on a related column to retrieve data from those tables.

Further detail

📌 INNER JOIN returns rows with matching values in both tables; LEFT JOIN retains all left-table rows, RIGHT JOIN retains all right-table rows, and FULL OUTER JOIN retains rows from both tables, using NULLs for non-matches.

17. Data Changes and Database Security

★ Must-know

  • INSERT adds rows to a table, UPDATE modifies existing rows, and DELETE removes rows.

  • Database security protects data integrity, confidentiality, and availability through measures including permissions, access control, and encryption where necessary.

Further detail

📌 Assigning users roles according to their responsibilities and the principle of least privilege enhances database security by managing access.

18. Database Interfaces and Wireframes

Key Concepts & Definitions

  • Wireframe: A wireframe is a visual guide to an interface's skeletal framework, used early in design to show structure and layout without detailed styling.

★ Must-know

  • A user interface is the primary point of interaction with a database and translates user queries and inputs into actions the database can process.

  • Effective database input forms use clear labels, logical organization, intuitive navigation, validation, and real-time feedback to support accurate data entry and prevent errors.

Further detail

  • Database normalization, entity relationships, and indexing influence how data is retrieved and displayed through the user interface.

  • Wireframes help teams plan functionality, user flow, and content placement, identify usability issues, and communicate a shared understanding before detailed design.

19. Database Testing

Key Concepts & Definitions

  • Test case: A test case is a set of conditions or actions used to verify a particular aspect of database functionality under specified conditions.
  • Test plan: A test plan is a document outlining the testing strategy, objectives, resources, schedule, and scope of testing activities.

★ Must-know

  • Database testing evaluates functionality, performance, and reliability against user and system requirements and can reveal issues before deployment.

  • Testing types include:

    • unit testing
    • integration testing
    • system testing
    • user acceptance testing
    • performance testing
    • security testing
  • 🔄 Testing levels progress in this order:
    1. unit testing
    2. integration testing
    3. system testing
    4. acceptance testing

Further detail

  • Testing benefits include: quality assurance, lower correction costs through early issue detection, risk mitigation, user satisfaction, compliance with standards and regulations

📌 Testing types describe testing methodologies or focus, whereas testing levels describe the stages at which testing progresses from units to the complete integrated system.

  • Test case parameters include:
    • ID
    • title
    • description
    • preconditions
    • inputs
    • expected results
    • actual results
    • status

20. Software Documentation

Key Concepts & Definitions

  • Software documentation: Software documentation comprises documents that explain how to understand, use, and manage software systems for developers, users, and stakeholders.
  • Software Design Document: A Software Design Document (SDD) details software architecture and design and links requirements to the code that will implement them.

Essential Points

  • Software documentation can provide: improved communication, facilitated onboarding, enhanced user experience, increased maintainability and scalability, a record for future reference or regulatory compliance

  • Product documentation typically covers: technical specifications and requirements, hardware and software environments, configuration settings and guidelines, installation and deployment instructions, operational procedures and troubleshooting tips

  • Types of user documentation include:

    • user manuals
    • quick start guides
    • FAQs
    • tutorials and walkthroughs
  • Creating a good user manual involves defining its audience, outlining a logical and navigable structure, using clear concise language, adding visual aids, and testing usability with real users.

  • Technical documentation serves IT professionals and developers with information on system design, architecture, and code, including architecture diagrams, code annotations, APIs, database schemas, and security and compliance.

  • An SDD includes: a system architecture overview, module descriptions and interactions, design patterns and methodologies, interface designs such as user interfaces and APIs, non-functional requirements such as performance, scalability, and security

  • API documentation gives external developers information needed to integrate with and use software services, including endpoints, request-response formats, authorization, error handling, examples, and usage policies.

Memory Hook

User documentation guides software users; technical documentation serves IT professionals and developers.

Synthesis Tables

Relationship Degrees and Cardinalities

ConceptDefinitionExample
Unary degreeOne entity relates to itselfA manager manages employees
Binary degreeTwo entities participateStudent and Course
Ternary degreeThree entities participateDoctor, Patient, and Hospital
1:1 cardinalityOne instance links to one counterpartOne husband and one wife in the stated example
1:M cardinalityOne instance links to multiple instances on the other sideOne mother and many children
M:N cardinalityMultiple instances on both sides can be linkedStudents and courses

UML Class Relationships

RelationshipMeaningKey feature
DependencyOne class relies on anotherSupplier changes can affect dependent class
GeneralizationSubclass inherits from superclassInheritance of attributes and behaviors
AssociationObjects of classes are structurally connectedConnection between classes
AggregationWhole-part associationPart exists independently of whole
CompositionStrong whole-part relationshipPart's lifecycle is tied to whole

Test yourself

Take a 11-question practice quiz on ERD, Normalization and Modeling, with every answer explained.

1. A retailer records customer names and purchase amounts, then aggregates and analyzes them to identify buying trends. What do the resulting trends represent?

2. Two departments maintain separate files containing the same supplier details. One updates a supplier’s address while the other does not, and users encounter conflicts when editing shared records. Which file-system problem does this situation illustrate?

Take the quiz →

Study with flashcards

Memorize the key concepts of ERD, Normalization and Modeling with 10 flashcards.

How does information differ from raw data?

It is data interpreted and assembled meaningfully.

Which three levels make up database architecture?

External, conceptual, and internal.

What are data before they are organized?

Raw, unprocessed facts.

See the flashcards →

Related classes

Make your own study guides

Upload your class notes and Revizly writes a study guide, a quiz and flashcards in about a minute.

Study guide maker