The New Home case study maintains information regarding new homebuilders and the models that they offer at various subdivisions in Indiana. The Indiana State Government runs this system as a service to people through terminal kiosks distributed in 50 different locations in IN. This project requires to be acomplished in 4 weeks. The program should provide accurate information because it will be open to public as officially provided by the state government. However, we never had any experience like this kind of services before. A homebuilder may offer the same model at more than one subdivision, and the price that they offer a model may be different at each subdivision. Each subdivision has lots that are identified by a lot number. Each lot has an associated street address, size in square feet and possibly an additional premium for that lot. A premium is usually assessed if the lot is larger than the average lot or is on a cul de sac or corner within the subdivision. When a lot is sold, there is a homebuilder’s model associated with the sale. The status of the sale is also recorded. a. Conceptual Design Assume the following based on the high-level introduction to the New Home enterprise:

-The characteristics of a homebuilder include a unique identification number, a name, and an address and phone number for the headquarters of that homebuilder. An address is further broken down into the street address, city and zip code. The state is not recorded in this enterprise, since the state is assumed. A homebuilder offers several models.

-The characteristics of a model include an identification number for the model that is unique for its homebuilder. A model also has a name and square footage. Whether the model is a single story or two story home is also recorded. A model may be offered at various subdivisions. The price that a model is offered at a given subdivision is recorded.

-The characteristics of a subdivision include its name, which is assumed to be unique, and the city and zip code where the subdivision is located. The state is assumed to be local to the enterprise.

-The characteristics of a lot include a lot number that is only unique for its subdivision. Each lot has an associated street address, size in square footage, and a possible premium. A sold lot has a particular homebuilder’s model associated with it. The status of the sale indicates whether the house construction is pending or completed.

Whenever you need to make decisions, please do so with the reasons described.

2.Please provide ACD and AFDs (from level 0 to level 2).

3.Please provide use case 1 (text version 1 directly collected description from users) and use case 3 (text version 3 using the format IEEE set.) for the project described in the question 7.

4.Please provide 2 ERDs for the project given in the question 7, one in the relational database model, and the other in the star schema of data warehouse models.