+ Reply to Thread
Results 1 to 24 of 24
  1. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #1

    Default 70-229 SQL Server Design Question of the Day

    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
    B) 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.
    Reply With Quote Quote  

  2. SS -->
  3. Senior Member
    Join Date
    Feb 2003
    Location
    who wants to know
    Posts
    327

    Certifications
    A+ , MCP 76-210 & 76-215 &70-218, network+ , MCSA, MCSE, Security+, Server+, CCNP, CCNA
    #2
    C) complies with the 1st, 2nd, and 3rd normal forms


    did some research
    Reply With Quote Quote  

  4. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #3
    Answer is of course..... tommorow
    Reply With Quote Quote  

  5. Grumpy old bugger RussS's Avatar
    Join Date
    Sep 2002
    Location
    Hamilton - New Zealand
    Posts
    2,109
    #4
    Yup - have to agree with ghaouf C)
    Reply With Quote Quote  

  6. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #5
    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
    B) 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
    Reply With Quote Quote  

  7. Senior Member
    Join Date
    Feb 2003
    Location
    who wants to know
    Posts
    327

    Certifications
    A+ , MCP 76-210 & 76-215 &70-218, network+ , MCSA, MCSE, Security+, Server+, CCNP, CCNA
    #6
    C) a Many-to-One relationship between tables
    D) a Many-to-Many relationship between tables

    http://msdn.microsoft.com/library/de...ngvws_1d4n.asp
    Reply With Quote Quote  

  8. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #7
    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 B) .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 B). 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
    B) FOREIGN KEY constraint
    C) CHECK constraint
    D) UNIQUE constraint

    Objective 1.2.1 Specify attributes that uniquely identify records.

    Answer tommorow.

    2lbs.
    Reply With Quote Quote  

  9. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #8
    anybody.....
    Reply With Quote Quote  

  10. Junior Member
    Join Date
    Jul 2003
    Posts
    4

    Certifications
    A+, CNA
    #9
    Umm, I think the answer would be UNIQUE Constraint
    Reply With Quote Quote  

  11. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #10
    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))

    B)
    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
    Reply With Quote Quote  

  12. Junior Member
    Join Date
    Jul 2003
    Posts
    4

    Certifications
    A+, CNA
    #11
    Umm, I think the correct answer is C
    Reply With Quote Quote  

  13. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #12
    Quote Originally Posted by dumb terminal
    Umm, I think the correct answer is A
    hmmm.... the thing is a bit tricky... take a good look at it.
    Reply With Quote Quote  

  14. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #13
    Quote Originally Posted by dumb terminal
    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

    B)
    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.
    Reply With Quote Quote  

  15. Junior Member
    Join Date
    Jul 2003
    Posts
    4

    Certifications
    A+, CNA
    #14
    B is correct

    ALTER TABLE Departments DROP CONSTRAINT FK_Dep_MngrId
    DROP TABLE Employees
    DROP TABLE Departments
    Reply With Quote Quote  

  16. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #15
    and of course the correct answer was B). 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.
    B) 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...
    Reply With Quote Quote  

  17. Junior Member
    Join Date
    Oct 2003
    Posts
    2
    #16

    Default Question #6 try and #3 why

    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?
    Reply With Quote Quote  

  18. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #17
    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.
    Reply With Quote Quote  

  19. Junior Member
    Join Date
    Oct 2003
    Posts
    2
    #18

    Default What it is asking

    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!
    Reply With Quote Quote  

  20. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #19
    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
    Reply With Quote Quote  

  21. Junior Member
    Join Date
    Mar 2004
    Posts
    1
    #20

    Default Answer to #6

    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.
    Reply With Quote Quote  

  22. Junior Member
    Join Date
    Dec 2004
    Location
    CO
    Posts
    1
    #21
    I agree with latoni. Is the answer B ?
    Reply With Quote Quote  

  23. Junior Member
    Join Date
    Dec 2004
    Posts
    1
    #22
    i agree too, the answer is B
    Reply With Quote Quote  

  24. Senior Member
    Join Date
    May 2003
    Location
    Toronto
    Posts
    1,160

    Certifications
    MCSD, RHCSA
    #23
    Yes. the correct answer is indeed B.

    2lbs.
    Reply With Quote Quote  

  25. Senior Member
    Join Date
    Sep 2005
    Location
    Toledo, Ohio
    Posts
    357

    Certifications
    Bsc. IT A+,NET+,CST,CNST, MCSA 2003,MCDBA: Next on the hitlist --> LPIC, OCP
    #24
    hey man don't stop this thread.............

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

+ Reply to Thread

Social Networking & Bookmarks