Physical Database Design

Relations

---

address ( addressid (PK),addressline1, addressline2, addressline3, city, state, zipcode)

property ( propertyid (PK), taxid, dedicationdate, sellingprice, addressid (FK) )

propertyvalue (propertyvalueid (PK), assessmentdate, value, propertyid (FK) )

insurancecompany (insurancecompanyid (PK), companyname, companyid, phonenumber, faxnumber, addressid (FK) )

insurancedata (insurancedataid (PK), policynumber, insurancecompanyid (FK), rate, policyeffectivedate, policyexpireationdate, paymentdate, propertyid (FK) )

homeowner (homeownerid (PK), firstname, middlename, lastname, birthdate, spousefirstname, spouselastname, spousebirthdate, currentaddressid (FK), moveindate, propertyid (FK) )

mortgagedata (homeownerid (PK) (FK), settlementdate, incomeasofsettlementdate, expectedmontlypayment, enddate, initialprinciplebalance, initialescrowbalance, principlebalance, escrowbalance )

mortgagepayment (mortgagepaymentid (PK), homeownerid (FK), invoicedate, paymentduedate, paymentreceiveddate, amount, amountpaid, amountappliedtoprinciple, paymenttype, paymentnumber )

arreagecredit (arreagecreditid (PK), amount, appliedfromid (FK), appliedtoid(FK) )

sweatequitybalance (homeownerid (PK, FK), sweatequityhours )

sweatequity (sweatequityid (PK), sedate, amount, name, sweatequitybalanceid (FK) )

taxdata (taxdataid (PK), duedate, datepaid, amount, taxtype, propertyid (FK) )

homeownerchild (homeownerchildid (PK), firstname, middlename, lastname, birthdate, homeownerid (FK) )

---

Referental Integrety Requirements

---

• addressid in property must exist in address as addressid
• addressid in insurancecompany must exist in address as addressid
• currentaddressid in homeowner must exist in address as addressid
• propertyid in propertyvalue must exist in property as propertyid
• propertyid in insurancedata must exist in property as propertyid
• propertyid in homeowner must exist in property as propertyid
• propertyid in taxdata must exist in property as propertyid
• homeownerid in mortgagepayment must exist in mortgagedata as homeownerid
• homeownerid in mortgagedata must exist in homeowner as homeownerid
• sweatequitybalanceid in sweatequity must exist in sweatequitybalance as sweatequitybalanceid
• homeownerid in sweatequitybalance must exist in homeowner as homeownerid
• homeownerid in homeownerchild must exist in homeowner as homeownerid
• insurancecompanyid in insurancedata must exist in insurancecompany as insurancecompanyid
• appliedtoid in arreagecredit must exist in mortgagepayment as mortgagepaymentid
• appliedfromid in arreagecredit must exist in mortgagepayment as mortgagepaymentid