i m 22 f living in pakistan, doing self study of db.
It would be appreciated if u help me to find out the followings.

1. A GENERALIZED SEMANTIC MODEL to identify entities, attributes, relationships
and subtypes indicated by the problem statement.

2. RELATION SCHEMA with primary keys and foreign keys identified together
an assessment of the degree of NORMAL FROM achieved within schema.

3. A discussion of other possible INTEGRITY CONSTRAINTS which could be defined
to maintain the representational accuracy of the database.

From the following problem statement

Problem Statement

ABC Housing plc is a large property company. They specialize in building
for sale and have many development sites in the country. The company have

decided to set up a database in order to get immediate access to the latest
on the current state of both building work and sales in progress at all
their sites.

Each different SITE within the same town has a UNIQUE NAME, eg the Top Hill,
Beach View etc., and is managed by a
SITE MANAGER. Contact information for the site manager includes name, site
office telephone number and mobile telephone number.

Each house PLOT on a site is identified by a plot number. ABC Housing offer
a variety of house types and specifications eg

The Cottage 5 bedrooms 2 bathrooms utility room double detached garage
The Villa 4 bedrooms 2 bathrooms utility room double integral garage
The Bungalow 3 bedrooms single integral garage

The PRICE of each house type varies with the location eg the Cottage house
type is $500,000 at upper class area , but is offered at $450,000 at low
class area.
When the original site plan is drawn up, a specific house type is allocated
to each plot.

A buyer may choose a number of optional extras :
1. superior kitchen appliances $10000
2. colour coordinated decorating service $5000
3. carpeting throughout price depends on house type eg
$15000 for the Cottage
Other optional extras may be introduced to the ABC Housing portfolio in the
future. The progress of each house is monitored during building and the following
key dates are noted:
completion of structure
completion of services (plumbing and electrical)
ready for occupation ( after final inspection )
Buyer may reserve a plot by paying a small deposit.

The buyer is identified in ABC Housing records by a unique CUSTOMER NUMBER,
plus of course details such as name, address and phone number.

After reserving a plot a buyer may specify which of the optional extras is
Once the purchase CONTRACT has been signed between ABC Housing and the buyer,
no more options can be asked for. The date of signing the contract is recorded.

When the home is ready for occupation, a completion date can be agreed. On
the completion date the remainder of the purchase price ( including optional
extras) is paid and the buyer can move in. Naturally buyer will not be willing
to move in until all options requested have also been completed.

All details of house sales are dealt with by a small team (usually 2-3) of

Information to be stored about the sales team includes first name and site
office telephone
number. Customer and potential customer may call to the site office at any
time, but to
be sure of seeing a sales negotiator, they can make an appointment.

The appointment details include :-
date and time of appointment (half-an-hour is allowed per appointment and

the office is open from 10am to 6pm)
name of negotiator
customer id (if they have one) and name
purpose eg reserve plot, choose options, view progress of property construction.

Once a plot has been reserved, the customer is allocated a customer number
and supplies
ABC Housing with the name of the SOLICITOR who will be handling the purchase.

Anticipated Queries
In order to assess the potential of your ORACLE database system for company
use, the database should be able to supply answer to the following queries:

1. a list of all currently allocated customer number and customer names
2. the full specification of the Cottage house type
3. a list of the house styles and prices at the high class state.
4. names and locations of all current development sites, including site manager
5. for plot 34 at the high class area, the house type and price, the construction
stage reached, plus the name of the buyer (if any), and the stage the sale
has reached
6. for sales negotiator John at the high class area, a list of all appointments
scheduled for Friday 10th together with times and customer names.
7. are there any clients who have booked to see more than one negotiator
on Friday 10 Nov.
8. the current status of all sales being handled by solicitor Umbrella Legals
9. a list of all plots on which Cottage style houses are currently being
built (excluding any already sold) together with any optional extras that
have been requested for each plot
10. The total number of buyer (both at each site and across the country as
a whole) who
have requested the color coordinated decorating service) - ABC Housing wish
to see how
much demand there is for this service.

best regards