Modern Database Management - Chapter 2 Flashcards
E-R Model Constructs
Entities: Represent a person, place, object, event, or concept in the real world that is tracked in a database.
Purpose: They serve as the fundamental building blocks around which data is organized. For example, in a university database, entities might include students, courses, and professors. Each entity is uniquely identifiable and corresponds to a row in a table.
Entity Instance: A single, specific occurrence of an entity type.
Details: For example, "John Doe" as a student is an instance of the entity type "Student". It represents a particular record in the database.
Entity Type: A collection of entities that share common properties or characteristics.
Details: This is a broader classification, like "all students" or "all courses." An entity type often corresponds to a table in a relational database, where each row represents an entity instance and each column represents an attribute.
Relationships: Illustrate the associations and interactions between entities. These associations are crucial for understanding how different entities relate to each other within the system.
Example: A student enrolling in a course represents a relationship between the "Student" and "Course" entities.
Relationship Instance: A specific link between individual entity instances.
Details: For example, "John Doe" (a student) enrolling in "Database Management" (a course) is a relationship instance. This corresponds to primary key-foreign key equivalencies in related tables, ensuring referential integrity.
Relationship Type: A category that defines the nature of the relationship between entity types.
Details: Common relationship types include one-to-one, one-to-many, and many-to-many. For instance, a "Professor" can teach multiple "Courses," representing a one-to-many relationship type.
Attributes: Represent properties or characteristics of an entity or a relationship type. These are the specific details that describe and qualify the entities and relationships.
Example: For the entity "Student", attributes might include "StudentID", "Name", "Major", and "GPA". Each attribute often corresponds to a field or column in a database table.
E-R Diagram Symbols
Entity Type: Graphically represented by a rectangle in an E-R diagram. The name of the entity type is usually placed inside the rectangle.
Weak Entity: Represented by a double-lined rectangle. This signifies its dependency on a strong entity for its existence.
Relationship: Represented by a diamond shape. The type of relationship (e.g., "enrolls", "manages") is written inside the diamond.
Identifying Relationship: Links strong entities to weak entities. This is crucial for defining the dependency of the weak entity on the strong entity.
Attributes Symbols
Identifier: Attributes that uniquely identify an entity instance are underlined. This indicates the primary key of the entity.
Partial Identifier: In weak entities, the attribute that partially identifies the entity. It is double-lined underlined, indicating that it becomes a unique identifier only in combination with the identifier of the related strong entity.
Optional Attribute: Represented in parentheses. This denotes that the attribute may not have a value for every entity instance.
Derived Attribute: Represented in square brackets. This indicates that the attribute's value can be derived or calculated from other attributes.
Multivalued Attribute: Represented in curly brackets. This shows that an entity instance can have multiple values for this attribute (e.g., an employee can have multiple skills).
Composite Attribute: Represented in parentheses, showing the constituent parts of the attribute (e.g., Address(Street, City, State, Zip)).
Business Rules
Statements that define or constrain aspects of the business. They dictate how data is managed and maintained within the database system.
Derived from policies, procedures, events, and functions within the organization. These rules are shaped by the specific needs and operational context of the business.
Assert business structure and control/influence behavior. They ensure that the database accurately reflects and enforces business practices.
Expressed in terms familiar to end-users, making them understandable and applicable to daily operations.
Automated through DBMS software to ensure consistent enforcement and data integrity.
Characteristics of a Good Business Rule
Declarative: Defines what should be enforced, not how it should be implemented. Focuses on the rule's purpose rather than its technical execution.
Precise: Has a clear, unambiguous meaning that is agreed upon by all stakeholders. Avoids vagueness to ensure consistent interpretation.
Atomic: Represents a single, indivisible statement. Each rule should address one specific aspect of the business.
Consistent: Must be internally consistent (no contradictions within the rule itself) and externally consistent (harmonious with other rules).
Expressible: Can be stated in a structured, natural language that is easily understood and implemented.
Distinct: Non-redundant; each rule should add unique value and not overlap with other rules.
Business-oriented: Understood and validated by business people, ensuring alignment with business goals.
Data Names
Should be related to business characteristics rather than technical details. Emphasizes the business relevance of the data.
Meaningful and self-documenting, making it easy to understand the purpose and content of the data.
Unique within the database to avoid confusion and ensure clarity.
Readable and easily understandable, following a consistent naming convention.
Composed of words from an approved list to maintain standardization.
Repeatable across the database, ensuring consistency in naming conventions.
Written in standard syntax to facilitate ease of understanding and use.
Data Definitions
Clear and concise explanations of terms or facts used in the database.
Term: A word or phrase with a specific meaning within the context of the database.
Fact: An association or relationship between two or more terms, providing context and meaning.
Guidelines:
Concise description of essential data meaning to ensure clarity and understanding.
Gathered in conjunction with systems requirements to align with business needs.
Accompanied by diagrams to provide visual context and aid understanding.
Achieved through consensus and iterative refinement, ensuring accuracy and relevance.
Entities
A person, place, object, event, or concept about which the organization wishes to maintain data. Serves as the subject of the data being tracked.
Entity Type: A collection of entities with common properties, defining a category or class of entities.
Entity Instance: A single occurrence of an entity type, representing a specific record in the database.
Appropriate Entities
Should possess multiple instances in the database to justify its existence as an entity.
Composed of multiple attributes that provide detailed information about the entity.
Something the model is trying to represent, aligning with the goals and objectives of the database.
Inappropriate Entities
Users of the database system, as they are actors rather than subjects of the data.
Outputs of the database system (e.g., reports), which are results rather than entities to be tracked.
Strong vs. Weak Entities
Strong Entity: Exists independently of other entities, having its own unique identifier.
Has its own unique identifier, which is underlined with a single line in E-R diagrams.
Weak Entity: Dependent on a strong entity (identifying owner) and cannot exist on its own. Its existence is contingent on the existence of the related strong entity.
Does not have a unique identifier of its own but relies on a partial identifier. Its entity box and partial identifier have double lines.
Identifying Relationship: The relationship that links strong entities to weak entities, defining the dependency.
Naming Entities
Use a singular noun for clarity and consistency.
Be specific to the organization to ensure relevance and accuracy.
Be concise or abbreviated for ease of use.
For event entities, name the result, not the process, focusing on outcomes.
Maintain consistency across diagrams for uniformity and ease of understanding.
Defining Entities
Start with "An X is…" to provide a clear and concise definition.
Describe unique characteristics of each instance to differentiate it from others.
Explicitly state what the entity is and is not, clarifying its boundaries.
Define when an instance is created or destroyed, managing its lifecycle.
Describe changes to other entity types that may occur, understanding its impact.
Specify history that should be kept for auditing and analysis.
Attributes
A property or characteristic of an entity or relationship type, providing descriptive information.
Classifications:
Required vs. Optional: Whether the attribute must have a value.
Simple vs. Composite: Whether the attribute can be further divided.
Single-Valued vs. Multivalued: Whether the attribute can have multiple values.
Stored vs. Derived: Whether the attribute is stored or calculated.
Identifier: The attribute that uniquely identifies an entity instance.
Required vs. Optional Attributes
Required: Must have a value for every entity instance, ensuring completeness.
Optional: May not have a value for every entity instance, allowing for flexibility.
Simple vs. Composite Attributes
Composite: Has meaningful component parts or sub-attributes that provide detailed information.
Single-Valued vs. Multivalued Attributes
Multivalued: May take on more than one value for a given entity instance, accommodating diverse information.
Stored vs. Derived Attributes
Derived: Values can be calculated from related attribute values and are not physically stored, saving space and ensuring accuracy.
Identifiers (Keys)
An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type.
Simple vs. Composite Identifier: Whether a single attribute or multiple attributes are used.
Candidate Identifier: An attribute that could be an identifier, evaluated for suitability.
Criteria for Identifiers
Choose identifiers that:
Will not change in value, ensuring stability.
Will not be null, guaranteeing uniqueness.
Avoid intelligent identifiers, as they may change over time.
Substitute new, simple keys for long, composite keys for efficiency.
Naming Attributes
Should be a singular noun or noun phrase for clarity.
Should be unique within the entity to avoid confusion.
Should follow a standard format for consistency.
Similar attributes of different entity types should use the same qualifiers and classes for uniformity.
Defining Attributes
State what the attribute is and why it is important, providing context.
Make clear what is and is not included in the attribute’s value, clarifying scope.
Include aliases in documentation for ease of reference.
State the source of values for traceability.
State whether the attribute value can change once set, managing mutability.
Specify whether required or optional for completeness.
State the minimum and maximum number of occurrences allowed, controlling multiplicity.
Indicate relationships with other attributes, understanding dependencies.
Modeling Relationships
Relationship Types vs. Relationship Instances: Distinguishing the general category from specific occurrences.
The relationship type is modeled as lines between entity types, while the instance is between specific entity instances, showing connections.
Relationships can have attributes that describe features pertaining to the association between entities, providing additional details.
Two entities can have more than one type of relationship between them (multiple relationships), reflecting diverse interactions.
Associative Entity: A combination of relationship and entity, merging both aspects.
Degree of Relationships
The number of entity types that participate in a relationship, defining its complexity.
Unary Relationship: Entities of the same entity type related to each other.
Binary Relationship: Entities of one type related to entities of another.
Ternary Relationship: Entities of three different types involved in the same relationship.
Unary: Relationships within the same entity type.
Binary: Relationships between two different entity types.
Ternary: Relationships involving three different entity types.
Cardinality of Relationships
One-to-One: Each entity has exactly one related entity, maintaining a strict correspondence.
One-to-Many: One entity can have many related entities, but the related entity can have a maximum of one related entity, reflecting hierarchical structures.
Many-to-Many: Entities on both sides can have many related entities on the other side, allowing for complex interactions.
Cardinality Constraints
The number of instances of one entity that can/must be associated with each instance of another entity, setting boundaries for relationships.
Minimum Cardinality: Dictates whether the relationship is optional or mandatory.
If zero, then optional.
If one or more, then mandatory.
Maximum Cardinality: Sets the upper limit for the number of related instances.
Associative Entities
Guidelines for when to use an associative entity instead of a relationship with attributes:
All relationships for the associative entity should be many, reflecting its role in complex connections.
The associative entity could have meaning independent of the other entities, justifying its existence.
Preferably has a unique identifier and other attributes, providing substance.
May participate in other relationships, expanding its utility.
Convert ternary relationships to associative entities for simplification.
An Associative entity is an entity that serves to link other entities together in a many-to-many relationship, acting as an intermediary.
Bill-of-Materials Structure
A bill-of-materials is a hierarchy of items, showing component relationships.
Time-Stamping
A time value associated with a data value, indicating when an event occurred that affected the data value, tracking changes over time.