Chapter 7: Logical Database Design BLCN-534 Fundamentals ✓ Solved

Chapter 7 Logical Database Design BLCN-534: Fundamentals of

Chapter Objectives

Describe the concept of logical database design.

Design relational databases by converting entity-relationship diagrams into relational tables.

Describe the data normalization process.

Perform the data normalization process.

Test tables for irregularities using the data normalization process.

Introduction to Logical Database Design

Logical database design is a critical aspect of database systems. It involves the creation of a detailed blueprint that outlines how data will be stored, organized, and accessed in a database. This chapter emphasizes the importance of aligning database structures with the actual business environment to ensure efficiency and effectiveness in data management.

The Concept of Logical Database Design

Logical design entails arranging the attributes of entities in a business environment into structured tables within a relational database. The main goal is to create well-structured tables that accurately reflect business processes and relationships. This requires converting entity-relationship (E-R) diagrams into relational tables while adhering to certain rules that govern the relationships between entities.

Converting E-R Diagrams into Relational Tables

The conversion process begins with each entity in the E-R diagram represented as a table in the database. Many-to-many relationships will require the creation of an associative entity to effectively manage the interplay between the participating entities. For instance, in a one-to-many relationship, the foreign key from the 'one' side must be placed in the table representing the 'many' side. This ensures that data integrity is maintained and that relationships are correctly represented in the database structure.

Data Normalization

Normalization is a systematic approach to organizing data to minimize redundancy and dependency. The process includes several stages known as normal forms, which are essential for ensuring that the database structure is optimal. The three major forms are:

  • First Normal Form (1NF): Each table contains only atomic values, with unique identifiers for each record.
  • Second Normal Form (2NF): No partial dependencies of any column on the primary key; all non-key attributes must depend entirely on the primary key.
  • Third Normal Form (3NF): No transitive dependencies; non-key attributes must not depend on other non-key attributes.

Implementing Data Normalization

The implementation of normalization involves examining tables to identify and eliminate redundancy. For example, in a table containing salespersons, it is crucial to ensure that each salesperson's data is listed only once, without duplication across multiple records. This makes the database more efficient and easier to maintain.

Practical Use Cases

The logical design concepts can be applied to various business scenarios such as:

  • The General Hardware Company, where identifying unique products and sales relationships is vital for inventory management.
  • The Good Reading Bookstores, which requires efficient categorization of books and customers.
  • The World Music Association, aiming to track membership and event participation effectively.
  • The Lucky Rent-A-Car, needing a streamlined way to manage rentals and customer transactions.

Conclusion

Logical database design is fundamental to creating relational databases that are both functional and efficient. By systematically converting E-R diagrams into normalized tables, organizations can better manage their data in alignment with business processes. As companies grow, the importance of a robust database structure becomes increasingly clear, as it supports ongoing data access, integrity, and operational success.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Harrington, J. L. (2016). Relational Database Design and Implementation. Course Technology.
  • Rob, P. & Coronel, C. (2016). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Date, C. J. (2012). An Introduction to Database Systems. Addison-Wesley.
  • Connolly, T. & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
  • Groff, J. R. & Wong, P. N. (2013). SQL & Relational Theory: How to Write Accurate SQL Code. O'Reilly Media.
  • Kroenke, D. M., & Auer, D. (2013). Database Concepts. Pearson.
  • Chaudhuri, S., & Narasayya, V. (1999). An Overview of Query Optimization in Relational Systems. ACM Computing Surveys.
  • Parker, D. (2014). E-R Modeling: An Introduction to Database Design. CreateSpace Independent Publishing Platform.
  • Finkelstein, H. (2013). Database Design: Principles and Practices. Routledge.

Related Assignments