Way-Up

Relational databases : modeling

• Entity-Relationship model, UML is one representation
• Usually, the "Crow Foot" notation is used
• What is "Crow Foot"? It is very similar to UML

Key concepts in ER modeling:

• Entities: Objects or concepts (e.g., Student, Course, Professor)
• Attributes: Properties of entities (e.g., student_name, course_number)
• Relationships: Associations between entities (e.g., Student enrolls in Course)
• Cardinality: Number of instances in a relationship (one-to-one, one-to-many, many-to-many)

Crow Foot Notation

            erDiagram
    STUDENT {
        int student_id PK
        string student_name
        string student_address
    }

    SEAT {
        int seat_no PK
        string seat_position
    }

    INSTRUCTOR {
        int instructor_no PK
        string instructor_name
        string instructor_faculty
    }

    COURSE {
        string course_name PK
        int course_number PK
    }

    SECTION {
        int section_number PK
    }

    PROFESSOR {
        int professor_id PK
        string professor_name
        string professor_faculty
    }

    %% Relationships
    STUDENT ||--o{ SEAT : "assigned"
    STUDENT }o--o{ SECTION : "enrolls"
    SECTION ||--|| COURSE : "belongs_to"
    INSTRUCTOR ||--o{ COURSE : "teaches"
    PROFESSOR ||--o{ SECTION : "teaches"
        

Crow Foot Notation (2)

Connectors explanation

Exercice : a patient appointment system

Toward implementation : a patient appointment system

You will probably have to add attributes to entities, like in the following examples

CREATE TABLE DOCTORS (
    doc_id INT AUTO_INCREMENT PRIMARY KEY,
    doc_firstname VARCHAR(255) NOT NULL,
    doc_lastname VARCHAR(500) NOT NULL,
    specialization VARCHAR(100),
    phone VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    

Modify table structure : ADD or DELETE columns

ALTER TABLE DOCTORS ADD COLUMN bdate DATE;
-- Note: No quotes needed for column names in MySQL
    

ALTER TABLE DOCTORS DROP COLUMN bdate;
    

Modify table structure : duplicate columns

When you need to create a copy of a column (useful for data migration or transformations):

ALTER TABLE DOCTORS ADD COLUMN "newCol" DATE;
UPDATE DOCTORS SET newCol = bdate;
    

Common use cases:

• Data migration: Preserve original data while testing transformations
• Type conversion: Create new column with different data type, migrate data, then drop old column
• Temporary backups: Keep original values before applying complex updates

Best practice: Always verify data after duplication before dropping original column

Creating constraints : primary and foreign keys

In order to keep database integrity, some "keys" are necessary to implement:

• It is necessary to add the primary key concept to ensure uniqueness of the targeted tuple. Primary keys will avoid duplicates to be created and ensures uniqueness while doing modifications queries
• To reference external relations, it is necessary to add the foreign key concepts. Foreign keys will be checked while removing some linked tuples to avoid integrity issues (foreign keys pointing to nothing)

Creating constraints : MySQL syntax

ALTER TABLE DOCTORS ADD CONSTRAINT doc_pk PRIMARY KEY (doc_id);
-- Note: MySQL doesn't use 'ONLY' keyword
    

ALTER TABLE APPOINTMENTS 
ADD CONSTRAINT fk_doc 
FOREIGN KEY (f_doc_id) REFERENCES DOCTORS(doc_id) 
ON DELETE SET NULL
ON UPDATE CASCADE;
    

Notice the ON DELETE SET NULL meaning that if a doctor is deleted, the appointment remains with NULL in f_doc_id

excercise : modify your scripts to integrate the constraints from the following schema

            erDiagram
            direction LR
            DOCTOR {
                string doc_physicianidnumber PK
                string doc_firstname
                string doc_lastname
            }
            APPOINTMENT {
                date app_date PK
                string app_time
                int app_duration
                string app_reason
            }
            BILL {
                int bill_number PK
                float bill_amountinsured
                float bill_amountnotinsured
                date bill_datesent
                string bill_status
            }
            PAYMENT {
                int pay_receiptnumber PK
                float pay_amount
                date pay_date
                string pay_method
            }
            PATIENT {
                int pat_idnumber PK
                string pat_firstname
                string pat_lastname
                string pat_address
                string pat_city
                string pat_state
            }
            INSURANCE_COMPANY {
                string ins_name PK
                string ins_benefitscontact
                string ins_phonenumber
                string ins_claimsaddress
            }
            DOCTOR ||--o{ APPOINTMENT : "schedules"
            PATIENT ||--o{ APPOINTMENT : "books"
            APPOINTMENT ||--|| BILL : "generates"
            BILL ||--o{ PAYMENT : "paid_by"
            PATIENT ||--o{ BILL : "receives"
            PATIENT }o--|| INSURANCE_COMPANY : "insured_by"
            INSURANCE_COMPANY ||--o{ BILL : "covers"
        

Exercise: Advanced Modeling

Create a complete e-commerce database with:

• Customers: customer_id, email (UNIQUE), password_hash, created_at
• Products: product_id, name, description, price, stock_quantity
• Orders: order_id, customer_id (FK), order_date, total_amount, status
• Order_Items: order_item_id, order_id (FK), product_id (FK), quantity, price_at_purchase
• Categories: category_id, name, parent_category_id (self-referencing FK)
• Product_Categories: product_id (FK), category_id (FK) - many-to-many

Include all appropriate constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, DEFAULT, CHECK)

Exercise: Referential Integrity

For your e-commerce database, define appropriate referential integrity rules:

1. What happens when a Customer is deleted? (CASCADE or SET NULL for Orders?)
2. What happens when a Product is deleted while it's in active Orders?
3. What happens when a Category parent is deleted?
4. Should you allow Order deletion if it has Order_Items?

-- Example with different cascading rules:
ALTER TABLE Orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) 
ON DELETE CASCADE 
ON UPDATE CASCADE;

ALTER TABLE Order_Items 
ADD CONSTRAINT fk_product 
FOREIGN KEY (product_id) REFERENCES Products(product_id) 
ON DELETE RESTRICT 
ON UPDATE CASCADE;
    

Database Design Process

1. Requirements Analysis: What data needs to be stored?
2. Conceptual Design: Entity-Relationship diagram (ERD)
3. Logical Design: Convert ERD to table schemas
4. Normalization: Apply normal forms
5. Physical Design: Choose data types and storage
6. Implementation: Create actual database
7. Testing & Optimization: Test queries and optimize performance

Good design upfront saves hours of refactoring later!

Exercise: Identify Database Concepts

For an online bookstore, identify:

1. What are the main entities? (tables)
2. What attributes does each entity have? (columns)
3. What should be the primary keys?
4. What relationships exist between entities?
5. Which relationships are one-to-many? many-to-many?

Entities to consider: Books, Authors, Customers, Orders, Categories, Reviews

Think about real-world scenarios: Can a book have multiple authors? Can an order contain multiple books?

Exercise: Design a Social Media Database

Design the conceptual model for a simple social media platform:

• Users can create posts
• Users can follow other users
• Users can like and comment on posts
• Posts can have hashtags (many-to-many)

Tasks:

1. List all entities needed
2. Identify attributes for each entity
3. Define primary keys
4. Map all relationships (type + cardinality)
5. Identify which tables need junction tables

Exercise: Normalization Practice

Given this denormalized table, identify issues:

Questions:

1. What's wrong with storing ProductNames as a comma-separated list?
2. What happens if John changes his email?
3. How would you normalize this to 3NF?
4. Which tables would you create?

Exercise: Design a University Database

Design a database for a university system. Consider:

• Students: student_id, name, email, enrollment_date, major
• Courses: course_id, name, credits, department
• Professors: prof_id, name, department, office
• Enrollments: Which students are in which courses?
• Teaching: Which professors teach which courses?

Tasks:

1. Draw the ERD with all relationships
2. Identify cardinalities (one-to-many, many-to-many)
3. List necessary junction tables
4. Define primary and foreign keys for each table

Common Database Design Mistakes

• ❌ Storing calculated values: Store raw data, calculate on query
  • Don't store total_price if you have quantity × unit_price
• ❌ Storing files in database: Use file storage + references
  • Store image_url VARCHAR(500), not BLOB
• ❌ Using meaningful primary keys: Use surrogate keys (auto-increment)
  • Email can change, but user_id shouldn't
• ❌ Over-normalization: Balance normalization with query performance
• ❌ Wrong data types: VARCHAR(255) for everything is lazy design

MySQL Storage Engines

-- Specify engine when creating table:
CREATE TABLE my_table (...) ENGINE=InnoDB;