Scribd
Upload
121 views

Course 1 Module 08 Exercise Problems

The exercise problems in Module 8 provide practice with analyzing simple narrative problems and design transformations related to entities such as homes, owners, agents, offices, buyers, offers, credit card statements, and transactions. The problems involve defining entity relationship diagrams and refining them by adding or transforming entity types and attributes. Users are encouraged to use the ER Assistant tool to complete the diagrams.

Uploaded by

Byron Martínez M.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
121 views

Course 1 Module 08 Exercise Problems

The exercise problems in Module 8 provide practice with analyzing simple narrative problems and design transformations related to entities such as homes, owners, agents, offices, buyers, offers, credit card statements, and transactions. The problems involve defining entity relationship diagrams and refining them by adding or transforming entity types and attributes. Users are encouraged to use the ER Assistant tool to complete the diagrams.

Uploaded by

Byron Martínez M.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as RTF, PDF, TXT or read online on Scribd
You are on page 1/ 2

Exercise Problems for Module 8

The exercise problems in Module 8 provide practice with analyzing simple narrative

problems and design transformations. You are encouraged to use the ER Assistant or other drawing

tool to complete the exercise problems in module 8. If you want to use the ER Assistant, I encourage

you to watch at least the first part of the software demonstration about the ER Assistant, available in

module 7.

1. Define an ERD for the following narrative. The database should track homes and owners. A

home has a unique home identifier, a street address, a city, a state, a zip, a number of bedrooms, a

number of bathrooms, and square feet. A home is either owner occupied or rented. An owner

has a unique owner number, a Social Security number (used for government reporting

requirements), a name, an optional spouse name, a profession, an optional spouse profession, and

an optional spouse Social Security number. An owner can possess one or more homes. Each

home has only one owner.

1. Refine the ERD from problem 1 by adding an agent entity type. Agents represent owners in the

sale of a home. An agent can list many homes, but only one agent can list a home. An agent has

a unique agent identifier, a name, an office identifier, and a phone number. When an owner

agrees to list a home with an agent, a commission (percentage of the sales price) and a selling

price are determined.

2. In the ERD from problem 2, transform the attribute, office identifier, into an entity type. Data

about an office include the phone number, the manager name, and the address.

3. In the ERD from problem 3, add a buyer entity type. A buyer entity type has a unique buyer

identifier, a Social Security number (used for government reporting only), a name, an address, a

phone number, optional spouse attributes (name and Social Security number), and preferences
Exercise Problems for Module 8 2

for the number of bedrooms and bathrooms, and a price range. An agent can work with many

buyers, but a buyer works with only one agent.

4. Revise the ERD from problem 5 by adding an offer entity type. A buyer makes an offer on a

home for a specified sales price. The offer starts on the submission date and time and expires on

the specified date and time. A unique offer number identifies an offer. A buyer can submit

multiple offers for the same home.

5. Design an ERD to represent a credit card statement. The statement has two parts: a heading

containing the unique statement number, the account number of the credit card holder, and the

statement date; and a detail section containing a list of zero or more transactions for which the

balance is due. Each detail line contains a line number, a transaction date, a merchant name, and

the amount of the transaction. The line number is unique within a statement.

6. Modify your ERD from problem 10. Everything is the same except that each detail line contains

a unique transaction number in place of the line number. Transaction numbers are unique across

statements.

You might also like