Please check the sample before making a payment. Pratt and Mary Z. This guide was used in CS at …. Solutions With the resulting test lab, you can add applications or services that require SQL Server.
A guide to sql solution manual Home Chicago A guide to sql solution manual. Facebook Twitter LinkedIn. If the email you sign up with is different from your own PayPal one, you can simply let us know by using the contact us form.
Always check both your Spam and Junk mail if you did not see incoming emails in your inbox. We provide free samples for any required Textbook solution or test bank to check and evaluate before making the final purchase for customer satisfaction.
By visiting our site, you agree to our privacy policy regarding cookies, tracking statistics, etc. Read more. Accept X. Pratt quantity. It builds on the success of previous editions by presenting basic SQL commands in the context of a running case in which a business uses SQL to manage orders, parts, customers, and sales reps.
The book covers the fundamentals of SQL programming using straightforward instruction and extensive hands-on exercises. Continuing with its focus on students learning the basics regardless of the database environment chosen, this edition features examples from the latest databases: Oracle 11g, Access , and MySQL.
The eighth edition expands on the use of running case studies by adding a third running case to the extensive hands-on pedagogy at the end of every chapter. If you require any further information, let me know. The analyst has also determined that a sales rep can represent many customers, but a customer must have exactly one sales rep in other words, a sales rep must represent a customer; a customer cannot be represented by zero or more than one sales reps.
All units of a particular part are stored in the same warehouse. For an order, store the order number, order date, the number and name of the customer that placed the order, and the number of the sales rep who represents that customer. Database Design Fundamentals 5. The analyst also obtained the following information concerning orders: a. There is only one customer per order.
On a given order, there is at most one line item for a given part. For example, part DR93 cannot appear on several lines within the same order. The quoted price might differ from the actual price when the sales rep discounts a certain part on a specific order. Database Design Process Example The following steps apply the design process to the requirements for Premiere Products to produce the appropriate database design: Step 1: There appear to be four entities: reps, customers, parts, and orders.
Step 2: From the collection of entities, review the data and determine the unique identifier for each entity. Step 3: The attributes mentioned in the first requirement all refer to sales reps. The requirement also mentions the number, first name, and last name of the sales rep who represents this customer.
To distinguish these attributes in the final collection, follow the name of the attribute by the name of the corresponding entity. The attributes mentioned in the third requirement refer to parts. The specific attributes include the order number, order date, number and name of the customer that placed the order, and number of the sales rep who represents the customer. According to requirement 5c, however, the quoted price might differ from the price, so you must add the quoted price to the list.
For relations corresponding to those entities identified in Step 1, you can use the name you already determined. To do so, you follow a process called normalization, in which you identify the existence of potential problems, such as data duplication and redundancy, and implement ways to correct these problems.
The goal of normalization is to convert unnormalized relations tables that satisfy the definition of a relation except that they might contain repeating groups into various types of normal forms. A table in a particular normal form possesses a certain desirable collection of properties.
Although there are several normal forms, the most common are first normal form, second normal form, and third normal form. Normalization is a process in which a table that is in first normal form is better than a table that is not in first normal form, a table that is in second normal form is better than one that is in first normal form, and so on. The goal of this process is to allow you to take a table or collection of tables and produce a new collection of tables that represents the same information but is free of problems.
First Normal Form According to the definition of a relation, a relation table cannot contain a repeating group in which multiple entries exist on a single row. However, in the database design process, you might create a table that has all the other properties of a relation, but contains a repeating group.
Removing repeating groups is the starting point when converting an unnormalized collection of data into a table that is in first normal form.
A table relation is in first normal form 1NF when it does not contain a repeating group. Figure shows a single order with multiple combinations of a part number and a corresponding number of units ordered. In Figure , this information is represented by two rows, the second and third. When you convert an unnormalized table to a table in first normal form, the primary key of the table in first normal form is usually the primary key of the unnormalized table concatenated with the key for the repeating group, which is the column in the repeating group that distinguishes one occurrence of the repeating group from another within a given row in the table.
Consider the sample of this table shown in Figure The description of a specific part, DR93 for example, occurs twice in the table. This duplication formally called redundancy causes several problems. It is certainly wasteful of space, but that is not nearly as serious as some of the other problems. These other problems are called update anomalies and they fall into four categories: 1. Chapter 2 Updates: If you need to change to the description of part DR93, you must change it twice—once in each row on which part DR93 appears.
Updating the part description more than once makes the update process much more cumbersome and time consuming. Inconsistent data: There is nothing about the design that prohibits part DR93 from having two different descriptions in the database.
In fact, if part DR93 occurs on 20 rows in the table, it is possible for this part to have 20 different descriptions in the database. Additions: When you try to add a new part and its description to the database, you will face a real problem. Certainly this is not an acceptable solution. Deletions: If you delete order from the database and it is the only order that contains part AT94, deleting the order also deletes all information about part AT For example, you would no longer know that part AT94 is an iron.
This situation leads to the definition of second normal form. Second normal form represents an improvement over first normal form because it eliminates update anomalies in these situations. A table relation is in second normal form 2NF when it is in first normal form and no nonkey column that is, a column that is not part of the primary key is dependent on only a portion of the primary key.
NOTE When the primary key of a table contains only a single column, the table is automatically in second normal form. Although it is important to identify the problem, what you really need is a method to correct it; you want to be able to convert tables to second normal form. First, take each subset of the set of columns that make up the primary key, and begin a new table with this subset as its primary key. Figure shows samples of these tables.
A description appears only once for each part, so you do not have the redundancy that existed in the original table design. Changing the description of part DR93 from Gas Range to Deluxe Range, for example, is now a simple process involving a single change. Because the description for a part occurs in a single place, it is not possible to have multiple descriptions for a single part in the database at the same time. To add a new part and its description, you create a new row in the PART table, regardless of whether that part has pending or actual orders.
Also, deleting order does not delete part number AT94 from the database because it still exists in the PART table. You can reconstruct the data in the original table from the data in the new tables. If the table were not in second normal form, some column would be dependent on only a portion of the primary key, which is impossible when the primary key is just one column. Although this table is in second normal form, Figure shows that it still possesses update problems similar to those identified for the ORDERS table shown in Figure In Figure , the sales rep name occurs many times in the table.
In addition to the problem of wasted space, you have the following update anomalies: 1. Updates: Changing the sales rep name requires changes to multiple rows in the table. Inconsistent data: The design does not prohibit multiple iterations of sales rep names in the database. For example, a sales rep might represent 20 customers and his name might be entered 20 different ways in the table. Additions: To add sales rep 87 Emily Daniels to the database, she must represent at least one customer.
If Emily does not yet represent any customers, you either cannot record the fact that her name is Emily Daniels or you must create a fictitious customer for her to represent until she represents an actual customer.
Neither of these solutions is desirable. Deletions: If you delete all the customers of sales rep 35 from the database, you will also lose all information about sales rep You have seen that tables in second normal form represent an improvement over tables in first normal form, but to eliminate problems with tables in second normal form, you need an even better strategy for creating tables.
Third normal form provides that strategy. Any column or collection of columns that determines another column is called a determinant. In fact, by definition, any candidate key is a determinant. Remember that a candidate key is a column or collection of columns that could function as the primary key. A table is in third normal form 3NF when it is in second normal form and the only determinants it contains are candidate keys.
This more recent definition, which is preferable to the original, is often referred to as Boyce-Codd normal form BCNF when it is important to make a distinction between this definition and the original definition.
This text does not make such a distinction but will take this to be the definition of third normal form. There are several steps for converting tables to third normal form. First, for each determinant that is not a candidate key, remove from the table the columns that depend on this determinant but do not remove the determinant.
Next, create a new table containing all the columns from the original table that depend on this determinant. Finally, make the determinant the primary key of this new table. This design prohibits a sales rep from having different names in the database. To add a new sales rep to the database, you add a row to the REP table; it is not necessary for a new rep to represent a customer. You can reconstruct all the data in the original table from the data in the new collection of tables.
All previously mentioned problems have indeed been solved. Step 2. A popular type of illustration used to represent the structure of a database is the entity-relationship E-R diagram.
In an E-R diagram, a rectangle represents an entity table. One-to-many relationships between entities are drawn as lines between the corresponding rectangles. Several different styles of E-R diagrams are used to diagram a database design. The important thing is that the arrows connect the appropriate rectangles. In this style, relationships are indicated in diamonds that describe the relationship. A relationship is an association between entities.
Give names to the entities and identify the unique identifiers for these entities. Next, identify the attributes for all the entities and the functional dependencies that exist among the attributes, and then use the functional dependencies to identify the tables and columns. Finally, identify any relationships between tables by looking at matching columns. To convert an unnormalized table to first normal form, remove the repeating group and expand the primary key to include the original primary key along with the key to the repeating group.
To convert a table in first normal form to a collection of tables in second normal form, take each subset of the set of columns that make up the primary key, and begin a new table with this subset as its primary key. Next, place each of the other columns with the appropriate primary key; that is, place each one with the minimal collection of columns on which it depends.
Finally, give each of these new tables a name that is descriptive of the meaning and contents of the table. There are several common styles of illustrating database design that use shapes to represent entities and connectors to illustrate the relationships between those entities.
What is an entity? What is an attribute? What is a relationship? What is a one-to-many relationship? What is a repeating group? What is a relation? What is a relational database? Describe the shorthand representation of the structure of a relational database.
Illustrate this technique by representing the database for Henry Books as shown in Figures through in Chapter 1. How do you qualify the name of a field, and when do you need to do this? What does it mean for a column to be functionally dependent on another column? What is a primary key? What is the primary key for each of the tables in the Henry Books database shown in Chapter 1?
A database at a college must support the following requirements: a. For a department, store its number and name. For an advisor, store his or her number, last name, first name, and the department number to which the advisor is assigned. For a course, store its code and description for example, MTH, Algebra. For a student, store his or her number, first name, and last name. For each course the student takes, store the course code, the course description, and the grade earned.
Assume that an advisor might advise any number of students but that each student has just one advisor. Design the database for the preceding set of requirements. Use your own experience as a student to determine any functional dependencies. List the tables, columns, and relationships. In addition, represent your design with an E-R diagram. Define first normal form.
Define second normal form. What types of problems might you encounter using tables that are not in second normal form? Define third normal form. What types of problems might you encounter using tables that are not in third normal form? Using the functional dependencies you determined in Question 11, convert the following table to an equivalent collection of tables that are in third normal form.
Indicate the changes using the shorthand representation that you would need to make to the original Premiere Products database design see Figure to support the following requirements.
A customer is not necessarily represented by a single sales rep, but can be represented by several sales reps. When a customer places an order, the sales rep who gets the commission on the order must be in the collection of sales reps who represent the customer.
Indicate the changes using the shorthand representation that you would need to make to the original Premiere Products database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order.
On the order, you need to identify both the customer placing the order and the sales rep responsible for the order. Draw an E-R diagram for the new design. Indicate the changes using the shorthand representation that you would need to make to the original Premiere Products database design in the event that the original Requirement 3 is changed as follows. In addition, for each warehouse in which the part is located, store the number of the warehouse, the description of the warehouse, and the number of units of the part stored in the warehouse.
Database Design Fundamentals 4. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form. Ray Henry is considering expanding the activities at his book stores to include movies.
He has some ideas for how he wants to do this and he needs you to help with database design activities to address these ideas. In particular, he would like you to design a database for him.
He is interested in movies and wants to store information about movies, stars, and directors in a database. He needs to be able to satisfy the following requirements: a. For each director, list his or her number, name, the year he or she was born, and the year of death if he or she is deceased. For each movie, list its number, title, the year the movie was made, and its type. For each movie star, list his or her number, name, birthplace, the year he or she was born, and the year of death if he or she is deceased.
For each movie, list its number and title, along with the number and name of all the stars who appear in it. For each movie star, list his or her number and name, along with the number and name of all the movies in which he or she stars. Determine the functional dependencies that exist in the following table, and then convert this table to an equivalent collection of tables that are in third normal form. Design a database that can satisfy the following requirements: a. Chapter 2 For each marina, list the number, name, address, city, state, and zip code.
For each boat owner, list the number, last name, first name, address, city, state, and zip code. For each marina, list all the slips in the marina. For each possible service category, list the category number and description. In addition, for each service request in a category, list the marina number and slip number for the boat receiving the service, estimated hours for the service, hours already spent on the service, and next date that is scheduled for the particular service.
For each service request, list the marina number, slip number, category description, description of the particular service, and a description of the current status of the service. In this chapter, you will begin your study of Structured Query Language SQL , which is one of the most popular and widely used languages for retrieving and manipulating database data.
In this chapter, you will learn the basics of working in SQL. You will learn how to create tables and assign data types to columns. You also will learn about a special type of value, called a null value, and 62 learn how to manage these values in tables. You will learn how to insert data into your tables after you create them.
Although the version of Oracle used in this text is the Oracle Database 10g Express Edition, the commands used in this text will work the same in any other version of Oracle. Internet Explorer will start and load the home page, which requests your username and password. Ask your instructor which username and password to use, or use the one you specified when you installed the software.
If a different Web browser starts, ask your instructor for help. Other Web browsers might not fully support the examples used in this text.
After entering this information, click the Login button. Figure shows the Oracle Database Express Edition home page. You click the icons on the home page to access the various tools. In this text, you will use the SQL tool. The other tools let you administer a database, work with database objects, and run different database utilities.
These features are beyond the scope of this book. Your instructor might assign you another username to use as your login, in which case, this name will appear on your screen. There are two ways to use the tools in the Oracle Database Express Edition. You can click the arrow for the icon to display a menu and then select an option from the menu. A submenu of commands for working with SQL scripts appears. To create a script using this approach, for example, you would click the arrow, point to SQL Scripts, and then click Create.
You will learn more about scripts later in this chapter. Clicking an arrow on an icon displays a submenu. The approach you choose is a matter of personal preference. You enter the command in the upper portion of this page, called the SQL editor pane, and then click the Run button to execute the command and display its results in the lower portion of the page, called the Results pane.
This reference is called a breadcrumb. You can click the pages in the breadcrumb to move back one or more pages. For example, to return to the home page, click Home in the breadcrumb. The word TABLE is followed by the name of the table to be created and then by the names and data types of the columns that the table contains.
The data type indicates the type of data that the column can contain for example, characters, numbers, or dates as well as the maximum number of characters or digits that the column can store. The restrictions placed on table and column names are as follows: 1. The The The The names names names names cannot exceed 30 characters.
You can think of the SQL command shown in Figure as creating an empty table with column headings for each column name. In SQL, commands are free format; that is, no rule says that a particular word must begin in a particular position on the line. This text will strive for such readability when writing SQL commands. There is one exception to this rule, however. When you are inserting character values into a table, you must use the correct case.
Figure also shows the message that appears in the Results pane after running the command, which indicates that the table was created. To run SQL commands in Access, you must first create a new query. In Access , open the database, and then create a new query in Design view.
Figure shows the command to create the REP table using Access In Access, it is common to create a table using Table Design view and then to add records to the table using Datasheet view. You still can run SQL commands when you create tables using Design view and enter data into them using Datasheet view. If you are using Management Studio and connecting to a database on your local computer, accept the default values for Server Type, Server Name, and Authentication, and then click the Connect button in the Connect to Server dialog box.
When Management Studio appears, double-click Databases, open the database on which you want to run SQL commands, and click the New Query button on the toolbar. Type the SQL command in the Query Editor window that opens, and then click the Execute button on the toolbar to execute the command.
The command shown in Figure creates the REP table and displays a message in the Messages pane to indicate that the command completed successfully. Instead of displaying a message that the table was created successfully, Oracle displays an error message about a problem that it encountered.
In reviewing the command, you see that CHAR is misspelled on line 4, line 5 is missing a comma, the CITY column was omitted, and line 7 should be deleted. If you run an SQL command and Oracle displays an error, you can use the mouse and the arrow keys on the keyboard to position the insertion point in the correct position so you can correct these errors using the same techniques that you might use in a word processor.
Then you can use the pointer to move the insertion point to the end of line 5 so you can type the missing comma, and then press Enter to insert the missing information to create the CITY column. You can use the pointer to select the contents of line 7 and then press Delete to remove it. After making these changes, you can click the Run button to execute the command again.
Another way of correcting errors in a table is to delete drop the table and start over. Suppose you do not discover the error and you execute the command, creating a table with these problems. Answer: Later in the text, you will see how to alter a table to make any necessary corrections.
Figure describes some common data types used in databases. You use the CHAR data type for columns that contain letters and special characters and for columns containing numbers that will not be used in any calculations. Unlike CHAR, only the actual character string is stored.
If a character string 20 characters long is stored in a CHAR 30 column, for example, it will occupy 30 characters 20 characters plus 10 blank spaces. However, both are legitimate choices. DATE Stores date data. The specific format in which dates are stored varies from one SQL implementation to another.
For example, the data type DECIMAL 5,2 represents a number with three places to the left and two places to the right of the decimal for example, INT Stores integers, which are numbers without a decimal part. The valid range is to You can use the contents of INT columns in calculations. This would be the appropriate choice, for example, when you want the DBMS to generate a value for a primary key. In other cases, some values might never be known—perhaps there is a customer that does not have a sales rep.
In SQL, you handle this situation by using a special value to represent cases in which an actual value is unknown, unavailable, or not applicable. This special value is called a null data value, or simply a null. When creating a table, you can specify whether to allow nulls in the individual columns. The primary key is supposed to uniquely identify a given row, and this would be impossible if nulls were allowed.
For example, if you stored two customer records without values in the primary key column, you would have no way to tell them apart. When adding rows to character columns, make sure you enclose the values in single quotation marks for example, 'Kaiser'.
You also must enter the values in the appropriate case, because character data is stored exactly as you enter it. If you need to enter an apostrophe single quotation mark into a column, you type two single quotation marks.
The command for this example is shown in Figure Note that the character strings '20','Kaiser','Valerie', and so on are enclosed in single quotation marks. When you execute the command, the record is added to the REP table. However, an easier and faster way to add these new rows to the table is to use the mouse and the keyboard to modify the previous INSERT command and execute it to add the record for the second sales rep, as shown in Figure Her name is Tina Webb.
In this case, you do not enter a value of null; you enter only the non-null values. To do so, you must indicate precisely which values you are entering by listing the corresponding columns as shown in Figure Just as with other SQL commands, the command ends with a semicolon.
In Oracle, you type the command shown in Figure , and then click the Run button to display the results. Access will display the query results in Datasheet view, as shown in Figure If the data does not fit on the screen, you can adjust the columns to best fit the data they contain by double-clicking the right edge of each column heading.
You can use the scroll bars when necessary to view data that has scrolled off the screen. If the data does not fit on the screen, you can adjust the columns to best fit the data they contain by dragging the right edge of each column heading to make it narrower or wider. Answer: The method you use to correct an error depends on the type of error you need to correct.
In many DBMSs, you save commands in a script file, or simply a script, which is a text file with the. When you use Oracle to create a script, Oracle stores the script in a special location called the script repository. If you want to save a script on the local file system, such as on a hard drive or USB drive, you can do so by downloading the script.
When you need to use a script that is stored on the local file system, but is not currently stored in Oracle, you can upload the script so you can use it in Oracle.
The following steps describe how to create and use scripts in the Oracle Database Express Edition. If you are using a different version of Oracle or another DBMS, use Help or consult the system documentation to determine how to accomplish the same tasks. To create a script: 1. The Script Editor page opens. In the Script Name text box, type a name for the script.
Click in the text box on the page to activate it, and then type the command or commands to save in the script. When necessary, click the Run button to execute the commands saved in the script.
When you are finished, click the Save button. You return to the SQL Scripts page and the script you created appears as an icon on the page. Load the Oracle Database Express Edition home page and log in. Click the icon for the desired script.
The script appears on the Script Editor page. You can use this page to view the content of the script or to make changes to it by editing the commands it contains. If you edit a script, click the Save button to save your changes. To run a script, click the Run button. The Run Script page loads and asks you to confirm running the script. Click the Run button on the Run Script page.
The Manage Script Results page opens and displays the script name and an icon in the View Results column. To see the results of the command stored in the script, click the icon in the View Results column. When you are finished using a script or no longer need to store it, you can delete it. To delete a script: 1. Follow the previous instructions to view the script.
Click the Delete button on the Script Editor page. Click the OK button to confirm the deletion. To download a script from the script repository so you can save it as a file: 1. Click the Download button. The File Download dialog box opens. In the dialog box, click the Save button, and then save the file to the desired location. Click the Close button to close the Download complete dialog box. To upload a script to the script repository: 1. The Upload Script page opens.
Click the Browse button. The Choose file dialog box opens. Navigate to and select the script file to upload. Click the Open button. If you want to upload the script with a different filename, type the new name in the Script Name text box. On the Upload Script page, click the Upload button.
An icon for the script appears on the SQL Scripts page. Open the database, create the query in SQL view, click the Save button on the Quick Access toolbar or on the menu bar , and then save the query with the desired object name. To run the query without first viewing the SQL command, right-click the query in the Navigation Pane or in the Database window , and then click Open on the shortcut menu.
The query results will appear in Datasheet view. If you want to view the SQL command before running the query, right-click the query in the Navigation Pane, and then click Design View on the shortcut menu. To run the SQL command, click the Run button. All scripts created in SQL Server are text files with the. To create a script file in SQL Server: 1. Open the appropriate database, and then click the New Query button.
Type the command or commands to save in the script. When necessary, click the Execute button to execute the commands saved in the script. When you are finished, click the Save button, navigate to the location in which to save the script, and then enter a name for the script. To view, edit, or run an existing script: 1.
Click the Open File button on the toolbar. Navigate to the folder containing the script file, select the script file, and then click the Open button in the Open File dialog box. If the Connect to Database Engine dialog box opens, click the Connect button. The script appears in the Query Editor window.
0コメント