Welcome to Lesson 1 of Module 10 on Schema Conversion. I'm going to start with two definitional questions about an important word in the context of database design. What does the word logical mean in a database design context? What is the opposite of logical in a database design context? Module 10 extends your database development background and skills. Modules 6 to 9 covers conceptual data modeling, emphasizing precise usage of ERD notation, analysis of narrative problems, and generation of alternative designs. Modules 10 and 11 cover the next phase in the database development process. This lesson provides a context for the details about logical database design covered in other lessons of Modules 10 and 11. The objectives in this lesson involve a broad focus on logical database design to provide a context for skill development. You can use this context to reflect on the skills you learned in other lessons in this module as well as in the lessons in Module 11. You should be able to explain the goals and steps of logical database design in the position of this module in the logical database design process. The goals of database development were covered in previous lessons, so I want to emphasize the goals applicable to logical database design. The conceptual data model phase establishes a common vocabulary in business rules. Logical database design emphasizes refinement of a conceptual data model or schema. The logical database design process creates a table design to implement and refine business rules and support data quality. Managing redundancy is a major theme of logical database design. Eliminating unwanted redundancy is an important data quality goal supported by business rules. Although the conceptual data modeling phase indirectly deals with redundancy, the logical database design phase directly manages redundancy. The logical database design phase uses constraints that can be analyzed to detect and eliminate unwanted redundancies. Transaction processing is sensitive to redundancy because redundancy complicates modification operations to insert, update, and delete rows. Business intelligence processing is less sensitive to redundancy, because reporting, not modification operations, dominates business intelligence processing. Modules 10 and 11 cover logical database design. The phase starting with an ERD created in a conceptual data modeling phase. The logical database design phase transforms a conceptual data model into a format understandable by a relational DBMS. The logical database design phase is not concerned with efficient implementation. Rather than logical database design phase, this concern with the refinements to the conceptual data model. The refinements preserve the information content of the conceptual data model while enabling implementation on a relational DBMS. Because most business databases are implemented on relational DBMS's, the logical database design phase produces a table design. The logical database design phase consists of two refinement activities, conversion and normalization. The conversion activity transforms an ERD into a table design using conversion rules. A table design consists of tables, columns, primary keys, foreign keys, and other constraints. The normalization activity removes redundancies in a table design using constraints or dependencies among columns. Before normalization occurs, constraints by possible redundancy are specified. After normalization, additional refinements can be made to enforce business rules, associate with unwanted redundancy, and other data quality concerns. Specifically, uniqueness constraints can be added to a table design to augment primary key constraints. Modules 10 and 11 cover most of the logical database design process. Module 10 presents conversion rules and provides practice applying them. The conversion rules will help you to understand the precise differences between the ERD notation and the relational data model. Module 11 covers the last three steps of the logical database design process, although simplifying some specialized details for brevity. In practice, the specification step is more important than the normalization step, because design tools can perform normalization if constraints are specified completely. Now, let's wrap up Lesson 1 about logical database design goals and steps. This lesson has provided a context for the concepts and skills covered in other lessons in Modules 10 and 11. You should not lose perspective of the goals and challenges of logical database design as you learn about the details and develop skills. In answer to the opening question, the word logical has a different meaning in a database design context than in general discourse. In general discourse, logical means agreeing to principles of orderly reasoning. The opposite of logical is unreasonable or incoherent. In a database design context, logical involves the focus on the information content of a database, not the implementation details. In a database design context, the opposite of logical is physical, implying implementation details on digital storage devices. The other lessons of Module 10 cover the first step of logical database design, to convert from an ERD to a table design. You will learn the details of each conversion rule and then apply the rules on practice and graded problems.
