1. Discuss each of the following terms:
2. What is data redundancy, and which characteristics of the file system can lead to it?
3. It is said that file systems lack data independence. Discuss.
4. What is a DBMS and what are its functions?
5. How does the hierarchical database structure address the problem of data redundancy?
6. What do each of the following acronyms represent, and how is each related to the birth of the Network Database Model?
7. What three languages were adopted by the DBTG to standardize the basic Network Database Model, and why was such standardization important to users and designers?
8. What is data independence, and why is it important?
9. Describe the basic features of the relational database model, and discuss their importance to the user and the designer.
10. Explain how the entity relationship (E-R) model helped produce a more structured relational database design environment.
11. Use the scenario described by “A customer can make many payments, but each payment is made by only one customer” as the basis for an entity relationship diagram (ERD) presentation.
12. What are connectivities, and what role do they play in database design?
13. Why is an object said to have greater semantic content than an entity?
14. What is the difference between an object and a class in the object oriented data model (OODM)?
15. How would you model question 11 with an OODM? (Use Figure 1.15 as your guide.)
16. What is an ERDM, and what role does it play in the modern (production) database environment?
17. Compare the file system with the five database systems discussed in this chapter, in terms of data and structural independence.
18. Explain the difference between data and information.
1. What is the difference between a database and a table?
2. What does a database expert mean when (s)he observes that a database displays both entity integrity and referential integrity?
3. Why are entity integrity and referential integrity important in a database?
4. A database manual notes that "the file contains two hundred records, each one of which contains nine fields." Use appropriate relational database terminology to "translate" the preceding statement.
5. Use the small database shown in Figure Q2.5 to illustrate the difference between a natural JOIN, an equiJOIN, and an outerJOIN.
6. Draw the basic Entity Relationship diagram for the database shown in Figure Q2.5.
7. Draw the relational schema for the database shown in Figure Q2.5.
8. Suppose that you have the Entity Relationship model shown in Figure Q2.8:
9. What are homonyms and synonyms, and why should they generally be avoided in
10. How would you implement a 1:M relationship in a database composed of two tables? Give an example.
11. Identify and describe the components of the database table shown in Figure Q2.11, using correct terminology. Use your knowledge of the naming conventions to identify the table's probable foreign key(s).
12. Suppose that you are using the following a database composed of the two tables shown in Figure Q2.12:
a. Identify the primary keys.
b. Identify the foreign key
c. Draw the Entity Relationship model.
d. Draw the relational schema to show the relationship between DIRECTOR and
e. Suppose you wanted quick lookup capability to get a listing of all the plays directed by a given director. What table would be the basis for the index table, and what would be the index key?
f. What would be the conceptual view of the index table described in part e? Depict the contents of the (conceptual) index table.
1. Name and discuss the different levels of data abstraction as defined by ANSI/SPARC.
2. What are the main building modules of the Entity Relationship model? Discuss each one.
3. What is a composite entity, and when is it used?
4. Why is data modeling considered a "communication tool"?
5. Suppose you are working within the framework of the conceptual model in Figure Q3.5:
6. How are database models related to the level of data abstraction? (Hint: Why do database designers find it so much easier to develop designs based on the relational model?)
7. How would you (graphically) identify each of the following E-R model components?
a. an entity
b. an attribute
c. a relationship
8. The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for its training program. The HEG administrator gives you the following description of the training group's operating environment:
The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five "advanced technology" courses, each of which may generate several classes. If a class has fewer than 10 trainees in it, it will be canceled. It is, therefore, possible for a course not to generate any classes during a session. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research. Each trainee may take up to two classes per session.
Given this information, do the following:
a. Draw the E‑R diagram for HEG.
b. Describe the relationship between instructor and course in terms of connectivity, cardinality, and existence dependence.
10. What two courses of action are available to a designer when a multivalued attribute is
11. What is a derived attribute? Give an example.
12. How is a relationship between entities indicated in an E-R diagram? Give an example, using the Chen and Crow’s Foot data models.
13. What is a weak entity, and how is it represented in an E-R diagram? Give an example, using the Chen and Crow’s Foot data models.
14. How is a composite entity represented in an E-R diagram, and what is its function? (Illustrate both the Chen and the Crow’s Foot models.)
15. Given the following business rules, create the appropriate Chen and Crow’s Foot E-R diagrams for each of the specified relationships:
a. A company operates four departments.
b. Each department employs employees.
c. Each of the employees may or may not have one or more dependents.
d. Each employee may or may not have an employment history.
16. Using the E-R diagram components developed in question 15, create Chen and Crow’s Foot E-R diagrams that include all the components.
17. What three (often conflicting) database requirements must be addressed in database design?
18. Briefly, but precisely, explain the difference between single-valued attributes and simple attributes. Give an example of each.
19. What are multivalued attributes, and how may they be handled within the database design?
20. Write the proper cardinalities for (a,b)______ (c,d)_____ (e,f)______
(g,h)______ (i,j)______ (k,l)_____ (m,n) _____ (o,p) _____
21. Write the proper connectivities for W____ X_____ Y_____ Z_____
22. What two attributes must be contained in the composite entity? Use proper terminology in your answer!
23. Describe precisely the composition of the weak entity's primary key. Use proper terminology in your answer.
24 Convert the Chen ERD in Figure Q3.20 to a Crow’s Foot ERD.
1. What is normalization?
2. When is a table in 1NF?
3. When is a table in 2NF?
4. When is a table in 3NF?
5. When is a table in BCNF?
6. Given the dependency diagram shown in Figure Q4.6, answer Questions 6a through 6c:
Figure Q4.6 The Dependency Diagram for Questions 6a Through 6c
a. Identify and discuss each of the indicated dependencies.
b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table.
7. What is a partial dependency? With what normal form is it associated?
8. What three data anomalies are likely to be the result of data redundancy? How can such anomalies be eliminated?
9. Define and discuss the concept of transitive dependency.
Chapter 5 – Review Questions
All Review Questions are based on the data shown in Figure R5.1. This database table is named EMP_1. The table structure is summarized in Table R5.1.
FIGURE R5.1 The Contents of the EMP_1 Table
Table R5.1 The EMP_1 Table Structure Summary
Attribute (Field) Name
Given this information, answer the following questions:
(Note: If your SQL implementation allows it, you may use DECIMAL(4,2), rather than NUMBER(4,2).
9. Write the SQL code to enter an EMP_PCT value of 3.85 for a person whose personnel number (EMP_NUM) is 103. Assume that all the EMP_PCT entries have been made at this point and that the EMP_2 table now contains the data shown in Figure R5.9:
11. Using a single command sequence, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher. When you are done with questions 10 and 11, the EMP_2 table will contain the data shown in Figure R5.11:
FIGURE R5.11 The EMP_2 Table Contents After the Modifications
(You may assume that the table has been saved again at this point!)
13. Write the two SQL command sequences required to:
FIGURE R5.16A The PROJECT Table Contents
Given this information, write the SQL code that will produce the results shown in Database Table R5.16B.
FIGURE R5.16B The Query results for Question 16
(Hint: You must join the EMP_1 and PROJECT tables.)
1. What is an information system? What is its purpose?
2. How do systems analysis and systems development fit into a discussion about information systems?
3. Discuss the distinction between data and information.
4. What does the acronym SDLC mean, and what does it portray?
5. What does the acronym DBLC mean, and what does it portray?
6. Discuss the distinction between centralized and decentralized conceptual database design.
7. What is the minimal data rule in conceptual design? Why is it important?
8. Discuss the distinctions between top-down and bottom-up in database design strategies.
9. What are business rules? Why are they important to a database designer?
10. What is the data dictionary's function in database design?
11. What is concurrency control? Why is it important?
Figure Q8.4b The ERD for Question 4
An employeee can take many courses, and each course can be taken by many employees.
Once you have traced the development of the ERD segment, verify it and then provide sample data for each of the three tables to illustrate how the design would be implemented.
7. You read in this chapter that:
An examination of the UCL's Inventory Management module reporting requirements uncovered the following problems:
What solution was proposed for this set of problems? How would such a solution be useful in other types of inventory environments?
1. Explain the following statement: a transaction is a logical unit of work.
2. What is a consistent database state, and how is it achieved?
3. "The DBMS does not guarantee that the semantic meaning of the transaction truly represents the real-world event." What are the possible consequences of this limitation? Give an example.
4. List and discuss the four transaction properties.
5. What is a transaction log, and what is its function?
6. What is a scheduler, what does it do, and why is its activity important to concurrency control?
7. What is a lock, and how does it (generally) work?
8. What is concurrency control, and what is its objective?
9. What is an exclusive lock, and under what circumstances is it granted?
10. What is a deadlock, and how can it be avoided? Discuss several deadlock-avoidance strategies.
11. What three levels of backup may be used in database recovery management? Briefly, describe what each of those three backup levels does.
1. Describe the evolution from centralized DBMS to distributed DBMS.
2. List and discuss some of the factors that influenced the evolution of the DDBMS.
3. What are the advantages of the DDBMS?
4. What are the disadvantages of the DDBMS?
5. Explain the difference between distributed database and distributed processing.
6. What is a fully distributed Database Management System?
7. What are the components of a DDBMS?
8. Explain the transparency features of a DDBMS.
9. Define and explain the different types of distribution transparency.
10. Describe the different types of database requests and transactions.
11. Explain the need for the two-phase commit protocol. Then describe the two phases.
12. What is the objective of the query optimization functions?
13. To what transparency feature are the query optimization functions related?
14. What are the different types of query optimization algorithms?
15. Describe the three data fragmentation strategies. Give some examples.
16. What is data replication, and what are the three replication strategies?
17. Explain the difference between a file server and client/server architecture.
1. Discuss the evolution of object-oriented concepts. Then explain how those concepts have affected computer-related activities.
2. How would you define object orientation? What are some of its benefits? How are OO programming languages related to object orientation?
3. Define and describe the following:
c. Object state
d. Object ID
4. Define and contrast the concepts of method and message. What OO concept provides the differentiation between a method and a message? Give examples.
5. Explain how encapsulation provides a contrast to traditional programming constructs such as record definition. What benefits are obtained through encapsulation? Give an example.
6. Using an example, illustrate the concepts of class and class instances.
7. What is a class protocol, and how is it related to the concepts of methods and classes? Draw a diagram to show the relationship between these OO concepts: object, class, instance variables, methods, object's state, object ID, behavior, protocol, and messages.
8. Define the concepts of class hierarchy, superclasses, and subclasses. Then explain the concept of inheritance and the different types of inheritance. Use examples in your explanations.
9. Define and explain the concepts of method overriding and polymorphism. Use examples in your explanations.
10. Explain the concept of abstract data types and how they differ from traditional or base data types. What is the relationship between a type and a class in OO systems?
11. Give a brief history of data models and how they evolved. What are some common characteristics of such data models? What have been the development trends in such data models?
12. What are the five minimum attributes of an OO data model? Describe the difference between early and late binding and how each affects the object-oriented data model. Give examples.
13. What is an object space? Using a graphic representation of objects, depict the relationship(s) that exist between a student taking several classes and a class taken by several students. What type of object is needed to depict this relationship?
14. Compare and contrast the OODM with the E-R and relational models. How is a weak entity represented in the OODM? Give examples.
15. Name and describe the 13 mandatory features of an OODBMS.
16. What are the advantages and disadvantages of an OODBMS?
17. Explain how OO concepts affect database design. How does the OO environment affect the DBA's role?
18. What are the essential differences between the relational database model and the object database model?
19. Using a simple invoicing system as your point of departure, explain how its representation in an entity relationship model (ERM) differs from its representation in an object data model (ODM). Hint: Check Figure 1.15 in Chapter 1, “File Systems and Databases.
20. What are the essential differences between an RDBMS and an OODBMS?
21. Discuss the object/relational model's characteristics.
Chapter 12 - Review Questions
1. Mainframe computing used to be the only way to manage data. Then personal computers changed the data management scene. How do these two computing styles differ and how did the shift to PC-based computing evolve?
2. What is client/server computing, and what benefits can be expected from client/server systems?
3. Explain how client/server system components interact.
4. Describe and explain the client/server architectural principles.
5. Describe the client and the server components of the client/server computing model. Give examples of server services.
6. Using the OSI network reference model, explain the communications middleware component's function.
7. What major network communications protocols are currently in use?
8. Explain what middleware is and what it does. Why would MIS managers be particularly interested in such software?
9. Suppose you are currently considering the purchase of a client/server DBMS. What characteristics should you look for? Why?
10. Describe and contrast the four client/server computing architectural styles that were introduced in this chapter.
11. Contrast client/server and traditional data processing.
12. Discuss and evaluate the following statement: "There are no unusual managerial issues related to the introduction of client/server systems."
13. ROBCOR, a medium-sized company has decided to update its computing environment. ROBCOR has been a minicomputer-based shop for several years, and its managerial and clerical personnel all have personal computers on their desks. ROBCOR has offered you a contract to help them move to client/server. Explain how you would implement such an environment.
14. Identify the main computing style of your university computing infrastructure. Then recommend improvements based on client/server strategy. (You might want to talk with your department's secretary or you may want to talk to your advisor to find out how well the current system meets their information needs.)
1. What are decision support systems, and what role do they play in the business environment?
2. Explain how the main components of a DSS interact to form a system.
3. What are the most relevant differences between operational and decision support data?
4. What is a data warehouse and what are its main characteristics?
5. Give three examples of problems likely to be found when operational data are integrated into the data warehouse.
Use the following scenario to answer questions 6 through 12.
While working as a database analyst for a national sales organization, you are asked to be part of its data warehouse project team.
6. Prepare a high-level summary of the main requirements to evaluate DBMS products for data warehousing.
7. Your data warehousing project group is arguing about prototyping a data warehouse before its implementation. The project group members are especially concerned about the need to acquire some data warehousing skills before implementing the enterprise-wide data warehouse. What would you recommend? Explain your recommendations.
8. Suppose you are selling the data warehouse idea to your users. How would you explain to them what multidimensional data analysis is and what its advantages are?
9. The Data Warehousing project group has invited you to provide an OLAP overview before making a commitment. The group's members are particularly concerned about the OLAP client/server architecture requirements and how OLAP will fit the existing environment. Your job is to explain to them the main OLAP client/server components and architectures.
10. One of your vendors recommends using an MDBMS. How would you explain this recommendation to your project leader?
11. The project group is ready to make a final decision between ROLAP and MOLAP. What should be the basis for this decision? Why?
12. The data warehouse project is in the design phase. Explain to your fellow designers how you would use a star schema in the design.
13. Trace the evolution of DSS from its origins to today's advanced analytical tools. What major technologies influenced this evolution?
14. What is OLAP, and what are its main characteristics?
15. Explain what ROLAP is and why you would recommend its use in the relational database environment.
16. Explain the use of facts, dimensions, and attributes in the star schema.
17. Explain what multidimensional cubes are and how the slice and dice technique fits into this model.
18. What are attribute hierarchies and aggregation levels in the star schema context and what is their purpose?
19. Discuss the most common performance improvement techniques used in star schemas.
20. Explain some of the most important issues in data warehouse implementation.
21. What is data mining and how does it differ from traditional DSS tools?
22. How does data mining work? Discuss the different phases in the data mining process.
1. What does the statement “the Web is a stateless system” mean? What implications does a stateless system have for database application developers?
1. Explain the difference between data and information. Give some examples of raw data and information.
2. Explain the interactions among end user, data, information, and decision-making. Draw a diagram and explain the interactions.
3. Suppose that you are a DBA staff member. What data dimensions would you describe to top-level managers in order to obtain their support for endorsing the data administration function?
4. How and why did database management systems become the organizational data management standard? Discuss some of the advantages of the database approach over the file-system approach.
5. Using a single sentence, explain the role of databases in organizations. Then explain your answer.
6. Define security and privacy. How are these two concepts related to each other?
7. Describe and contrast the information needs at the strategic, tactical, and operational levels in an organization. Use examples to explain your answers.
8. What special considerations must you take into account when you consider the introduction of a DBMS into an organization?
9. Who is the database administrator? Describe the DA's responsibilities.
10. How can the DBA function be placed within the organizational chart? What effect(s) will such placement have on the DBA function?
11. Why and how are new technological advances in computers and databases changing the DBA's role?
12. Explain the DBA department's internal organization, based on the DBLC approach.
13. Explain and contrast the differences and similarities between the DBA and DA.
14. Explain how the DBA plays an arbitration role for an organization's two main assets. Draw a diagram to facilitate your explanation.
15. Describe and characterize the skills desired for a DBA.
16. What are the DBA's managerial roles? Describe the managerial activities and services provided by the DBA.
17. What DBA activities are used to support the end user community?
18. Explain the DBA's managerial role in the definition and enforcement of policies, procedures, and standards.
19. Protecting data security, privacy, and integrity are important database functions in authorization management. What activities are required in the DBA's managerial role of enforcing these functions?
20. Discuss the importance and characteristics of database data backup and recovery procedures. Then describe the actions that must be detailed in backup and recovery plans.
21. Assume that your company assigned you the responsibility of selecting the corporate DBMS, and develop a checklist for the technical and other aspects involved in the selection of the DBMS.
22. Describe the activities that are typically associated with the design and implementation services of the DBA technical function. What technical skills are desirable in the DBA's personnel?
23. Why are testing and evaluation of the database and applications not done by the same people who are responsible for the design and implementation? What minimum standards must be met during the testing and evaluation process?
24. Identify some bottlenecks in DBMS performance. Then propose some solutions used in DBMS performance tuning.
25. What are the typical activities involved in the maintenance of the DBMS, utilities, and applications? Would you consider application performance tuning as part of the maintenance activities? Explain your answer.
26. Define the concept of a data dictionary. Then discuss the different types of data dictionaries. If you were to manage an organization's entire data set, what characteristics would you look for in the data dictionary?
27. Using SQL statements, give some examples of how you would use the data dictionary to monitor the security of the database.
Note: If you use IBM's OS/2 EE DBM V 1.3, the names of the main tables are SYSTABLES, SYSCOLUMNS, and SYSTABAUTH.
28. What characteristics do a CASE tool and a DBMS have in common? How can these characteristics be used to enhance the data administration function?
29. Briefly explain the concepts of Information Engineering (IE) and Information Systems Architecture (ISA). How do these concepts affect the data administration strategy?
30. Identify and explain some of the critical success factors in the development and implementation of a successful data administration strategy.
31. What is the tool used by Oracle to create users?
32. In Oracle, what is a tablespace?
33. In Oracle, what is a database role?
34. In Oracle, what is a datafile, and how does it differ from a file systems file?
35. In Oracle, what is a database profile?
36. In Oracle, what is a database schema?
37. In Oracle, what role is required to create triggers and procedures?