Douglas Properties & Management

1. DouglasProperties & Management MishaalAlruwaili Spencer Lauber Linh Vu

2. Project Description • Housing company that operates in Spokane, WA • South Hill, North Hill, Logan, Riverside • Large number of properties that include residential spaces including: • Standalone houses • Apartment complexes • Townhouses

3. Project Description • Douglas Properties & Management has lease agreements for each room in each house/apartment • Keeps track of all the individual tenants in the units

4. Project Objectives • Due to the complexity of Douglas’s information, we have designed a database model that increases the effectiveness and efficiency of managing data • Increase efficiency in viewing the properties and maintaining all units in each property • Easily track renters’ information including location, payments • Differentiate Douglas form other competitors in the housing industry

5. System Development Life Cycle • Analysis • Design: Three major parts • Tenant • Property • Employee • Implementation • Tenant, Payment, Contract • Property, Room • Employee (Administrative, Landscaping) • (Look-up tables: Neighborhood, Neighborhood Properties) • Maintenance • Continuously add more data to the database immediately after any changes

6. ERD Model TENANT & PROPERTIES

7. EMPLOYEE & LOOK-UP TABLES

8. Third Normal Form

9. SQL Commands (Payment table) • DROP TABLE payment CASCADE CONSTRAINTS; • CREATE TABLE payment (tenant_idNUMBER(5), contract_idNUMBER(5), date_paid DATE, on_timeCHAR(1), amount_paidNUMBER(6,2), CONSTRAINT payment_cpk PRIMARY KEY (Tenant_ID, Contract_ID, Date_Paid), CONSTRAINT payment_On_Time_ck CHECK (On_Time IN ('Y','N')), CONSTRAINT payment_contract_id_fk FOREIGN KEY (contract_id) REFERENCES contract (contract_id), CONSTRAINT payment_tenant_id_fk FOREIGN KEY (tenant_id) REFERENCES tenant (tenant_id));

10. SQL Commands (Payment table) INSERT INTO payment (tenant_id, contract_id, date_paid, on_time, amount_paid) VALUES ('11001', '22001', '01-JUN-2013', 'Y', '400');

11. SQL Query 1 • Listing of all our tenants addresses to send them a notice of inspection SELECT t.fname, t.lname, k.room_id, p.address, p.city, p.state, p.zip FROM tenant t, room r, property p, contract k WHERE k.room_id = r.room_id AND k.tenant_id = t.tenant_id AND r.property_id = p.property_id ORDER BY lname;

12. SQL Query 1 Result

13. SQL Query 2 • Number of rooms in each building and the type of building. SELECT p.address, p.building_type, COUNT(r.room_id) "NUMBER_OF_ROOMS“ FROM property p, room r WHERE r.property_id = p.property_id GROUP BY p.building_type, p.address;

14. SQL Query 2 Result

15. SQL Query 3 • Number of rooms still available in each building. SELECT p.address, p.building_type, COUNT(r.room_id) "AVAILABLE_ROOMS“ FROM property p, room r WHERE r.property_id = p.property_id AND r.room_id NOT IN (SELECT k.room_id FROM contract k) GROUP BY p.building_type, p.address;

16. SQL Query 3 Result

17. SQL Query 4 • Which rooms are available in 110 Wellesey? SELECT r.room_id, r.square_footage FROM room r, property p WHERE r.property_id = p.property_id AND p.address = '110 WELLESY ST‘ AND r.room_id NOT IN (SELECT k.room_id FROM contract k);

18. SQL Query 4 Result

19. SQL Query 5 • Renters who has a half year contract SELECT t.lname || ', ' || t.fname "NAME", t.tenant_id AS "TENANT_NUMBER“ FROM tenant t, contract k WHERE t.tenant_id = k.tenant_id AND (k.date_end - k.date_begin) = 213 ORDER BY t.lname;

20. SQL Query 5 Result

21. SQL Query 6 • Average hourly wage of landscaping employees. SELECT TO_CHAR(AVG(hourly_wage), '$999.99') FROM landscaping_employee;

22. SQL Query 6 Result

23. SQL Query 7 • Which Landscaping employee makes the highest hourly wage among all other employees, and in which neighborhood is he/she assigned to? SELECT DISTINCT e.employee_id, e.fname, e.lname, TO_CHAR(lse.hourly_wage, '$999.99'), lse.neighborhood_id FROM employee e, landscaping_employeelse WHERE e.employee_id = lse.lsemployee_id AND lse.hourly_wage= (SELECT MAX(hourly_wage) FROM landscaping_employee);

24. SQL Query 7 Result

25. SQL Query 8 • Name, monthly rent and contract id of tenant who lives in room 11007 SELECT r.room_id, t.fname, t.lname, t.tenant_id, TO_CHAR(c.monthly_rent_due, '$999.99'), c.contract_id FROM room r, contract c, tenant t WHERE t.tenant_id = c.tenant_id AND c.room_id = r.room_id AND t.tenant_id = 11007;

26. SQL Query 8 Result

27. SQL Query 9 • List all tenants who have made a late payment, includes: id, names, number of late payments they have made SELECT DISTINCT t.tenant_id, t.fname, t.lname, COUNT(on_time) AS "# OF LATE PAYMENTS" FROM tenant t, payment p WHERE t.tenant_id = p.tenant_id AND p.on_time = 'N‘ GROUP BY t.tenant_id, t.fname, t.lname;

28. SQL Query 9 Result

29. Managerial and organizational impacts • Making Douglas Properties & Management operations run much more efficiently • Improve the ease and relevance of data retrieval • Efficiently analyze historical data to find out important information • Manage the company more efficiently

30. Conclusion • Have a chance to experience a real life example of interacting with a decent sized database system • Teamwork • Minimize conflicts in data entry stage • Time saving • Minimize database anomalies • Encounter several challenges because of transitive dependencies

31. Questions ?