Skip to main content

Bartleby Sitemap - Textbook Solutions

All Textbook Solutions for Database Systems: Design, Implementation, & Management

1RQ 2RQ 3RQ 4RQ 5RQ 6RQ 7RQ 8RQ 9RQ 10RQ 11RQ 12RQ 13RQ What are some basic database functions that a spreadsheet cannot perform?15RQ 16RQ 17RQ 1P 4P 5P 6P 7P 8P 9P 10P Using 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 FORMAT 1RQ What is a business rule, and what is its purpose in data modeling?How do you translate business rules into data model components?4RQ Explain how the entity relationship (ER) model helped produce a more structured relational database design environment.6RQ 7RQ 8RQ 9RQ 10RQ What 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?14RQ 15RQ 16RQ 17RQ 18RQ 19RQ 21RQ 22RQ 1P 2P 3P 4P 5P Using 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.7P 8P Typically, 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.10P 11P 12P 13P 14P 15P 16P 17P What is the difference between a database and a table?2RQ 3RQ What 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.8RQ 9RQ 10RQ 11RQ 12RQ Use 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 TABLES Using the tables in Figure Q3.13, create the table that results from MACHINE DIFFERENCE BOOTH. FIGURE Q3.13 THE CH03_VENDINGCO DATABASE TABLES 18RQ 19RQ 20RQ Identify 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 TABLE Identify the primary keys. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLES Identify the foreign keys. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLES Create the ERM. FIGURE Q3.22 THE CH03_THEATER DATABASE TABLES Create the relational diagram to show the relationship between DIRECTOR and PLAY.26RQ What would be the conceptual view of the INDEX table described in Question 26? Depict the contents of the conceptual INDEX table.1P 2P Do 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.5P 6P 7P 8P Create 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.10P 11P Create the relational diagram to show the relationship between EMPLOYEE and JOB.13P Do 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.15P 16P For 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 TABLES 18P Do 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 TABLES Identify the TRUCK tables candidate key(s). FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLES For each table, identify a superkey and a secondary key. FIGURE P3.17 THE CH03_TRANSCO DATABASE TABLES 22P 23P 24P Create 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.)26P 27P 28P 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 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.1RQ What is a strong (or identifying) relationship, and how is it depicted in a Crows Foot ERD?4RQ Suppose you are working within the framework of the conceptual model in Figure Q4.5. FIGURE Q4.5 THE CONCEPTUAL MODEL FOR QUESTION 5 6RQ 7RQ Discuss 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?What is a derived attribute? Give an example. What are the advantages or disadvantages of storing or not storing a derived attribute?11RQ Discuss two ways in which the 1:M relationship between COURSE and CLASS can be implemented. (Hint: Think about relationship strength.)13RQ 14RQ Briefly, 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 1720 17RQ 18RQ What 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.21RQ 1P Create 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.4P 5P 6P 7P 8P 9P 10P 11C 1RQ What kinds of data would you store in an entity subtype?3RQ What is a subtype discriminator? Give an example of its use.5RQ What is a disjoint subtype? Give an example.What is the difference between partial completeness and total completeness?8RQ According 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?10RQ 11RQ 12RQ 13RQ 14RQ When 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?17RQ 1P 2P 3P 4P 5P 6P 7P 8C 9C 10C 1RQ 2RQ 3RQ 4RQ 5RQ 6RQ 7RQ 8RQ 9RQ 10RQ 11RQ 12RQ 13RQ How 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.1P 2P 3P 4P 1RQ Explain 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;What is a cross join? Give an example of its syntax.What three join types are included in the outer join classification?Using tables named T1 and T2, write a query example for each of the three join types you described in Question 4. Assume that T1 and T2 share a common column named C1.6RQ 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?11RQ 12RQ 13RQ 14RQ 15RQ What does it mean to say that SQL operators are set-oriented?17RQ 18RQ 19RQ 20RQ Given the employee information in Question 19, list the query output for the INTERSECT query.Given the employee information in Question 19, list the query output for the EXCEPT (MINUS) query of EMPLOYEE to EMPLOYEE_1.23RQ 24RQ 25RQ What Oracle function should you use to calculate the number of days between your birth date and the current date?What string function should you use to list the first three characters of a companys EMP_LNAME values? Give an example using a table named EMPLOYEE. Provide examples for Oracle and SQL Server.28RQ Given the structure and contents of the Ch07_ConstructCo database shown in Figure P7.1, use SQL commands to answer the following problems. Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity.Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value. FIGURE P7.2 THE QUERY RESULTS FOR PROBLEM 2 Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by the employees last name.4P Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.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, sorted by employee number. The results of running that query are shown in Figure P7.6. FIGURE P7.6 TOTAL HOURS AND CHARGES BY EMPLOYEE 7P 8P 9P 10P Generate a listing of all purchases made by the customers, using the output shown in Figure P7.11 as your guide. Sort the results by customer code, invoice number, and product description. FIGURE P7.11 LIST OF CUSTOMER PURCHASES 12P 13P Modify the query in Problem 13 to include the number of individual product purchases made by each customer. (In other words, if the customers invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in Figure P7.14, sorted by customer code. FIGURE P7.14 CUSTOMER TOTAL PURCHASE AMOUNTS AND NUMBER OF PURCHASES 15P 16P 17P 18P 19P 20P 21P 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.22. FIGURE P7.22 BALANCE SUMMARY FOR ALL CUSTOMERS 23P 24P 25P Find the total value of the product inventory. The results are shown in Figure P7.26. FIGURE P7.26 TOTAL VALUE OF ALL PRODUCTS IN INVENTORY 27P Write 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 (Figure P7.28). FIGURE P7.28 WATER-BASED SEALERS 29P 30P 31P 32P 33P 34P 35P 36P 37P 38P 39P 40P 41P 42P 43P 44P 45P 46P 47P 48P 49P 50P 51P 52P 53P 54P 55P 56P 57P 58P 59P 60P 61P 62P 63P 64P 65P 66P 67P 68P 69P 70P 71P 72P 73P

Page: [1][2][3]