We are asked to design a database management system for all information related to a real-estate company which has several branc
hes 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.
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.
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.
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.
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.
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.
1 answer:
Answer:
Below is the extended ER diagram of the given scenario. It has 6 main entities connected via 5 relationship .IS A hierarchy is used to Contract and its sub classes Rent and Sell. Each entity has certain set of attributes and primary key is shown using underlined attribute.
Cardinality is shown using crow's feet notation.
You might be interested in
Answer:
1). Read & execute
2). List folder contents
3). Read
Explanation:
See image
