70-441 Designing Database Solutions by Using Microsoft SQL Server 2005 Practice Test

Demo Question 3.

Bilco, Scenario OVERVIEW: Background Bilco started off as an Insurance Provider. Bilco is currently a dual purpose company after a decision was taken by the Bilco management to diversify. The services on offer from Bilco thus also include pay rolling for small companies. Physical locations The Bilco head quarters is situated in Chicago. The company has 50 branch offices in different cities all around the United States of America. Customer Services The current customer count at Bilco totals 10,000. This figure is expected to increase two-fold over the next three fiscal years. Each of these Bilco customers is different and their numbers of employees range from five to five-hundred employees per customer. The service that Bilco offers to these customers involves pay rolling their employees. Since all the customers are separate companies on their own and thus different from each other, some customers pay their employees weekly, while others pay their employees fortnightly. At present the checks are mailed to the customers. Included in this mail package is a packing slip that contains a document that shows the payroll liability as well as a report on per-employee earnings. The necessary tax forms for federal and local taxes are also prepared by Bilco. There are four benefits packages on offer currently. Though, not all customers provide benefits to their employees. Bilco currently accepts insurance forms by mail and enters them into a database for tracking. The original forms are sent to the appropriate insurance companies on a weekly basis. Since not all customers are the same with the same needs, they all require different services from Bilco. Several customers want to be able to take out custom deductions and thus requested this service. These custom deductions include retirement deductions, stock option deductions as well as other types of deductions. These deductions would be handled on a per-employee basis. Concerns Currently the number of working hours per person is frequently transposed this leads to the current system falling victim to data entry error frequently. The administration of the benefits and payroll processes requires more efficiency improvement since Bilco management is concerned that they might not be able to scale to meet their anticipated growth. TECHNICAL ENVIRONMENT: The Chicago office The Chicago office is responsible for writing the paychecks and mailing it directly to employees. The Chicago office network consists of a single Active Directory domain named bilco.com. All servers, including the database server and all client computers enjoy domain membership. There is a single database server named BILCO-DB01 that runs Microsoft SQL Server 2000. Payroll data is faxed to the Chicago office. This payroll data is entered manually by payroll administrators. A third party application is used for this purpose. Benefits administrators belong to a global group named BenefitsAdmins. At present the Benefits administrators handle all tasks manually. They provide the Payroll administrators with a list of employees who are enrolled for each benefits package and the appropriate paycheck deduction. Payroll administrators belong to a global group named PayrollAdmins. At present the Payroll administrators must manually enter the deductions. The Branch offices There are between 10 and 15 computers per branch office. These computers run Microsoft Windows XP Professional and are all workgroup members. BUSINESS REQUIREMENTS: Bilco customers requested to be allowed to enter payroll data online. Between the time the payroll data is entered and the time paychecks are available to print, a payroll administrator will check each paycheck to ensure that the appropriate deductions are taken out and that there are no obvious data entry errors. The payroll administrator will contact the customer for clarification in the event of finding a suspected error. Bilco customers must also be allowed to change employee deductions, salary, and company-specific deductions online. The procedure involved in a salary change is as follows: an e-mail must be sent to the customer, the customer must verify the change by clicking a link in the e-mail and logging on to the Web site. Then only will the salary change be committed. A decision was taken to decentralize certain operations. The checks are thus to be printed in the branch offices and delivered by courier to each customer company. A monthly report showing each employee's pay, the total pay, and details about deductions must be sent to each customer company. The customers want the ability to view the report as a summary, as well as the ability to drill down through the detailed information. Social security numbers are used to track employees. It does occur that some employees are employed by more than one company. In such a case, their payroll data and personal information must be tracked separately. Employees, even those within the same company are either paid a salary, or a HourlyRate. The HourlyRate employees earn time-and-a-half for overtime and double-time for holiday hours. There is a need to keep up to date with state and federal tax tables as these tend to change often. To this end Bilco plans to subscribe to a Web service that will make provision for updated tax table information. The Web service has two methods. 1. One method will accept a state and pay amount and returns the amount that should be deducted for state taxes. 2. The other methods will accept the pay amount and the number of deductions and returns the amount that should be deducted for federal taxes. Since the customer needs all vary, some customers prefer that their withholding reports must be generated monthly, while others prefer this service on a quarterly basis. All companies must have annual reports generated also. Some companies have different withholding schedules for state than for federal taxes. A withholding report must be accurate as of the end of the period and must be consistent even if it is regenerated at a later date. The federal and state governments require the report to be submitted in a specific Extensible Markup Language (XML) format or printed and mailed. Companies want to be able to retrieve the information in XML, Microsoft Excel, or HTML. Changes are often being made to the benefits package deductions. Bilco wants a more automated way to make effect these changes. Bilco also wants employees to be able to enter insurance form information online and to transfer insurance forms to the insurance providers electronically. The insurance providers can all accept documents using the Simple Object Access Protocol (SOAP). All insurance provider forms are different and require different data. Following is a partial schema as envisaged by Bilco management: TECHNICAL REQUIREMENTS: The main concern and consideration for Bilco is Security. Highly confidential data like employee payroll and benefits form an integral part of the business information. The new system must ensure that all sensitive data is protected from unauthorized access. Sensitive data includes: 1. Employee social security number 2. Address 3. Phone number 4. Salary 5. Full Renumeration 6. Net Renumeration 7. Insurance information All access to insurance form data must be logged. The information must include the user name of the person who attempted to access the data and the table the user accessed. Only a single computer will be available to run SQL Server 2005. You must accomplish the tasks using the fewest possible instances of SQL Server. Performance should always be optimized, and disk space should always be conserved. A period of 7 years after the end of the financial year marks the period that tax and withholding data must be kept. The database design must adhere to the third normal form (3NF. . Topic 1, Bilco (13 Questions) You are designing the schema of the SalaryInfo table for Bilco. To this end you need to design a schema that will simplify the logic required to calculate pay checks. You thus need to take a decision as to which default constraint and which nullability option you must use for the Salary attribute. What should you do?

A. Set the default constraint at 0 and use Null as the nullability option.
B. Set the default constraint at 0 and use Not Null as the nullability option.
C. Set the default constraint at 1 and use Null as the nullability option.
D. Set the default constraint at No Default and use Not Null as the nullability option.


Display Answer

Answer: B

Explanation: By setting the column as Not Null and setting the default constraint at 0, you will be able to use a simple calculation that will be required to accommodate both the salaries and hourly rate paid employees. Because the Salary will be 0 if the employee is not paid on a salary basis, the hourly rate calculation will be accurate. 1. Employees, even those within the same company are either paid a salary, or a HourlyRate. The HourlyRate employees earn time-and-a-half for overtime and double-time for holiday hours. Incorrect answers: A. This option is only partly correct in that you need to set the default constraint to 0, however, you should not allow NULL, if you do you will have to include special logic to test for nulls. C. If you set the default constraint to 1, then the hourly employees will receive $1 more per pay period than they should. In addition you should not allow nulls as the nullability options. D. If there is no default then an error will be generated if a salary is not specific unless the column allows nulls.