Database Design Fundamentals
Schema Architecture
External Level
Each external view displays only the data relevant to a specific end-user, hiding unnecessary information. Examples: (External View 1), (External View 2).
Conceptual Level
This level describes the overall structure of the database, typically using a relational model where data is stored in tables (Conceptual Schema).
Internal Level
The internal level details the physical data storage, including indexes used for data retrieval (Internal Schema).
Data Independence
Logical Data Independence
The ability to modify the conceptual schema without impacting external schemas or views.
Physical Data Independence
The ability to alter the physical schema without affecting the conceptual schema. For example, replacing a hard drive should not affect database users.
ER-Model Terms
Attributes
Composite Attribute: Composed of multiple attributes (e.g., First Name + Last Name).
Atomic/Simple Attribute: Cannot be further subdivided (e.g., Age).
Multivalued Attribute: Can have multiple values (e.g., College Degrees).
Derived Attribute: Can be derived from another attribute (e.g., Age derived from Birthdate).
Stored Attribute: Directly stored and used more often than derived attributes as they don’t require conversion (e.g., Birthdate).
ER Diagrams – Notation
ER Diagrams – Participation
Total (Mandatory): Every entity in the entity set MUST be related to the other entity set via the relationship (e.g., every Employee must WorkFor a Department). Represented by a double-line.
Partial (Optional): Some, but not all, entities in the entity set are related to the other entity set (e.g., some Employees Manage a Department). Represented by a single-line.
Other Notation: (min, max) where 0 represents partial participation and 1 represents total participation. Each entity must participate in at least min and at most max relationships. Thus, a min of 0 implies partial participation. Partial = (0, M) and Total = (1, M).
ER Diagrams – Weak Entities
- Have no key attribute of their own.
- Cannot exist without their identifying owner.
- Always have total participation with the owner.
- Denoted by a double line around the entity and the relationship.
- Partial key of a weak entity is denoted by a dashed underline.
Relationships – Keys
Key: An attribute or combination of attributes that enforces the uniqueness of tuples (e.g., Social Security Number). Must be unique and non-null.
Super Key: Any set of attributes that ensures no two tuples are identical (e.g., SSN + First Name).
Candidate Key: A “minimal superkey” (e.g., SSN or First Name from the Super Key example).
Primary Key: One candidate key chosen to identify tuples in the relation (underlined). Can be a composite key.
Relational Database Terminology
Domain (D): The set of atomic values (e.g., the Phone Numbers domain is 10-digit phone numbers valid in Canada).
Relation Schema (R): Denoted by R(A1, A2, …, An), consisting of a relation name and attributes. Each attribute Aj represents a role played by some domain D in the relation schema R (e.g., a Student table or relational schema). Example: Student(StudentNum, UWO_ID, FirstName, etc.).
Attribute: A named column in a relation schema.
Tuple: A row in a relation (an instance of a table).
Degree of a Relation: The number of attributes it contains.
Cardinality of a Relation: The number of tuples it contains.
Mapping Relationships and Entities
Mapping 1-to-1 and 1-to-Many Relationships
Add the key from the entity on the “one” side of the relationship as a foreign key to the entity on the “many” side. For 1-to-1 relationships where one side is total participation, add the foreign key to that side. Example: Add the Department Chair’s Employee ID as a foreign key in the Department table.
Mapping Many-to-Many Relationships
Create a new table. The key for this table is a composite key formed by combining the primary keys of the participating entities. Include any relationship attributes in the new table.
Mapping Weak Entities
Include the primary key of the owning entity as part of the primary key for the weak entity. Example: The key for ClassSection is SectionNum + CourseNum (foreign key).
Mapping Multivalued Attributes
Create a new table. The key for this table is a composite key combining the primary key of the original entity and the multivalued attribute. Remove the multivalued attribute from the original entity.
Referential Integrity
A foreign key in one table must reference an existing primary key in another table. Enforcement options include:
- Cascade Delete: Deleting a row also deletes related rows with matching foreign keys.
- Restrict Delete: Prevents deletion of a row if it has related rows with matching foreign keys.
- Set Null: Deleting a row sets the corresponding foreign keys in related rows to NULL.
Semantic Integrity Constraints
Enforced through triggers and assertions.
State Constraints: Define valid states of the database (e.g., Hours worked cannot exceed 50).
Transition Constraints: Define how the database state can change (e.g., salaries can only increase).