The mapping between internal conceptual and external views involves establishing correspondences between the conceptual model's entities, attributes, and relationships, and the corresponding elements in the external view, such as database tables, columns, and foreign keys. This step ensures that the logical model's constructs accurately represent the structure and semantics of the underlying data as it will be stored and accessed in the database.
To achieve this mapping effectively:
1. Analyze Data Requirements: Identify the specific data requirements of the business applications that will interact with the database from the business or requirements gathering phase. This includes user needs, report queries, transaction updates, and security considerations.
2. Normalize the Conceptual Model: Normalize the conceptual model to reduce duplication and improve data integrity. Identify entities, attributes, and relationships based on real-world business rules and logical groupings. Normalize the model according to database design principles such as first normal form (1NF), second normal form (2NF), and third normal form (3NF).
3. Identify Database Structure: Determine the appropriate database structure and data types for each attribute identified in the conceptual model. Choose appropriate data types (e.g., numbers, strings, dates, timestamps) in the database that closely match the characteristics of the attributes.
4. Map Objects to Tables: Map conceptual entities to database tables. Typically, each entity becomes a corresponding table in the database.
5. Map Attributes to Columns: Map attributes (columns) from the conceptual model to columns within the database tables. Ensure that the data types assigned in the database align with the intended attribute properties.
6. Assign Primary Keys: Identify unique identifiers for each table through primary key assignments. Primary keys uniquely identify each row in a table and play a crucial role in establishing relationships between tables.
7. Map Relationships and Foreign Keys: Identify conceptual relationships and determine how they should be represented in the database. Typically, they are implemented using foreign key constraints between related tables. For each relationship, a corresponding foreign key column (with matching data type) is added to the related table. This constraint enforces that values in the foreign key column must match values in the referenced primary key, preventing invalid data entries and preserving referential integrity.
8. Consider Performance and Flexibility: Optimize the data model with considerations for query performance and flexibility. Indexes may be defined on certain columns (e.g., ones frequently used in query filters) to speed up data retrieval. Reserve room for extending tables through additional attributes based on foreseen future requirements.
Performing this mapping step allows you to effectively bridge the gap between the conceptual design and the actual implementation of the database, ensuring an accurate representation and use of business data.