Exam Prep Questions

1:	You are a database developer for Northwind Traders. A system that you are developing for your company's SQL Server 2000 will store an online transaction-processing database. Many of the entities are expected to have a very large number of data elements, and these elements will contain a large number of attributes. You want to develop a model for optimal performance. What should you do? (Choose one.) A. Develop a fully normalized structure to minimize the number of joins used to process data. B. Develop a fully normalized structure and then split entities in half, placing the same number of attributes into each entity. Create one-to-one relationships between the two entities. C. Develop a fully normalized structure and then split entities in half based on frequently and infrequently used attributes. Use appropriate relationships to connect the entities. D. Develop a fully normalized structure and then split entities in logical divisions based on commonalities within data element sets to minimize the number of records in each entity. E. Develop a denormalized structure that limits the number of attributes and records in any entity. Create many entities that will have smaller content and apply appropriate relationships.
A1:	Answer: C. Answer A is incorrect because a fully normalized structure will not usually provide for optimum performance. B is not the best approach to provide for performance because there is no measure for usage of attributes or other reasoning for the entity divisions. D, although minimizing the number of rows, does nothing to limit the number of attributes. E is taking the approach too far and will end up with more entities than desired, which will also detract from performance. C is the best approach because it addresses the entity size and performance issues and does so based on sound reasoning.
2:	You are a database developer for Northwind Traders. The company is planning to put in place a training facility for employee enrichment purposes. The single room will be scheduled based on the three shifts currently worked by the employees. A senior employee on that shift will teach each course. The entity design, which will also utilize the Employee entity, has been roughly sketched and will contain the following: Schedule Entity . ScheduleID . CourseID . EmployeeID . CourseTime Course Entity . CourseID . CourseTitle . Description . InstructorLastName . InstructorFirstName . InstructorTitle You want to promote quick response times for queries and minimize redundant data. What should you do? (Choose one.) A. Create a new table named Instructors. Include InstructorID, InstructorFirstName, InstructorLastName, and InstructorTitle attributes. Remove these elements from the Course entity and replace them with an InstructorID attribute. B. Move all the columns from the Course entity and place them in the Schedule entity, creating just a single entity. C. Remove the InstructorFirstName, InstructorLastName, and InstructorTitle attributes from the Course entity. Replace them with an EmployeeID attribute. D. Remove the CourseTime attribute from the Schedule entity and place it into the Course entity.
A2:	Answer: C. Answer A would be an appropriate answer if the instructors for the courses were external to the company. Because these instructors are internal, the personal information can be drawn from the Employee entity, making C a better choice. Making a singular entity as suggested in B would provide far too much redundant storage of data. D is incorrect because the CourseTime attribute is a function of the Schedule not of the Course.
3:	You are designing a database model for Northwind Traders that will be used in a customer order process. Customers will be able to order multiple products each time they place an order. You review the model to date, shown here: Customer .CustomerID .OrderID .CompanyName .ContactName .Address .City .Region .PostalCode Order .OrderID .ProductID .OrderDate .Quantity .Discount Product .ProductID .Description .UnitPrice You want good performance while removing redundant data. What should you do? (Each correct answer presents part of a correct solution; choose three.) A. Create a new entity named OrderDetail. Add OrderID, ProductID, Quantity, and Discount attributes to this entity. B. Ensure that a composite primary key on the OrderID and ProductID attributes is defined on the Orders entity. C. Remove the ProductID and Quantity attributes from the Order entity. D. Decompose the ContactName attribute of the Customer entity to provide for FirstName and LastName attributes. E. Move the UnitPrice attribute from the Product entity to the Order entity. F. Remove the OrderID attribute from the Customer entity and place a CustomerID attribute into the Order entity.
A3:	Answer: A, C, and F. Both A and C are part of the same principle in data modeling and remove the redundant storage of Discount information. Because a customer can make many orders, the relationship needs to be made such that an Order refers to a Customer and not the other way around. The UnitPrice attribute is a property of a Product and for that reason should stay in that entity. Though the ContactName could conceivably be decomposed, there is nothing in the problem statement that would indicate this as a requirement.
4:	You are a database consultant for Northwind Traders and you have been hired to develop a database design. This design will be used to develop a database system to be used by a brick-and-mortar store. The information to be maintained in the database will track product categories and suppliers. You create an entity named Product that contains the following: Product .ProductID .CategoryID .SupplierID .QuantityPerUnit .UnitPrice .UnitsInStock .UnitsOnOrder .ReorderLevel .Discontinued You must ensure that each product has a valid value for the Category and Supplier attributes. What should you do? (Choose one.) A. Define the Product entity to have a compound primary key that uses the ProductID, CategoryID, and SupplierID attributes. B. Create two relationships in which the SupplierID and CategoryID attributes each refer to other kernel entities. C. Create a CategorySupplier entity and relate the Product table to this entity using both the CategoryID and the SupplierID. D. Remove the CategoryID and SupplierID attributes from this entity and move them to a more valid kernel entity.
A4:	Answer: B. The CategoryID and SupplierID attributes represent foreign keys that will refer to primary keys within a kernel entity. They are in the correct entity for this purpose and should be referencing the Category and Supplier entities, respectively.
5:	You are designing a portion of the database model that will be used by Northwind Traders for its order process. A quick sketch of the model has been made and is shown here: Product .ProductID .Description .QuantityPerUnit .UnitsInStock .Unitprice .SupplierName OrderDetail .OrderID .ProductID .CustomerID .Quantity .Discount Order .OrderID .OrderDate .Freight Customer .CustomerID .CompanyName .ContactName .Address .City .Region .Phone .Fax You want to obtain speed and efficiency within the model. What changes should be made? (Choose one.) A. Decompose the ContactName attribute so that there are FirstName and LastName attributes. B. Remove the SupplierName attribute from the Product entity and place it into the Order entity. C. Remove the ProductID from the OrderDetail entity and place it into the Order entity. D. Remove the CustomerID attribute from the OrderDetail entity and place it into the Order entity. E. Remove the Quantity attribute from the OrderDetail entity. Add a Quantity column to the Order entity.
A5:	Answer: D. The CustomerID present within the OrderDetail entity would be repeated several times per Order when it is needed only once. It is therefore more appropriate for the CustomerID to be in the Order entity.
6:	You are a database developer for Northwind Traders. The company is planning a major expansion and desires to begin tracking sales information on a regional basis. Employees of the company will be assigned to a region and are permitted to perform sales only within their designated area. To accommodate this facet of the Order process, a rough sketch has been created of two entities that are to be used. These two entities are illustrated here: RegionSale .RegionSaleID .OrderID .RegionID Region .RegionID .RegionTitle .EmployeeID You would like the new entities to exist within the system as already defined. You would also like to have the system operate quickly with as little redundant information as possible. You would also like key usage to remain consistent with the rest of the system. What should you do? (Select two answers; each answer represents a part of the correct solution.) A. Create only a single entity for the process, combining the attributes from the two sketched entities. B. Create a third new entity, RegionEmployee, to connect the Region entity to the Employee entity. C. Remove the EmployeeID attribute and add a RegionID attribute to the Employee entity. D. Remove the RegionSaleID attribute from the RegionSale entity. E. Move the EmployeeID from the Region entity to the RegionSale entity. F. Remove the OrderID attribute from the RegionSale entity.
A6:	Answer: C and D. To remain consistent with the other many-to-many relationships in the system, the RegionSaleID should be removed and a compound primary key should be based on the OrderID and the RegionID. The employee should have an attribute for region and not vice versa.
7:	You are a database developer for Northwind Traders. You are creating a database model that includes an entity named Order. The Order entity contains attributes as indicated in the following sketch: Order .OrderDate .RequiredDate .ShipDate .Freight Employees take orders from the customers and receive a commission on each fulfilled order. Orders can be taken only from the listing of existing customers. Shippers can be selected only from a set of existing shippers. Which additional attributes should be included to complete the entity design? (Choose one.) A. OrderID, CustomerID, ShipperID B. OrderID, CustomerID, ShipperID, EmployeeID C. OrderID, ShipperID, EmployeeID D. OrderID, CustomerID, EmployeeID
A7:	Answer: B. The Order entity as defined by the order process relates to the Customer, Shipper, and Employee entities and should for that reason have foreign keys for each of those kernel entities.
8:	You are a database developer for Northwind Traders. The company heads would like to track customer demographics so that they can target advertising budgets and promotions. It is desired to have all budgets based on the past purchases of existing customers. The idea is to target buying patterns by one or more demographics. The demographics to be tracked are the following: gender age postal code region To implement this, area management has sketched the following entities: CustomerDemo .DemographicID .DemographicDescription CustCustomerDemo .CustomerID .DemographicID What should you do? (Choose one.) A. Leave the entities as they are to represent an appropriate many-to-many relationship. B. Combine the entities to form one singular entity. C. Add additional attributes to the CustCustomerDemo entity. D. Add additional attributes to the CustomerDemo entity.
A8:	Answer: A. This is a proper many-to-many relationship in which each customer can fit into many demographic categories and any demographic can apply to a number of customers.
9:	You are a database developer for Northwind Traders. You are designing a entity to record information about potential new products. A rough sketch of the entity is shown here: TestProduct .TestProductID .CategoryID .SupplierName .SupplierPhone .Rating You would like the new entity to be consistent with the remaining system while still storing data in an efficient manner. What should you do? (Choose one.) A. Relate the TestProduct entity to the Product entity. B. Define a compound primary key that uses both the TestProductID attribute and the CategoryID attribute. C. Ensure that the TestProductID is unique from an existing ProductID. D. Replace the SupplierName and SupplierPhone attributes with a SupplierID.
A9:	Answer: D. There is already a Supplier entity in the system that could easily be used in a relationship with the newly defined TestProduct entity. Placement of the SupplierName and SupplierPhone attributes into this new entity is therefore redundant.
10:	As part of the preparation for the database model for Northwind Traders, you have sketched out a set of entities. The sketch as it stands is shown here: Order .OrderID .CustomerID .EmployeeID .OrderDate .RequiredDate .ShippingDate .Shipvia .Freight .Shipname .ShipAddress .ShipCity .ShipRegion .ShipPostalCode .ShipCountry .ShipperID OrderDetail .OrderID .ProductID .UnitPrice .Quantity Product .ProductID .ProductName .SupplierID .CategoryID .QuantityPerUnit .UnitPrice .UnitsInStock .UnitsOnOrder .ReorderLevel .Discontinued Supplier .SupplierID .CompanyName .ContactName .ContactTitle .Address .City .Region .PostalCode .Country .Phone .Fax .HomePage You are now setting up the relationships for the entities. How should these be applied? (Each correct answer represents part of the solution; choose three.) A. Create a one-to-many relationship on the Product entity that references the OrderDetail entity. B. Create a many-to-one relationship on the Product entity that references the OrderDetail entity. C. Create a one-to-many relationship on the Product entity that references the Supplier entity. D. Create a many-to-one relationship on the Product entity that references the Supplier entity. E. Create a one-to-many relationship on the Order entity that references the OrderDetail entity. F. Create a many-to-one relationship on the Order entity that references the OrderDetail entity.
A10:	Answer: A, D, and E. There will be many OrderDetail elements for each Order, many products to a supplier, and many OrderDetail elements that refer to any product.