Module 2 Designing a Logical Database Model

1. Module 2 Designing a Logical Database Model

2. Module Overview • Guidelines for Building a Logical Database Model • Planning for OLTP Activity • Evaluating Logical Models

3. Lesson 1: Guidelines for Building a Logical Database Model • Guidelines for Transforming a Conceptual Database Design into a Logical Model • Best Practices for Working with Entities and Attributes • Considerations for Selecting Primary Keys • Best Practices for Finalizing the Logical Model • Discussion: Creating a Simple Database Model

4. Guidelines for Transforming a Conceptual Database Design into a Logical Model Use an automated design tool Design an initial ER diagram Use portable database types Use independent naming conventions Validate the ER diagram with the development team

5. Best Practices for Working with Entities and Attributes Identify independent and dependent entities Distinguish between tables and views Identify candidate keys Specify attribute constraints Identify special security requirements for attributes

6. Considerations for Selecting Primary Keys Surrogate Keys Natural Keys Surrogate keys have the following advantages: Natural keys have the following advantages: • Enforced compliance • Less joins • Automatic constraints • User verifiable • Smaller size • Better support for primary key changes • Better availability • Easier joins Each database table has a column or a combination of columns containing values that uniquely identify each row in the table

7. Best Practices for Finalizing the Logical Model Identify remaining constraints Identify schemas for grouping entities Compare the conceptual and logical models Normalize all data tables

8. Discussion: Creating a Simple Database Model • Scenario: • You are a database designer for a small school named Elm High • School. The school management needs following data with respect • to students and teachers: • Tracking of each student • The courses for which a particular student is enrolled and • is/her grades for each term. • Teachers who teach a particular course • Students who learn from particular teachers • In addition, the management needs the classroom allocation or • scheduling information. • Questions: • Identify the probable entities and their characteristics. • Discuss the differences and issues between the conceptual model and • the logical model of the proposed database.

9. Lesson 2: Planning for OLTP Activity • Guidelines for Identifying Functional Dependencies • Benefits of Performing Data Normalization • Degrees of Normalization • Achieving a Normalized Design • Guidelines for Performing Denormalization • Discussion: Normalizing to an Absurd Level

10. Guidelines for Identifying Functional Dependencies Identify the concepts of a relational schema Identify the semantics of attributes Relation Schema Key Attribute Tuple

11. Benefits of Performing Data Normalization Ohio -.-.- .-.-. Ohio Reduce redundant values in tuples Reduce or eliminate null values in tuples Avoid generating spurious tuples

12. Degrees of Normalization First normal form (1NF) Name ID Age • Relations do not contain any repeating groups • Every attribute is atomic Second normal form (2NF) Age Name • Relation is in the first normal form • Attributes of the relation depend on the whole key Skills Third normal form (3NF) E-mail Name • Relation is in the second normal form • Some attributes of the relation are not transitively dependent on the primary key Phone

13. Achieving a Normalized Design Ensure a high degree of normalization Avoid update anomalies with normalization Ensure dependency of all values on simple and on composite keys Ensure that the design process is reversible

14. Guidelines for Performing Denormalization Avoid denormalization when: • Simplifying data access • Providing reporting data • Preventing calculations based on values from the same row Use denormalization when: • Pre-aggregating data from other tables • Avoiding multiple or complex joins • Improving performance Conditions to Denormalize a Design • Performance with the normalized design is unacceptable • Performance improves if denormalization is applied • Update anomalies caused by denormalization can be predicted

15. Discussion: Normalizing to an Absurd Level • Scenario: • Normalization is about reducing duplicated data. • 1234 Flower Blvd • Normalize into the following fields: • AddressNumber | AddressNameID | AddressTypeID • 1234 412 14 • AddressNameID 412 is ‘Flower’ in the AddressName table • AddresssTypeID is ‘Blvd’ in the AddressType table • Questions: • Why would you not normalize to the level of absurdity? • Provide a business scenario where an absurd level of data normalization could prove useful.

16. Lesson 3: Evaluating Logical Models • Guidelines for Analyzing an Existing Logical Model • Identifying Problems with a Logical Model • Discussion: Expanding Legacy Projects

17. Guidelines for Analyzing an Existing Logical Model Validate the Accuracy of Documentation Validate the accuracy of documentation for an existing logical model Reverse Engineer the Model Reverse engineer the logical model if it differs significantly from the documented model

18. Identifying Problems with a Logical Model Existing Logical Model

19. Discussion: Expanding Legacy Projects Scenario: Your development lead wants to add a new functionality to an existing data project. You cannot find any documentation about the design of the project. You cannot locate any change logs, bug logs or bug fixes. You have access to the current users and to the current production database. Question: How would you gather the required information to add anew functionality to the existing data project?

20. Lab 2: Designing a Logical Database Model Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd • Exercise 1: Creating a Logical Database Model • Exercise 2: Normalizing the Logical Database Model Logon Information Estimated time: 60 minutes

21. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project, which is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp2008 sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: • Provide managers with current and historical information about employee vacation and sick-leave data. • Provide permission to individual employees to view their vacation and sick-leave balances. • Provide permission to selected employees in the HR department to view and update employee vacation and sick-leave data. • Provide permission to the HR manager to view and update all the data. • Standardize employee job titles. The management has provided you with a Requirements document. The Requirements document contains information about business requirements, cost benefits, availability and reliability for business needs, security features, and performance requirements. The Requirements document also contains statements about how the HR department wants to store information about employee vacation and sick-leave hours. In lab 1, you created a conceptual model based on the Requirements document provided by the management. In this lab, you will build a logical model based on the conceptual model created earlier, and normalize its entities.

22. Lab Review • Why do we need an intermediate table between the entities to support many-to-many relationships? • How can we determine the column that can be used as a primary key? • What are the key considerations while selecting a candidate for normalization? • Why do you use composite primary keys very often in normalized tables?

23. Module Review and Takeaways • Review Questions • Real-world Issues and Scenarios • Additional Reading Material