CSC105 Modern Database Design

Study Notes

Consists of requirements collection, analysis, conceptual design, and relational database schema design.
Emphasizes the importance of understanding the "Miniworld" to tailor database design to specific needs.

Established for over 35 years, offering abstraction ideal for data modeling.
Easily translatable into relational models, resulting in ER schemas depicted through ER diagrams.
Based on two core concepts:
- Entities: Tables storing specific information.
- Relationships: Associations between entities.

Illustrates ER model concepts focusing on employees, departments, and projects.
Each department has a unique ID, name, office location, and a managing employee.
Departments oversee multiple projects that each have a unique name, number, and budget.
Employee records include name, ID number, address, salary, and birthdate, tied to a department, with project participation noted by start date and supervisors tracked.
Employee dependents are recorded, including their name, birthdate, and relationship to the employee.

Entity: An object in the real world identifiable from others, existing either physically (e.g., department, employee) or conceptually (e.g., project).

Weak Entities: Existence-dependent on another entity, with primary keys derived from their parent. Example: Spouse table in the COMPANY database depends on Employee.
Strong Entities: Can exist independently, having primary keys that are not foreign keys. Examples include Employee and Department in the COMPANY database.

Independent Entities (Kernels):
- Serve as foundational elements in a database.
- Possess a primary key that is not a foreign key and do not require association with other entities for existence.
Dependent Entities (Derived Entities):
- Connect kernels together, existing dependent on one or more tables.
- Often create associative tables with multiple foreign keys to establish many-to-many relationships.
- Can contain additional attributes that enhance the linkage between associated tables.
- Primary key options include composites of foreign keys or a combination with additional qualifiers if unique.

Consists of requirements collection, analysis, conceptual design, and relational database schema design.
Emphasizes the importance of understanding the "Miniworld" to tailor database design to specific needs.

Established for over 35 years, offering abstraction ideal for data modeling.
Easily translatable into relational models, resulting in ER schemas depicted through ER diagrams.
Based on two core concepts:
- Entities: Tables storing specific information.
- Relationships: Associations between entities.

Illustrates ER model concepts focusing on employees, departments, and projects.
Each department has a unique ID, name, office location, and a managing employee.
Departments oversee multiple projects that each have a unique name, number, and budget.
Employee records include name, ID number, address, salary, and birthdate, tied to a department, with project participation noted by start date and supervisors tracked.
Employee dependents are recorded, including their name, birthdate, and relationship to the employee.

Entity: An object in the real world identifiable from others, existing either physically (e.g., department, employee) or conceptually (e.g., project).

Weak Entities: Existence-dependent on another entity, with primary keys derived from their parent. Example: Spouse table in the COMPANY database depends on Employee.
Strong Entities: Can exist independently, having primary keys that are not foreign keys. Examples include Employee and Department in the COMPANY database.

Independent Entities (Kernels):
- Serve as foundational elements in a database.
- Possess a primary key that is not a foreign key and do not require association with other entities for existence.
Dependent Entities (Derived Entities):
- Connect kernels together, existing dependent on one or more tables.
- Often create associative tables with multiple foreign keys to establish many-to-many relationships.
- Can contain additional attributes that enhance the linkage between associated tables.
- Primary key options include composites of foreign keys or a combination with additional qualifiers if unique.