Questions and Answers of Essentials Database Management

Figure 4-32 shows an EER diagram for a simplified credit card environment. There are two types of card accounts: debit cards and credit cards. Credit card accounts accumulate charges with merchants.
Describe the difference between how a 1:M unary relationship and an M:N unary relationship are implemented in a relational data model.
How do you represent an M:N ternary relationship in a relational data model?
What is the primary purpose of normalization?
Review Figure 2-8 and Figure 2-21.a. Identify any attributes in Figure 2-21 that might be composite attributes but are not shown that way. Justify your suggestions. Redraw the ERD to reflect any
A rental car agency classifies the vehicles it rents into four categories: compact, midsize, full size, and sport utility. The agency wants to record the following data for all vehicles: Vehicle ID,
Give an example of each of the following:a. A supertype/subtype relationship where the disjoint rule applies.b. A supertype/subtype relationship where the overlap rule applies.
What types of business rules are normally captured in an EER diagram?
What is the purpose of a subtype discriminator?
Add a subtype discriminator (named Person Type) to the diagram you created in Problem and Exercise 9.Data from Exercise 9.Draw an EER diagram for the following problem using this text’s EER
Based on the EER diagram constructed for Problem and Exercise 14, develop a sample definition for each entity type, attribute, and relationship in the diagram.Data from Exercise 14.Develop an EER
When is a member of a supertype always a member of at least one subtype?
Draw an EER diagram for the following situation: You are working for a large country club. This country club wants to keep a database on its members and their guests. For each member, the club keeps
Draw an EER diagram for the following situation: TomKat Entertainment is a chain of theaters owned by former husband and wife actors/entertainers who, for some reason, can’t get a job performing
Define each of the following terms:a. Determinantb. Functional dependencyc. Transitive dependencyd. Recursive foreign keye. Normalizationf. Composite keyg. Relationh. Normal formi. Partial functional
Match the following terms to the appropriate definitions:_____ well-structured relation _____ anomaly _____ functional dependency _____ determinant _____ composite key _____
For each of the following relations, indicate the normal form for that relation. If the relation is not in third normal form, decompose it into 3NF relations. Functional dependencies (other than
For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all
Fill in the blanks in each of the following statements:a. A relation that has no partial functional dependencies is in _____ normal form.b. A relation that has no transitive dependencies is in _____
Table 4-5 shows a shipping manifest. Your assignment is as follows:a. Draw a relational schema and diagram the functional dependencies in the relation.b. In what normal form is this relation?c.
Transform the relational schema developed in Problem and Exercise 9 into an EER diagram. State any assumptions that you have made.
For your answers to the following Problems and Exercises from prior chapters, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the
Table 4-8 shows a portion of a shipment table for a large manufacturing company. Each shipment (identified by Shipment#) uniquely identifies the shipment Origin, Destination, and Distance. The
How are relationships between entities represented in the relational data model?
For your answers to Problem and Exercise 13, transform the EER diagrams into a set of relational schemas, diagram the functional dependencies, and convert all the relations to third normal form.Data
How do you represent a 1:M unary relationship in a relational data model?
How do you represent an associative entity in a relational data model?
What is the relationship between the primary key of a relation and the functional dependencies among all attributes within that relation?
A pet store currently uses a legacy flat file system to store all of its information. The owner of the store, Peter Corona, wants to implement a Web-enabled database application. This would enable
Under what conditions must a foreign key not be null?
For Problem and Exercise 21, draw the E-R diagram based on the normalized relations.Data from Exercise 21.A pet store currently uses a legacy flat file system to store all of its information. The
Explain what can be done with primary keys to eliminate key ripple effects as a database evolves.
How would Problems and Exercises 21 and 22 change if a type of fish could be supplied by multiple suppliers?
Because Visio does not explicitly show associative entities, it is not clear in Figure 2-21 which entity types are associative. List the associative entities in this figure. Why are there so many
Examine the hierarchy for the university EER diagram (Figure 3-10). As a student, you are an instance of one of the subtypes: either UNDERGRAD STUDENT or GRADUATE STUDENT. List the names of all the
Match the following terms and definitions:_____ supertype_____ subtype_____ specialization_____ subtype discriminator_____ attribute inheritance_____ overlap rulea. Subset of supertypeb. Entity
Our friend Wally Los Gatos, realizing that his wallcovering business had a few wrinkles in it, decided to pursue a law degree at night. After graduating, he has teamed up with Lyla El Pàjaro to form
Wally Los Gatos, owner of Wally’s Wonderful World of Wallcoverings, has hired you as a consultant to design a database management system for his chain of three stores that sells wallpaper and
After completing a course in database management, you are asked to develop a preliminary ERD for a symphony orchestra. You discover the entity types that should be included as shown in Table 2-2.
Prepare an ERD for a real estate firm that lists property for sale. Also prepare a definition for each entity type, attribute, and relationship on your diagram. In addition, draw a data model for
Modify Figure 2-11a to model the following additional information requirements: The training director decides for each employee who completes each class who (what employees) should be notified of the
Figure 2-27 shows two diagrams (A and B), both of which are legitimate ways to represent that a stock has a history of many prices. Which of the two diagrams do you consider a better way to model
The Is Married To relationship in Figure 2-12a would seem that is, until time plays a role in modeling data. Draw a data model for the PERSON entity type and the Is Married To relationship for
Add minimum and maximum cardinality notation to each of the following figures, as appropriate:a. Figure 2-5b. Figure 2-10ac. Figure 2-11bd. Figure 2-12 (all parts)e. Figure 2-13cf. Figure 2-14
The entity type STUDENT has the following attributes: Student Name, Address, Phone, Age, Activity, and No of Years. Activity represents some campus-based student activity, and No of Years represents
In the three-tiered database architecture, is it possible for there to be no database on a particular tier? If not, why? If yes, give an example.
Define a three-tiered database architecture.
How are relationships between tables expressed in a relational database?
The following attributes represent data about a movie copy at an online video rental service. Each movie is identified by a movie number and has a title and information about the director and the
The following attributes form a relation that includes information about individual computers, their vendors, software packages running on the computers, computer users, and user authorizations.
A bus company is responsible for offering public transportation in the suburbs of a large metropolitan area. The company has significant data management requirements: It needs to keep track of its
Define each of the following terms:a. File organizationb. Sequential file organizationc. Indexed file organizationd. Hashed file organizatione. Denormalizationf. Composite keyg. Secondary keyh. Data
Consider the following two relations for Millennium College:STUDENT(StudentID, StudentName, CampusAddress, GPA) REGISTRATION(StudentID, CourseID, Grade)Following is a typical query against these
Contrast the following terms:a. Physical file; tablespaceb. Normalization; denormalizationc. Range control; null controld. Secondary key; primary key
Choose Oracle data types for the attributes in the normalized relations that you created in Problem and Exercise 19 in Chapter 4.Data from Exercise 19.Figure 4-35 includes an EER diagram for a
Because of referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce
Which student has the highest Read score?The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that
If an SQL statement includes a GROUP BY clause, the attributes that can be requested in the SELECT statement will be limited. Explain that limitation.
Display the territory ID and the number of salespersons in the territory for all territories that have more than one salesperson. Label the number of salespersons NumSalesPersons.
Write the SQL command to add MATH SCORE to the STUDENT table. TUTOR (TutorlD, CertDate, Status) MATCH HISTORY (MatchID, TutorlD, StudentID, StartDate, EndDate) TutorlD CertDate Status 1/05/2008
Display the order IDs for customers who have not made any payment, yet, on that order. Use the set command UNION, INTERSECT, or MINUS in your query.
Discuss UDDI. Compare and contrast it with the white, yellow, and green pages of the telephone book. (If your telephone book does not have green pages, you will need to research this feature found in
Explain why using XML Schema is a step forward from using document type declarations (DTDs).
Using your schema from Question 10, write an XSLT program to display the tutor information in the form of an HTML table.Data from Question 10:Construct a simple XML schema that describes a tutor.
Using your schema from Question 10, write an FLWOR XQuery expression that lists only the tutors’ names and lists them alphabetically by last name.
Visit the Web sites of at least two cloud service providers that offer cloud database services. What are some of the common benefits of using cloud database services listed on these Web sites? If you
Consider the example code shown in Figures 8-10 and 8-11. Assume that instead of accessing data from a local server, you are going to access data from a cloud provider that offers the appropriate
What database APIs are commonly used to access databases from various programming languages?
Rewrite the example show in Figure 8-4 using Java.
Contrast the following terms:a. Two-tier architecture; three-tier architectureb. Fat client; thin clientc. Odbc; jdbcd. Xhtml; xslte. Sql; xqueryf. Web services; soa
Rewrite your answer to Problem and Exercise 33 for each customer, not just customer 16.Data from Exercise 33.Display the name of customer 16 and the names of all the customers that are in the same
Display the EmployeeID and EmployeeName for those employees who do not possess the skill Router. Display the results in order by EmployeeName. Show how you constructed this query using a Venn or
This chapter discusses the data dictionary views for Oracle 11g. Research another RDBMS, such as Microsoft SQL Server, and report on its data dictionary facility and how it compares with Oracle.
Can an outer join be easily implemented when joining more than two tables? Why or why not?
Explain the use of derived tables.
Under what conditions can a UNION clause be used?
Write the SQL command to add SUBJECT to TUTOR. The only values allowed for SUBJECT will be Reading, Math, and ESL. TUTOR (TutorlD, CertDate, Status) MATCH HISTORY (MatchID, TutorlD, StudentID,
Care must be exercised when writing triggers for a database. What are some of the problems that could be encountered?
Determine the relationships among the four entities in Figure 7-15. List primary keys for each entity and any foreign keys necessary to establish the relationships and maintain referential integrity.
Explain the processing order of a correlated subquery.
For each salesperson, list the total number of orders by month for the year 2010. (If you are using Access, use the Month function. If you are using Oracle, convert the date to a string, using the
Display the SalesPersonID and a count of the number of orders for that salesperson for all salespersons except salespersons 3, 5, and 9. Write this query with as few clauses or components as
List all sales territories (TerritoryID) that have more than one salesperson.
For each customer who had more than two orders, list the CustomerID and the total number of orders placed.
For each salesperson, list the total number of orders.
For each customer, list the CustomerID and the total number of orders placed in 2010.
Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column
For each salesperson, display a list of CustomerIDs.
For each customer, list the CustomerID and total number of orders placed.
For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the most popular product first and the least popular last.
What can be changed about a table definition, using the ALTER SQL command? Can you identify anything about a table definition that cannot be changed using the ALTER SQL command?
Display the product line ID and the average standard price for all products in each product line.
List all raw materials that are made of cherry and that have dimensions (thickness and width) of 12 by 12.
Within which clauses of an SQL statement can a derived table be defined?
Which employees were hired during 1999?
List the employees whose last names begin with an L.
When would the use of the LIKE keyword with the CREATE TABLE command be helpful?
Add an order to the Order_T table and include a sample value for every attribute.a. First, look at the data in the Customer_T table and enter an order from any one of those customers.b. Enter an
Enter sample data of your own choosing into QtyOnHand in the Product_T table. Test the modification you made in Problem and Exercise 16 by attempting to update a product by changing the inventory to
Modify the Product_T table by adding an attribute QtyOnHand that can be used to track the finished goods inventory. The field should be an integer field of five characters and should accept only

Showing 1 - 100 of 129