Entity Relation Diagram for InfoIsUS

 

Click to download Visio diagram file

Click to download Access Database file

 

Schema for the InfoIsUs database

Human (humanID, fName, lName, addSTHuman, cityID, stateID, zipID, phoneNOAreaCodeID, phoneNumber, sex, dOB, maritalStatus, resType, resOwnStatus, creditNameID, creditAccountNOID, employeeRecID, bLTNFFRecID, purchaseTransRecID)
Foreign Key:  cityID references City.cityID
Human.cityID must exist in City.cityID
Foreign Key:  stateID references State.stateID
Human.stateID must exist in State.stateID
Foreign Key:  zipID references ZipCode.zipID
Human.zipID must exist in ZipCode.zipID
Foreign Key:  phoneAreaCodeID references PhoneAreaCode.phoneAreaCodeID
Human.phoneAreaCodeID must exist in PhoneAreaCode.phoneAreaCodeID
Foreign Key:  creditNameID references CreditAccountName.creditNameID
Human.creditNameID must exist in CreditAccountName.creditNameID
Foreign Key:  creditAccountNOID references CreditAccountNumber.creditAccountNOID
Human.creditAccountNOID must exist in CreditAccountNumber.creditAccountNOID
Foreign Key:  employeeRecID references EmployeeRecID.employeeRecID
Human.employeeRecID must exist in EmployeeRecID.employeeRecID
Foreign Key:  bLTNFFRecID references BLTNFFRecID.bLTNFFRecID
Human.bLTNFFRecID must exist in BLTNFFRecID.bLTNFFRecID
Foreign Key:  purchaseTransRecID references PurchaseTransRecID.purchaseTransRecID
Human.purchaseTransRecID must exist in PurchaseTransRecID.purchaseTransRecID

City (cityID, cityName)

State (stateID, stateName)

ZipCode (zipID, zipNO)

PhoneAreaCode (phoneAreaCodeID, phoneAreaCodeName)

EmployeeRecordID (employeeRecID, humanID)

EmployeeRecord (employeeRecID, startDate, endDate, employerID, sSNOID, jobTitleID, deptID, occupCodeID)
Foreign Key:  employerID references Employer.employerID
EmployeeRecord.employerID must exist in Employer.employerID
Foreign Key:  sSNOID references SocialSecurityNO.sSNOID
EmployeeRecord.sSNOID must exist in SocialSecurityNO.sSNOID
Foreign Key:  jobTitleID references JobTitle.jobTitleID
EmployeeRecord.jobTitleID must exist in JobTitle.jobTitleID
Foreign Key:  deptID references Department.deptID
EmployeeRecord.deptID must exist in Department.deptID
Foreign Key:  occupCodeID references OccupationCode.occupCodeID
EmployeeRecord.occupCodeID must exist in OccupationCode.occupCodeID

Employer (employerID, employerName, employerSector, addSTEmployer, cityID, stateID, zipID)
Foreign Key:  cityID references City.cityID
Employer.cityID must exist in City.cityID
Foreign Key:  stateID references State.stateID
Employer.stateID must exist in State.stateID
Foreign Key:  zipID references ZipCode.zipID
Employer.zipID must exist in ZipCode.zipID

SocialSecurityNO (sSNOID, sSNO)

JobTitle (jobTitleID, jobTitleName)

Department (deptID, deptName)

OccupationCode (occupCodeID, occupCodeName)

PurchaseTransRecID (purchaseTransRecID, humanID)

PurchaseTrans (purchaseTransRecID, transDate, itemID, pPerU, quantity, retailerID, addST, cityID, stateID, zipID, retTranNO, creditNameID, creditAccountNOID, bestBuyMembNO, bestBuyPointsPerTrans)
Foreign Key:  itemID references Item.itemID
PurchaseTrans.itemID must exist in Item.itemID
Foreign Key:  retailerID references Retailer.retailerID
PurchaseTrans.retailerID must exist in Retailer.retailerID
Foreign Key:  cityID references City.cityID
PurchaseTrans.cityID must exist in City.cityID
Foreign Key:  stateID references State.stateID
PurchaseTrans.stateID must exist in State.stateID
Foreign Key:  zipID references ZipCode.zipID
PurchaseTrans.zipID must exist in ZipCode.zipID
Foreign Key:  creditNameID references CreditAccountName.creditNameID
PurchaseTrans.creditNameID must exist in CreditAccountName.creditNameID
Foreign Key:  creditAccountNOID references CreditAccountNumber.creditAccountNOID
PurchaseTrans.creditAccountNOID must exist in CreditAccountNumber.creditAccountNOID

Item (itemID, itemName)

Retailer (retailerID, retailerName)

CreditAccountName (creditNameID, creditName)

CreditAccountNumber (creditAccountNOID, creditAccountNO)

BLTNFFRecordID (bLTNFFRecID, humanID)

BLTNFFRecord (bLTNFFRecID, bLTNFFProgramNO, flightDate, fromID, toID, milesFlown, bLTNFFLevelID)

Foreign Key:  fromID references FromLocation.fromID
BLTNFFRecord.fromID must exist in FromLocation.fromID
Foreign Key:  toID references ToLocation.toID
BLTNFFRecord.toID must exist in ToLocation.toID
Foreign Key:  bLTNFFLevelID references EliteLevel.bLTNFFLevelID
BLTNFFRecord.bLTNFFLevelID must exist in EliteLevel.bLTNFFLevelID

FromLocation (fromID, fromCity)
ToLocation (toID, toCity)
EliteLevel (bLTNFFLevelID, levelName)