Database Design And Business Rules For John Smith’s Real-Estate Business
- December 21, 2023/ Uncategorized
Entity Relation Diagram
Explain Data Model for John Smith’s Real-estate Business.
In accordance with the database design, documentation and identification of business rules are essential. The business rules will be useful in developing the constraints and relation participation rules for the relational model of John Smith’s real-estate’s database. The business rules in the organization’s data base will be as following.
- Customer ——– Buys ——- Properties
- Customer ——– makes —— Payments
- Property ——— receives ——– Payments
Figure 1: Entity Relation Diagram for John Smith’s real-estate business
Figure 2: Relational Model of John Smith’s real-estate business’ Data Base
|
Attribute |
Data Type |
Size |
Key Type |
|
cust id |
CHAR |
10 |
Primary Key |
|
name |
CHAR |
10 |
|
|
address |
CHAR |
10 |
|
|
contact |
NUMBER |
10 |
|
|
|
CHAR |
10 |
|
Attribute |
Data Type |
Size |
Key Type |
|
property id |
CHAR |
10 |
Primary Key |
|
name |
CHAR |
10 |
|
|
location |
CHAR |
10 |
|
|
construction type |
CHAR |
10 |
|
|
roofing type |
CHAR |
10 |
|
|
number of bedrooms |
CHAR |
10 |
|
|
number of toilets |
CHAR |
10 |
|
|
living room size |
CHAR |
10 |
|
|
dining room size |
CHAR |
10 |
|
|
kitchen size |
CHAR |
10 |
|
|
price |
NUMBER |
10 |
|
Attribute |
Data Type |
Size |
Key Type |
|
CHAR |
10 |
Primary Key |
|
|
property id |
CHAR |
10 |
Foreign key |
|
DATETIME |
|||
|
start date |
DATETIME |
||
|
end date |
DATETIME |
||
|
detail |
CHAR |
100 |
|
|
stamp duty |
CHAR |
10 |
|
|
status |
CHAR |
10 |
|
Attribute |
Data Type |
Size |
Key Type |
|
CHAR |
10 |
Primary Key |
|
|
property id |
CHAR |
10 |
Foreign key |
|
settlement date |
DATETIME |
||
|
selling date |
DATETIME |
||
|
detail |
CHAR |
10 |
|
|
stamp duty |
CHAR |
10 |
|
Attribute |
Data Type |
Size |
Key Type |
|
CHAR |
10 |
Primary Key |
|
|
property id |
CHAR |
10 |
Foreign key |
|
settlement date |
DATETIME |
||
|
stamp duty |
CHAR |
10 |
|
Attribute |
Data Type |
Size |
Key Type |
|
payment id |
CHAR |
10 |
Primary Key |
|
property id |
CHAR |
10 |
Foreign key |
|
cust id |
CHAR |
10 |
Foreign key |
|
amount |
Numeric |
10 |
|
|
date |
DATETIME |
The customer contacts John Smith’s real-estate for acquiring property. The customer then make payment against the purchase and payments are received against the purchase. The organization presents three kinds of property such as for lease, buy and sell. Buy property represents the properties that the organization have bought. The payment will contain the primary key of property and customer as foreign keys for connecting the customer table with the property table. All the selected relationships and the entities are free from any kind of problem. Various assumptions have been made such as the payment procedure will be same for all types of property and in terms of buying property from a third party, the third party will be the customer. The properties will be storing the settlement date for the properties that will have been leased or sold.
5.1 Customer:
CREATE TABLE customer
( cust_id char (10) NOT NULL,
name char(10) NOT NULL,
address char(10),
contact number,
email char(10),
CONSTRAINT customer_pk PRIMARY KEY (cust_id));
5. 2 Property:
CREATE TABLE property
(property_id char (10) NOT NULL,
name char(10) NOT NULL,
location char(10),
construction type char(10),
roofing type char(10),
number of bedrooms char(10),
number of toilets number
living room size number
dining room size number
kitchen size number
price number
CONSTRAINT propertyr_pk PRIMARY KEY (property_id));
5.3 Property for Lease:
CREATE TABLE property for lease
(lease id char (10
property_id char (10) NOT NULL,
settlement date DATE,
start date DATE,
end date DATE,
detail char(100),
stamp duty char(10),
status char(10),
CONSTRAINT property for lease _pk PRIMARY KEY (lease id),
CONSTRAINT fk_ property for lease
FOREIGN KEY (property_id)
REFERENCES property (property_id));
5.4 Property for Sale:
CREATE TABLE property for sale
(sale id char (10
property_id char (10) NOT NULL,
selling date DATE,
detail char(100),
stamp duty char(10),
CONSTRAINT property for sale _pk PRIMARY KEY (sale id),
CONSTRAINT fk_ property for sale
FOREIGN KEY (property_id)
REFERENCES property (property_id));
5.5 Property Bought:
CREATE TABLE property for buy
(buy id char (10),
property_id char (10) NOT NULL,
settlement date DATE,
stamp duty char(10),
CONSTRAINT property for buy_pk PRIMARY KEY (buy id),
CONSTRAINT fk_ property bought
FOREIGN KEY (property_id)
REFERENCES property (property_id));
5.6 Payment:
CREATE TABLE payment
(payment id char (10),
property_id char (10,
cust id char (10),
amount number,
payment date DATE,
stamp duty char(10),
CONSTRAINT property for buy _pk PRIMARY KEY (payment id),
CONSTRAINT fk_ payment bought
FOREIGN KEY (property _id)
REFERENCES property (property _id),
CONSTRAINT fk2_ payment bought
FOREIGN KEY (cust _id)
REFERENCES property (cust _id));
6.1 Customer:
Insert into Customer values (1, “Allen”, “Canberra”, “93214”, “[email protected]”);
Relational Model
Insert into Customer values (2, “Brien”, “Sydney”, “21134”, “[email protected]”);
Insert into Customer values (3, “Rachel”, “Brisbane”, “12345”, “[email protected]”);
Insert into Customer values (4, “James”, “Melbourne”, “54321”, “[email protected]”);
Insert into Customer values (5, “Lily”, “Sydney”, “65498”, “[email protected]”);
|
cust_id |
name |
address |
contact |
|
|
1 |
Allen |
Canberra |
93214 |
|
|
2 |
Brien |
Sydney |
21134 |
|
|
3 |
Rachel |
Brisbane |
12345 |
|
|
4 |
James |
Melbourne |
54321 |
|
|
5 |
Lily |
Sydney |
65498 |
6.2 Property:
Insert into Property values (1, “Sydney Heights”, “Sydney”, “brick veneer”, “steel”, 2, 1, “12 x 18 ft”, “18 x 26 ft”, “7 x 10 ft”, 3000.00);
Insert into Property values (2, “Melbourne District”, “Melbourne”, “brick veneer”, “tiled,”, 3, 2, “15 x 22ft”, “22 x 26 ft”, “12 x 18 ft”, 80000.00);
Insert into Property values (3, “Brisbane Apartment”, “Brisbane”, “double brick”, “tiled”, 2, 1, “11x 17ft”, “22 x 26 ft”, “8 x 12 ft”, 4000.00);
Insert into Property values (4, “Home Sweet Home”, “Melbourne”, “weatherboards”, “steel,”, 3, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 120000.00);
Insert into Property values (5, “My House”, “Sydney”, “double brick”, “steel”, 2, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 9000.00);
Insert into Property values (6, “Street View”, “Sydney”, “brick veneer”, “steel”, 2, 1, “12 x 18 ft”, “18 x 26 ft”, “7 x 10 ft”, 30000.00);
Insert into Property values (7, “Lacer’s House”, “Melbourne”, “brick veneer”, “tiled,”, 3, 2, “15 x 22ft”, “22 x 26 ft”, “12 x 18 ft”, 8000.00);
Insert into Property values (8, “Biram’s Apartment”, “Brisbane”, “double brick”, “tiled”, 2, 1, “11x 17ft”, “22 x 26 ft”, “8 x 12 ft”, 40000.00);
Insert into Property values (9, “Melbourne’s Paradise”, “Melbourne”, “weatherboards”, “steel,”, 3, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 120000.00);
Insert into Property values (10, “Sydney Primes”, “Sydney”, “double brick”, “steel”, 2, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 90000.00);
Insert into Property values (11, “Happy Apartment”, “Sydney”, “brick veneer”, “steel”, 2, 1, “12 x 18 ft”, “18 x 26 ft”, “7 x 10 ft”, 30000.00);
Insert into Property values (12, “Paradise”, “Melbourne”, “brick veneer”, “tiled,”, 3, 2, “15 x 22ft”, “22 x 26 ft”, “12 x 18 ft”, 80000.00);
Insert into Property values (13, “Rain Fall”, “Brisbane”, “double brick”, “tiled”, 2, 1, “11x 17ft”, “22 x 26 ft”, “8 x 12 ft”, 40000.00);
Insert into Property values (14, “Roof top”, “Melbourne”, “weatherboards”, “steel,”, 3, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 12000.00);
Insert into Property values (15, “My House”, “Sydney”, “double brick”, “steel”, 2, 1, “15x 19ft”, “27 x 31 ft”, “14 x 17 ft”, 90000.00);
|
Property_id |
name |
location |
construction_type |
roofing_type |
Number_of_bedrooms |
Number_of_toilets |
Living_room_size |
Dining_room_size |
Kitchen_size |
price |
|
1 |
Sydney Heights |
Sydney |
brick veneer |
steel |
2 |
1 |
12 x 18 ft |
18 x 26 ft |
7 x 10 ft |
3000.00 |
|
2 |
Melbourne District |
Melbourne |
brick veneer |
tiled |
3 |
2 |
15 x 22ft |
22 x 26 ft |
12 x 18 ft |
80000.00 |
|
3 |
Brisbane Apartment |
Brisbane |
double brick |
tiled |
2 |
1 |
11x 17ft |
22 x 26 ft |
8 x 12 ft |
4000.00 |
|
4 |
Home Sweet Home |
Melbourne |
weatherboards |
steel |
3 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
120000.00 |
|
5 |
My House |
Sydney |
double brick |
steel |
2 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
9000.00 |
|
6 |
Street View |
Sydney |
brick veneer |
steel |
2 |
1 |
12 x 18 ft |
18 x 26 ft |
7 x 10 ft |
30000.00 |
|
7 |
Lacer’s House |
Melbourne |
brick veneer |
tiled |
3 |
2 |
15 x 22ft |
22 x 26 ft |
12 x 18 ft |
8000.00 |
|
8 |
Biram’s Apartment |
Brisbane |
double brick |
tiled |
2 |
1 |
11x 17ft |
22 x 26 ft |
8 x 12 ft |
40000.00 |
|
9 |
Melbourne’s Paradise |
Melbourne |
weatherboards |
steel |
3 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
120000.00 |
|
10 |
Sydney Primes |
Sydney |
double brick |
steel |
2 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
90000.00 |
|
11 |
Happy Apartment |
Sydney |
brick veneer |
steel |
2 |
1 |
12 x 18 ft |
18 x 26 ft |
7 x 10 ft |
30000.00 |
|
12 |
Paradise |
Melbourne |
brick veneer |
tiled |
3 |
2 |
15 x 22ft |
22 x 26 ft |
12 x 18 ft |
80000.00 |
|
13 |
Rain Fall |
Brisbane |
double brick |
tiled |
2 |
1 |
11x 17ft |
22 x 26 ft |
8 x 12 ft |
40000.00 |
|
14 |
Roof top |
Melbourne |
weatherboards |
steel |
3 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
12000.00 |
|
15 |
My House |
Sydney |
double brick |
steel |
2 |
1 |
15x 19ft |
27 x 31 ft |
14 x 17 ft |
90000.00 |
6.3 Property_for_lease:
Insert into Property_for_lease values (1, 5, “5/5/16”, “7/6/16”, “21/9/16”, “”, 123.00, “Not Started”);
Insert into Property_for_lease values (2, 14, “”, “”, “”, “”, 275.00, “”);
Insert into Property_for_lease values (3, 1, “17/5/16”, “21/5/16”, “11/7/16”, “”, 345.00, “Active”);
Insert into Property_for_lease values (4, 7, “”, “”, “”, “”, 134.00, “”);
Insert into Property_for_lease values (5, 3, “17/5/16”, “25/6/16”, “11/9/16”, “”, 75.00, “Expired”);
|
lease_id |
property_id |
settlement_date |
start_date |
end_date |
detail |
stamp_duty |
status |
|
1 |
5 |
5/5/16 |
7/6/16 |
21/9/16 |
123.00 |
Not Started |
|
|
2 |
14 |
275.00 |
|||||
|
3 |
1 |
17/5/16 |
21/5/16 |
11/7/16 |
345.00 |
Active |
|
|
4 |
7 |
134.00 |
|||||
|
5 |
3 |
17/1/16 |
25/1/16 |
11/3/16 |
75.00 |
Expired |
6.4 Property_for_sale:
Insert into Property_for_sale values (1, 8, “5/3/16”, “7/4/16”, “”, 1123.00);
Insert into Property_for_sale values (2, 15, “15/5/16”, “”, “”, 2275.00);
Insert into Property_for_sale values (3, 11, “”, “”, “”, 3445.00);
Insert into Property_for_sale values (4, 6, “21/5/16”, “25/5/16”, “”, “”, 1534.00);
Insert into Property_for_sale values (5, 2, “22/4/16”, “4/5/16”, “”, 2275.00);
|
sale_id |
property_ id |
settlement_date |
selling_date |
detail |
stamp_duty |
|
1 |
8 |
5/3/16 |
7/4/16 |
1123.00 |
|
|
2 |
15 |
15/5/16 |
2275.00 |
||
|
3 |
11 |
3445.00 |
|||
|
4 |
6 |
21/5/16 |
25/5/16 |
1534.00 |
|
|
5 |
2 |
22/4/16 |
4/5/16 |
2275.00 |
6.5 Property_bought:
Insert into Property_bought values (1, 9, “5/3/16”, “7/4/16”, “”, 1123.00);
Insert into Property_bought values (2, 12, “15/5/16”, “”, “”, 2275.00);
Insert into Property_bought values (3, 10, “”, “”, “”, 3445.00);
Insert into Property_bought values (4, 4, “21/5/16”, “25/5/16”, “”, “”, 1534.00);
Insert into Property_bought values (5, 13, “22/4/16”, “4/5/16”, “”, 2275.00);
|
buy_id |
property_ id |
settlement_date |
stamp_duty |
|
1 |
9 |
5/3/16 |
1123.00 |
|
2 |
12 |
15/5/16 |
2275.00 |
|
3 |
10 |
27/3/16 |
3445.00 |
|
4 |
4 |
21/5/16 |
1534.00 |
|
5 |
13 |
22/4/16 |
2275.00 |
6.6 Payment:
Insert into Payment values (1, 2, 1, “5/5/16”);
Insert into Payment values (2, 1, 2, “17/5/16”);
Insert into Payment values (3, 8, 1, “5/3/16”);
Insert into Payment values (4, 15, 5, “15/5/16”);
Insert into Payment values (5, 13, 1, “22/4/16”);
Insert into Payment values (6, 9, 5, “5/3/16”);
Insert into Payment values (7, 3, 3, “17/1/16”);
Insert into Payment values (8, 2, 2, “22/4/16”);
Insert into Payment values (9, 4, 4, “21/5/16”);
Insert into Payment values (10, 12, 4, “15/5/16”);
Insert into Payment values (11, 6, 3, “21/5/16”);
|
payment_id |
property_ id |
cust_id |
date |
|
1 |
5 |
1 |
5/5/16 |
|
2 |
1 |
2 |
17/5/16 |
|
3 |
8 |
1 |
5/3/16 |
|
4 |
15 |
5 |
15/5/16 |
|
5 |
13 |
1 |
22/4/16 |
|
6 |
9 |
5 |
5/3/16 |
|
7 |
3 |
3 |
17/1/16 |
|
8 |
2 |
2 |
22/4/16 |
|
9 |
4 |
4 |
21/5/16 |
|
10 |
12 |
4 |
15/5/16 |
|
11 |
6 |
3 |
21/5/16 |
|
12 |
10 |
4 |
27/3/16 |
Akram, M. and Ashraf, W., 2015. Analytical Study of Black Box and White Box Testing for Database Applications.
Alkoshman, M.M., 2015. Unified Modeling Language and Enhanced Entity Relationship: An Empirical Study. International Journal of Database Theory and Application, 8(3), pp.215-227.
Byrne, B.M. and Qureshi, Y.S., 2013. UML CLASS DIAGRAM OR ENTITY RELATIONSHIP DIAGRAM? AN OBJECT-RELATIONAL CONCEPTUAL IMPEDANCE MISMATCH. ICERI2013 Proceedings, pp.3594-3604.
Charlet, K.J., Church, N.D., Hite, K.D. and Tran, R.V., INTERNATIONAL BUSINESS MACHINES CORPORATION, 2015. MANAGING DATA CONVERSION FOR USER-DEFINED DATA TYPES WITHIN A DATABASE MANAGEMENT SYSTEM. U.S. Patent 20,150,254,315.
Clifford, P., Bhandari, R. and Rogers, T., FACE Recording and Measurements, Ltd., 2015. DATABASE MANAGEMENT SYSTEM. U.S. Patent 20,150,356,130.
Varshney, G., 2012. Database Management System. Global Vision Publishing House.
