Database Design And Business Rules For John Smith’s Real-Estate Business

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 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	Data Type	Size	Key Type
cust id	CHAR	10	Primary Key
name	CHAR	10
address	CHAR	10
contact	NUMBER	10
email	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
lease id	CHAR	10	Primary Key
property id	CHAR	10	Foreign key
settlement date	DATETIME
start date	DATETIME
end date	DATETIME
detail	CHAR	100
stamp duty	CHAR	10
status	CHAR	10

Attribute	Data Type	Size	Key Type
sale id	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
buy id	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	email
1	Allen	Canberra	93214	[email protected]
2	Brien	Sydney	21134	[email protected]
3	Rachel	Brisbane	12345	[email protected]
4	James	Melbourne	54321	[email protected]
5	Lily	Sydney	65498	[email protected]

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.