Conceptual_Design_ERD.pdf

Conceptual Design (ERD)  Conceptual Design (ERD) The Conceptual Design phase of relational database design involves creating a high-level representation of the data. This is done using an Entity-Relationship Diagram (ERD), which visually describes the data and its relationships in a si...

Conceptual Design (ERD)  Conceptual Design (ERD) The Conceptual Design phase of relational database design involves creating a high-level representation of the data. This is done using an Entity-Relationship Diagram (ERD), which visually describes the data and its relationships in a simple and understandable format.  The conceptual model is independent of any specific database management system (DBMS) or technology and focuses on what data needs to be stored rather than how it will be stored.  Key Concepts in ERD 1. Entities 2. Attributes 3. Primary Key (PK) 4. Relationships 5. Cardinality 6. Weak Entities  Entities An entity represents a real-world object or concept that stores data. Example: A university database might have entities like Student, Course, and Instructor. Entities are often represented as rectangles in ERD.  Attributes Attributes are properties or details that describe an entity. Example: A Student entity might have attributes such as StudentID, Name, Birthdate, and Email. Attributes are often represented as ovals in ERD.  Primary Key (PK) A primary key is a unique identifier for each instance of an entity. Example: The StudentID in the Student entity is a primary key. Primary keys are underlined in ERD to signify their uniqueness.  Relationships Relationships define how entities are related to each other. Can be one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N). Example: A Student enrolls in multiple Courses (1:N relationship). Relationships are represented as diamonds in ERD.  Cardinality Cardinality defines the number of occurrences in one entity that are associated with occurrences in another entity. Example: A Student enrolls in one or more Courses (1:N cardinality).  Weak Entities A weak entity cannot be uniquely identified by its attributes and depends on another entity. Example: A Dependent entity (such as a child) in an insurance database depends on an Employee entity. Weak entities are represented by a double rectangle.  Example ERD Components Entities: Student, Course, Instructor Relationships: Enrollment (many-to-many), Teaches (one-to-many) Attributes: StudentID, Name, CourseID, Credits, etc.  2. Logical Database Design At this stage, the conceptual model is translated into a logical model that can be implemented in a DBMS. The design is refined according to the rules of the specific data model, typically the relational model. The steps are:  Normalization: Ensure the design avoids redundancy and maintains data integrity. This involves organizing the data into tables following normal forms (e.g., 1NF, 2NF, 3NF).Mapping Entities to Tables: Convert entities into tables and define primary keys (unique identifiers).  Define Relationships as Foreign Keys: Translate relationships from the ERD into foreign keys to maintain referential integrity between tables. Specify Attributes and Data Types: Identify data types and constraints (e.g., integer, varchar, NOT NULL, UNIQUE). This step remains DBMS-independent but is closer to how the data will be stored in a relational database.  3. Physical Database Design This stage involves translating the logical database design into a physical schema that considers the performance, storage, and specific DBMS optimizations. The key steps include: Map Tables to the DBMS: Create tables in the chosen DBMS (e.g., MySQL, Oracle, SQL Server) using SQL syntax.  Define Indexes: Improve query performance by adding indexes to frequently queried columns (e.g., primary keys, foreign keys). Partitioning and Clustering: If needed, optimize performance by partitioning large tables and clustering data to minimize disk I/O.  Storage and Security: Determine storage settings (e.g., file locations, space allocation) and apply security measures like user permissions and encryption. Performance Tuning: Optimize queries, indexes, and resource usage based on the anticipated workload. By the end of the physical design, the database is fully implemented and ready for testing and use.