Apply Your Knowledge

Exercises

2.1 Creating a One-to-Many Relationship

The purpose of this exercise is to show you how to create a basic one-to-many relationship between the Employee entity and the Course entity. Employees teach courses at Lloyd's Hospital. A single employee or teacher may teach many courses, such as blood composition and blood circulation, therefore creating a one-to-many relationship.

Estimated Time: 5 minutes.

1. Draw out the two entities, Employee and Course, as boxes, including the attributes defined in each as rows inside the box. Define a Primary Key element for each entity.

2. Creating a Foreign Key is required on the child table ( Course ) so that it can be related to the parent table ( Employee ). Create a Foreign Key named EmployeeID on the Course table that references the EmployeeID Primary Key in the Employee table.

3. Draw a line from the Employee entity to the Course entity, making a large dot at the end of the line pointing to Course. This indicates a one-to-many relationship, as shown in Figure 2.13.

   Figure 2.13. An example of a one-to-many relationship.

   

2.2 Creating a Many-to-Many Relationship

This exercise demonstrates how to create a many-to-many relationship between the Trainee entity and the Course entity. Trainees enroll in courses taught at Lloyd's. A single trainee can enroll in many courses, and at the same time many trainees can enroll to a single course, thus developing a many-to-many relationship.

Estimated Time: 5 minutes.

1. Draw out the entities participating in this exercise; that is, the Trainee and Course entity. Include in each entity the attributes defined as rows inside the box.

2. Recall that a many-to-many relationship can only be implemented with the help of an associative entity as an intermediate. Create a new entity between Trainee and Course and name it TraineeCourse .

3. The new associative entity needs to link both the Course and Trainee entities. To do this, the associative entity must contain two Foreign Keys: TraineeID and CourseID .

4. After you have placed these two attributes onto the associative entity, draw two lines from the associative entity to both other entities, placing a large dot at the ends of the lines pointing to the associative entity. The final model should look similar to the one in Figure 2.14.

   Figure 2.14. An example of a many-to-many relationship.

   

2.3 Deciding on Primary Keys and Attribute Characteristics

This exercise demonstrates the use of characteristics and Primary Keys, as well how to identify them.

Estimated Time: 15 minutes

1. The entity to which you will be adding column constraints is the Employee entity. If you have not already copied out the Employee entity, do so now.

2. Now you will need to decide upon a Primary Key for this entity. You could use the employee's first name as a Primary Key, but doing so allows for duplicate values. Creating a Primary Key with the IDENTITY property enforces uniqueness on any attribute. Create a new attribute named EmployeeID that will be used as the Primary Key and mark it as IDENTITY .

3. You can decide which values are required and which values do not need to be entered; use NULL and NOT NULL to do this. Place NOT NULL for the FirstName , LastName , Hire_Date , SSN , Division , and Salary attributes. Place NULL beside the ContractType , Address , and Phone attributes.

4. To disallow changes on an attribute, use the NO CHANGES property. Because changes are not going to be made on the date an employee was hired , place this value beside the Hire_Date attribute.

5. The UNIQUE characteristic specifies that duplicate values not be permitted on any given column. In the Employee entity, the Phone , SSN , and Address attributes cannot have duplicate values; that is, no employee can have the same phone number as any other employee.

6. The final entity should look similar to the one in Figure 2.15. If you have not already copied out the entity, then copy it out.

   Figure 2.15. An example of choosing attribute characteristics.

   

Review Questions

1:	How does data processing occur in the client/server model?
A1:	The client/server model can be thought of as a connection between a client program or computer requesting a service or data from the server. When the client application needs certain data, it makes a call to the server. The server searches for only the specific data sought by the client and then fulfills the client's request by sending the requested data back to the client application.
2:	What are entities, attributes, and relationships?
A2:	An entity can be thought of as a table. Entities are usually represented as boxes. Attributes are the characteristics of the table. Attributes are usually represented as rows inside an entity. They can be thought of as the columns of a table. The Primary and Foreign Keys on those tables make up the relationships. They show the relationship of one table to another in a real-world situation.
3:	Why would you want to decompose an attribute?
A3:	Decomposing an attribute provides many benefits in contrast to using general-built attributes. Decomposing an attribute is done when data integrity is a key factor and also when data query performance needs to be improved. See "Optimizing Attributes."
4:	How are Foreign Keys related to Primary Keys?
A4:	A Foreign Key is a column or multiple columns whose values match the Primary Key of another table. Foreign Keys help in the relational process between two entities by connecting to a Primary Key. When a Primary Key is created on a parent table, it is connected to another table by hooking onto the other table's Foreign Key. See the section "Foreign Keys."
5:	Name four constraints that can be placed on columns. Why would you want to place constraints?
A5:	The four constraints that can be placed on columns are Default , Null , Duplicate , and Changes . When the data model approaches perfection , certain measures must be taken to provide the most for your needs and to keep data integrity at its peak. These restrictions or constraints maintain the correctness of the data being entered.
6:	When would you consider denormalizing a database?
A6:	Denormalization is undertaken when the database lacks performance. A database that has been normalized requires more join queries to gather information from multiple tables. Therefore, CPU usage might overwhelmingly increase and cause applications to freeze. In situations like this, denormalization is appropriate.

Exam Questions

1:	You have prepared the logical design for a very large database system that will act as the back end for an Internet application, as well as being accessed from the corporate WAN. You need to support a large number of concurrent users who will be accessing the database at various bandwidth speeds. Which SQL Server technologies could assist in allowing the users access while providing good performance? Choose all that apply. Analysis services Replication Partitioned views English query Meta data services
A1:	B , C. Replication and partitioned views enable you to spread the load of a very large database system across several machines. The benefit of additional processing power and getting the data closer to the user could be recognized by both features, assuming they were properly partitioned and configured. For more information, see the section titled "The Client/Server Model."
2:	You are designing a database that will be used for a small office. The client machines have minimal hard drive space and very little RAM. Other than the database server, there are no server-grade machines available. You have chosen a SQL Server in a client/server architecture as the best implementation for this system. Which application architecture is best suited for this system? Single- tier Two-tier thin client Two-tier thick client N-tier Internet
A2:	B. With few resources on the clients, you have to make the clients as small as possible. N-tier or Internet could be potential solutions, but with the lack of sufficient processing power in the form of a server-grade machine, these would not suit this scenario. For more details, see the section "The Client/Server Model."
3:	In a large office with hundreds of users, you have several servers that are centrally located in a secured room that only IT staff can access. One server is used as a security server and also acts as a DHCP server. A second dual processor server is running SQL Server and another machine runs an e-mail system with IIS operational. The office does not permit any other user access to the Internet nor does it expose any information to the Internet through a web site. You must select an application architecture suitable to this configuration. No other software is available on the servers. What application architecture would be best suited? Single-tier Two-tier thin client Two-tier thick client N-tier Internet
A3:	E. This is a good candidate for an Internet solution because you already have an IIS server available. Whether you have Internet access or not is irrelevant because everything can be performed in-house using an HTML or XML solution across the network. For more information, see the "Internet Applications" section.
4:	You are creating a database for a large government office. The Primary Key has already been established but you need to supply another column that has to have different values for each record. What data types could you use without creating additional constraints? Choose all that apply. t imestamp bigint uniqueidentifier nvarchar sql_variant
A4:	A , C. By definition timestamp and u nique identifier data types are guaranteed to be globally unique. The timestamp is an automatically entered value. The uniqueidentifier is usually entered using a NEWID() function to generate the uniqueidentifier . For more details, see the "Using Appropriate Data Types" section.
5:	You are creating a database for a large government office. The Primary Key has already been established, but you need to supply another column that has to have different values for each record. What implementation techniques are available other than data type selection? Choose all that apply. Identity Foreign Key Unique index Unique constraint Rule
A5:	A , C , D. An Identity provides for uniqueness by incrementing a value continually, and therefore it is a standard choice for a column that requires a unique value. Unique indexes and unique constraints enforce the uniqueness of entered values and do not let any entry come into the system that already exists. For more information, see the section titled "Entity Integrity."
6:	You are putting together the logical design for a database. Tables to be included in the database are Employees , Customers , Supplies , Products , and Sales . The table used to store customer data has the following attributes: CustomerID (Primary Key), CustomerName , StreetAddress , City , State , ZipCode , BalanceOwing , SalesmanID , SalesmanName . Which of the following rules of normalization are not being maintained ? Select all that apply. First normal form Second normal form Third normal form Decomposable normal form Boyce-Codd normal form
A6:	A , C. Fullname can be broken down into firstname and lastname and is therefore not in its most decomposed form. This breaks the first normal form rule of normalization. The salesman name should not be stored in this entity because it depends on the salesmanID and not the customerID . This breaks the third normal form rule of normalization. For more information, see "Understanding Database Normalization."
7:	You are working for an automobile dealership that tracks inventory in a SQL Server database. The database contains information on the autos in stock. A partial listing of attributes is as follows : VehicleIDNo(20 char) , InvoiceNo (bigint) , Make(20 char) , Model(15 char) , Year(smalldatetime) , Colorcode(int) , PurchasePrice(smallmoney) , StickerPrices (smallmoney) . Which of the columns would you choose as a Primary Key? Use a compound key with Make , Model , and Year . Create a surrogate identity key. Use the VehicleIDNo as the key. Use the InvoiceNo as the key. Use a compound key with InvoiceNo and VehicleIDNo .
A7:	B. An automobile's VIN number, though unique, is character data and is much too large to use as a Primary Key. This is a perfect situation for an automatically incremented numeric surrogate key that will take up a lot less storage space. For more information, consult the section on "Primary Keys."
8:	You are working in a database that has a nchar(5) attribute used to store solely numeric data. You want to minimize the amount of disk space used for storage and need to select an alternative data type. Which of the following data types would you select? char(5) real smallint int bigint
A8:	D. According to byte sizes, int would take a quarter of the space of the current nchar(5) setting. Smallint would even be better, but has an upper limit of 32,767. Char(5) would cut the space used in half but is not as good as using actual numeric storage. Whenever a variable is going to contain only numbers , numeric storage is always more efficient. For more details, see the "Using Appropriate Data Types" section.
9:	You are creating a historical database that stores information about important dates in history. You need to be able to store dates from the beginning of the 14th century. You want to minimize the storage space used by the data. Which data type would you use? datetime smalldatetime bigint int char(8)
A9:	D. This is a tricky question to resolve, and if it were not for the space restriction there would be a temptation to use characters for the storage. At 8 bytes a piece (double that of int ) the easier technique would be to track days from the beginning of recorded time in an integer. (20011300)x365 1/4 requires 6 digits, and therefore int is the closest to the size required. Datetime allows dates in only the 1700s and smalldatetime in the 1900s. For more information, see the "Using Appropriate Data Types" section.
10:	You are preparing a database structure for a large construction company. At any one time the company is working on five or more job sites, and each site has between 25 and 200 homes . In charge of each site is a site supervisor who organizes the subcontractors at each phase of the building process (landscaping, framing, drywalling, electrical, plumbing, and so on). Any subcontractor who is planning on working on a given site must be found in a database of approved vendors . The company would like a structure that would allow for storage of the subcontractors' personal information and information about each site that includes the subcontractors assigned to the site. How would you set up this structure? A Site entity and a Contractor entity A Site entity, a Contractor entity, and a Site / Contractor entity A Site entity, a Process entity, and a Contractor entity A Site entity, a Contractor entity, and a Site/Process entity
A10:	B. The many-to-many relationship in this scenario occurs because many contractors can work on a single site, and a single contractor can work at many sites. The connection needs to involve both sites and contractors for an appropriate relationship to be drawn. For further information, refer to the "Understanding Entity Relationships" section.
11:	A small manufacturing company has a considerable number of data sources because no standardization has occurred across any platform. One of the database servers has SQL Server installed; the others come from a variety of vendors. For a project you are working on, you need to gather data from the SQL Server and merge it together with data from two other sources. You then need to bring the data into Excel to do some charting. How would you accomplish this? Export the data from the other sources into a comma-delimited file for import to SQL Server. Then export from SQL Server the data that is to be imported into Excel. Export the data from all three sources so that it can be imported into Excel. Use SQL Server to transfer all the data from all sources directly into Excel. Use Excel to transfer data from all three sources into a spreadsheet.
A11:	C. SQL Server is ideal for this situation. Depending on the actual details of the process, this can be performed directly using replication or data transformation services (DTS). Given the complexity of the scenario, it is likelier that DTS would be used because of its limitless flexibility. For more information, consult SQL Server Books OnLine.
12:	A small scientific laboratory needs a powerful database server to perform analysis of complex measures performed on scientists' regular experiments. The lab requires exact accuracy with all calculations because the results determine the fracture points of various metals. Which data type offers the most accurate results? smallmoney money float real decimal
A12:	C. Float gives accuracy up to 308 decimal places, which is almost ten times better than can be achieved with any of the other types. Real and decimal data types provide only 38 decimal places of accuracy at most, whereas money and smallmoney have accuracy to only the ten-thousandths. For more details, see the "Using Appropriate Data Types" section.
13:	In a large department store, an inventory database is maintained for all products sold. Data is updated frequently by multiple computer terminals. Forty computer terminals throughout the offices and store can access the database simultaneously to perform updates. You want to minimize conflict situations and reduce the load on the server as much as possible. The client systems have very little processing power of their own. What architecture would you select for this system? Single-tier Two-tier thin client Two-tier thick client N-tier Internet
A13:	D. To minimize the amount of processing performed on a server, you would need to offload the processing onto the client by way of the two-tier thick client approach, or use a middle tier component such as an n-tier model. Because the client machines don't have much processing capability, the only remaining choice is to use the n-tier approach. For more information, see the "One- and Two-Tier Systems" section.
14:	You are implementing a database for a military warehouse that needs to track the components that make up their equipment. Each piece of equipment has more than a hundred parts, each of which is made up of many smaller components. Any given aircraft has thousands of components from wheel assemblies to jet engines. A wheel assembly is made up of approximately 50 other components , each of which may come from a different supplier. Many of the separate parts are used in other components. What set of entities would be used in this structure? Suppliers, Equipment, Components, Parts Suppliers, Equipment, Components, Parts, Supplier/Parts Suppliers, Equipment, Components, Parts, Equipment/Parts Suppliers, Equipment, Components, Parts, Component/Parts Suppliers, Equipment, Components, Parts, Component/Equipment
A14:	D. The many-to-many relationship in this process occurs because each part can be used in a number of different components. You can say that each component has many parts and each part can belong to a number of different components. For more details, see the section "Understanding Entity Relationships."

Suggested Readings and Resources

1. SQL Server Books Online

   • Relational Database Components

   • Relational Database Engine Architecture

   • Database Design Considerations

2. Graeme Simsion, Data Modeling Essentials (Van Nostrand Reinhold, 1994).