MCSE Microsoft SQL Server 2000: Database Design and Implementation Readiness Review

• • Further Reading
  • Objective 1.1
  • Objective 1.2
  • Objective 1.3
• 1.1 Define entities.
• 1.2 Design entity keys.
• 1.3 Design attribute domain integrity.

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.

(Image Unavailable)

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.

(Image Unavailable)

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.

(Image Unavailable)

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:

• Airplane
• Flight
• Airline

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.

1.2 Design entity keys.

This section of the exam focuses on identifying the primary keys, foreign keys, and any unique constraints.

The first step to identifying entity keys is to determine all possible candidates for the primary key. These are any attributes, or combination of attributes, that can uniquely identify an instance of the entity, or a row. There might be more than one candidate for selection as the primary key, and each one of these possibilities needs to be identified. In collaboration with the business, a determination of the most appropriate candidate is selected as the primary key.

There are three major considerations for a primary key. A primary key should be:

• Applicable
• Minimal
• Stable

An applicable primary key simply means that this is the best selection for uniquely identifying each instance in this entity.

A minimal key means that it is as small as possible because it might be heavily used throughout the model, and subsequently the database. The combination of a customer name, address, and phone number is not a good example of a minimal primary key.

A stable primary key means that the possibility of the value changing is very small. In general, it is best not to change a primary key because if it is part of a relationship, the value will need to be migrated throughout the system. This can have a large impact.

Although every entity must, by definition, have a candidate for a primary key, it is quite common to use a system-generated key, called a surrogate key, as the primary key. An example of this is a people entity. A true unique identifier for a person is a large combination of many things, and it might not actually exist at all, so a unique key that has no meaning outside of the system is generated.

After a primary key is determined for each entity, this key is migrated to every entity that has a foreign key reference to it. The key can be renamed to be better understood in the context of the referring entity, but it must have the same data type.

Lastly, every candidate primary key that is not selected as the actual primary key, including attribute combinations, must have a unique constraint to enforce the logical candidate. For example, although you might decide to create a surrogate key called Country Code for a Country entity, and designate this as the primary key, the Country Name is a candidate key and could also have been selected as the primary key. It should, therefore, have a unique constraint to enforce this.

MCS

70-229.01.02.001

C

You are creating a database to track music compositions submitted for copyright to a large international copyright company. Copyrights might be acquired for every composition submitted, but a particular composer can submit only one composition of the same name at any time. You create a composer entity with a composer ID for the primary key. You also create a composition entity. Which of the following provides the best logical primary key for each row in the composition entity?

A.   Composition name

Incorrect

A.   Selecting composition name for the primary key would not allow submission of more than one composition with the same name. Although this might be rare, it would not be impossible.

B.   Composition name and composition year

Incorrect

B.   Selecting composition name and composition year would allow for more than one composition of the same name to be submitted in the same year, but in this case, composition year is an arbitrary selection that is not indicated by any of the given business criteria. Again, more than one composition of the same name by different composers would not be allowed for submission in the same year.

C.   Composition name and composer ID

Correct

C.   Selecting composition name and composer ID provides the best candidate key for the scenario. It allows submission of compositions with the same names, but only one per composer.

D.   Composition name and copyright number

Incorrect

D.   This selection has two obvious problems. One is that a copyright number is not known at all times, and primary keys cannot be null. Also, if the copyright number were known, it would be assumed to be unique in itself, and a composition name would not be needed as well to uniquely identify a row in the entity.

MCM

70-229.01.02.002

B and D

You are designing a data model to track software projects. A project might be undertaken in one department or in multiple departments. Each project is assigned a group of programmers. A programmer who is a staff member of one department might also be assigned to a project that is being undertaken by another department, and a programmer might perform multiple jobs on a particular project. You design the model with the foreign key relationships as follows. Which of the following is true? (Choose all that apply.)

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   The programmers on each project can be determined.

Incorrect

A.   Although the foreign key relationships in the model describe which department a programmer is part of, they do not allow us to determine which project the programmer is working on.

B.   The programmers on staff in each department can be determined.

Correct

B.   The model allows us to determine the programmers in each department because there is a foreign key in the Programmer entity that references the Department entity.

C.   The job being performed by a programmer on a project can be determined.

Incorrect

C.   There is no information about a specific job in the model.

D.   The projects being performed in a department can be determined.

Correct

D.   Because the ProjectDetail entity contains a foreign key to the Department entity, projects being performed in a department can be determined.

MCS

70-229.01.02.003

B

You are creating a model to track employee reviews. Reviews are currently held at six-month intervals, and only one reviewer performs an employee’s review. You create an employee entity and the following review entity. Which attributes would determine the uniqueness of a review?

Click the Exhibit button to view the Review Entity below.

Examine the Review Entity shown here.

(Image Unavailable)

A.   EmployeeID

Incorrect

A.   Selecting only EmployeeID as the primary key allows only one review per employee. This is not the correct primary key for this entity because the requirement is for employees to have multiple reviews.

B.   EmployeeID, ReviewDate

Correct

B.   The combination of EmployeeID and ReviewDate uniquely identifies an instance of a review for an employee. Although the scenario states that reviews are currently held at six-month intervals, the combination of the two attributes allows for this, as well as any other review interval. This makes this selection flexible. It also prohibits more than one reviewer per employee review.

C.   EmployeeID, ReviewerID

Incorrect

C.   Selecting the combination of EmployeeID and ReviewerID allows only one review per employee and reviewer combination. An employee could be reviewed more than once by the same reviewer, which this selection would prohibit.

D.   EmployeeID, ReviewDate, ReviewGrade

Incorrect

D.   There is no need to include the ReviewGrade in the primary key. This allows for multiple reviews on the same date for an employee, if there were different ReviewGrades. This is not the situation described in the scenario.

MCS

70-229.01.02.004

D

You are creating a model of employees in a large international company. Each country provides a government-generated number given to every person authorized to work. Other information contained in the employee entity would be family name, given name, phone number, and address. Which of the following is the best choice to uniquely identify each employee?

A.   Family name and given name

Incorrect

A.   The selection of family name and given name for a primary key will not uniquely identify an employee because there can be many people with the same name within a large company. This selection also has one other problem, it does not meet the stability requirement for a good primary key. In many countries names change over time according to marital status. Every attempt should be made to ensure that primary keys do not change.

B.   Government number

Incorrect

B.   Although this might seem to be a good solution, there is no guarantee that these numbers are not recycled when a previous person has passed on (this is the case with Social Security numbers in the United States). Also, there is no guarantee that a number given out by one country will be unique in the world, or that there is a common type for these numbers (that is, numeric versus alphanumeric).

C.   Family name, given name, phone number, and address

Incorrect

C.   Although the chances are small that more than one person with the same name would have the same address and phone number, this is not impossible. (Consider George Forman and his sons, George, George, George, George, and George.) This selection does not uniquely identify an employee because the same person can be entered into the table with a different address. This selection also suffers from the instability problem and is simply too much data to use as a primary key, where the same data would need to be migrated to any tables that would refer to this entity.

D.   System-generated identity number

Correct

D.   Because there does not appear to be a simple, applicable, and stable candidate for a unique identifier for this entity, the best option is to create a unique number, generated by the system.

MCM

70-229.01.02.005

A, B, and D

You are designing a logical model for museum art collections. You have currently designed Museum, Artist, and ArtObject entities. You want to accomplish the following results:

• Museums can be recognized as being part of a larger museum.
• The museum owning artwork created by a particular artist can be known.
• An art object that was created by more than one artist can be stored.
• A surrogate key can be used as the primary key for the museum.
• No museum can have the same name as another museum.

You perform the following actions:

• You add a one-to-many relationship between the Artist and ArtObject entities.
• You add a surrogate primary key attribute called MuseumID to the Museum entity.
• You add an attribute called ParentMuseumID and a self-referencing foreign key relationship to the Museum entity.
• You add a surrogate primary key attribute called ArtistID to the Artist table.
• You add a foreign key relationship between the ArtObject and the Museum entities.

Which of the results do these actions produce? (Choose all that apply.)

A.   You ensure that museums can be recognized as being part of a larger museum.

Correct

A.   By adding an attribute called ParentMuseumID and creating a self-referencing foreign key relationship to the Museum entity, you ensure that any museum can have another museum as a parent. This implementation allows only hierarchical relationships, and allows a museum to have only one parental museum.

B.   You ensure that the museum holding artwork created by a particular artist can be known.

Correct

B.   Because there is a relationship between the Artist and ArtObject entities, and a relationship between the ArtObject and Museum entities, the museum that is holding artwork by a particular artist can be determined through these relationships.

C.   You ensure that an art object that was created by more than one artist can be stored.

Incorrect

C.   Because there is only a one-to-many relationship between the Artist and ArtObject entities, there can only be one Artist associated with a particular ArtObject. To accomplish this result, a many-to-many relationship would need to be created and, in SQL Server 2000, this would require an associated table with a row for each Artist for an ArtObject.

D.   You use a surrogate key for the primary key for the museum.

Correct

D.   Adding the MuseumID attribute to the Museum entity and then defining this attribute as the primary key produces this result.

E.   You ensure that no museum can have the same name as another museum.

Incorrect

E.   There is nothing in the listed actions that ensures that only one museum has a particular name. The primary key, which will be unique by definition, has been defined on the surrogate key. A unique constraint will need to be added to the Museum name to produce this result. Whenever a surrogate key is used in an entity definition, it is important to also add any required unique constraints on candidate keys; otherwise, duplicate data can be entered.

MCM

70-229.01.02.006

A and B

You are creating a logical data model for tracking grants to nonprofit organizations for your company. Some organizations are part of another organization. Grants can be split between organizations. You need to be able to track how much money was given to any particular organization at any level. You design the model with the foreign key relationships as follows. Which of the following is true? (Choose all that apply.)

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   You are able to determine all of the organizations that are part of any particular parent organization.

Correct

A.   The self-referencing foreign key relationship on Organization allows for all child organizations of a parent organization to be known.

B.   Grants that are awarded to any organization can be determined, including any organizations for which it is considered a parent organization.

Correct

B.   Because there is a many-to-many relationship between the Organization entity and the Grant entity, all grants awarded to an institution can be determined through the GrantRecipient entity. Grants can also be summarized for parent organizations because of the self-referencing relationship on Organization.

C.   The organization that awards the grant can be determined.

Incorrect

C.   This information is not contained in the model because the only relationship between an Organization and a Grant is through the GrantRecipient entity.

1.3 Design attribute domain integrity.

Determining the domain integrity for an attribute can include determining the data type, nullability, and other constraints on the attribute. Determining attribute domains ensures that only the allowable values can be entered into the database. This includes whether alphanumeric or numeric data is allowed, whether a value can be null, and any other type of constraint or rule on the data, such as a range of allowable values.

Generally, determining a data type is straightforward. Discussions with the business about current and future possible values usually lead to accurate data type evaluations. The same is true for range constraints and other check constraints. Consistency in data type selection is important and can prevent problems later in the development process.

However, determining whether an attribute can be null is more problematic. Excessive usage of nulls can lead to difficulties in programming because SQL queries must be written differently when a null is expected. Any expression that contains a null will evaluate as unknown.

The question of whether nulls should be used in a relational database is fairly controversial. Because the use of nulls requires a three-valued logic that not all developers are aware of, you might find that to help ensure accuracy in your systems, you need to limit null usage. The following tables show the three-valued logic truth tables.

In general, it is best to use nulls very sparingly, and as little as possible for any attribute that participates in a relationship. One good practice is to allow nulls only in descriptive text attributes. Some people use codes to represent the different types of nulls. For example, some attributes need to be null because they might not be applicable in every case; or the attribute value is not known at the moment, but can be considered required later; or the attribute value truly is not known. Using a code to represent these three "null states" can help with data integrity and data cleaning.

MCS

70-229.01.03.001

D

Evaluate the Customer and Repair entities shown below. A customer with an order should not be deleted. How should you enforce a relationship between the Customer and Repair entities to ensure integrity?

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   Add a CHECK constraint to the RepairID attribute.

Incorrect

A.   A CHECK constraint enforces strict values that can be stored in an attribute. A constraint contains a logical expression that must be evaluated before an INSERT into an attribute. Having a CHECK constraint could enforce some logical expression on the RepairID but would not prevent deletion of a customer.

B.   Add a CHECK constraint to the CustomerID attribute.

Incorrect

B.   Using a CHECK constraint on the CustomerID could enforce other rules on the CustomerID attribute but would not prevent the deletion of a customer.

C.   Add a DELETE trigger to the Repair entity.

Incorrect

C.   A DELETE trigger is a stored procedure executed each time you delete a record from the Repair entity. This trigger would execute when you delete a record on the Repair entity but would not prevent a deletion of a record from the Customer entity.

D.   Add the CustomerID attribute to the Repair entity with a FOREIGN KEY constraint to the Customer entity.

Correct

D.   You can add a CustomerID attribute to the Repair entity and configure it to have a FOREIGN KEY constraint referencing the Customer entity. You cannot delete a record from the referenced entity if you still have rows in the referencing entity. In this case, the Repair entity references the Customer entity, and rows cannot be deleted from the Customer entity until they are deleted first from the Repair entity.

MCS

70-229.01.03.002

C

You have to enforce relationships between the entities shown below. Telephone sales agents are compensated according to the number of customer calls and the duration of the time spent on the phone talking to customers. The agents are using different extensions as they move around. How would you enforce the relationship between the Agent records and the CallDetails to calculate commissions?

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   Add a FOREIGN KEY constraint on the Extension attribute in CallDetails referencing the Agent entity.

Incorrect

A.   Although the attribute referencing the agents by extension would be sufficient if they could not use more than one extension, the Extension attribute cannot be used correctly. You could associate agents with calls they did not make.

B.   Create a procedure to query the Agent and CallDetails entities to determine the commissions.

Incorrect

B.   You cannot query the CallDetails and Agents entities to arrive at a commission because there is nothing to associate the two entities with each other. The Extension attribute cannot be used because the agents can use multiple extensions. You cannot tell who used the phone at that extension without adding an AgentID attribute to the CallDetails entity.

C.   Add AgentID to the CallDetails entity and create a FOREIGN KEY constraint referencing the Agent entity.

Correct

C.   Adding the AgentID to the CallDetails entity and then creating the FOREIGN KEY constraint referencing the Agent entity is the correct relationship as described by the scenario.

MCS

70-229.01.03.003

A

You are developing a database model for a high school. Evaluate the Student and State entities below. What should you change in the model to maintain data integrity?

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   Add a FOREIGN KEY constraint to the Student entity referencing the State entity’s StateID attribute.

Correct

A.   Using a FOREIGN KEY constraint will guarantee data validity and guarantee that records cannot be deleted from the State entity while records referencing them exist in the Student entity.

B.   Add a CHECK constraint to the State entity on the StateID attribute.

Incorrect

B.   A CHECK constraint can verify the validity of the new value. In this example you need to make sure that you cannot insert a student record without the correct StateID. A CHECK constraint placed on the State entity will validate data in the State entity but not in the Student entity. It will not guarantee data integrity between the two entities.

C.   Add a CHECK constraint to the Student entity on the StateID attribute.

Incorrect

C.   Although a CHECK constraint can determine which data can be placed in the Student entity based on the State entity, it does not stop deletion of records from the State entity.

D.   Add a FOREIGN KEY constraint to the State entity referencing the Student entity’s StateID attribute.

Incorrect

D.   A FOREIGN KEY constraint is correct, but the constraint should be placed on the Student entity referencing the State entity, not the other way around.

MCS

70-229.01.03.004

D

You are building a rent-a-car tracing program. Evaluate the Customers, Agreements, and Cars entities shown below. Each customer can rent one or more cars. Each car can be rented to only one customer at a time. A car not owned by the rent-a-car office cannot be rented. How should you enforce integrity between the Agreements and Cars entities?

Click the Exhibit button to view the logical model below.

Examine the following logical model.

(Image Unavailable)

A.   Add a CHECK constraint to the AgreementID attribute.

Incorrect

A.   A CHECK constraint enforces domain integrity by restricting the values that can be entered into an attribute. A CHECK constraint contains a logical expression that must be true in order to accept the new value. A CHECK constraint on the AgreementID attribute would not enforce integrity between the Agreements entity and the Cars entity.

B.   Add the AgreementID attribute to the Cars entity with a FOREIGN KEY constraint referencing the Agreements entity.

Incorrect

B.   If a car could be rented only once, you could add the AgreementID to the Cars entity. Because a car can be rented many times, this will not work.

C.   Add an INSERT trigger to the rental entity.

Incorrect

C.   An INSERT trigger is a stored procedure that is executed automatically whenever you try to insert a record into the entity. A trigger can include most Transact-SQL (T-SQL) statements. Although a trigger could be placed on the Agreements entity to roll back the transaction if a car that is being rented is not in stock, a trigger should be used only when a constraint does not provide the necessary functionality.

D.   Add the CarID attribute to the Agreements entity with a FOREIGN KEY constraint referencing the Cars entity.

Correct

D.   You can add a CarID attribute to the Agreements entity and configure that attribute as a foreign key. This process enforces integrity between the Agreements entity and the Cars entity.

MCS

70-229.01.03.005

F

You have designed an entity for tracking orders and have added all of the attributes, including one called UnitPrice that will be used to store the actual price charged per product. Which data types and nullability should be set for the UnitPrice attribute to ensure that the results of any mathematical functions on UnitPrice will always be accurate?

A.   A nullable integer.

Incorrect

A.   An integer is not the appropriate data type for UnitPrice because a price requires decimals. UnitPrice should also be non-nullable because a price is always known. It might have a UnitPrice of zero, but it would not be an unknown value.

B.   A nullable float.

Incorrect

B.   A float is not the appropriate data type for UnitPrice because float data types are approximate and can have rounding errors in calculations, something that is frowned upon in money transactions. UnitPrice should also be non-nullable because a price is always known. It may have a UnitPrice of zero, but it would not be an unknown value.

C.   A nullable money.

Incorrect

C.   Money is the correct data type for UnitPrice. This data type was designed specifically for monetary data, and it will not have the rounding problems that a float data type will exhibit. However, UnitPrice should be non-nullable because a price is always known. It may have a UnitPrice of zero, but it would not be an unknown value.

D.   A non-nullable integer.

Incorrect

D.   An integer would not be the appropriate data type for UnitPrice because a price will require decimals. However, UnitPrice should be non-nullable because a price is always known.

E.   A non-nullable float.

Incorrect

E.   A float would not be the appropriate data type for UnitPrice because float data types are approximate and can have rounding errors in calculations, something that is frowned upon in money transactions. However, UnitPrice should be non-nullable because a price is always known.

F.   A non-nullable money.

Correct

F.   Money is the correct data type for UnitPrice. This data type was designed specifically for monetary data, so it will not have the rounding problems that a float data type will exhibit. UnitPrice should also be non-nullable because a price is always known. It may have a UnitPrice of zero, but it would not be an unknown value. This combination is correct for the business function and rules required by the question.

MCS

70-229.01.03.006

B

You are designing a logical model to collect information about the composition of fragrances for perfumeries. This model will be used to create a database containing the recipes for different fragrances, and the percentages of each ingredient in the fragrance recipe will need to be tracked. You design an entity called PerfumeComposition and an entity called Ingredient. The percentage of each ingredient in a particular perfume will be stored in the PerfumeComposition table, one row for each ingredient. You want to ensure that the sum of the ingredient percentages for a perfume does not exceed 100. How can you accomplish this?

A.   Add a CHECK constraint to the PerfumeComposition table that would disallow percentages of more than 100 per cent for a perfume.

Incorrect

A.   It would not be possible to add a CHECK constraint to check across rows, summing the percentage.

B.   Add a trigger to the PerfumeComposition table that would reject any percentage that would cause the sum of all of the percentages for a perfume to exceed 100 per cent.

Correct

B.   This is the only way to implement this business rule in the database.

C.   Create a procedure to calculate any offending percentage every night and to delete the rows from the PerfumeComposition table.

Incorrect

C.   It is best not to change data after it has been entered, but rather to disallow its entry in the first place if it is incorrect. Deleting offending rows is a dangerous strategy because data would quickly become incorrect and invalid.

D.   Add a unique constraint to the percentage attribute in the PerfumeComposition table to disallow rows with the same percentages.

Incorrect

D.   This does not address the sum of the rows in the PerfumeComposition table for a particular perfume exceeding 100 per cent.