Programming Languages Chapter 2 Homework Guide Sql Ninth Edition Solutions Database Design Fundamentals Solutions Answers Review

Unlock access to all the studying documents.

View Full Document

A Guide to SQL, Ninth Edition Solutions 2-1

Chapter 2: Database Design Fundamentals

Solutions

Answers to Review Questions

1. An entity is a person, place, thing, or event.

3. A relationship is an association between tables (entities). A one-to-many relationship

4. A repeating group is multiple entries in a single location in a table.

5. A relation is a two-dimensional table in which the entries in the table are single-valued

7. For each table, you write the name of the table and then within parentheses list all of

the columns in the table. Underline the primary keys.

CUSTOMER (CUSTOMER_NUM, LAST_NAME, FIRST_NAME, ADDRESS, CITY,

8. To qualify the name of a field, indicate the table in which the field appears. You do this

by preceding the name of the field with the name of the table and a period.

9. A column (attribute), B, is functionally dependent on another column, A (or possibly a

10. Column A (or a collection of columns) is the primary key for a table if (1) All

columns in the table are functionally dependent on A and (2) No subcollection of the

columns in A (assuming A is a collection of columns and not just a single column) also

11. Functional dependencies:

DEPARTMENT_NUM → DEPARTMENT_NAME

ADVISOR_NUM → ADVISOR_LAST_NAME, ADVISOR_FIRST_NAME, DEPARTMENT_NUM

COURSE_CODE → DESCRIPTION

14. A table is in third normal form if it is in second normal form and if the only

determinants it contains are candidate keys. A change in a value can require not just

15.

STUDENT (STUDENT_NUM, STUDENT_LAST_NAME, STUDENT_FIRST_NAME,

16. [Critical Thinking] If a student can have more than one advisor, there is a many-to–

many relatioship between students and advisors. Remove ADVISOR_NUM from the

17. [Critical Thinking] If students can repeat a course, then the STUDENT_NUM,

COURSE_CODE, YEAR, and SEMESTER determine the grade.

STUDENT (STUDENT_NUM, STUDENT_LAST_NAME, STUDENT_FIRST_NAME,

Answers to TAL Distributors Exercises

1. NOTES: The CUSTOMER_REP table in the following lists implements the

relationship between customers and reps. If customer 126, for example, is represented

by both rep 15 and rep 30, there would be a row in the table in which the customer

number is 126 and the rep number is 15 as well as a row in which the customer

number is 126 and the rep number is 30. A row would only be allowed in the

ORDERS table if the combination of the customer number and the rep number match

a row in the CUSTOMER_REP table.

A Guide to SQL, Ninth Edition Solutions 2-4

Relationships: There are one-to-many relationships from REP to

CUSTOMER_REP, CUSTOMER to CUSTOMER_REP, CUSTOMER_REP to

ORDERS, ORDERS to ORDER_LINE, and ITEM to ORDER_LINE.

Entity-Relationship diagram: (NOTE: Your rectangles may be in different

positions as long as they are connected by the same arrows.)

2. NOTES: There is no relationship between customers and reps, so there is no

REP_NUM column in the CUSTOMER table nor is there an additional table like the

CUSTOMER_REP table in Exercise 1. A row can only exist in the ORDERS table if

the customer number matches a row in the CUSTOMER table and the rep number

matches a row in the REP table.

REP (REP_NUM, LAST_NAME, FIRST_NAME, STREET, CITY, STATE, POSTAL_CODE,

COMMISSION, RATE)

3. NOTES: The STOREHOUSE_NUM and ON_HAND columns do not appear in the

ITEM table. There is a STOREHOUSE table, whose key is STOREHOUSE_NUM and

which contains the STOREHOUSE description. Information about units on hand is stored

in a new table, the ITEM_STOREHOUSE table, whose key is the combination of the

ITEM number and STOREHOUSE number. If there are 10 units of ITEM BR23 on hand

in STOREHOUSE 2, for example, there would be a row in ITEM_STOREHOUSE on

which the ITEM number is BR23, the STOREHOUSE number is 2, and the number of

units on hand is 10.

Relationships: There are one-to-many relationships from REP to CUSTOMER,

CUSTOMER to ORDERS, ORDERS to ORDER_LINE, ITEM to ORDER_LINE, ITEM

to ITEM_STOREHOUSE, and STOREHOUSE to ITEM_STOREHOUSE.

A Guide to SQL, Ninth Edition Solutions 2-6

CUSTOMER

ORDERS ORDER_LINE ITEM STOREHOUSE

ITEM_STOREHOUSE

REP

4. Functional Dependencies:

ITEM_NUM → DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE

ORDER_NUM → ORDER_DATE, CUSTOMER_NUM

5. [Critical Thinking] One way to address this change is to add two tables to the database:

STOREHOUSE and MANAGER.

Answers to Colonial Adventure Tours Exercises

A Guide to SQL, Ninth Edition Solutions 2-7

TripGuides (TripID, GuideNum, TripName,)

2. Functional Dependencies:

TRIP_ID → TRIP_NAME, STATE_ABBREVIATION, STATE_NAME

GUIDE_NUM → GUIDE_LAST, GUIDE_FIRST

STATE_ABBREVIATION → STATE_NAME

Tables (Relations):

3. [Critical Thinking] 3NF:

Participant (ParticipantNum, LastName, FirstName, Address, City,

State, PostalCode, Phone, BirthDate)

Class (ClassNum, Description, MaxPersons, ClassFee)

Diagram: The student’s diagram should have the following boxes (rectangles):

Guide, Trip, Reservation, Customer, TripGuides, Participants, Class

Answers to Solmaris Condominium Group Exercises

1. Functional Dependencies

LOCATION_NUM LOCATION_NAME

A Guide to SQL, Ninth Edition Solutions 2-8

3NF

LOCATION (LOCATION_NUM, LOCATION_NAME)

2. Functional Dependencies:

CONDO_ID → LOCATION_NUM, UNIT_NUM, SQR_FT, BDRMS, BATHS,

CONDO_FEE, OWNER_NUM, LAST_NAME, FIRST_NAME

OWNER_NUM → LAST_NAME, FIRST_NAME

3. [Critical Thinking] Functional Dependencies

NOTE: The design assumes that the weekly rate can very with the rental agreement. If

students assume that the weekly rate is always the same then the rate would be stored

only in the CONDO_UNIT table. The design also assumes that both LOCATION_NUM

and CONDO_UNIT_NUM uniquely identify a given condo. This is different than the

way Solmaris database is designed for this text. As an alternative you can use the same

design for the CONDO_UNIT table as that shown in the text.

RENTER_NUM → FIRST_NAME, MID_INITIAL, LAST_NAME, ADDRESS,

CITY, STATE, POSTAL_CODE, PHONE_NUM, EMAIL

LOCATION_NUM → LOCATION_NAME, ADDRESS, CITY, STATE, POSTAL_CODE

LOCATION_NUM, CONDO_UNIT_NUM → SQR_FT, BEDRMS, BATHS,

MAX_PERSONS, WEEKLY_RATE

RENTER_NUM, LOCATION_NUM, CONDO_UNIT_NUM → START_DATE, END_DATE,

RENTAL_RATE

3 NF

RENTER (RENTER_NUM, FIRST_NAME, MID_INITIAL, LAST_NAME, ADDRESS,

CITY, STATE, POSTAL_CODE, PHONE_NUM, EMAIL)

A Guide to SQL, Ninth Edition Solutions 2-9