On the MCSD Architecture exam (70-100) there are a number of questions on SQL tables. I have two questions about what Microsoft Wants as the answer to these. This may be a question on the mechanics of answering the questions.

First. Given two tables A and B with a many to many relationship linked by a join table X: Clearly the join table only needs the following:

A-Key B-Key

which is a unique relationship. Now normally I would create this table with three attributes:

X-Key A-Key B-Key

Where A-Key and B-Key are foreign Keys and X-Key is a unique Key. I would put a unique constraint on the combination A-Key|B-Key. In fact it is possible to create the table X having

A-Key B-Key

With the primary key being A-Key|B-Key. If this is done on the exam it is necessary to answer as follows:

Answer #1

X-Table
Primary Key
A-Key
B-Key
Foreign Key
A-Key
B-Key
Attributes

If my preferred structure is used it is necessary to answer:

Answer #2

X-Table
Primary Key
X-Key
Foreign Key
A-Key
B-Key
Attributes

I believe that I missed this question on the exam using answer #2 does anyone know what Microsoft believed the correct answer is?

The other question has to do with Primary Keys. Given a table of peoples names and addresses that DOES NOT include the SSN but does include:

First Name
Last Name
Date of Birth
Street Address
City, State ...

There are two ways to lay out the "People Table"

Layout #1

People Table
Primary Key
People-Key
Foreign Key
Attributes
First Name
Last Name
Date of Birth
Street Address
City, State ...

Layout #2

People Table
Primary Key
First Name
Last Name
Date of Birth
Street Address
Foreign Key
Attributes
City, State ...


Now I would always use the first layout even though the second does save a few bits. I believe that Microsoft wanted the second layout on this table. Does anyone know?