Purpose and content of
ER Diagrams (Chapter 2 – ER Diagram) Key Concepts
Entity:
A person, place, thing, or event you store data about (e.G., Student, Course).
Attribute:
A detail about an entity (e.G., StudentName, StudentID).
Identifier (Key):
Uniquely identifies each entity (StudentID).
Relationship:
How entities connect (e.G., Student registers for Course).
| Type | Meaning | Example |
|---|---|---|
| Simple | Single value | Name |
| Composite | Can be split | FullName → FirstName, LastName |
| Multivalued | Has several values | Skills = {Java, SQL} |
| Derived | Computed from others | Age (from DOB) |
0
Entity Types
Strong Entity:
Exists independently (e.G., Employee).
Weak Entity:
Depends on another entity (e.G., Dependent of Employee).
Relationships
| Type | Meaning | Example |
|---|---|---|
| Unary | Entity relates to itself | Employee manages Employee |
| Binary | Two entities | Student enrolls in Course |
| Ternary | Three entities | Doctor treats Patient using Drug |
Cardinality
1:1 – One to One
1:N – One to Many
M:N – Many to Many
Defines how many instances connect and whether they must connect (mandatory or optional)
Enhanced ER Diagrams (EnhancedERD)
Supertypes & Subtypes
Supertype:
Big, general group (e.G., Employee).
Subtype:
Smaller, more specific group (e.G., Manager, Technician).
Inheritance:
Subtypes get all attributes of the supertype.
Generalization (Bottom-Up)
Combine smaller entities into one big group.
→ Car, Truck, Bike → Vehicle.
Specialization (Top-Down)
Split one big entity into smaller, specific ones.
→ Vehicle → Car, Truck, Motorcycle.
Constraints
| Type | Meaning | Symbol |
|---|---|---|
| Completeness | Do all supertype entities belong to a subtype? | Total (double line) / Partial (single line) |
Disjointness | Can one supertype be in multiple subtypes? | Disjoint (only one) / Overlap (can be several) |
Subtype Discriminator | Attribute used to tell which subtype applies | e.G., EmployeeType = “Hourly” or “Salaried” |
Relational Model (Relational_Model(2))
Core Idea
A relational database stores data in tables (relations)
made of rows (tuples)
and columns (attributes).
Keys
| Key Type | Purpose |
|---|---|
| Primary Key (PK) | Uniquely identifies each row; can’t be NULL |
| Foreign Key (FK) | Connects tables; references a PK in another table |
Candidate Key | Any possible key that could be the PK |
| Alternate Key | A candidate key not chosen as the PK |
| Composite Key | Combination of multiple columns to form a PK |
Integrity Rules
| Rule | Description |
|---|---|
| Domain Integrity | Values must be of the right type |
| Entity Integrity | PK can’t be NULL |
| Referential Integrity | FK must match a valid PK or be NULL |
Nulls
Means unknown or not applicable.
Affects math and comparisons — any math with NULL = NULL.
ERD → Relational Model
Goal
Turn an ERD (diagram) into tables for a relational database.
7 Steps
| Step | Process | Example |
|---|---|---|
| 1. Regular Entities | Each entity → Table; attributes → Columns | STUDENT(Name, ID, Major) |
2. Weak Entities | New table with FK from parent + partial key | DEPENDENT(DepName, EmpID FK) |
3. Binary Relationships | Add FK to “many” side (1:N) or create table (M:N) | ORDER has FK for CUSTOMER |
| 4. Associative Entities | New table with FKs; may have its own ID | ENROLLMENT(StudentID, CourseID, Grade) |
5. Unary Relationships | FK within same table | EMPLOYEE(ManagerID FK) |
6. Ternary Relationships | New table with all PKs as FKs | PATIENT_TREATMENT(DoctorID, PatientID, DrugID) |
7. Super/Subtype | Table for supertype + table for each subtype | EMPLOYEE, MANAGER, TECHNICIAN |
Normalization
Purpose
Clean up data tables to remove redundancy and update problems (anomalies).
Common Anomalies
| Type | Problem |
|---|---|
| Insertion | Can’t add data without other data |
| Deletion | Lose data you wanted to keep |
| Update | Must change the same data in multiple places |
Goals
Reduce redundancy
Enforce data integrity
Make updates simple and safe
Normal Forms
| Form | Rule | Fixes |
|---|---|---|
| 1NF | No repeating groups, one value per cell | Split multivalued attributes |
| 2NF | No partial dependency (depends on full key) | Split into smaller tables |
| 3NF | No transitive dependency (non-key → non-key) | Separate unrelated attributes |
| BCNF | Every determinant is a candidate key | Stronger 3NF |
| 4NF/5NF | Remove multivalued dependencies | Rarely used in practice |