Step 1: The first step in the logical design stage of the (DBLC) database life cycle is to create a conceptual model. This involves converting business objects (and their characteristics) identified during requirements analysis into the language of entities and attributes for use in an ER diagram.
Here's a breakdown of that step in the Database Life Cycle (DBLC):
Logical Design Stage: Conceptual Model Creation Step: Creating a Conceptual Model
Purpose: The conceptual model aims to represent the real-world entities and their relationships in a structured, abstract form that does not depend on any specific database management system (DBMS).
Process:
Identify Business Objects: During the requirements analysis, business objects or concepts important to the organization are identified. These could include things like "Customer", "Order", "Product", etc.
Conversion to Entities and Attributes:
Entities: These are the primary objects or concepts from the business domain, translated into the database world. For example, "Customer" becomes an entity in the ER diagram.
Attributes: These are the properties or characteristics of the entities. For "Customer", attributes might include "CustomerID", "Name", "Address", etc.
Use of ER Diagrams:
An Entity-Relationship (ER) diagram is used to visually represent these entities and their attributes. Relationships between entities are also depicted here, showing how they interact or relate to each other within the business context.
Normalization: Though not strictly part of conceptual modeling, considerations for normalization might begin here to ensure data integrity and avoid redundancy.
Output:
A high-level ER diagram that maps out the conceptual schema of the database. This diagram is independent of hardware or software considerations and focuses purely on business rules and data requirements.
This conceptual model serves as a foundation for the subsequent steps in the DBLC, where logical and physical designs will further refine how this data will be stored and accessed in a database system. Remember, the conceptual model should be easily understandable by all stakeholders, including those without a deep technical background in databases, ensuring that everyone agrees on the data structure before proceeding further.
Attributes, Entites and the ERD Model
In an Entity-Relationship Diagram (ERD) model, an entity represents a real-world object or concept that is distinguishable from other objects and has certain attributes. Here's a breakdown of what attributes typically represent:
Attributes: These are the properties or characteristics of an entity. They describe the entity in more detail. Attributes can be thought of as columns in a database table where entities would be represented as rows.
Simple Attribute: Cannot be divided into smaller subparts. For example, "Age" of a person.
Composite Attribute: Can be divided into smaller subparts. For example, "Address" might be composed of "Street", "City", "State", "Zip Code".
Single-Valued Attribute: Holds only one value for each entity instance. For example, "Nationality" of a person.
Multi-Valued Attribute: Can hold multiple values for each entity instance. For example, "Phone Numbers" where a person might have more than one phone number.
Derived Attribute: Values are derived from other attributes. For example, "Age" could be derived from "Date of Birth".
Key Attribute: Uniquely identifies an entity instance. There can be:
Primary Key (PK): Uniquely identifies each record in the entity. No two entities can have the same primary key value.
Candidate Key: Any column or set of columns that could act as a primary key.
Alternate Key: Other candidate keys that are not chosen as the primary key.
Foreign Key (FK): Links one table to another, indicating a relationship between two entities.
Relationships: While not attributes themselves, relationships in an ERD define how entities interact with each other. Relationships can be:
Entity Instance: Each row in the database table representing a specific occurrence of the entity. For example, each row in an "Employee" entity would represent a specific employee.
Cardinality: Defines how many instances of one entity can or must be associated with instances of another entity. This dictates the "to-one" or "to-many" aspect of relationships.
Participation/Optionality: Indicates whether an entity instance must participate in a relationship (mandatory) or can choose not to (optional).
Attributes in an ERD serve to detail what data is stored about each entity, how it might be structured, and how it relates to data in other entities, facilitating the design of a database that effectively models real-world scenarios or business processes.
An entity is a business object and can be either tangible (such as a person or an item) or intangible (such as an event or a reservation). Every entity in a database must have a different name. It is common practice (but not required) to name entities in the singular. Step 2: In "Step 2" Entities become tables in a database. Special types of entities, discussed in a later module, are sometimes created to represent the relationship between other entities.
Understand common elements typically found in diagrams related to databases, such as Entity-Relationship Diagrams (ERDs).
Common Features of ERD-related Diagrams:
Entities:Represented as rectangles, these are usually used to symbolize database tables. Each entity holds a table name which describes a collection of similar objects (e.g., PERSON).
Attributes:Often shown as ovals, attributes are connected to their respective entities with lines. They represent the columns or fields within a table (e.g., Name, Age, Gender).
Primary Keys:Sometimes underlined within an entity, these identify unique records within the table.
Relationships:Lines or arrows that connect entities, showing how data in one table may relate to data in another.
Labels on Relationships:Descriptive text on the connecting lines/arrows that explain the nature of the relationship between entities, like one-to-many or many-to-one.
Tables Illustrated:Diagrams might convert entities and attributes directly into a visual representation of what the database table structure looks like, showing headers for columns derived from the attributes.
These components help visually organize data structures and relationships within a database, making it easier to understand and design the database schema.
A database consists of a PERSON entity and the table consists of the attributes of the PERSON
When thinking about what constitutes an entity, it is important not to confuse an aggregate concept, such as an inventory or a medical history, with a single entity. These aggregates actually represent groups of entities and not single, discrete entities.
Attributes:
Just as business objects have characteristics that describe them, entities are described by their attributes. When we represent an entity in a database, what we actually store are that entity's attributes. In a nutshell, attributes store data values[1] that either 1) describe or 2) identify entities.
Attributes become fields in a table:
Attributes that describe a person (for instance, customer, employee, student, etc.) would include such things as name, address, and telephone number. Attributes that identify a person would include such things as social security number or any combination of letters and numbers that uniquely identify a person.
Attributes that describe entities are called non-key attributes.
Attributes that identify entities (entity identifiers) are called key attributes.
The next lesson explains the purpose of entity identifiers.
Entities Attributes - Exercise
Before moving on to the next lesson, click the Exercise link below to test your understanding of entities and attributes. Entities Attributes - Exercise
[1]data value: Data entered at the intersection of a row (record) and column (field); the data describes or identifies the subject of the record.
