Bartleby Sitemap - Textbook Solutions
All Textbook Solutions for Database Systems: Design, Implementation, & Management
1RQ2RQ3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQ11RQ12RQ13RQWhat are some basic database functions that a spreadsheet cannot perform?15RQ16RQ17RQ1P3P4P5P6P7P8P9P10PUsing your schools student information system, print your class schedule. The schedule probably would contain the student identification number, student name, class code, class name, class credit hours, class instructor name, the class meeting days and times, and the class room number. Use Figure P1.11 as a template to complete the following actions. FIGURE P1.11 STUDENT SCHEDULE DATA FORMAT1RQWhat is a business rule, and what is its purpose in data modeling?How do you translate business rules into data model components?4RQExplain how the entity relationship (ER) model helped produce a more structured relational database design environment.6RQ7RQ8RQ9RQ10RQWhat is a relationship, and what three types of relationships exist?Give an example of each of the three types of relationships.What is a table, and what role does it play in the relational model?14RQ15RQ16RQ17RQ18RQ19RQ20RQ21RQ22RQ23RQ1P2P3P4P5PUsing Figure P2.6 as your guide, work Problems 68. The Tiny College relational diagram shows the initial entities and attributes for the college. FIGURE P2.6 THE TINY COLLEGE RELATIONAL DIAGRAM Identify each relationship type and write all of the business rules.7P8PTypically, a hospital patient receives medications that have been ordered by a particular doctor. Because the patient often receives several medications per day, there is a 1:M relationship between PATIENT and ORDER. Similarly, each order can include several medications, creating a 1:M relationship between ORDER and MEDICATION. a. Identify the business rules for PATIENT, ORDER, and MEDICATION. b. Create a Crows Foot ERD that depicts a relational database model to capture these business rules.10P11P12P13P14P15P16P17PWhat is the difference between a database and a table?2RQ3RQWhat are the requirements that two relations must satisfy to be considered union-compatible?Which relational algebra operators can be applied to a pair of tables that are not union-compatible?Explain why the data dictionary is sometimes called the database designers database.A database user manually notes that The file contains two hundred records, each record containing nine fields. Use appropriate relational database terminology to translate that statement.8RQ9RQ10RQ11RQ12RQUse Figure Q3.13 to answer Questions 1317. FIGURE Q3.13 THE CH03_VENDINGCO DATABASE TABLES Write the relational algebra formula to apply a UNION relational operator to the tables shown in Figure Q3.13.Create the table that results from applying a UNION relational operator to the tables shown in Figure Q3.13.Write the relational algebra formula to apply an INTERSECT relational operator to the tables shown in Figure Q3.13.Create the table that results from applying an INTERSECT relational operator to the tables shown in Figure Q3.13. FIGURE Q3.13 THE CH03_VENDINGCO DATABASE TABLESUsing the tables in Figure Q3.13, create the table that results from MACHINE DIFFERENCE BOOTH. FIGURE Q3.13 THE CH03_VENDINGCO DATABASE TABLES18RQ19RQ20RQIdentify and describe the components of the table shown in Figure Q3.21, using correct terminology. Use your knowledge of naming conventions to identify the tables probable foreign key(s). FIGURE Q3.21 THE CH03_NOCOMP DATABASE EMPLOYEE TABLEIdentify the primary keys. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLESIdentify the foreign keys. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLESCreate the ERM. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLESCreate the relational diagram to show the relationship between DIRECTOR and PLAY.26RQWhat would be the conceptual view of the INDEX table described in Question 26? Depict the contents of the conceptual INDEX table.1P2PDo the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.Describe the type(s) of relationship(s) between STORE and REGION.5P6P7P8PCreate the relational diagram to show the relationships among EMPLOYEE, STORE, and REGION. FIGURE P3.10 THE CH03_BENECO DATABASE TABLES Use the database shown in Figure P3.10 to work Problems 1016. Note that the database is composed of four tables that reflect these relationships: An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs. An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs. Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity.10P11PCreate the relational diagram to show the relationship between EMPLOYEE and JOB.13PDo the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.15P16PFor each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None. FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLES18PDo the tables exhibit referential integrity? Answer yes or no, and then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key. FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLESIdentify the TRUCK tables candidate key(s). FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLESFor each table, identify a superkey and a secondary key. FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLES22P23P24PCreate the ERD. (Hint: Look at the table contents. You will discover that an AIRCRAFT can fly many CHARTER trips but that each CHARTER trip is flown by one AIRCRAFT, that a MODEL references many AIRCRAFT but that each AIRCRAFT references a single MODEL, and so on.)26P27P28PCreate the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by either employee 105 or employee 109.Create the table that would result from applying the SELECT and PROJECT relational operators to the CHARTER table to return only the CHAR_TRIP, CHAR_PILOT, and CHAR_COPILOT attributes for charters flown by both employee 105 and employee 109.Create the table that would result from applying a DIFFERENCE relational operator of your result from Problem 29 to your result from Problem 30.1RQWhat is a strong (or identifying) relationship, and how is it depicted in a Crows Foot ERD?4RQSuppose you are working within the framework of the conceptual model in Figure Q4.5. FIGURE Q4.5 THE CONCEPTUAL MODEL FOR QUESTION 56RQ7RQDiscuss the difference between a composite key and a composite attribute. How would each be indicated in an ERD?What two courses of action are available to a designer who encounters a multivalued attribute?11RQDiscuss two ways in which the 1:M relationship between COURSE and CLASS can be implemented. (Hint: Think about relationship strength.)13RQ14RQBriefly, but precisely, explain the difference between single-valued attributes and simple attributes. Give an example of each.What are multivalued attributes, and how can they be handled within the database design? Questions 1720 are based on the ERD in Figure Q4.17. FIGURE Q4.17 THE ERD FOR QUESTION 172017RQ18RQWhat two attributes must be contained in the composite entity between STORE and PRODUCT? Use proper terminology in your answer.Describe precisely the composition of the DEPENDENT weak entitys primary key. Use proper terminology in your answer.21RQ1PCreate a complete ERD in Crows Foot notation that can be implemented in the relational model using the following description of operations. Hot Water (HW) is a small start-up company that sells spas. HW does not carry any stock. A few spas are set up in a simple warehouse so customers can see some of the models available, but any products sold must be ordered at the time of the sale. HW can get spas from several different manufacturers. Each manufacturer produces one or more different brands of spas. Each and every brand is produced by only one manufacturer. Every brand has one or more models. Every model is produced as part of a brand. For example, Iguana Bay Spas is a manufacturer that produces Big Blue Iguana spas, a premium-level brand, and Lazy Lizard spas, an entry-level brand. The Big Blue Iguana brand offers several models, including the BBI-6, an 81-jet spa with two 6-hp motors, and the BBI-10, a 102-jet spa with three 6-hp motors. Every manufacturer is identified by a manufacturer code. The company name, address, area code, phone number, and account number are kept in the system for every manufacturer. For each brand, the brand name and brand level (premium, mid-level, or entry-level) are kept in the system. For each model, the model number, number of jets, number of motors, number of horsepower per motor, suggested retail price, HW retail price, dry weight, water capacity, and seating capacity must be kept in the system.4P5P6P7P8P9P10P11C1RQWhat kinds of data would you store in an entity subtype?3RQWhat is a subtype discriminator? Give an example of its use.5RQWhat is the difference between partial completeness and total completeness?7RQAccording to the data model, is it required that every entity instance in the PRODUCT table be associated with an entity instance in the CD table? Why, or why not?9RQ10RQ11RQ12RQ13RQWhen implementing a 1:1 relationship, where should you place the foreign key if one side is mandatory and one side is optional? Should the foreign key be mandatory or optional?What is time-variant data, and how would you deal with such data from a database design point of view?16RQ1P2P3P4P5P6P7P8C9C10C1RQ2RQ3RQ4RQ5RQ6RQ7RQ8RQ9RQ10RQ11RQ12RQ13RQHow would you describe a condition in which one attribute is dependent on another attribute when neither attribute is part of the primary key?Suppose someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?A table is in __________ normal form when it is in __________ and there are no transitive dependencies.1P2P3P4P1RQExplain why the following command would create an error and what changes could be made to fix the error: SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;3RQExplain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.What is the difference between a column constraint and a table constraint?What are referential constraint actions?Rewrite the following WHERE clause without the use of the IN special operator: WHERE V_STATE IN (TN, FL, GA)Explain the difference between an ORDER BY clause and a GROUP BY clause.Explain why the following two commands produce different results: SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;What is the difference between the COUNT aggregate function and the SUM aggregate function?11RQ12RQWrite the SQL code that will create the table structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. (Note that the JOB_CODE is the FK to JOB.) ATTRIBUTE (FIELD) NAME DATA DECLARATION EMP_NUM CHAR(3) EMP_LNAME VARCHAR(15) EMP_FNAME VARCHAR(15) EMP_INITIAL CHAR(1) EMP_HIREDATE DATE JOB_CODE CHAR(3)Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P7.2.3P4PWrite the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107. After you have completed the task, examine the results and then reset the job code to its original value.Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)Write the SQL code that will restore the data to its original status: that is, the table should contain the data that existed before you made the changes in Problems 5 and 6.Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to the structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are: EMP_PCTNUMBER(4,2) PROJ_NUMCHAR(3) (Note: If your SQL implementation allows it, you may use DECIMAL(4,2) or NUMERIC (4,2) rather than NUMBER(4,2).)Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values, as shown in Figure P7.9.Using a single command sequence, write the SQL code that will change the project number (PRQJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500.11PWrite the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.)Write the two SQL command sequences required to: a.Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT. b.Copy the matching EMP_2 values into the TEMP_1 table.14P15PUsing the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database (see Figure P7.1), write the SQL code that will produce the results shown in Figure P7.16.Write the SQL code that will produce the same information that was shown in Problem 16, but sorted by the employees last name.Write the SQL code to find the average bonus percentage in the EMP_2 table you created in Problem 8.19P20PWrite the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The results of running that query are shown in Figure P7.22.23PWrite the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P7.24. (Hint: This is a nested query. If you use Microsoft Access, you can use the output shown in Figure P7.22 as the basis for the query that will produce the output shown in Figure P7.24.)25P26P27PGenerate a listing of all purchases made by the customers, using the output shown in Figure P7.28 as your guide. (Hint: Use the ORDER BY clause to order the resulting rows shown in Figure P7.28.)Using the output shown in Figure P7.29 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the list of customer purchases in Problem 28, delete the INV_DATE column, and add the derived attribute LINE_UNITS LINE_PRICE to calculate the subtotals.30P31PUse a query to compute the average purchase amount per product made by each customer. (Hint: Use the results of Problem 31 as the basis for this query.) Your output values must match those shown in Figure P7.32. Note that the average purchase amount is equal to the total purchases divided by the number of purchases per customer.33P34P35P36P37PUsing the results of the query created in Problem 37, provide a summary of customer balance characteristics, as shown in Figure P7.38.Create a query to find the balance characteristics for all customers, including the total of the outstanding balances. The results of this query are shown in Figure P7.39.40P41P42P43P44PWrite a query to display the SKU (stock keeping unit), description, type, base, category, and price for all products that have a PROD_BASE of water and a PROD_CATEGORY of sealer.46P47P48P49P50P51P52P53P54P55P56P57P58P59P60P61P62P63P64P65P66P67P68P69P70P71P72P73P74P75P76P77P78P79P80P81P82P83P84P85P86P87P88P