_2025-05-13 _{#data-visualization #data-management #sql #hmp669} ^{[[maps-of-content|🌐 Maps of Content — All Notes]]} ^{Series: [[hmp669|Data Management and Visualization]]} ## Database Design: From Business Rules to Implementation > [!abstract]- Quick Review > > Database design involves translating business rules into Entity-Relationship Diagrams (ERD) and then into Relational Schemas, moving from conceptual entities and relationships to detailed tables with attributes and key relationships. > > **Key Concepts**: > > - ERDs show entities and relationships graphically without attributes > - Many-to-many relationships must be decomposed into two one-to-many relationships using associative entities > - Relational schemas add attributes and define primary/foreign keys to maintain integrity > > **Must Remember**: > > - Business rules provide nouns (potential entities) and verbs (potential relationships) > - Every table needs a primary key (bold and underlined in schemas) > - Foreign keys implement relationships between tables > > **Critical Relationships**: > > - Business Rules → ERD → Relational Schema → Implementation (refinement) > - One-to-many: foreign key goes in the "many" table > - Many-to-many: requires an associative entity with foreign keys from both original entities > [!code]- Code Reference > > |Notation/Symbol|Purpose|Example| > |---|---|---| > |**ERD Notation**||| > |`Entity`|Represents a person, object, concept, or event|`VOLUNTEER`, `TASK`| > |`Relationship Line`|Shows connection between entities|Line connecting entities| > |`Cardinality (1:M)`|Shows one-to-many relationship| \|\|--o{ | > |`Cardinality (M:N)`|Shows many-to-many relationship|`}o--o{`| > |**Relational Schema Notation**||| > |`Table(attribute1, attribute2)`|Defines table with attributes|`Volunteer(Volunteer_ID, Name)`| > |`**Bold Underline**`|Indicates primary key|<u>**Volunteer_ID**</u>| > |`Foreign Key`|References primary key in another table|`Task_Code in Assignment table`| > |**Implementation Terms**||| > |`Entity Integrity`|Rule requiring unique, non-null primary key|Every table has a primary key| > |`Referential Integrity`|Rule requiring valid foreign key references|Foreign keys match existing primary keys| > |`Associative Entity`|Resolves many-to-many relationships|`Assignment`, `Contents`| ## The Database Design Process ### Design Stages Overview Database design follows a progression from conceptual to physical implementation: 1. **Business Rules Analysis**: Understanding operational requirements 2. **Entity-Relationship Diagram (ERD)**: High-level graphical representation 3. **Relational Schema**: Detailed tabular format with attributes and keys 4. **Physical Implementation**: Actual database creation ```mermaid flowchart TB A[Business Rules] -->|"Identify entities & relationships"| B[ERD] B -->|"Add attributes & keys"| C[Relational Schema] C -->|"Create tables & constraints"| D[Database Implementation] style A fill:#f9f,stroke:#333,stroke-width:2px style B fill:#bbf,stroke:#333,stroke-width:2px style C fill:#bfb,stroke:#333,stroke-width:2px style D fill:#fbb,stroke:#333,stroke-width:2px ``` > [!note] Progressive Detail > Each step in the process adds more detail and gets closer to the actual implementation. The ERD focuses on **what** exists and how things relate, while the relational schema begins to address **how** to store the data. ### Business Rules to ERD Business rules describe how an organization operates and what data it needs to track. To create an ERD: 1. **Identify nouns** as potential entities 2. **Identify verbs** as potential relationships 3. **Determine relationship types** (one-to-one, one-to-many, many-to-many) 4. Focus only on entities and relationships, **ignore attributes** at this stage > [!tip] Entity vs. Attribute > A common challenge is distinguishing between entities and attributes. Generally, if something has its own properties or participates in relationships independently, it's an entity. If it merely describes something else, it's an attribute. ## Understanding Entity-Relationship Diagrams (ERDs) ### ERD Components An ERD consists of: - **Entity boxes**: Represent objects, people, concepts, or events - **Relationship lines**: Show how entities connect to each other - **Cardinality notations**: Indicate how many instances of one entity relate to instances of another ### Relationship Types ERDs can show three types of relationships: - **One-to-One (1:1)**: One instance of Entity A relates to exactly one instance of Entity B - **One-to-Many (1:M)**: One instance of Entity A relates to multiple instances of Entity B - **Many-to-Many (M:N)**: Multiple instances of Entity A relate to multiple instances of Entity B > [!warning] Many-to-Many Implementation > Relational database systems cannot directly implement many-to-many relationships. These must be decomposed into two one-to-many relationships using an **associative entity** (also called a junction, bridging, or intersection entity). ```mermaid erDiagram ENTITY-A ||--o{ ASSOCIATIVE-ENTITY : has ENTITY-B ||--o{ ASSOCIATIVE-ENTITY : has ``` ## From ERD to Relational Schema ### What is a Relational Schema? A relational schema translates the ERD into a tabular format where: - Each **entity becomes a table** - Each table includes **attributes** (columns) - **Primary keys** uniquely identify each record - **Foreign keys** implement relationships between tables ### Rules for Translation When converting an ERD to a relational schema: 1. Create a table for each entity in the ERD 2. Add attributes to each table based on business rules 3. Define a primary key for each table (bold and underlined in notation) 4. Implement relationships: - For one-to-many: Add the primary key from the "one" side as a foreign key in the "many" side table - For many-to-many: Create an associative entity table with foreign keys from both original entities ### Ensuring Data Integrity Two key integrity rules must be followed: 1. **Entity Integrity**: Every table must have a primary key that is unique and not null 2. **Referential Integrity**: Foreign key values must match existing primary key values in the referenced table > [!note] Primary Key > Visual Notation In relational schema diagrams, "the primary key for any table gets in bold and is underlined. It's uniquely identifiable what is the primary key for that table." ## Case Application: United Helpers Non-Profit Database > [!example]- United Helpers Case Study > > **Business Scenario**: United Helpers is a non-profit organization that manages volunteers, tasks, supply packing lists, packages, and inventory items. The organization needs a database to track these elements and their relationships. > > **Business Rules Excerpts**: > > - "Individuals volunteer their time" > - "Volunteers carry out the task of the organization" > - "Many different packing lists to produce" > - "Each individual package of supplies is produced and tracked" > - "The items included in each package should be tracked" > > **Entity Identification**: > > - **Volunteer**: People who donate time > - **Task**: Activities performed by volunteers > - **List**: Packing lists for tasks > - **Package**: Containers of supplies > - **Item**: Individual products included in packages > > **Relationship Analysis**: > > - Volunteers perform many Tasks, and Tasks can have many Volunteers → Many-to-Many > - Tasks use one List, Lists can be used by many Tasks → One-to-Many > - Tasks produce many Packages, each Package is from one Task → One-to-Many > - Packages contain many Items, Items can be in many Packages → Many-to-Many > > **Associative Entities Needed**: > > - **Assignment**: Links Volunteers to Tasks > - **Contents**: Links Packages to Items > [!example]- ERD for United Helpers > > ![[erd-and-relational-schema-1747281158940.webp]] > > ```mermaid > erDiagram > VOLUNTEER ||--o{ ASSIGNMENT : performs > TASK ||--o{ ASSIGNMENT : has > LIST ||--o{ TASK : used-by > TASK ||--o{ PACKAGE : produces > PACKAGE ||--o{ CONTENTS : contains > ITEM ||--o{ CONTENTS : included-in > ``` > > ### Relational Schema for United Helpers > [!example]- Relational Schema > After analyzing the business rules, we create the relational schema with tables, attributes, and keys: > > ![[erd-and-relational-schema-1747283018978.webp]] > > > > | Table Name | Attributes | Primary Key(s) | Foreign Key(s) | > | :------------- | :----------------------------------------------------------- | :------------- | :--------------------------------------------------------------- | > | **Volunteer** | Volunteer_ID, Name, Address, Telephone | Volunteer_ID | | > | **Task** | Task_Code, Description, Type, Status, List_ID | Task_Code | List_ID (references List) | > | **Assignment** | Assignment_ID, Volunteer_ID, Task_Code, Start_Time, End_Time | Assignment_ID | Volunteer_ID (references Volunteer), Task_Code (references Task) | > | **List** | List_ID, List_Name, Description | List_ID | | > | **Package** | Package_ID, Date, Total_Weight, Task_Code | Package_ID | Task_Code (references Task) | > | **Item** | Item_ID, Description, Value, Quantity_On_Hand | Item_ID | | > | **Contents** | Content_ID, Package_ID, Item_ID, Quantity | Content_ID | Package_ID (references Package), Item_ID (references Item) | > > > [!tip] Alternative Primary Keys > > For associative entities like Assignment and Contents, you could use composite primary keys combining the foreign keys (e.g., Volunteer_ID + Task_Code for Assignment) instead of creating a new ID field. Both approaches are valid. > ## Key Considerations in Database Design ### Identifying Entities vs. Attributes A common challenge is determining whether something should be an entity or an attribute: - **Entity**: Independent object that has its own properties and relationships - **Attribute**: Property that describes an entity Ask yourself: "Does this have its own attributes or participate in relationships?" If yes, it's likely an entity. ### Handling Many-to-Many Relationships Many-to-many relationships cannot be directly implemented in relational databases. They must be decomposed: 1. Create a new associative entity 2. Create two one-to-many relationships from the original entities to this new entity 3. Include foreign keys from both original entities in the associative entity 4. Add any attributes specific to the relationship itself (e.g., start/end times for assignments) ```mermaid flowchart TD A[Many-to-Many Relationship] --> B[Create Associative Entity] B --> C[Implement Two One-to-Many Relationships] C --> D[Include Foreign Keys from Both Entities] D --> E[Add Relationship-Specific Attributes] ``` ### Primary and Foreign Key Implementation Keys are the foundation of relational databases: - **Primary Keys**: Uniquely identify each record in a table (bold and underlined in schemas) - **Foreign Keys**: Create relationships between tables by referencing primary keys in other tables - **Composite Keys**: Multiple attributes that together uniquely identify a record ## Summary: The Database Design Process The database design process flows from business rules to ERD to relational schema: 1. **Analyze business rules** to identify entities, relationships, and attributes 2. **Create an ERD** focusing only on entities and relationships 3. **Transform the ERD** into a relational schema by adding attributes 4. **Define primary and foreign keys** to implement relationships and ensure data integrity 5. **Decompose many-to-many relationships** using associative entities 6. **Validate** that the schema correctly represents all business rules ### The Single Most Important Takeaway Database design is accurately translating business rules into a structured model that properly identifies entities and their relationships, while ensuring data integrity through appropriate use of primary and foreign keys. The progression from ERD to relational schema provides a methodical path from concept to implementation. -- Reference: