Transcript 50401A-ENU_Powerpnt_02

Module 2
Designing a Logical Database
Model
Module Overview
• Guidelines for Building a Logical Database Model
• Planning for OLTP Activity
• Evaluating Logical Models
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
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
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
Considerations for Selecting Primary Keys
Each database table has a column or a combination of columns containing values that
uniquely identify each row in the table
Natural
Keys
Surrogate
Keys
Surrogate keys have the
following advantages:
Natural keys have the following
advantages:
• Smaller size
• Enforced compliance
• Better support for primary key
• Less joins
changes
• Better availability
• Easier joins
• Automatic constraints
• User verifiable
Best Practices for Finalizing the Logical Model
Identify remaining constraints
Normalize all data tables
Identify schemas for grouping entities
Compare the conceptual and
logical models
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.
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
Guidelines for Identifying Functional Dependencies
Identify the semantics of
attributes
Identify the concepts of a
relational schema
Relation
Tuple
Attribute
Key
Schema
Benefits of Performing Data Normalization
Ohio
Ohio
Reduce redundant values in tuples
Reduce or eliminate null values in tuples
-.-..-.-.
Avoid generating spurious tuples
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
• Relation is in the first normal form
Name
Skills
• Attributes of the relation depend on the whole key
Third normal form (3NF)
• Relation is in the second normal form
• Some attributes of the relation are not
transitively dependent on the primary key
E-mail
Name
Phone
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
Guidelines for Performing Denormalization
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
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
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.
Lesson 3: Evaluating Logical Models
• Guidelines for Analyzing an Existing Logical Model
• Identifying Problems with a Logical Model
• Discussion: Expanding Legacy Projects
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
Identifying Problems with a Logical Model
Analyze the model for consistency
Existing Logical
Model
Identify anomalies that are caused by
insufficient normalization
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 a
new functionality to the existing data project?
Lab 2: Designing a Logical Database Model
• Exercise 1: Creating a Logical Database Model
• Exercise 2: Normalizing the Logical Database Model
Logon Information
Virtual machine
NYC-SQL1
User name
Administrator
Password
Pa$$w0rd
Estimated time: 60 minutes
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.
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?
Module Review and Takeaways
• Review Questions
• Real-world Issues and Scenarios
• Additional Reading Material