Database Systems CS3402 Cheat Sheet

Database systems provide a structured, non-redundant, and persistent way to store and manage data, offering significant advantages over traditional file-processing systems by enabling efficient retrieval, data integrity, and controlled access.

Core Principles

Database Management System (DBMS): A software set for creating, storing, updating, and accessing data in a DB.
Data Abstraction (3-level architecture): Hides complexity by offering physical/internal, conceptual, and view/external levels of data representation.
Data Independence: Allows schema modifications at one level without affecting higher levels, enabling physical and logical data independence.
Entity-Relationship (ER) Model: A graphical notation for database design, proposed by P. Chen in 1976, using entities, attributes, and relationships.

Action Steps

Identify entity sets (e.g., DEPARTMENT, PROJECT, EMPLOYEE, DEPENDENT) based on requirements.
Define initial attributes for each entity set.
Introduce relationships between entity sets to capture specific meanings (e.g., EMPLOYEE works for DEPARTMENT).
Specify cardinality (1:1, 1:N, M:N) and participation (total/partial) for relationships.
Refine the ER diagram using standard notation: Rectangles for Entity Sets, Ovals for Attributes, Diamonds for Relationship Sets, Lines for connections.

Key Terms

Database (DB): A non-redundant, persistent collection of logically-related data that are structured to support various processing and retrieval needs.
Database Management System (DBMS): A set of software programs for creating, storing, updating, and accessing the data of a DB.
Data Abstraction: Provides an abstract view of data, simplifying interaction and hiding storage details.
Physical Data Independence: Ability to modify the physical schema without altering the conceptual schema or application programs.
Logical Data Independence: Ability to modify the conceptual schema without altering application programs.
Entity: A distinguishable object with an independent existence (e.g., John Chan).
Attribute: A piece of information describing an entity (e.g., Name, ID).
Relationship: An association among several entities (e.g., Patrick is taking cs3450).
Key: An attribute that uniquely identifies an entity in an entity set (e.g., studentID).
Weak Entity Set: An entity set that does not have enough attributes to form a key on its own.
Derived Attribute: An attribute whose value can be derived from other attributes (e.g., Area from Radius).

Pro Tips

Stick to notations used in lecture notes, as they may vary between different books.
Understand that composite and multi-valued attributes can be nested to any level.
Recognize that relationships can carry their own attributes (e.g., grade in 'takes course').

Pitfalls to Avoid

Ignoring data redundancy and inconsistency inherent in traditional file-processing systems.
Failing to account for concurrent access anomalies, security, and integrity problems in file systems.
Attempting to modify physical schemas without considering the impact on the conceptual schema and application programs (lack of physical data independence).

Real World Examples

Storing student records with IDs, names, courses, and grades.: Demonstrates the need for database systems to manage structured data efficiently and avoid redundancy compared to simple file storage.
Designing a COMPANY database schema.: Illustrates identifying entity sets (DEPARTMENT, PROJECT, EMPLOYEE, DEPENDENT) and their relationships (e.g., EMPLOYEE works for DEPARTMENT) to model real-world organizational structures.
Representing employee-project assignments.: Shows a Many-to-Many (M:N) relationship (WORKS_ON) where multiple employees can work on multiple projects, requiring specific modeling in the ER diagram.

Timeline

1976: The Entity-Relationship Model proposed by P. Chen.
1960s: File-based data models emerge.
1970s: Hierarchical and Network data models are prominent.
1980s: Relational and Entity-Relationship models gain dominance.
1990s: Object-oriented and Web-based models start to develop.
2000+: Continued evolution with focus on web-based and advanced models.

People

Dr. LIAO Jing: Lecturer for CS3402 Database Systems
P. Chen: Proposer of the Entity-Relationship Model