Read an Excerpt
Ojective Domain 1: Developing a Logical Data Model
The Developing a Logical Data Model domain focuses on the skills required to build the foundation for any database system—the logical data model. This model is used to validate and document the business rules and functions of the database in a graphic manner.
The first step in developing the model consists of defining the pertinent business entities, determining the relationships between these entities, and identifying the cardinality of these relationships. Then the defining attributes are added to each entity, and the model is checked for normalization. Next the primary keys are determined, and through the relationships, the foreign keys are defined. Once the fundamental entity relationships are understood, attribute data types, scale and precision, allowed values, and nullability are determined. All of these steps are covered in this section of the exam.
Although any modeling tool can be used to create a logical model (especially because a logical model should be product-independent), Microsoft Visio, with its data modeling stencils, is an excellent modeling tool that can be used throughout the database design and implementation phases.
Tested Skills and Suggested Practices
The skills that you need to successfully master the Developing a Logical Data Model objective domain on the Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition exam include:
- Defining entities; considerations include entity composition and normalization.
- Practice 1: For this practice, create a list of all of the reasonable entities, with the entity name only, that would exist for a sample business function.
- Practice 2: Add the attributes that describe the entity.
- Practice 3: Validate that the model meets the three main requirements for normal form. Ensure that there are no repeating groups, that all of the attributes in an entity depend on the whole primary key, and that attributes depend only on the primary key and not on any other attributes.
- Defining entity keys; considerations include primary keys, foreign keys, and unique constraints.
- Practice 1: Using the logical entity model created in the previous practice, determine the relationships between the entities, identifying the cardinality as one-to- one, one-to-many, or many-to-many. If your chosen design tool does not include the ability to model a many-to-many relationship, you can add an associative entity in between.
- Practice 2: Evaluate your logical model for redundancies, circular relationships, and invalid assumptions. Determine whether all business objects are included. It is critical that this simple logical model be flexible, stable, and appropriate for the business function being modeled because it will provide the foundation for all remaining work on the project. Take the time to validate the relationships.
- Practice 3: Determine all possible candidate keys for each entity and identify the primary keys. Add the primary key names to the models, as well as the corresponding foreign key names, based on the relationships already defined.
- Defining attribute domain integrity; considerations include CHECK constraints, data types, and nullability.
- Practice 1: Determine the data types, including the scale and precision of each attribute, the allowable values, and the nullability of each attribute.
- Practice 2: Validate the logical model against the business rules and functions and ensure that all possible requirements that can be met are met by the relational model. Keep in mind that businesses change over time, and it is not to your advantage to have to change the fundamentals of the model to include these types of business rules.
Further Reading
This section lists supplemental readings by objective. Study these sources thoroughly before taking exam 70- 229.
Objective 1.1
Date, C. J. An Introduction to Database Systems. Reading, Mass.: Addison-Wesley, 1995. Review Chapter 4, "Relational Data Objects: Domains and Relations." This is the fundamental book about database systems.
Fleming, Candace C. and Barbara von Halle. Handbook of Relational Database Design. Reading, Mass.: Addison-Wesley, 1989. Review Chapter 5, "Build Skeletal User Views," in Part Two, "Building a Logical Data Model." This book is considered a classic for an introduction to relational design.
Elmasri, Ramez A. and Shamkant B. Navathe. Fundamentals of Database Systems. Reading, Mass.: Addison-Wesley, 1994. Review Chapter 3, "Data Modeling using the Entity-Relationship Approach," and Chapter 6, Section 1, "Relational Model Concepts." This book is fairly academic, but provides a concise description of the fundamentals.
Litwin, Paul. "Fundamentals of Relational Database Design." (This paper can be downloaded for free at http://www.microsoft.com/TechNet. You can find this paper by searching on the title.) Review the sections on relationships and normalization. This paper provides a good, short overview of relational database design.
Simsion, Graeme C. Data Modeling Essentials: Analysis, Design, and Innovation. Boston, Mass.: International Thompson Computer Press, 1993. Review Chapter 1, "What is Data Modeling?"; Chapter 2, "Basic Normalization"; Chapter 3, "The Entity Relationship Approach"; Chapter 6, "More About Relationships and Foreign Keys"; and Chapter 7, "Advanced Normalization." These chapters provide user-friendly descriptions of data modeling fundamentals.
Teorey, Toby J. Database Modeling and Design. San Francisco: Morgan Kaufmann, 1999. Review Chapter 3, "ER Modeling in Logical Database Design," and Chapter 5, "Normalization." These chapters provide user-friendly descriptions of data modeling fundamentals.
Microsoft SQL Server 2000 product documentation. To access Books Online (BOL), click the Start button, and point to Programs, then Microsoft SQL Server, and then Books Online. Click the Contents tab and double-click any of the following topics: "Database Design Considerations," "Creating a Database Plan," "Logical Database Modeling," and "Normalization." Although the BOL information about database design is limited, it is a good starting place.
Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. Redmond, Washington: Microsoft Press, 2001. Read and complete the practices in Lessons 1 and 2 in Chapter 3, "Designing SQL Server Database."
Objective 1.2
Date, C. J. An Introduction to Database Systems. Reading, Mass.: Addison-Wesley, 1995. Review Chapter 5, "Relational Data Integrity: Candidate Keys and Related Matters."
Fleming, Candace C. and Barbara von Halle. Handbook of Relational Database Design. Reading, Mass.: Addison-Wesley, 1989. Review Chapter 6, "Add Keys to User Views," in Part Two, "Building a Logical Data Model."
Litwin, Paul. "Fundamentals of Relational Database Design." (This paper can be downloaded for free at http://www.microsoft.com/TechNet. You can find this paper by searching on the title.)
Simsion, Graeme C. Data Modeling Essentials: Analysis, Design, and Innovation. Boston, Mass.: International Thompson Computer Press, 1993. Review Chapter 8, "Primary Keys and Identity," and Chapter 9, "Attributes."
Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, and point to Programs, then Microsoft SQL Server, and then Books Online. Click the Contents tab, double-click "Creating and Maintaining Databases," and review the following topics: "Primary Key Constraints," "Foreign Key Constraints," and "Unique Constraints."
Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. Redmond, Washington: Microsoft Press, 2001. Read and complete the practices in Lessons 1 and 2 in Chapter 5, "Implementing Data Integrity."
Objective 1.3
Fleming, Candace C. and Barbara von Halle. Handbook of Relational Database Design. Reading, Mass.: Addison-Wesley, 1989. Review Chapter 9, "Determine Additional Attribute Business Rules," in Part Two, "Building a Logical Data Model."
Simsion, Graeme C. Data Modeling Essentials: Analysis, Design, and Innovation. Boston, Mass.: International Thompson Computer Press, 1993. Review Chapter 8, "Primary Keys and Identity," and Chapter 9, "Attributes."
Microsoft SQL Server 2000 product documentation. To access Books Online, click the Start button, and point to Programs, then Microsoft SQL Server, and then Books Online. Click the Contents tab and double- click the "Creating and Maintaining Databases" section. Review the "Data Integrity" topic.
Microsoft Corporation. MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. Redmond, Washington: Microsoft Press, 2001. Read and complete the practices in Lessons 2 and 3 in Chapter 4, "Implementing SQL Server Databases and Tables," and Lessons 1 and 2 in Chapter 5, "Implementing Data Integrity."
1.1 Define entities.
The first step to a well-designed database model to be implemented in SQL Server 2000 is to determine the valid entities for the model. Although this phase might seem simple at first glance—just a diagram of a group of boxes with titles in them—this phase is critical to the success of any dependent project. Changes to a logical model after development has begun, or worse, has been implemented, can have huge implications, much like a pebble thrown into a pond. The ripples affect every part of an organization's code, documentation, and users. It is critical to understand the business functions and their relationships at a deep and fundamental level. The logical model allows you to work your way to this understanding, by the iterative process of the modeling, discussion with the pertinent business and subject matter experts, and validation of the model.
This model must also be flexible and it should be considered as part of an enterprise data model. Although you might not be modeling the entire organization's data at this time, there is a very good chance that the model will need to be extended to a new area or incorporated with an independently designed model. It is to the organization's best advantage to ensure that the model adheres to fundamental modeling concepts and the enterprise perspective. Consider all of the code, documentation, training manuals, stored procedures, business objects and components, and so on that need to be changed if the basic logical model is flawed and invalid for the organization. The cost of such a change is huge, so the time spent getting it right at this point is worth the effort.
A well-designed data model must not only be valid for the business, but also it needs to be scalable, maintainable, and extensible. An elegant data model will encompass all of these attributes.
Many examples of "generic" data models have already been created, validated, and implemented that might fit your needs. Usually you will need to customize these, or slice off the particular parts that are pertinent to your project, but adhering to these common models will help to ensure extensibility.
The last critical piece to a logical data model is to ensure that the appropriate resources are available to help validate the model. This will need to include business people, who might have no understanding of databases, but who understand the business and its processes, and a database architect who can translate the business into a model.
After the entities have been determined, adding the attributes for the entities helps validate that the correct entity selections have been made. When the entities and their attributes have been designed, the model should be checked to verify that it meets at least the first three levels of normal form. These levels are
- No repeating groups or multivalue attributes.
- Every attribute in the entity must be dependent on the whole primary key.
- Every attribute in the entity must be dependent only on the primary key.
You can easily remember the last two by the following saying: "All attributes must be dependent on the key, the whole key, and nothing but the key."
MCS
70-229.01.01.001
A
You design the following entities for a school course system. You would like to normalize the entities. What should you do?
Click the Exhibit button to view the entities below.
Examine the entities shown here....
Click to view graphic
A. Remove the CourseTitle1, CourseTitle2, and CourseTitle3 attributes in the Student entity.
Correct
A. The multiple CourseTitle attributes are an example of repeating groups, and are a violation of first normal form. This will obviously waste space and is inflexible.
B. Remove the Course entity.
Incorrect
B. The Course entity will be needed. Removing it will do nothing to normalize the Student entity, and the CourseTitle attributes are still left as repeating groups.
C. Add enough CourseTitle attributes in the Student entity to accommodate all of the courses that a student might take.
Incorrect
C. This proposed solution is a violation of first normal form, in that there are repeating groups. This causes many problems, including wasted space, and is very difficult to program against.
MCS
70-229.01.01.002
C
You have created a model with two entities for a school course system. You would like to normalize the entities. What should you do?
Click the Exhibit button to view the entities below.
Examine the entities shown here....
Click to view graphic
A. Add a TeacherNumber to the Department entity.
Incorrect
A. The TeacherNumber is not part of the Department entity and does not describe the Department entity in any way, but rather describes the Teacher.
B. Replace the FirstName and LastName attributes in the Teacher entity with TeacherName.
Incorrect
B. Combining the names does not address any normalization problems. It does not remove repeating groups.
C. Remove the DepartmentName from the Teacher entity.
Correct
C. The DepartmentName is dependent on the DepartmentNumber and anything that would be considered a primary key for a Teacher. Leaving this in can cause data inconsistency in that a change to a DepartmentName will require not only a change to the Department entity, but also to all of the Teacher rows that have the same name.
MCS
70-229.01.01.003
B
You design a logical model for current enrollment in a university. You want to remove any redundant attributes or derived attributes that are dependent on other attributes. What should you do?
Click the Exhibit button to view the entities below.
Examine the entities shown here....
Click to view graphic
A. Remove the InstructorID from the Schedule entity.
Incorrect
A. The InstructorID is part of the primary key of the Schedule entity and it is required to describe a Schedule instance. It is not a derived attribute, and it is not redundant because it is also a foreign key reference to the Instructor entity.
B. Remove the CoursesTaught attribute from the Instructor entity.
Correct
B. This attribute would need to be derived from a calculation based on the count for a teacher in the Schedule entity. This derived data would also be redundant and could lead to data inconsistencies if this attribute were changed without a change to the instances in the Schedule entity.
C. Remove the CourseName from the Course entity.
Incorrect
C. The CourseName is not redundant or derived; it is a description that is required for the Course entity.
D. Remove the CourseID from the Schedule entity.
Incorrect
D. The CourseID is part of the primary key for the Schedule entity. It is not derived, and it is not redundant because it is also a foreign key reference to the Course entity.
MCS
70-229.01.01.004
D
You are designing a logical model to track airline flights. In your system, an airplane is owned by one airline, and a flight would always be flown by one airplane. You create the following entities:
In the following sentence diagrams, the arrows indicate a one-to-many relationship. Which of the following diagrams describes the relationship between these three entities that meets the scenario requirements?
A. Flight --> Airline --> Airplane
Incorrect
A. In this model, an airplane would have one foreign key reference to an airline, and an airline would have many airplanes. This is correct. However, the model shows an airline as having a foreign key relationship with a flight, which is not correct as described by the scenario. There would not be any way to determine what airplane was assigned to a flight.
B. Airplane --> Airline --> Flight
Incorrect
B. In this model, there is a one-to-many relationship between an airplane and an airline. This is not correct because we know from the scenario that an airplane is owned by one airline. Also, in this model one airline would have many flights. Although this is correct, the relationship between an airline and a flight should be tracked through the airplane, not directly through the airline.
C. Airplane --> Flight --> Airline
Incorrect
C. Here one airplane is allowed per flight, which is correct. However, one flight is allowed per airline, and that is obviously not accurate because an airline would have many flights.
D. Airline --> Airplane --> Flight
Correct
D. Here one airline is allowed per airplane, and many airplanes per airline, which follows the scenario requirement that an airplane is owned by one airline. There is one airplane per flight, and many flights per airplane, which is again correct. We know that a flight is flown by only one plane....
Read More
