You are creating an electronic version of the records of your countys register of deeds. The county has a number of municipalities (towns, villages, cities, etc.), and for each one you want to store a name, a type (city, township, borough, etc.), and date of incorporation. Each municipality has numerous buildings, each of which is identified by a block number and lot number (each building within a given municipality has a unique combination of block number and lot number). For each building, you want to store a street address, zip code, and the year in which it was constructed. Note that zip codes do not correspond perfectly to municipalities: some municipalities have multiple zip codes, and the boundaries of zip codes and municipalities are not perfectly aligned. Each building may have been sold many times; for each sale, you want to record its price, date, and the name of the person or company the building was sold to (you record owners names using a single, long name field because some owners are corporations). Finally, a small percentage of buildings in the county are in the national register of historic places; for these buildings, you want to record a 255 character description of the unique features of the building, the date it was inducted into the historic register, and the ID number the register assigned to it. Assume that you have access to a data table that includes the zip code and corresponding post office name for each zip code that intersects the county; include this table in your database (all these zip codes are in the same state as your county, so the state need not be stored). Set up your database so that the records for non historic buildings do not have to store

Design an appropriate database, drawing an ER diagram and writing a database design outline.

Assume that any address information specified is in US format, consisting of fields for street address, city, two-letter state code, and zip code. Unless stated otherwise in an individual problem, assume that you do not have the master zip code table in your databases, so that city, state, and zip code may be treated as independent.

1. An ER diagram contains all entities, relationships and cardinality constraints. No attribute is needed. 2. Design of all tables using outline notation. Must contain tables names, attributes, primary keys and necessary foreign keys. 3. Your ER diagram and table outline notation should be consistent with each other.