Conceptual Data Modeling and ER Model

• Conceptual data modeling captures the overall structure of data within an organization.
• Conceptual data modeling is independent of any database management system (DBMS).
• Steps for conceptual data modeling:
• Develop a data model for the current system.
• Develop a new model that includes all requirements of the new system.
• Translate into physical design.
• Project repository links all design and data modelling steps performed during the software development life cycle (SDLC).
• Top-down approach is based on a deep understanding of the business.
• Bottom-up approach involves reviewing specifications and business documents.
• Requirement determination is needed to identify data entities, which are subjects/objects of the business and primary keys, which are unique characteristics.

ER Model

• An entity is a person, place, or object.
• An entity type shares common characteristics.
• An entity instance is a single, unique example of that entity.

Entity Types

• Entity types use a name that is a singular noun, descriptive, specific, and concise.

Attributes

• Attributes are named using a noun and are unique, following a standard format.
• Indicate if an attribute is required, optional, changeable, or related to other attributes.

Attribute Types

• Identifier: Primary key selected from the candidate keys.
• Select an identifier by choosing a candidate key that will not change its value or be null.
• Avoid using intelligent keys.
• Required: Must have.
• Optional: May not have.
• Derived: Can be computed from related values.
• Multivalued: May take on >1 value.
• Repeating group: 2 or more multi-valued attributes that are logically related.
• Composite: Has meaningful component parts.

Relationships

• Relationships are named using a verb phrase and avoid vague names.

Relationship Degrees

• Unary/recursive: Between one entity type.
• Binary: Between two entity types.
• Ternary: Three entity types.
• Cardinality involves maximum and minimum constraints.

Associative Entities

• Associative entities represent a many-to-many relationship between two entities.
• They break down complex many-to-many relationships into simpler one-to-many relationships.

Supertype

• A supertype is a general characteristic shared by many subtypes.

Subtype

• Subtypes are specific types within that supertype category.

Rules

• Total specialization: Every instance of the supertype must be a member of at least one subtype.
• For example, all vehicles must be either a car, truck, or motorcycle.
• Partial specialization: A supertype may or may not be a member of any subtype.
• For example, a vehicle can exist simply as a vehicle without a specific subtype.
• Disjoint: Can belong to only one subtype at a time
• For example, a car cannot also be a truck.
• Overlap: Can belong to >1 subtype simultaneously
• For example, a vehicle can be both a car and an electric vehicle.

Basic types of business rules

• Entity integrity ensures uniquely identifiable records, using unique, non-null identifiers.
• Referential integrity constraints ensure relationships between tables remain valid by governing relationships.
• Domains define permissible values for an attribute, with constraints on valid values and data types.
• Triggering operations are actions that automatically occur in response to certain events in the database to protect validity, such as insert or update operations.