70-229 SQL Server Design Question of the Day

2lazybutsmart

OK Guys, looks like this QOD is going quite well. Well just to let everyone know that you can post a question on this thread. Just make sure you include the exam objective since we might want to compile all questions into a PDF or practice exams later on. I've edited the last 3 questions to include their exam objectives. Good Luck ~ 2lbs.

Here we go (we'll start with easy stuff first):

In SQL Server, a normalized database is a database that:

A) doesn't have more than the required amount of users
includes as few tables as possible
C) complies with the 1st, 2nd, and 3rd normal forms
D) runs on a P4 Server or higher

Objective: 1.1.2 Specify Degree of Normalization

answer when some people take a shot.

2lbs.

ghaouf

C) complies with the 1st, 2nd, and 3rd normal forms


did some research

2lazybutsmart

Answer is of course..... tommorow

RussS

Yup - have to agree with ghaouf C)

2lazybutsmart

and of course... the answer was C.

OK, Q#2

In SQL Server, a junction table establishes:

A) a One-to-One relationship between tables
a One-to-Many relationship between tables
C) a Many-to-One relationship between tables
D) a Many-to-Many relationship between tables

Objective 1.2.2 Specify attributes that reference other entities.

Answer..tommorow

ghaouf

C) a Many-to-One relationship between tables
D) a Many-to-Many relationship between tables

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/repospr/rpgeneratingvws_1d4n.asp

2lazybutsmart

nice try. and of course the correct answer is (one of the multiple choices) D) Many-to-Many relationship.

a One-to-Many or Many-to-One relationship (same thing) relates two tables (i.e. A and .Table A can have many matching rows in table B, whereas table B can have only one matching row in table A. This type of a relationship is a typical Primary Key - Foreign Key relationship.

a Many-to-Many relationship on the other hand, is used to relate two tables (i.e. table A and . But in this instance, table A can have many matching rows in table B, and vice versa. This type of a relationship is creating by using a third table (a junction table) that has a One-to-Many relationship with both tables, thus, maintaining entity integrity and normalization.

2lbs.

Q# 3

A customers table contains a firstname and lastname column, an address column, and a SSN column. A composite PRIMARY KEY constraint has been defined on the firstname and lastname columns. What type of constraint would be the most appropirate to uniquely identify values in the Social Security Number (SSN) column.

A) PRIMARY KEY constraint
FOREIGN KEY constraint
C) CHECK constraint
D) UNIQUE constraint

Objective 1.2.1 Specify attributes that uniquely identify records.

Answer tommorow.

2lbs.

2lazybutsmart

anybody.....

dumb terminal

Umm, I think the answer would be UNIQUE Constraint

2lazybutsmart

and of course the answer was.... D) UNIQUE Constraint. Good job dumb terminal. (ahm.... are dumb terminals still around? thought thas was back in NT 3)

OK, Here we go Q#4

Your supervisor wants you to create a database to store information about the company’s employees and departments. Sam, your fellow colleague, has already created the database for you. You are now required to create the Employees and Departments tables. The Employees table should include the EmployeeID, FirstName, LastName, Age, Salary, and DepartmentID columns. The EmployeeID column should be an automatically generated sequence number and should be used to uniquely identify employees. Your company doesn't hire employees younger than 16 years of age. Every employee should be part of an existing department. The departments table should include the DepartmentID, DepartmentName, and ManagerID columns. The DepartmentID column should be an automatiacally generated sequence number and should be used to uniquely identify departments. Every department manager is also an employee in the company. The ManagerID column should contain the EmployeeID of an existing employee.

Employees table column data types: FirstName varchar(20), LastName varchar(20), Age int, Salary money, DepartmentID int

Departments table column data types: DepartmentID int, DepartmentName varchar(20), ManagerID int.

Which of the following SQL Server scripts would you use to create the above mentioned solution?


A)
CREATE TABLE Departments
(DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName varchar(20),
ManagerID int CONSTRAINT FK_Dep_MngrID REFERENCES Employees(EmployeeID))

CREATE TABLE Employees
(EmployeeID int IDENTITY PRIMARY KEY,
FirstName varchar(20),
LastName varchar(20),
Age int CHECK (Age >= 16),
Salary money,
DepartmentID int CONSTRAINT FK_Emp_DepID REFERENCES Departments (DepartmentID))


CREATE TABLE Departments
(DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName varchar(20))

CREATE TABLE Employees
(EmployeeID int IDENTITY REFERENCES Departments(ManagerID),
FirstName varchar(20),
LastName varchar(20),
Age int DEFAULT (Age >= 16),
Salary money,
DepartmentID int CONSTRAINT FK_Emp_DepID REFERENCES Departments (DepartmentID))

ALTER TABLE Departments
Add ManagerID int CONSTRAINT FK_Dep_MngrID REFERENCES Employees(EmployeeID)

C)
CREATE TABLE Departments
(DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName varchar(20))

CREATE TABLE Employees
(EmployeeID int IDENTITY PRIMARY KEY,
FirstName varchar(20),
LastName varchar(20),
Age int CHECK (Age >= 16),
Salary money,
DepartmentID int CONSTRAINT FK_Emp_DepID REFERENCES Departments (DepartmentID))

ALTER TABLE Departments
Add ManagerID int CONSTRAINT FK_Dep_MngrID REFERENCES Employees(EmployeeID)

D)
CREATE TABLE Departments
(DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName varchar(20))

CREATE TABLE Employees
(EmployeeID int IDENTITY PRIMARY KEY,
FirstName varchar(20),
LastName varchar(20),
Age int DEFAULT (Age >= 16),
Salary money)

ALTER TABLE Departments
Add ManagerID int CONSTRAINT FK_Dep_MngrID REFERENCES Employees(EmployeeID)

ALTER TABLE Employees
Add DepartmentID int CONSTRAINT FK_Emp_DepID REFERENCES Departments(DepartmentID)

Exam Objective:
2.2 Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views.

2.2.1 Specify table characteristics. Characteristics include cascading actions, CHECK constraints, clustered, defaults, FILLFACTOR, foreign keys, nonclustered, primary key, and UNIQUE constraints.

OK Guys...... Answer tommorow..... Good Luck

dumb terminal

Umm, I think the correct answer is C

2lazybutsmart

dumb terminal wrote:

Umm, I think the correct answer is A

hmmm.... the thing is a bit tricky... take a good look at it.

2lazybutsmart

dumb terminal wrote:

Umm, I think the correct answer is C

Good. and of course, the correct answer was C).

Explanation

In SQL Server, tables that reference or depend on each other should be created in the right order. In this scenario, the departments table references the employees table and vice versa. Creating the departments table with all the right references and column definitions without the existence of the employees table will produce a dependency error. Same thing applies to the employees table. The only right way to create these tables is as shown in choice C). The following are the steps used to create the tables

1- You create the departments table first without the MangerID column that references the EmployeeID column of the employees table.

2- You create the employees table including the DepartmentID column since the departments table has already been created.

3- You alter the departments table to include the ManagerID column and create a reference to the EmployeeID column of the employees table. The purpose you alter the departments table and create a new ManagerID column instead of including the ManagerID in the departments table definition in the first place without the reference to the employees table is because you can not alter a column and define a new constraint on it. (I hope you get that part)

Choices A,B, and D are all wrong because either they don't create the tables in the right order, or they have syntax errors. Like choice B, a Foreign Key is created on the EmployeeID column instead of a Primary Key. This not only nullifies the entity integrity of the table, it also causes the creation of other tables that reference the EmployeeID column to fail. Remember, a foreign key can only reference a primary key in another table or a unique column. Choice D) is wrong because a default constraint (which is wrong because of the check constraint-like expression) is created on the age column instead of a check constraint.


OK. Q#5.

Very easy this time. If you understood Q#4, you should be able to answer this without much ado.

After you have successfully created the tables outlined in the solution, you are now required to delete these tables. Which of the following statement’s sequence is correct?

A)
1- Drop Employees table 2- Drop Departments table 3- Remove Constraint FK_Dep_MngrID


1- Remove Constraint FK_Dep_MngrID 2- Drop Employees table 3- Drop Departments table

C)
1- Remove Constraint FK_Dep_MngrID 2- Drop Departments table 3- Drop Employees table

D)
1- Drop Departments table 2- Drop Employees table

Exam Objective:
2.2 Create and alter database objects. Objects include constraints, indexes, stored procedures, tables, triggers, user-defined functions, and views.

OK guys..... Good Luck... and watch OUT
A bonus point for anyone who posts the T-SQL statement itself.

2lbs.

dumb terminal

B is correct

ALTER TABLE Departments DROP CONSTRAINT FK_Dep_MngrId
DROP TABLE Employees
DROP TABLE Departments

2lazybutsmart

and of course the correct answer was . good job dumb terminal, you got the bonus point..

Well here comes a very easy question. Although it might just turn out to a little combersome.

Q#6

Passwords and other confidential information have been passed out to unauthorized personnel in your company. Since more than 5000 employees work in your company, your supervisor want's you to produce a report that lists all employees who are database designers/admins, work in the same department, and live in the same ZIP code area. Data is in the employees table. Which of the following join methods would you use to produce the required report?

A) an RIGHT OUTER JOIN to join the Employees table to itself.
an INNER JOIN to join the Employees table to itself.
C) a LEFT JOIN to join the Employees table to itself.
D) a FULL OUTER JOIN to join the Employees table to itself.

Exam Objective:
3.3 Retrieve, filter, group, summarize, and modify data by using Transact-SQL.

Good Luck...

omhoge

Hey there.

No more answers? I say B to the last one.

so... I don't agree with your answer for Q#3.
Given those columns, a primary key on SSN makes a lot more sense to me. It assures uniqueness and keys the table.
I'm curious... :
Why did you think that was a bad idea and incorrect answer?
Thanks for the great questions so far.
May we have some more sir?

2lazybutsmart

that's the right answer omhoge. well done.

Explanation:
An self join in used to join a table to itself using aliases in an inner join SQL statement. Since all colums from both table are identical, aliases uniquely define columns participating in the JOIN.

Given those columns, a primary key on SSN makes a lot more sense to me. It assures uniqueness and keys the table.
I'm curious...

Well, the trick here is, and is in every exam, that you only answer what the question is really asking you. Of course you're right. The SSN column is the most unique field in a customers table. Yet, the scenario stipulates that a composite primary key has already been defined on the first and last name columns. Now the catch-22 in the question is that if you define a primary key on the ssn column, you'd be right if the scenario didn't explain the creation of a primary key already, but since it has, you're caught in the "No table can have more than one primary key" rule. This is ,of course, the whole point the question is driving at.

Thanks for the great questions so far.
May we have some more sir?

You're welcome. You certainly may.

omhoge

Thank you 2lazybutsmart.

That line "Well, the trick here is, and is in every exam, that you only answer what the question is really asking you." made a big impact. I think I'm tending to read into the questions automatically.

Seems there's a shift in thinking from project work to the exam mindset. And those "pick the best one" kind of questions get me, my answer would usually work but is not what they considered "best".

much appreciated!

2lazybutsmart

OK guys... here we go, we've got some fresh questions. So I'd like to serve 'em right off the pan.

Q#6

Your company’s customers are either individual people or companies. Since information pertaining to both types is different, your DB designer has created two tables to accommodate for the difference and has named them after their types, respectively. Customer details information are included in the CustomerDetails table and Company details information are included in the CompanyDetails table.

The CustomerDetails table’s columns are as follows:

FirstName
MiddleName
LastName
Telephone

The CompanyDetails table’s columns are as follows:

CompanyName
Telephone

You are required to concatenate the two tables into a single result set with two columns: CustomerName and Telephone. Which of the following SQL statements would you use?

(Select only one)

A)SELECT FirstName, MiddleName, LastName as CustomerName, Telephone
FROM CustomerDetails
UNION
SELECT CompanyName as CustomerName, Telephone
FROM CompanyDetails

B)SELECT FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as CustomerName, Telephone
FROM CustomerDetails
UNION
SELECT CompanyName as CustomerName, Telephone
FROM CompanyDetails

C)SELECT FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as CustomerName, Telephone
FROM CustomerDetails
UNION
SELECT CompanyName, Telephone
FROM CompanyDetails

D)SELECT FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as CustomerName, Telephone
FROM CustomerDetails
UNION
SELECT Telephone, CompanyName as CustomerName
FROM CompanyDetails

latoni

Hey y'all....I'm taking a shot at this. I say the answer is B. When using the Union Operator, the columns being selected must match.

sturlapaty

I agree with latoni. Is the answer B ?

bher2

i agree too, the answer is B

2lazybutsmart

Yes. the correct answer is indeed B.

2lbs.

elover_jm

hey man don't stop this thread.............

keep it going...because i'm in my second month preparing for 229