Notes on Relational Database Management Systems

Relational Database Management Systems (RDBMS) Notes

Introduction to the Relational Model

  • The relational model presents a way to structure and manage databases through the use of tables (relations), rows (tuples), and columns (attributes).

Components of Database Design

  • Requirement Analysis: Understanding what users need from the database.
  • Conceptual Design: Developing a high-level model of the requirements.
  • Logical Design: Translating the conceptual model into a logical structure, typically represented using an ER diagram.
  • Physical Design: Actual implementation of the database on a storage system.
  • Database Creation: Final step of setting up the database in the chosen system.

Entity-Relationship to Relational Data Model Mapping

  • Mapping involves translating ER and Enhanced ER models into a relational schema.
  • Steps include:
    1. Mapping Regular Entity Types
    2. Mapping Weak Entity Types
    3. Mapping Relationships (1:1, 1:N, M:N)
    4. Mapping Multivalued Attributes
    5. Mapping N-ary Relationships
    6. Handling Specialization/Generalization

Key Database Concepts

  • Primary Key: A unique identifier for each record in a table. It cannot have NULL values, ensuring entity integrity.
  • Candidate Key: A set of attributes that can uniquely identify records; one is selected as the primary key.
  • Super Key: A set that includes one or more attributes capable of uniqueness; may contain unnecessary attributes.
  • Foreign Key: An attribute in one table referring to the primary key in another, establishing relationships between tables and maintaining referential integrity.
  • Composite Key: A key made up of two or more attributes when a single attribute is insufficient for uniqueness.

Steps for Mapping

  1. Mapping of Regular Entity Types: Create a relation that includes all simple attributes of the entity type.
  2. Mapping of Weak Entity Types: Ensure the resulting relation includes the primary key of the owner as a foreign key.
  3. Mapping of Binary 1:1 Relationships: Add the primary key of one entity as a foreign key in the other and move attributes accordingly.
  4. Mapping of Binary 1:N Relationships: Identify the N-side entity and include its primary key as a foreign key.
  5. Mapping of Binary M:N Relationships: Create a new relation that includes primary keys of both entity types.
  6. Mapping of Multivalued Attributes: Create a new relation and use a combination of identifiers for the primary key.
  7. Mapping of N-ary Relationship Types: Create a new relation that represents the relationship type.
  8. Specialization/Generalization Mapping Options: Decide on the structure to represent subclasses and their superclass.

Mapping Special Cases

  • Specialization: Options include multiple relations for superclasses/subclasses, single relations with type attributes, or multiple type attributes for overlapping subclasses.
  • Categories (Union Types): When defining superclasses that have different keys, a new key attribute must be specified.

Summary of the ER to Relational Mapping

  • Mapping from the ER model includes translating entity types and relationships into a tabular format, using foreign keys where necessary to maintain relationships.
  • Consideration of both regular and weak entities is essential in maintaining the structural integrity of the database in the relational model.

Example Schema Mappings

  • EMPLOYEE: Attributes include Ssn, Fname, Minit, Lname, Address, etc.
  • DEPARTMENT: Includes Dname, Dnumber, and Manager's information.
  • PROJECT: Attributes such as Pname, Pnumber, and Plocation.
  • DEPENDENT: Attributes include Essn, Dependent_name, and relationships.

Additional Considerations in Design

  • Building a resilient design includes considering referential integrity and ensuring efficient querying capabilities through proper indexing and normalization techniques.
  • Always test mappings with sample data to validate correctness and integrity after the mapping process is complete.