CH-7-9 Introduction to Structured Query Language (SQL) – Database Systems: Design, Implementation, and Management : Book Guide

This Document Contains Chapters 7 to 9 Chapter 7 Introduction to Structured Query Language (SQL) NOTE Several points are worth emphasizing: • We have provided the SQL scripts for both chapters 7 and 8. These scripts are intended to facilitate the flow of the material presented to the class. However, given the comments made by our students, the scripts should not replace the manual typing of the SQL commands by students. Some students learn SQL better when they have a chance to type their own commands and get the feedback provided by their errors. We recommend that the students use their lab time to practice the commands manually. • Because this chapter focuses on learning SQL, we recommend that if you use Microsoft Access, that you use the Microsoft Access SQL window to type SQL queries. Using this approach, you will be able to demonstrate the interoperability of standard SQL. For example, you can cut and paste the same SQL command from the SQL query window in Microsoft Access, to Oracle SQL * Plus and to MS SQL Query Analyzer. This approach achieves two objectives:  It demonstrates that adhering to the SQL standard means that most of the SQL code will be portable among DBMSes.  It also demonstrates that even a widely accepted SQL standard is sometimes implemented with slight distinctions by different vendors. For example, the treatment of date formats in Microsoft Access and Oracle is slightly different. • Chapter 7 is all about SELECT queries to retrieve data. We choose to start with SELECT queries because simple SELECT queries are conceptually easy to understand, which gives students a good place to start. Also, most database jobs will require students to work with databases that are already in place. We emphasize to students the importance to learning the data model in which they work. This also provides an opportunity to highlight the importance of good naming conventions when creating the database design. Students can see how helpful it is to have a proper naming convention for attributes within an entity, the importance of having the name of the foreign key reflect the table from which it originates, and the benefits of descriptive entity and attribute names. Answers to Review Questions 1. Explain why it would be preferable to use a DATE data type to store date data instead of a character data type. The DATE data type uses numeric values based on the Julian calendar to store dates. This makes date arithmetic such as adding and subtracting days or fractions of days possible (as well as numerous special date-oriented functions discussed in the next chapter!). 2. 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; The command would generate an error because an aggregate function is applied to the P_QOH attribute but V_CODE is neither in an aggregate function nor in a GROUP BY clause. This can be fixed by either 1) placing V_CODE in an appropriate aggregate function based on the data that is being requested by the user, 2) adding a GROUP BY clause to group by values of V_CODE (i.e. GROUP BY V_CODE), 3) removing the V_CODE attribute from the SELECT clause, or 4) removing the Sum aggregate function from P_QOH. Which of these solutions is most appropriate depends on the question that the query was intended to answer. 3. What is a CROSS JOIN? Give an example of its syntax. A CROSS JOIN is identical to the PRODUCT relational operator. The CROSS JOIN is also known as the Cartesian product of two tables. For example, if you have two tables, AGENT, with 10 rows and CUSTOMER, with 21 rows, the CROSS JOIN resulting set will have 210 rows and will include all of the columns from both tables. Syntax examples are: SELECT * FROM CUSTOMER CROSS JOIN AGENT; or SELECT * FROM CUSTOMER, AGENT If you do not specify a join condition when joining tables, the result will be a CROSS Join or PRODUCT operation. 4. What three join types are included in the OUTER JOIN classification? An OUTER JOIN is a type of JOIN operation that yields all rows with matching values in the join columns as well as all unmatched rows. (Unmatched rows are those without matching values in the join columns). The SQL standard prescribes three different types of join operations: LEFT [OUTER] JOIN RIGHT [OUTER] JOIN FULL [OUTER] JOIN. The LEFT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the left table. (The left table is the first table named in the FROM clause.) The RIGHT [OUTER] JOIN will yield all rows with matching values in the join columns, plus all of the unmatched rows from the right table. (The right table is the second table named in the FROM clause.) The FULL [OUTER] JOIN will yield all rows with matching values in the join columns, plus all the unmatched rows from both tables named in the FROM clause. 5. Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named C1. LEFT OUTER JOIN example: SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1; RIGHT OUTER JOIN example: SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C1; FULL OUTER JOIN example: SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.C1 = T2.C1; 6. What is a recursive join? A recursive join is a join in which a table is joined to itself. 7. Rewrite the following WHERE clause without the use of the IN special operator. WHERE V_STATE IN (‘TN’, ‘FL’, ‘GA’) WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA' Notice that each criteria must be complete (i.e. attribute-operator-value). 8. Explain the difference between an ORDER BY clause and a GROUP BY clause. An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those rows into the specified order. A GROUP BY clause does impact the rows that are returned by the query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate functions. 9. Explain why the two following commands produce different results. SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; The difference is in the order of operations. The first command executes the Count function to count the number of values in V_CODE (say the count returns "14" for example) including duplicate values, and then the Distinct keyword only allows one count of that value to be displayed (only one row with the value "14" appears as the result). The second command applies the Distinct keyword to the V_CODEs before the count is taken so only unique values are counted. 10. What is the difference between the COUNT aggregate function and the SUM aggregate function? COUNT returns the number of values without regard to what the values are. SUM adds the values together and can only be applied to numeric values. 11. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? Both a WHERE clause and a HAVING clause can be used to eliminate rows from the results of a query. The differences are 1) the WHERE clause eliminates rows before any grouping for aggregate functions occurs while the HAVING clause eliminates groups after the grouping has been done, and 2) the WHERE clause cannot contain an aggregate function but the HAVING clause can. 12. What is a subquery, and what are its basic characteristics? A subquery is a query (expressed as a SELECT statement) that is located inside another query. The first SQL statement is known as the outer query, the second is known as the inner query or subquery. The inner query or subquery is normally executed first. The output of the inner query is used as the input for the outer query. A subquery is normally expressed inside parenthesis and can return zero, one, or more rows and each row can have one or more columns. A subquery can appear in many places in a SQL statement: • as part of a FROM clause, • to the right of a WHERE conditional expression, • to the right of the IN clause, • in a EXISTS operator, • to the right of a HAVING clause conditional operator, • in the attribute list of a SELECT clause. Examples of subqueries are: INSERT INTO PRODUCT SELECT * FROM P; DELETE FROM PRODUCT WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_AREACODE = ‘615’); SELECT V_CODE, V_NAME FROM VENDOR WHERE V_CODE NOT IN (SELECT V_CODE FROM PRODUCT); 13. What are the three types of results a subquery can return? A subquery can return 1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns). 14. What is a correlated subquery? Give an example. A correlated subquery is subquery that executes once for each row in the outer query. This process is similar to the typical nested loop in a programming language. Contrast this type of subquery to the typical subquery that will execute the innermost subquery first, and then the next outer query … until the last outer query is executed. That is, the typical subquery will execute in serial order, one after another, starting with the innermost subquery. In contrast, a correlated subquery will run the outer query first, and then it will run the inner subquery once for each row returned in the outer subquery. For example, the following subquery will list all the product line sales in which the “units sold” value is greater than the “average units sold” value for that product (as opposed to the average for all products.) SELECT INV_NUMBER, P_CODE, LINE_UNITS FROM LINE LS WHERE LS.LINE_UNITS > (SELECT AVG(LINE_UNITS) FROM LINE LA WHERE LA.P_CODE = LS.P_CODE); The previous nested query will execute the inner subquery once to compute the average sold units for each product code returned by the outer query. 15. Explain the difference between a regular subquery and a correlated subquery. A regular, or uncorrelated subquery, executes before the outer query. It executes only once and the result is held for use by the outer query. A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query. Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query. 16. What does it mean to say that SQL operators are set-oriented? The description of SQL operators as set-oriented means that the commands work over entire tables at a time, not row-by-row. 17. The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition? Union compatible means that the relations yield attributes with identical names and compatible data types. That is, the relation A(c1,c2,c3) and the relation B(c1,c2,c3) have union compatibility if both relations have the same number of attributes, and corresponding attributes in the relations have “compatible” data types. Compatible data types do not require that the attributes be exactly identical – only that they are comparable. For example, VARCHAR(15) and CHAR(15) are comparable, as are NUMBER (3,0) and INTEGER, and so on. Note that this is a practical definition of union-compatibility, which is different than the theoretical definition discussed in Chapter 3. From a theoretical perspective, corresponding attributes must have the same domain. However, the DBMS does not understand the meaning of the business domain so it must work with a more concrete understanding of the data in the corresponding columns. Thus, it only considers the data types. 18. What is the difference between UNION and UNION ALL? Write the syntax for each. UNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice that for two rows to be duplicated, they must have the same values in all columns. To illustrate the difference between UNION and UNION ALL, let’s assume two relations: A (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean) and B (ID, Name) with rows (1, River, 2, Lake, and 3, Ocean). Given this description, SELECT * FROM A UNION SELECT * FROM B will yield: ID Name 1 Lake 2 River 3 Ocean 1 River 2 Lake while SELECT * FROM A UNION ALL SELECT * FROM B will yield: ID Name 1 Lake 2 River 3 Ocean 1 River 2 Lake 3 Ocean 19. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query? (List the query output.) The query output will be: Alice Cordoza John Cretchakov Anne McDonald Mary Chen 20. Given the employee information in Question 19, what is the query output for the UNION ALL query? (List the query output.) The query output will be: Alice Cordoza John Cretchakov Anne McDonald John Cretchakov Mary Chen 21. Given the employee information in Question 19, what is the query output for the INTERSECT query? (List the query output.) The query output will be: John Cretchakov 22. Given the employee information in Question 19, what is the query output for the MINUS query? (List the query output.) This question can yield two different answers. If you use SELECT * FROM EMPLOYEE MINUS SELECT * FROM EMPLOYEE_1 the answer is Alice Cordoza Ann McDonald If you use SELECT * FROM EMPLOYEE_1 MINUS SELECT * FROM EMPLOYEE the answer is Mary Chen 23. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE. Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively. The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and 126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the VEND_CODE in the VENDOR table.) Given that information, what would be the query output for: Because the common attribute is V_CODE, the output will only show the V_CODE values generated by the each query. a. A UNION query based on these two tables? 125,124,123,126 b. A UNION ALL query based on these two tables? 125,124,124,123,123,124,125,126 c. An INTERSECT query based on these two tables? 123,124,125 d. A MINUS query based on these two tables? If you use PRODUCT MINUS VENDOR, the output will be NULL If you use VENDOR MINUS PRODUCT, the output will be 126 24. Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query? MINUS queries are analogous to algebraic subtraction – it results in the value that existed in the first operand that is not in the second operand. UNION queries are analogous to algebraic addition – it results in a combination of the two operands. (These analogies are not perfect, obviously, but they are helpful when learning the basics.) Addition and UNION have the commutative property (a + b = b + a), while subtraction and MINUS do not (a – b ≠ b – a). 25. What MS Access/SQL Server function should you use to calculate the number of days between the current date and January 25, 1999? SELECT DATE()-#25-JAN-1999# NOTE: In MS Access you do not need to specify a FROM clause for this type of query. 26. What Oracle function should you use to calculate the number of days between your birthday and the current date? The SYSDATE keyword can be used to retrieve the current date from the server. By subtracting your birthdate from the current date, using date arithmetic, the number of dates will be returned. Note that in Oracle, the SQL statement requires the use of the FROM clause. In this case, you may use the DUAL table. (The DUAL table is a dummy “virtual” table provided by Oracle for this type of query. The table contains only one row and one column so queries against it can return just one value.) 27. What string function should you use to list the first three characters of a company’s EMP_LNAME values? Give an example, using a table named EMPLOYEE. In Oracle, you use the SUBSTR function as illustrated next: SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE; In SQL Server, you use the SUBSTRING function as shown: SELECT SUBSTRING(EMP_LNAME, 1, 3) FROM EMPLOYEE; 28. What two things must a SQL programmer understand before beginning to craft a SELECT query? Before crafting a SELECT query, the SQL programmer must 1) understand the data model in which the query will operate, and 2) the problem being solved. Data models are often complex to the point that knowing what data is available, the meaning of that data, and how to transform the data to produce the desired results will require the programmer to become very familiar with the data model before the query can be created. Problem statements that seem clear to users can often be interpreted in many ways, so it is important for the programmer to understand exactly what the user is requesting. Problem Solutions All of the problems in the Problem section require writing SQL code. Since there are minor differences in the code based on the DBMS used, solutions for all of the problems are provided in separate files for Oracle, MySQL, and Microsoft SQL Server. Solutions for Microsoft Access are provided in .mdb files for each data model used in the problem section. The files are located in the “Teacher” data files that accompany the book, and are named as follows: Oracle: Ch07_ProblemSolutions_ORA.txt MySQL: Ch07_ProblemSolutions_MySQL.txt SQL Server: Ch07_ProblemSolutions_SQL.txt MS Access: Ch07_ConstructCo.mdb Ch07_Fact.mdb Ch07_LargeCo.mdb Ch07_SaleCo.mdb Chapter 8 Advanced SQL NOTE Several points are worth emphasizing: • Chapter 8 focuses on creating database structures and manipulating the data in tables. The material covers creating databases and objects within the databases, such as tables, indexes, and views. • We have provided the SQL scripts for this chapter. These scripts are intended to facilitate the flow of the material presented to the class. However, given the comments made by our students, the scripts should not replace the manual typing of the SQL commands by students. Some students learn SQL better when they have a chance to type their own commands and get the feedback provided by their errors. We recommend that the students use their lab time to practice the commands manually. • In this chapter, the stored procedures and triggers are executed in the Oracle RDBMS. Unlike SQL, which is standardized, languages for creating stored procedures and triggers are not standardized across different DBMS products. For example, while PL/SQL in Oracle and TSQL (Transact SQL) in SQL Server perform similar tasks in roughly similar ways, the syntax and keywords for these languages are very different. This material is presented to help students understand the nature of the tasks performed by these program modules. The programs shown in the text are to illustrate these concepts. The concepts are common across most DBMS products, even though the actual syntax for the languages is different. Even if instructors do not use Oracle or do not teach the syntax of PL/SQL that is presented in the chapter, students still benefit from understanding the need for programs such as the ones presented and the nature of how these programs are implemented. Answers to Review Questions 1. What type of integrity is enforced when a primary key is declared? Creating a primary key constraint enforces entity integrity (i.e. no part of the primary key can contain a null and the primary key values must be unique). 2. Explain 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. An attribute that contains only digits may be properly defined as character data when the values are nominal; that is, the values do not have numerical significance but serve only as labels such as ZIP codes and telephone numbers. One easy test is to consider whether or not a leading zero should be retained. For the ZIP code 03133, the leading zero should be retained; therefore, it is appropriate to define it as character data. For the quantity on hand of 120, we would not expect to retain a leading zero such as 0120; therefore, it is appropriate to define the quantity on hand as a numeric data type. 3. What is the difference between a column constraint and a table constraint? A column constraint can refer to only the attribute with which it is specified. A table constraint can refer to any attributes in the table. 4. What are “referential constraint actions”? Referential constraint actions, such as ON DELETE CASCADE, are default actions that the DBMS should take when a DML command would result in a referential integrity constraint violation. Without referential constraint actions, DML commands that would result in a violation of referential integrity will fail with an error indicating that the referential integrity constrain cannot be violated. Referential constraint actions can allow the DML command to successfully complete while making the designated changes to the related records to maintain referential integrity. 5. What is the purpose of a CHECK constraint? A CHECK constraint is used to limit the values that can appear in an attribute. It performs the function of enforcing a domain. 6. Explain when an ALTER TABLE command might be needed. ALTER TABLE is used to modify the structure of an existing table by adding, removing, or modifying column definitions and, in some cases, constraints. Many database structures have long, useful lives in an organization. It is not uncommon for a database to exist in organizational systems for decades. If the existing database structure needs to be modified to accommodate changes in business requirements or the integration of new systems, the existing structure will be modified with ALTER TABLE commands. This preserves the existing data in the table, as opposed to dropping the table and then re-creating it. 7. What is the difference between an INSERT command and an UPDATE command? The INSERT command is used to add a new row to a table. The UPDATE command changes the values in attributes of an existing row. UPDATE will not increase the number of rows in a table, but INSERT will. 8. What is the difference between using a subquery with a CREATE TABLE command and using a subquery with an INSERT command? Using a subquery with a CREATE TABLE command is a DDL command and will create a new database table. The table will be structured to match the structure of the data returned by the subquery, and the data from the subquery will be placed in the table. Therefore, using a subquery with CREATE TABLE will both create the structure and place data inside that structure. Using a subquery with an INSERT command is a DML command and will add data to an existing table. This operation requires that the target table where the data should be stored must already exist. The programmer must ensure that the structure of the data being returned by the subquery is appropriate in terms of data types and constraints for the structure of the table where the results are to be stored. 9. What is the difference between a view and a materialized view? A view defines a query to retrieve data, but it does not create another copy of the data. Whenever the view is used, the defined query is executed to retrieve the current data from the base tables. A materialized view also defines a query, but it also stores another copy of the data in the materialized view. When the materialized view is used, the data from the secondary copy is returned. A materialized view must be periodically be refreshed as the data in the base tables changes over time. 10. What is a sequence? Write its syntax. A sequence is a special type of object that generates unique numeric values in ascending or descending order. You can use a sequence to assign values to a primary key field in a table. A sequence provides functionality similar to the Auto number data type in MS Access. For example, both, sequences and Auto number data types provide unique ascending or descending values. However, there are some subtle differences between the two: • In MS Access an Auto number is a data type; in Oracle a sequence is a completely independent object, rather than a data type. • In MS Access, you can only have one Auto number per table; in Oracle you can have as many sequences as you want and they are not tied to any particular table. • In MS Access, the Auto number data type is tied to a field in a table; in Oracle, the sequence-generated value is not tied to any field in any table and can, therefore, be used on any attribute in any table. The syntax used to create a sequence is: CREATE SEQUENCE CUS_NUM_SEQ START WITH 100 INCREMENT BY 10 NOCACHE; 11. What is a trigger, and what is its purpose? Give an example. A trigger is a block of PL/SQL code that is automatically invoked by the DBMS upon the occurrence of a data manipulation event (INSERT, UPDATE or DELETE.) Triggers are always associated with a table and are invoked before or after a data row is inserted, updated, or deleted. Any table can have one or more triggers. Triggers provide a method of enforcing business rules such as: • A customer making a credit purchase must have an active account. • A student taking a class with a prerequisite must have completed that prerequisite with a B grade. • To be scheduled for a flight, a pilot must have a valid medical certificate and a valid training completion record. Triggers are also excellent for enforcing data constraints that cannot be directly enforced by the data model. For example, suppose that you must enforce the following business rule: If the quantity on hand of a product falls below the minimum quantity, the P_REORDER attribute must the automatically set to 1. To enforce this business rule, you can create the following TRG_PRODUCT_REORDER trigger: CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER BEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN ON PRODUCT FOR EACH ROW BEGIN IF :NEW.P_ONHAND <= :NEW.P_MIN THEN NEW.P_REORDER := 1; ELSE :NEW.P_REORDER := 0; END IF; END; 12. What is a stored procedure, and why is it particularly useful? Give an example. A stored procedure is a named block of PL/SQL and SQL statements. One of the major advantages of stored procedures is that they can be used to encapsulate and represent business transactions. For example, you can create a stored procedure to represent a product sale, a credit update, or the addition of a new customer. You can encapsulate SQL statements within a single stored procedure and execute them as a single transaction. There are two clear advantages to the use of stored procedures: 1. Stored procedures substantially reduce network traffic and increase performance. Because the stored procedure is stored at the server, there is no transmission of individual SQL statements over the network. 2. Stored procedures help reduce code duplication through code isolation and code sharing (creating unique PL/SQL modules that are called by application programs), thereby minimizing the chance of errors and the cost of application development and maintenance. For example, the following PRC_LINE_ADD stored procedure will add a new invoice line to the LINE table and it will automatically retrieve the correct price from the PRODUCT table. CREATE OR REPLACE PROCEDURE PRC_LINE_ADD (W_LN IN NUMBER, W_P_CODE IN VARCHAR2, W_LU NUMBER) AS W_LP NUMBER := 0.00; BEGIN -- GET THE PRODUCT PRICE SELECT P_PRICE INTO W_LP FROM PRODUCT WHERE P_CODE = W_P_CODE; -- ADDS THE NEW LINE ROW INSERT INTO LINE VALUES(INV_NUMBER_SEQ.CURRVAL, W_LN, W_P_CODE, W_LU, W_LP); DBMS_OUTPUT.PUT_LINE('Invoice line ' || W_LN || ' added'); END; Problem Solutions All of the problems in the Problem section require writing SQL or PL/SQL code. Since there are minor differences in the code based on the DBMS used, solutions for problems are provided in separate files for Oracle, MySQL, and Microsoft SQL Server. Solutions for Microsoft Access are provided in .mdb files for each data model used in the problem section. A very few of the problems do not apply to all DBMS products. For example, MySQL is installed in “autocommit” mode by default, therefore, issuing COMMIT commands are not necessary. On the other hand, Oracle does not use autocommit by default and does require COMMIT commands to make DML command results permanent in the database. Therefore, instructions about issuing commands to make DML changes permanent do not apply to MySQL, but are necessary for Oracle. Also, since only PL/SQL is presented in the text for creating stored procedures and triggers, problems that require creating these types of modules are only provided in PL/SQL for Oracle. The files are in the “Teacher” data files that accompany the book, and are named as follows: Oracle: Ch08_ProblemSolutions_ORA.txt MySQL: Ch08_ProblemSolutions_MySQL.txt SQL Server: Ch08_ProblemSolutions_SQL.txt MS Access: Ch08_AviaCo.mdb Ch08_ConstructCo.mdb Ch08_MovieCo.mdb Ch08_SaleCo.mdb Ch08_SimpleCo.mdb Chapter 9 Database Design Discussion Focus What is the relationship between a database and an information system, and how does this relationship have a bearing on database design? An information system performs three sets of services: • It provides for data collection, storage, and retrieval. • It facilitates the transformation of data into information. • It provides the tools and conditions to manage both data and information. Basically, a database is a fact (data) repository that serves an information system. If the database is designed poorly, one can hardly expect that the data/information transformation will be successful, nor is it reasonable to expect efficient and capable management of data and information. The transformation of data into information is accomplished through application programs. It is impossible to produce good information from poor data; and, no matter how sophisticated the application programs are, it is impossible to use good application programs to overcome the effects of bad database design. In short: Good database design is the foundation of a successful information system. Database design must yield a database that: • Does not fall prey to uncontrolled data duplication, thus preventing data anomalies and the attendant lack of data integrity. • Is efficient in its provision of data access. • Serves the needs of the information system. The last point deserves emphasis: even the best-designed database lacks value if it fails to meet information system objectives. In short, good database designers must pay close attention to the information system requirements. Systems design and database design are usually tightly intertwined and are often performed in parallel. Therefore, database and systems designers must cooperate and coordinate to yield the best possible information system. What is the relationship between the SDLC and the DBLC? The SDLC traces the history (life cycle) of an information system. The DBLC traces the history (life cycle) of a database system. Since we know that the database serves the information system, it is not surprising that the two life cycles conform to the same basic phases. Suggestion: Use Figure 9.8 as the basis for a discussion of the parallel activities. What basic database design strategies exist, and how are such strategies executed? Suggestion: Use Figure 9.14 as the basis for this discussion. There are two basic approaches to database design: top down and bottom up. Top down design begins by identifying the different entity types and the definition of each entity's attributes. In other words, top down design: • starts by defining the required data sets and then • defines the data elements for each of those data sets. Bottom up design: • first defines the required attributes and then • groups the attributes to form entities. Although the two methodologies tend to be complementary, database designers who deal with small databases with relatively few entities, attributes, and transactions tend to emphasize the bottom up approach. Database designers who deal with large, complex databases usually find that a primarily top down design approach is more appropriate. In spite of the frequent arguments concerning the best design approach, perhaps the top down vs. bottom up distinction is quite artificial. The text's note is worth repeating: NOTE Even if a generally top down approach is selected, the normalization process that revises existing table structures is (inevitably) a bottom up technique. E-R models constitute a top-down process even if the selection of attributes and entities may be described as bottom-up. Since both the E-R model and normalization techniques form the basis for most designs, the top down vs. bottom-up debate may be based on a distinction without a difference. Answers to Review Questions 1. What is an information system? What is its purpose? An information system is a system that • provides the conditions for data collection, storage, and retrieval • facilitates the transformation of data into information • provides management of both data and information. An information system is composed of hardware, software (DBMS and applications), the database(s), procedures, and people. Good decisions are generally based on good information. Ultimately, the purpose of an information system is to facilitate good decision making by making relevant and timely information available to the decision makers. 2. How do systems analysis and systems development fit into a discussion about information systems? Both systems analysis and systems development constitute part of the Systems Development Life Cycle, or SDLC. Systems analysis, phase II of the SDLC, establishes the need for and the extent of an information system by • Establishing end-user requirements. • Evaluating the existing system. • Developing a logical systems design. Systems development, based on the detailed systems design found in phase III of the SDLC, yields the information system. The detailed system specifications are established during the systems design phase, in which the designer completes the design of all required system processes. 3. What does the acronym SDLC mean, and what does an SDLC portray? SDLC is the acronym that is used to label the System Development Life Cycle. The SDLC traces the history of a information system from its inception to its obsolescence. The SDLC is composed of six phases: planning, analysis, detailed system, design, implementation and maintenance. 4. What does the acronym DBLC mean, and what does a DBLC portray? DBLC is the acronym that is used to label the Database Life Cycle. The DBLC traces the history of a database system from its inception to its obsolescence. Since the database constitutes the core of an information system, the DBLC is concurrent to the SDLC. The DBLC is composed of six phases: initial study, design, implementation and loading, testing and evaluation, operation, and maintenance and evolution. 5. Discuss the distinction between centralized and decentralized conceptual database design. Centralized and decentralized design constitute variations on the bottom up and top down approaches we discussed in the third question presented in the discussion focus. Basically, the centralized approach is best suited to relatively small and simple databases that lend themselves well to a bird's eye view of the entire database. Such databases may be designed by a single person or by a small and informally constituted design team. The company operations and the scope of its problems are sufficiently limited to enable the designer(s) to perform all of the necessary database design tasks: 1. Define the problem(s). 2. Create the conceptual design. 3. Verify the conceptual design with all user views. 4. Define all system processes and data constraints. 5. Assure that the database design will comply with all achievable end user requirements. The centralized design procedure thus yields the design summary shown in Figure Q9.5A. Figure Q9.5A The Centralized Design Procedure Note that the centralized design approach requires the completion and validation of a single conceptual design. NOTE Use the text’s Figures 9.15 and 9.16 to contrast the two design approaches, then use Figure 9.6 to show the procedure flows; demonstrate that such procedure flows are independent of the degree of centralization. In contrast, when company operations are spread across multiple operational sites or when the database has multiple entities that are subject to complex relations, the best approach is often based on the decentralized design. Typically, a decentralized design requires that the design task be divided into multiple modules, each one of which is assigned to a design team. The design team activities are coordinated by the lead designer, who must aggregate the design teams' efforts. Since each team focuses on modeling a subset of the system, the definition of boundaries and the interrelation between data subsets must be very precise. Each team creates a conceptual data model corresponding to the subset being modeled. Each conceptual model is then verified individually against the user views, processes, and constraints for each of the modules. After the verification process has been completed, all modules are integrated in one conceptual model. Since the data dictionary describes the characteristics of all the objects within the conceptual data model, it plays a vital role in the integration process. Naturally, after the subsets have been aggregated into a larger conceptual model, the lead designer must verify that the combined conceptual model is still able to support all the required transactions. Thus the decentralized design activities may be summarized as shown in Figure Q9.6B. Figure Q9.6B The Decentralized Design Procedure Keep in mind that the aggregation process requires the lead designer to assemble a single model in which various aggregation problems must be addressed: • synonyms and homonyms. Different departments may know the same object by different names (synonyms), or they may use the same name to address different objects (homonyms.) The object may be an entity, an attribute, or a relationship. • entity and entity subclasses. An entity subset may be viewed as a separate entity by one or more departments. The designer must integrate such subclasses into a higher level entity. • Conflicting object definitions. Attributes may be recorded as different types (character, numeric), or different domains may be defined for the same attribute. Constraint definitions, too, may vary. The designer must remove such conflicts from the model. 6. What is the minimal data rule in conceptual design? Why is it important? The minimal data rule specifies that all the data defined in the data model are actually required to fit present and expected future data requirements. This rule may be phrased as All that is needed is there, and all that is there is needed. 7. Discuss the distinction between top-down and bottom-up approaches to database design. There are two basic approaches to database design: top down and bottom up. Top down design begins by identifying the different entity types and the definition of each entity's attributes. In other words, top down design: • starts by defining the required data sets and then • defines the data elements for each of those data sets. Bottom up design: • first defines the required attributes and then • groups the attributes to form entities. Although the two methodologies tend to be complementary, database designers who deal with small databases with relatively few entities, attributes, and transactions tend to emphasize the bottom up approach. Database designers who deal with large, complex databases usually find that a primarily top down design approach is more appropriate. 8. What are business rules? Why are they important to a database designer? Business rules are narrative descriptions of the business policies, procedures, or principles that are derived from a detailed description of operations. Business rules are particularly valuable to database designers, because they help define: • Entities • Attributes • Relationships (1:1, 1:M, M:N, expressed through connectivity’s and cardinalities) • Constraints To develop an accurate data model, the database designer must have a thorough and complete understanding of the organization's data requirements. The business rules are very important to the designer because they enable the designer to fully understand how the business works and what role is played by data within company operations. NOTE Do keep in mind that an ERD cannot always include all the applicable business rules. For example, although constraints are often crucial, it is often not possible to model them. For instance, there is no way to model a constraint such as “no pilot may be assigned to flight duties more than ten hours during any 24-hour period.” It is also worth emphasizing that the description of (company) operations must be done in almost excruciating detail and it must be verified and re-verified. An inaccurate description of operations yields inaccurate business rules that lead to database designs that are destined to fail. 9. What is the data dictionary's function in database design? A good data dictionary provides a precise description of the characteristics of all the entities and attributes found within the database. The data dictionary thus makes it easier to check for the existence of synonyms and homonyms, to check whether all attributes exist to support required reports, to verify appropriate relationship representations, and so on. The data dictionary's contents are both developed and used during the six DBLC phases: DATABASE INITIAL STUDY The basic data dictionary components are developed as the entities and attributes are defined during this phase. DATABASE DESIGN The data dictionary contents are used to verify the database design components: entities, attributes, and their relationships. The designer also uses the data dictionary to check the database design for homonyms and synonyms and verifies that the entities and attributes will support all required query and report requirements. IMPLEMENTATION AND LOADING The DBMS's data dictionary helps to resolve any remaining attribute definition inconsistencies. TESTING AND EVALUATION If problems develop during this phase, the data dictionary contents may be used to help restructure the basic design components to make sure that they support all required operations. OPERATION If the database design still yields (the almost inevitable) operational glitches, the data dictionary may be used as a quality control device to ensure that operational modifications to the database do not conflict with existing components. MAINTENANCE AND EVOLUTION As users face inevitable changes in information needs, the database may be modified to support those needs. Perhaps entities, attributes, and relationships must be added, or relationships must be changed. If new database components are fit into the design, their introduction may produce conflict with existing components. The data dictionary turns out to be a very useful tool to check whether a suggested change invites conflicts within the database design and, if so, how such conflicts may be resolved. 10. What steps are required in the development of an ER diagram? (Hint: See Table 9.3.) Table 9.3 is reproduced for your convenience. TABLE 9.3 Developing the Conceptual Model, Using ER Diagrams STEP ACTIVITY 1 Identify, analyze, and refine the business rules. 2 Identify the main entities, using the results of Step 1. 3 Define the relationships among the entities, using the results of Steps 1 and 2. 4 Define the attributes, primary keys, and foreign keys for each of the entities. 5 Normalize the entities. (Remember that entities are implemented as tables in an RDBMS.) 6 Complete the initial ER diagram. 7 Validate the ER model against the user’s information and processing requirements. 8 Modify the ER diagram, using the results of Step 7. Point out that some of the steps listed in Table 9.3 take place concurrently. And some, such as the normalization process, can generate a demand for additional entities and/or attributes, thereby causing the designer to revise the ER model. For example, while identifying two main entities, the designer might also identify the composite bridge entity that represents the many-to-many relationship between those two main entities. 11. List and briefly explain the activities involved in the verification of an ER model. Section 9-4c, “Data Model Verification,” includes a discussion on verification. In addition, Appendix C, “The University Lab: Conceptual Design Verification, Logical Design, and Implementation,” covers the verification process in detail. The verification process is detailed in the text’s Table 9.5, reproduced here for your convenience. TABLE 9.5 The ER Model Verification Process STEP ACTIVITY 1 Identify the ER model’s central entity. 2 Identify each module and its components. 3 Identify each module’s transaction requirements: Internal: Updates/Inserts/Deletes/Queries/Reports External: Module interfaces 4 Verify all processes against the module’s processing and reporting requirements. 5 Make all necessary changes suggested in Step 4. 6 Repeat Steps 2−5 for all modules. Keep in mind that the verification process requires the continuous verification of business transactions as well as system and user requirements. The verification sequence must be repeated for each of the system’s modules. 12. What factors are important in a DBMS software selection? The selection of DBMS software is critical to the information system’s smooth operation. Consequently, the advantages and disadvantages of the proposed DBMS software should be carefully studied. To avoid false expectations, the end user must be made aware of the limitations of both the DBMS and the database. Although the factors affecting the purchasing decision vary from company to company, some of the most common are: • Cost. Purchase, maintenance, operational, license, installation, training, and conversion costs. • DBMS features and tools. Some database software includes a variety of tools that facilitate the application development task. For example, the availability of query by example (QBE), screen painters, report generators, application generators, data dictionaries, and so on, helps to create a more pleasant work environment for both the end user and the application programmer. Database administrator facilities, query facilities, ease of use, performance, security, concurrency control, transaction processing, and third-party support also influence DBMS software selection. • Underlying model. Hierarchical, network, relational, object/relational, or object. • Portability. Across platforms, systems, and languages. • DBMS hardware requirements. Processor(s), RAM, disk space, and so on. 13. List and briefly explain the four steps performed during the logical design stage. 1) Map conceptual model to logical model components. In this step, the conceptual model is converted into a set of table definitions including table names, column names, primary keys, and foreign keys to implement the entities and relationships specified in the conceptual design. 2) Validate the logical model using normalization. It is possible for normalization issues to be discovered during the process of mapping the conceptual model to logical model components. Therefore, it is appropriate at this stage to validate that all of the table definitions from the previous step conform to the appropriate normalization rules. 3) Validate logical model integrity constraints. This step involves the conversion of attribute domains and constraints into constraint definitions that can be implemented within the DBMS to enforce those domains. Also, entity and referential integrity constraints are validated. Views may be defined to enforce security constraints. 4) Validate the logical model against the user requirements. The final step of this stage is to ensure that all definitions created throughout the logical model are validated against the users' data, transaction, and security requirements. Every component (table, view, constraint, etc.) of the logical model must be associated with satisfying the user requirements, and every user requirement should be addressed by the model components. 14. List and briefly explain the three steps performed during the physical design stage. 1) Define data storage organization. Based on estimates of the data volume and growth, this step involves the determination of the physical location and physical organization for each table. Also, which columns will be indexed and the type of indexes to be used are determined. Finally, the type of implementation to be used for each view is decided. 2) Define integrity and security measures. This step involves creating users and security groups, and then assigning privileges and controls to those users and group. 3) Determine performance measurements. The actual performance of the physical database implementation must be measured and assessed for compliance with user performance requirements. 15. What three levels of backup may be used in database recovery management? Briefly describe what each of those three backup levels does. A full backup of the database creates a backup copy of all database objects in their entirety. A differential backup of the database creates a backup of only those database objects that have changed since the last full backup. A transaction log backup does not create a backup of database objects, but makes a backup of the log of changes that have been applied to the database objects since the last backup. Problem Solutions 1. The ABC Car Service & Repair Centers are owned by the SILENT car dealer; ABC services and repairs only SILENT cars. Three ABC Car Service & Repair Centers provide service and repair for the entire state. Each of the three centers is independently managed and operated by a shop manager, a receptionist, and at least eight mechanics. Each center maintains a fully stocked parts inventory. Each center also maintains a manual file system in which each car’s maintenance history is kept: repairs made, parts used, costs, service dates, owner, and so on. Files are also kept to track inventory, purchasing, billing, employees’ hours, and payroll. You have been contacted by the manager of one of the centers to design and implement a computerized system. Given the preceding information, do the following: a. Indicate the most appropriate sequence of activities by labeling each of the following steps in the correct order. (For example, if you think that “Load the database.” is the appropriate first step, label it “1.”) ____ Normalize the conceptual model. ____ Obtain a general description of company operations. ____ Load the database. ____ Create a description of each system process. ____ Test the system. ____ Draw a data flow diagram and system flowcharts. ____ Create a conceptual model, using ER diagrams. ____ Create the application programs. ____ Interview the mechanics. ____ Create the file (table) structures. ____ Interview the shop manager. The answer to this question may vary slightly from one designer to the next, depending on the selected design methodology and even on personal designer preferences. Yet, in spite of such differences, it is possible to develop a common design methodology to permit the development of a basic decision making process and the analysis required in designing an information system. Whatever the design philosophy, a good designer uses a specific and ordered set of steps through which the database design problem is approached. The steps are generally based on three phases: analysis, design, and implementation. These phases yield the following activities: ANALYSIS 1. Interview the shop manager 2. Interview the mechanics 3. Obtain a general description of company operations 4. Create a description of each system process DESIGN 5. Create a conceptual model, using E R diagrams 6. 8. Draw a data flow diagram and system flow charts 7. Normalize the conceptual model IMPLEMENTATION 8. Create the table structures 9. Load the database 10. Create the application programs 11. Test the system. This listing implies that, within each of the three phases, the steps are completed in a specific order. For example, it would seem reasonable to argue that we must first complete the interviews if we are to obtain a proper description of the company operations. Similarly, we may argue that a data flow diagram precedes the creation of the E-R diagram. Nevertheless, the specific tasks and the order in which they are addressed may vary. Such variations do not matter, as long as the designer bases the selected procedures on an appropriate design philosophy, such as top down vs. bottom up. Given this discussion, we may present problem 1's solution this way: __7__ Normalize the conceptual model. __3__ Obtain a general description of company operations. __9__ Load the database. __4__ Create a description of each system process. _11__ Test the system. __6__ Draw a data flow diagram and system flow charts. __5__ Create a conceptual model, using E R diagrams. _10__ Create the application programs. __2__ Interview the mechanics. __8__ Create the file (table) structures. __1__ Interview the shop manager. b. Describe the various modules that you believe the system should include. This question may be addressed in several ways. We suggest the following approach to develop a system composed of four main modules: Inventory, Payroll, Work order, and Customer. We have illustrated the Information System's main modules in Figure P9.1B. Figure P9.1B The ABC Company’s IS System Modules The Inventory module will include the Parts and Purchasing sub-modules. The Payroll Module will handle all employee and payroll information. The Work order module keeps track of the car maintenance history and all work orders for maintenance done on a car. The Customer module keeps track of the billing of the work orders to the customers and of the payments received from those customers. c. How will a data dictionary help you develop the system? Give examples. We have addressed the role of the data dictionary within the DBLC in detail in the answer to review question 10. Remember that the data dictionary makes it easier to check for the existence of synonyms and homonyms, to check whether all attributes exist to support required reports, to verify appropriate relationship representations, and so on. Therefore, the data dictionary's contents will help us to provide consistency across modules and to evaluate the system's ability to generate the required reports. In addition, the use of the data dictionary facilitates the creation of system documentation. d. What general (system) recommendations might you make to the shop manager? (For example. if the system will be integrated, what modules will be integrated? What benefits would be derived from such an integrated system? Include several general recommendations.) The designer's job is to provide solutions to the main problems found during the initial study. Clearly, any system is subject to both internal and external constraints. For example, we can safely assume that the owner of the ABC Car Service and Repair Center has a time frame in mind, not to mention a spending limitation. As is true in all design work, the designer and the business owner must prioritize the modules and develop those that yield the greatest benefit within the stated time and development budget constraints. Keep in mind that it is always useful to develop a modular system that provides for future enhancement and expansion. Suppose, for example, that the ABC Car Service & Repair company management decides to integrate all of its service stations in the state in order to provide better statewide service. Such integration is likely to yield many benefits: The car history of each car will be available to any station for cars that have been serviced in more than one location; the inventory of parts will be on line, thus allowing parts orders to be placed between service stations; mechanics can better share tips concerning the solution to car maintenance problems, and so on. e. What is the best approach to conceptual database design? Why? Given the nature of this business, the best way to produce this conceptual database design would be to use a centralized and top down approach. Keep in mind that the designer must keep the design sufficiently flexible to make sure that it can accommodate any future integration of this system with the other service stations in the state. f. Name and describe at least four reports the system should have. Explain their use. Who will use those reports? REPORT 1 Monthly Activity contains a summary of service categories by branch and by month. Such reports may become the basis for forecasting personnel and stock requirements for each branch and for each period. REPORT 2 Mechanic Summary Sheet contains a summary of work hours clocked by each mechanic. This report would be generated weekly and would be useful for payroll and maintenance personnel scheduling purposes. REPORT 3 Monthly Inventory contains a summary of parts in inventory, inventory draw down, parts reorder points, and information about the vendors who will provide the parts to be reordered. This report will be especially useful for inventory management purposes. REPORT 4 Customer Activity contains a breakdown of customers by location, maintenance activity, current balances, available credit, and so on. This report would be useful to forecast various service demand factors, to mail promotional materials, to send maintenance reminders, to keep track of special customer requirements, and so on. 2. Suppose you have been asked to create an information system for a manufacturing plant that produces nuts and bolts of many shapes, sizes, and functions. What questions would you ask, and how would the answers to those questions affect the database design? Basically, all answers to all (relevant) questions help shape the database design. In fact, all information collected during the initial study and all subsequent phases will have an impact on the database design. Keep in mind that the information is collected to establish the entities, attributes, and the relationships among the entities. Specifically, the relationships, connectivity’s, and cardinalities are shaped by the business rules that are derived from the information collected by the designer. Sample questions and their likely impact on the design might be: • Do you want to develop the database for all departments at once, or do you want to design and implement the database for one department at a time? • How will the design approach affect the design process? (In other words, assess top down vs. bottom-up, centralized or decentralized, system scope and boundaries.) • Do you want to develop one module at a time, or do you want an integrated system? (Inventory, production, shipping, billing, etc.) • Do you want to keep track of the nuts and bolts by lot number, production shift, type, and department? Impact: conceptual and logical database design. • Do you want to keep track of the suppliers of each batch of raw material used in the production of the nuts and bolts? Impact: conceptual and logical database design. E-R model. • Do you want to keep track of the customers who received the batches of nuts and bolts? Impact: conceptual and logical database design. ER model. • What reports will you require, what will be the specific reporting requirements, and to whom will these reports be distributed? The answers to such questions affect the conceptual and logical database design, the database’s implementation, its testing, and its subsequent operation. a. What do you envision the SDLC to be? The SDLC is not a function of the information collected. Regardless of the extent of the design or its specific implementation, the SDLC phases remain: PLANNING Initial assessment Feasibility study ANALYSIS User requirements Study of existing systems Logical system design DETAILED SYSTEMS DESIGN Detailed system specifications IMPLEMENTATION Coding, testing, debugging Installation, fine tuning MAINTENANCE Evaluation Maintenance Enhancements b. What do you envision the DBLC to be? As is true for the SDLC, the DBLC is not a function of the kind and extent of the collected information. Thus, the DBLC phases and their activities remain as shown: DATABASE INITIAL STUDY Analyze the company situation Define problems and constraints Define objectives Define scope and boundaries DATABASE DESIGN Create the conceptual design Create the logical design create the physical design IMPLEMENTATION AND LOADING Install the DBMS Create the database(s) Load or convert the data TESTING AND EVALUATION Test the database Fine tune the database Evaluate the database and its application programs OPERATION Produce the required information flow MAINTENANCE AND EVOLUTION Introduce changes Make enhancements 3. Suppose you perform the same functions noted in Problem 2 for a larger warehousing operation. How are the two sets of procedures similar? How and why are they different? The development of an information system will differ in the approach and philosophy used. More precisely, the designer team will probably be formed by a group of system analysts and may decide to use a decentralized approach to database design. Also, as is true for any organization, the system scope and constraints may be very different for different systems. Therefore, designers may opt to use different techniques at different stages. For example, the database initial study phase may include separate studies carried out by separate design teams at several geographically distant locations. Each of the findings of the design teams will later be integrated to identify the main problems, solutions, and opportunities that will guide the design and development of the system. 4. Using the same procedures and concepts employed in Problem 1, how would you create an information system for the Tiny College example in Chapter 4? Tiny College is a medium sized educational institution that uses many database intensive operations, such as student registration, academic administration, inventory management, and payroll. To create an information system, first perform an initial database study to determine the information system's objectives. Next, study Tiny College's operations and processes (flow of data) to identify the main problems, constraints, and opportunities. A precise definition of the main problems and constraints will enable the designer to make sure that the design improves Tiny College's operational efficiency. An improvement in operational efficiency is likely to create opportunities to provide new services that will enhance Tiny College's competitive position. After the initial database study is done and the alternative solutions are presented, the end users ultimately decide which one of the probable solutions is most appropriate for Tiny College. Keep in mind that the development of a system this size will probably involve people who have quite different backgrounds. For example, it is likely that the designer must work with people who play a managerial role in communications and local area networks, as well as with the "troops in the trenches" such as programmers and system operators. The designer should, therefore, expect that there will be a wide range of opinions concerning the proposed system's features. It is the designer's job to reconcile the many (and often conflicting) views of the "ideal" system. Once a proposed solution has been agreed upon, the designer(s) may determine the proposed system's scope and boundaries. We are then able to begin the design phase. As the design phase begins, keep in mind that Tiny College's information system is likely to be used by many users (20 to 40 minimum) who are located on distant sites across campus. Therefore, the designer must consider a range of communication issues involving the use of such technologies as local area networks. These technologies must be considered as the database designer(s) begin to develop the structure of the database to be implemented. The remaining development work conforms to the SDLC and the DBLC phases. Special attention must be given to the system design's implementation and testing to ensure that all the system modules interface properly. Finally, the designer(s) must provide all the appropriate system documentation and ensure that all appropriate system maintenance procedures (periodic backups, security checks, etc.) are in place to ensure the system's proper operation. Keep in mind that two very important issues in a university wide system are end user training and support. Therefore, the system designer(s) must make sure that all end users know the system and know how it is to be used to enjoy its benefits. In other words, make sure that end user support programs are in place when the system becomes operational. 5. Write the proper sequence of activities in the design of a video rental database. (The initial ERD was shown in Figure 9.9.) The design must support all rental activities, customer payment tracking, and employee work schedules, as well as track which employees checked out the videos to the customers. After you finish writing the design activity sequence, complete the ERD to ensure that the database design can be successfully implemented. (Make sure that the design is normalized properly and that it can support the required transactions. Given its level of detail and (relative) complexity, this problem would make an excellent class project. Use the chapter’s coverage of the database life cycle (DBLC) as the procedural template. The text’s Figure 9.3 is particularly useful as a procedural map for this problem’s solution and Figure 9.6 provides a more detailed view of the database design’s procedural flow. Make sure that the students review section 9-3b, “Database Design,” before they attempt to produce the problem solution. Appendix B, “The University Lab: Conceptual Design,” and Appendix C “The University Lab: Conceptual Design Verification, Logical Design, and Implementation” show a very detailed example of the procedures required to deliver a completed database. You will find a more detailed video rental database problem description in Appendix B, problem 4. This problem requires the completion of the initial database design. The solution is shown in this manual’s Appendix B coverage. This design is verified in Appendix C, Problem 2. The Visio Professional files for the initial and verified designs are located on your instructor’s CD. Select the FigB-P04a-The-Initial-Crows-Foot-ERD-for-the-Video-Rental-Store.vsd file to see the initial design. Select the Fig-C-P02a-The-Revised-Video-Rental-Crows-Foot-ERD.vsd file to see the verified design. 6. In a construction company, a new system has been in place for a few months and now there is a list of possible changes/updates that need to be done. For each of the changes/updates, specify what type of maintenance needs to be done: (a) corrective, (b) adaptive, and (c) perfective. a. An error in the size of one of the fields has been identified and it needs to be updated status field needs to be changed. This is a change in response to a system error – corrective maintenance. b. The company is expanding into a new type of service and this will require to enhancing the system with a new set of tables to support this new service and integrate it with the existing data. This is a change to enhance the system – perfective maintenance. c. The company has to comply with some government regulations. To do this, it will require adding a couple of fields to the existing system tables. This is a change in response to changes in the business environment – adaptive maintenance. 7. You have been assigned to design the database for a new soccer club. Indicate the most appropriate sequence of activities by labeling each of the following steps in the correct order. (For example, if you think that “Load the database” is the appropriate first step, label it “1.”) _10__ Create the application programs. __4__ Create a description of each system process. _11__ Test the system. __9__ Load the database. __7__ Normalize the conceptual model. __1__ Interview the soccer club president. __5__ Create a conceptual model using ER diagrams. __2__ Interview the soccer club director of coaching. __8__ Create the file (table) structures. __3__ Obtain a general description of the soccer club operations. __6__ Draw a data flow diagram and system flowcharts. Solution Manual for Database Systems: Design, Implementation, and Management Carlos Coronel, Steven Morris 9781337627900, 9781305627482