Data Model for John Smith’s Real-estate Business

Question: Explain Data Model for John Smiths Real-estate Business. Answer: 1. Business Rules: 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 Smiths real-estates database. The business rules in the organizations data base will be as following. Customer -------- Buys ------- Properties Customer -------- makes ------ Payments Property --------- receives -------- Payments 2. Entity Relationship Diagram in 3NF: Figure 1: Entity Relation Diagram for John Smiths real-estate business 3. Relational Model: Figure 2: Relational Model of John Smiths real-estate business Data Base Customer table: Attribute Data Type Size Key Type cust id CHAR 10 Primary Key name CHAR 10 address CHAR 10 contact NUMBER 10 email CHAR 10 Property table: 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 Property for lease table: 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 Property for sale table: 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 Property bought table: 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 Payment table: 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 4. Justification of Choices: The customer contacts John Smiths 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. Create Query: 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. Insert Into Query: 6.1 Customer: Insert into Customer values (1, Allen, Canberra, 93214, alen@mail.com); Insert into Customer values (2, Brien, Sydney, 21134, brien@mail.com); Insert into Customer values (3, Rachel, Brisbane, 12345, rachel@mail.com); Insert into Customer values (4, James, Melbourne, 54321, james@mail.com); Insert into Customer values (5, Lily, Sydney, 65498, lily@mail.com); cust_id name address contact email 1 Allen Canberra 93214 alen@mail.com 2 Brien Sydney 21134 brien@mail.com 3 Rachel Brisbane 12345 rachel@mail.com 4 James Melbourne 54321 james@mail.com 5 Lily Sydney 65498 lily@mail.com 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, Lacers 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, Birams Apartment, Brisbane, double brick, tiled, 2, 1, 11x 17ft, 22 x 26 ft, 8 x 12 ft, 40000.00); Insert into Property values (9, Melbournes 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 Lacers House Melbourne brick veneer tiled 3 2 15 x 22ft 22 x 26 ft 12 x 18 ft 8000.00 8 Birams Apartment Brisbane double brick tiled 2 1 11x 17ft 22 x 26 ft 8 x 12 ft 40000.00 9 Melbournes 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 Bibliography: 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.