We are asked to design a database management system for all information related to a real-estate company which has several branches throughout the United States. The first step is to organize the information given about company. We have collected the following data:

• Each branch has a unique branch number. It allocated staff, which includes one Manager. Also, each branch has a list of available houses to rent/sell.

• The manager is responsible for the day-to-day running of a given branch.

• Each staff has a unique staff number, name, position, salary, and branch number.

• Each house has a unique house number, address, rent cost, sell value, status, branch number.

• The statue of a house indicates whether it is available for rent/sell. The branch number indicate which branch of the real-estate company can rent/sell the house.

• A Customer has SSN, name, contract number, house number.

• Each Contract has a unique contract number, type, date, branch number, and customer number.

• If the type of a contract is rent, it will have a deposit, and rent cost, and lengths of contract. The length of a rent contract can be six months, one year or 2 years but costumers can extend their contract by signing a new contract.

• Each contract extension has a unique extension number, the original contract number, and discount value.

• If the contract type is "sell", it will have sell value.

• Customers can issue a contract termination request. Each termination request has a unique request number, customer number, and termination cost.

1. Draw an ER diagram for this database. Make sure to indicate primary keys, cardinality constraints, weak entities (if any), and participation constraints. List any assumptions you make in the process. Hint: You may need an ISA hierarchy somewhere.

2. Translate the ER diagram in #1 into relational database tables (i.e. give the SQL DDL statements). Make sure that the translation captures key constraints (primary keys and foreign keys if applicable) and participation constraints in the ER diagram. Identify constraints, if any, that you are not able to capture.