Database Design

Exercise 1

The DreamHome case study 16.13 of the textbook.

Create a conceptual data model for the Branch user views of DreamHome documented in Appendix A.

Compare your ER diagram with Figure 13.8 of the textbok and justify any differences found. Analyze the DreamHome case study and examine if there are situations that call for enhanced modeling. Present the enhanced data model of the case.

Exercise 2

Answer the following questions with reference to how the ER model in Figure 17.13 maps of the book to relational tables.

(a) How many relations will represent the ER model?

(b) How many foreign keys are mapped to the relation representing X?

(c) Which relation(s) will have no foreign key?

(d) Using only the letter identifier for each entity, provide appropriate names for the relations mapped from the ER model.

(e) If the cardinality for each relationship is changed to one-to-one with total participation for all entities, how many relations would be derived from this version of the ER model?

Exercise 3

Create your own store! Your store should sell one type of things, like clothing or bikes, whatever you want your store to specialize in. You should have a table for all the items in your store, and at least 5 columns for the kind of data you think you’d need to store. You should sell at least 15 items, and use select statements to order your items by price and show at least one statistic about the items.

Exercise 4

We’ve created a database for customers and their orders. Not all of the customers have made orders, however. Come up with a query that lists the name and email of every customer followed by the item and price of orders they’ve made. Use a LEFT OUTER JOIN so that a customer is listed even if they’ve made no orders, and don’t add any ORDER BY.

Exercise 1

The DreamHome case study 16.13 of the textbook.

Create a conceptual data model for the Branch user views of DreamHome documented in Appendix A.

Compare your ER diagram with Figure 13.8 of the textbok and justify any differences found. Analyze the DreamHome case study and examine if there are situations that call for enhanced modeling. Present the enhanced data model of the case.

Exercise 2

Answer the following questions with reference to how the ER model in Figure 17.13 maps of the book to relational tables.

(a) How many relations will represent the ER model?

(b) How many foreign keys are mapped to the relation representing X?

(c) Which relation(s) will have no foreign key?

(d) Using only the letter identifier for each entity, provide appropriate names for the relations mapped from the ER model.

(e) If the cardinality for each relationship is changed to one-to-one with total participation for all entities, how many relations would be derived from this version of the ER model?

Exercise 3

Create your own store! Your store should sell one type of things, like clothing or bikes, whatever you want your store to specialize in. You should have a table for all the items in your store, and at least 5 columns for the kind of data you think you’d need to store. You should sell at least 15 items, and use select statements to order your items by price and show at least one statistic about the items.

Exercise 4

We’ve created a database for customers and their orders. Not all of the customers have made orders, however. Come up with a query that lists the name and email of every customer followed by the item and price of orders they’ve made. Use a LEFT OUTER JOIN so that a customer is listed even if they’ve made no orders, and don’t add any ORDER BY.