1.3 Relational Database Design (ERD and Normalization).pdf

Relational Database Design (ERD and Normalization) MRV Learning Objectives 1. Differentiate between conceptual, logical and physical design 2. Define normalization and ETL 3. Create an ERD that shows progression from conceptual to logical to physical design MRV...

Relational Database Design (ERD and Normalization) MRV Learning Objectives 1. Differentiate between conceptual, logical and physical design 2. Define normalization and ETL 3. Create an ERD that shows progression from conceptual to logical to physical design MRV 2  Relational Database Design (ERD and Normalization) Defining Relational Databases A relational database is a collection of related data stored in a centralized location or repository. The stored data is organized into tables that house information about various objects stored in the database. Relational databases provide an efficient, flexible, and scalable way to store and access structured information. Relational databases are typically hosted and managed using a relational database management system (RDBMS). The RDBMS employs Structured Query Language (SQL) to enable retrieval and interaction with data in various tables. These systems also typically deploy authentication, authorization, performance tuning, and many other features. Relational databases are organized by groupings of objects which have a unique identifier or primary key. The primary key identifies the row in a table that corresponds to an individual record and its associated data. The primary key can also be used as a foreign key in another table to indicate relationship. Foreign keys create logical connections between tables and establish relationship. MRV 1st SEM 2022-2023 3 Relational Database Design (ERD and Normalization) Entity-Relationship Diagram (ERD) An entity-relationship diagram (ERD) is a graphical representation of a database design. The example diagrams below (Figures 1-3) illustrate a simple ERD that describes the overall design and lays the foundation and requirements for implementation in an RDBMS. The ERD also establishes relationships among objects and serves as documentation for the database system. MRV 4 Relational Database Design (ERD and Normalization) The Process of Designing Databases Data modeling is the process of designing databases, and there are three data models: conceptual, logical data, and physical data. Conceptual Design Figure 1: Entity business objects (conceptual design) Conceptual design establishes entities, attributes, and relationships. The purpose of a conceptual data model is to present a high-level picture of the Figure 1 depicts the entity business objects that either system to be implemented with a focus on the business objects involved in interact with or are part of an information system. In this the system. Database tables are not designed at the conceptual level. example, we have customers ordering products. The base relationships are identified using crow’s foot notation. A single line indicates a single relationship (i.e., a product can only be in one category), and a three-lined crow’s foot indicates a “many”-type relationship (i.e., a category can have many products). MRV 1st SEM 2022-2023 5 Relational Database Design (ERD and Normalization) Logical Design Logical design defines the structure of data elements and establishes relationships among data elements. The logical data model adds a layer of detail to the conceptual design by defining the columns of data that need to be included with each entity, as seen in Figure 2. At this stage of design, there is still no consideration for a specific database system since the focus is on structure and relationship. Each business object or entity now includes attributes or Figure 2: Entity business objects (conceptual design with attributes) [Long Description] columns that will describe individual records within the eventual database table. These attributes begin to detail the information that makes up a single record (or row) within an eventual MRV database table. 6 Relational Database Design (ERD and Normalization) Physical Design Physical design describes database-specific implementation details and provides a blueprint for the relational database. The physical data model includes additional details about each column within an entity. At this stage of design, it is important to operate within the constructs of a specific RDBMS since structures, conventions, and restrictions can vary. As Figure 3 depicts, we now have a fully defined database design Figure 3: Entity business objects (physical data model) [Long Description] that is ready for implementation in our selected RDBMS. The primary keys for each table are marked with a “+” symbol, and the data types for each column are identified and follow data MRV types acceptable for the MySQL RDBMS. 7 Relational Database Design (ERD and Normalization) Normalization and Denormalization The concepts of normalization and denormalization describe the organization of the contents of a database. ▪ Normalization involves separating data to well-defined objects to limit data redundancy. In normalization, there is a heavy focus on relationships between tables, and each table holds unique information that is required to describe an individual record or entity. ▪ Denormalization combines data into a single table to remove external relationships and dependencies. Though this approach can speed up SQL queries, it also very often results in redundant or duplicated data throughout the database. The normalization and denormalization table contain more details about each of these concepts. MRV 8  Online - Activity 1 Instructions: Application of Entity-Relationship Diagram 1.Select a system and create an ERD with progression from conceptual to logical to physical design. -Do not Answer. –this is part of the PPTX MRV 9  Supplemental Resources The following is a list of optional resources that you may find useful to enhance your understanding of the topics in this module. Video: Lucidchart. (2018). The Basics of Relational Database Design. https://www.youtube.com/watch?v=wR0jg0eQsZA Video: CBT Nuggets. (2019). How to Normalize Databases. https://www.youtube.com/watch?v=upS2HlUj1gI Article: Guru99. (n.d.). What is Normalization? 1NF, 2NF, 3NF, BCNF Database Example. https://www.guru99.com/databasenormalization.html#:~:text=Normalizati on%20is%20a%20database%20design,and%20links%20them%20using %20relationships. Reference: https://www.coursera.org/learn/nature-of-data-relational-database-design Thank You MRV 10