Database Systems: Levels of Abstraction

Database System Abstraction Levels

• Database systems present three levels of abstraction: Physical, Logical, and External.
• Physical level: Involves how data is stored.
• Logical level: Pertains to what data is stored and the relationships among them.
• External level: Offers customized views of the database for different users, like students vs. administrators.
• Data abstraction minimizes exposure to storage details, enhancing user understanding and interaction.

Data Abstraction Benefits

• Data abstraction simplifies complexity, focusing on essential data organization.
• Different users access the database at their preferred detail levels, promoting tailored interactions.

Schema and Instances

• A schema defines the logical structure of a database, such as relationships (e.g., Student taking a course).
• It includes logical schema (design) and physical schema (storage specification).
• Instances represent the actual data in the database at any given time.

Conceptual Design Elements

• Identify entities (e.g., Student, Course) and their relationships (e.g., Student takes Course).
• Maintain integrity constraints to ensure data validity and quality.

Entity-Relationship (ER) Model

• An Entity is a distinguishable real-world object, while an entity set groups similar entities.
• Each entity has attributes, which are characteristics defining it, and similar entities share the same attributes.

Attribute Types

• Simple Attributes: Have a single atomic value (e.g., SSN).
• Composite Attributes: Can be subdivided into smaller components (e.g., Address).
• Multi-valued Attributes: Allow an entity to have multiple values for an attribute (e.g., colors of a car).
• Derived Attributes: Values can be calculated from other attributes (e.g., Age from Birth Date).

Entity Keys

• Keys uniquely identify entities in a set (e.g., Employee ID).
• A key can be composite, made up of multiple attributes.
• Each entity type can have more than one key attribute.

ER Diagram Notation

• Entity types are depicted in rectangular boxes; attributes in ovals, with connectors indicating relationships.
• Composite and multi-valued attributes have special notation, such as double ovals for multi-valued attributes.

Relationship Types

• Relationships connect entities, categorized by degree:
  • Unary: One entity type involved.
  • Binary: Two different entity types.
  • Ternary: Three different entity types.
• Cardinality constraints determine how many instances of one entity relate to another (e.g., one-to-many).

Cardinality Constraints

• One-to-One (1:1): Each entity in A relates to at most one in B and vice versa.
• Many-to-One (N:1): An entity in A relates to one in B, while B can relate to many in A.
• Many-to-Many (M:N): Entities in A relate to multiple in B and vice versa.

Participation Constraints

• Total Participation: Every entity must engage in a relationship (represented as double lines).
• Partial Participation: Entities may or may not participate (single line).

Recursive Relationships

• Occurs when an entity relates to itself in different roles (e.g., supervisor/subordinate within EMPLOYEEs).
• Distinction is made in ER diagrams to clarify roles.

Weak Entity Types

• Entities without a key attribute reliant on another entity are classified as weak entities.
• They are recognized by a combination of their partial key and their associated owner entity.

Relationship Attributes

• Relationship types can have attributes to describe the relationship (e.g., hours worked on a project).
• Primarily relevant in many-to-many relationships, they can sometimes be moved to an entity type for easier management.

Example: COMPANY Database

• Entity types identified include DEPARTMENT, PROJECT, EMPLOYEE, and DEPENDENT.
• Illustrates relationships like WORKS_FOR (Employee-Department) and WORKS_ON (Employee-Project).

Notation for ER Diagrams

• Names convey meanings: nouns for entities and verbs for relationships.
• Consistency in casing (uppercase for types, capitalized attributes) enhances readability.

Case Study: SBS Bank

• Bank structured by branches, customers, accounts, and loans, each with distinct attributes and relationships.
• Relationships such as customer-account and customer-loan display key characteristics of ER design.### Entity Relationship Model
• Constraints are usually described using minimum (min), maximum (max) values, or entity counts (1, M, N).
• Specifying constraints becomes challenging and ambiguous in higher-degree relationships, requiring careful attention.

University Database Design

• A new database is created to track enrollments, grades, colleges, departments, courses, sections, and instructors.
• Important entities include COLLEGEs, DEPARTMENTS, COURSES, SECTIONs, and INSTRUCTORs.
• Relationships among these entities must be clearly defined to ensure structure and integrity.

Key Concepts in ER Modeling

• Entity-Relationship (ER) Model principles include defining entities, attributes, and relationships.
• Constraints in the ER model cover keys, participation conditions, and cardinality.
• Conceptual design is inherently subjective; multiple design approaches exist.

Design Considerations

• Common design choices involve distinctions between:
  • Entity vs. attribute
  • Entity vs. relationship
  • Binary vs. n-ary relationships
• Continuous refinement of design is crucial for informed decision-making in later stages.

Extended Entity-Relationship (EER) Model

• The traditional ER model has limitations regarding specialization and generalization abstractions.
• The next chapter will introduce extensions including:
  • Type-subtype and set-subset relationships
  • Specialization and generalization hierarchies
  • Notation for representing these relationships in EER diagrams.