Database Management Project MIS 6326 Dr. Radha Mookerjee

GROUP 8 Sheng Liang Xing Jin Chung­Jen Chen Min Yen Ya­Chen Chen

Table of Contents
1.Company Background 2.Problem Statement 3.Database Scope and Table Design 4.Entity Relationship Diagram 5.Database Schema 6.Data Input Screens Forms 7.Contributions

1. Company Background
Rancho Palisades is a privately owned apartment leasing company based in Dallas, Texas. The leasing company, Management Support, owns and manages approximately 11,000 multi­family apartments with extraordinary amenities in Texas and Arizona. For the past 45 years, Management Support properties have been home to more than 400,000 residents. The company provides an outstanding living experience including the highest level of maintenance and service to its residents. Located on Frankford Road, Rancho Palisades apartment homes offer unique interiors including plank flooring, vaulted ceilings, ceiling fans with lights and decorative fireplaces. Extraordinary amenities include arcade game room, two­stall carwash, business center with high­speed Internet and two of each of the following: resort­style pool, indoor basketball and racquetball courts, lighted tennis and sand volleyball, fitness center with HDTV and indoor Jacuzzi and sauna. Rancho Palisades offer Flex­lease© terms for any duration up to 14 months including fully furnished guest suites.

2. Problem Statement
With the increase of the population, the apartment complex has enormous growth over the past few years. The huge data that needs to be stored, managed and effectively drawn in request. Given the current information system of recording and storing, our groups directly believe that the execution of a database by the apartment complex would be tremendously helpful and remarkable increase the efficiency of the employees. The former process is filled with inefficiency and is time consuming. The while process involves signing a contract and getting resident information down on paper. If there were accidental human error in recording or storing the data of new residents applying, the apartment complex would face a severe problem. Therefore the database is definitely necessary to the apartment complex to prevent such cases of negligence from happening.

The database set up by the apartment complex will help to improve majority of the problems. One of the biggest advantages of having a database is easily removal, updating and accessing resident data for the employees. Also the database provides the function to check errors in contracts and records. Additionally, the apartment complex would gain enormous benefit from this database when the large number of changes in resident records, contracts and rent that happen month in month out. Therefore database management is not only useful in collecting data and recording, but also effective to manage the apartments.

3. Database Scope and Table Design

Apartment The Apartment Table contains details about the apartments in the database. Each apartment has a unique ApartmentNo, which is the primary key in the Apartment Table. The BuildingNo is the foreign key in the Apartment Table, and it references the ApartmentBuilding Table. The Apartment Table consists of the following attributes: AptPrice, SquareFeet, AptGarage, AptNumberOfBeds and AptNumberOfBaths. The AptPrice attribute is the price of the each apartment. SquareFeet attribute is the size of the indicated Apartment. The AptGarage attribute indicates whether the Apartment has a garage, or not. The AptNumberBeds and AptNumberBaths attributes in the Apartment Table indicate the number of bedrooms and bathrooms in each apartment. ApartmentBuilding Table
 The ApartmentBuilding Table includes information about the apartment building which contains a certain number of apartments. Each apartment building has a unique ABID, which is the table’s primary key. The only

attribute in this table is ABFloor. The ABFloor attribute is an integer that describes the number of floors in each of the apartment. Contract The Contract Table contains information about each contract that has been filed between the company and the resident. Each contract has a unique ContractID, which is the primary key in the Contract Table. The foreign keys in this table include: ApartmentNo and EmpID. The ApartmentNo references the Apartment Table and the EmpID references the Employee Table. The Apartment Contract Table also has the following attributes: ContractPrice, ConLeaseEffectDate, ConLeaseEndDate and ConDiscount. The ContractPrice attribute is the final negotiated price after the discount from the AptPrice. The ConLeaseEffectDate attribute is the date in which the contract is valid, and the ConLeaseEndDate is the expiration and termination of the signed contract. The ConDiscount attribute is the preferential price for residents such as UTD students.

Employee Table
 The Employee Table includes information about each employee hired by the leasing office. Each employee is assigned a unique EmployeeID, which is the primary key. The other attributes included in the table are: EmpFirstName, EmpLastName, EmpGender, EmpEmail, EmpSSN and EmpPhoneNo. The EmpFirstName and EmpLastName attributes provide the employee’s full name. The EmpGender indicates whether the employee is male or female and the EmpEmail is the email address of that employee. The EmpSSN is the social security number of the employee and

EmpPhoneNo is the phone number of each employee. The Employee table has a self referencing relationship because managers are also includes in the table. Each manager can manage none or many employees and each employee can only be managed by one manager. The Employee Table has no foreign keys Maintenance The Maintenance Table contains information about each employee working at the apartments. Each employee is given a unique MaintenanceID that is the Maintenance’s primary key. The foreign keys in this table include: EmpID and ApartmentNo. The EmpID references the Employee Table and the ApartmentNo references the Apartment Table. The Maintenance Table also has the following attributes: MaintenaneDescription and MaintenanceDate. The MaintenanceDescription attribute is the item that was worked on by the employee, and the MaintenanceDate attribute gives the date is the date that the item was worked on.

ParkingSpace Table
 The ParkingSpace Table includes the information about each parking lot the company provides. The ParkingSpace Table contains a PSID that is unique to each parking space which is provided by leasing office. The table also has the ApartmentID and ContractID as foreign keys, which reference the apartment table and contract table. The only attribute in the table is the PSCharge, which is charged to residents who rents a specific parking space.

Resident Table
 The Resident Table contains information about each resident who lives in the apartments. ResidentID, the primary key of the table, is assigned to every resident .The rest in the Resident Table include: ResFirstName, ResLastName, ResGender, ResPhoneNo, ResEmail. The ResFirstName and ResLastName attributes demonstrate the resident’s full name. The ResGender attribute states that the resident is a male or female. The ResPhoneNo and ResEmail are the phone number and email address of the resident whose name is listed in the table. Rent Table
 The Rent Table contains information about each resident in the database. The primary key of the table, RentID, is unique to each month’s rent for each resident at the apartments. Since the Rent Table is dependent on the Resident Table, the ResidentID is a foreign key in this table and references the Resident Table. The other attributes in the Rent Table include: RentMonth, RentYear and RentDue. RentMonth is the attribute that shows the month of the rent, the RentYear shows the year and the RentDue is the dollar amount of the rent due at that certain time period. WaterBill The WaterBill Table contains information about the water bill for each apartment. Every water bill has a unique WaterBillID, which is the WaterBill Table’s primary key. The other attributes in the UtilityBill Table include: WBCharge and WBDueDate. The WBCharge is water charge of every month, incurred for a certain apartment in the month indicated by WBDueDate. SocietyCharges Table
 The SocietyCharges Table includes the ‘other fees’ associated with each apartment. The Society Charge Table lists the SCID, which is unique to each apartment and

thus is the primary key of the table. The ApartmentID is also the foreign key in the SocietyCharges Table, and it references the Apartment Table. The other attributes in the table include: GarbageFee, GrassCuttingFee. The GrassCuttingFee is the fee associated with maintaining the apartment’s surroundings. The GarbageFee is the fee associated with disposing of the garbage. Reference Table The Reference Table includes the basic contact information about the references. The RefFirstName and RefLastName attributes demonstrate the reference’s full name. The RefGender attribute states that the reference is a male or female. The RefPhoneNo and RefEmail are the phone number and email address of the reference whose name is listed in the table.

ReferenceProcess Table The ReferenceProcess Table contains the information about the relationtionship between the resident and the reference. The attribute Discount shows how much discount in each reference. The ApartmentID, RefID and ResID are primary key and also foreign key in the ReferenceProcess Table.

4. Entity Relationship Diagram

5. Database Schema
Apartment (Apartment ID, AptNumberOfBeds, AptNumberOfBathes, AptGarage, Aptprice, ABID) Apartment.ABID references ApartmentBuilding.ABID Apartment Building (ABID,ABFloor) Contract(ContractID,ContractPrice,ConMove­InDate,ConLeaseExpires,ConLease Term,ConDiscount, ApartmentID,EmpID) Contract.ApartmentID references Apartment.ApartmentID Contract.EmpID references Employee.EmpID Parking Space (PSID, PSCharge, ApartmentID, ContractID) ParkingSpace.ApartmentID references Apartment.ApartmentID ParkingSpace.ContractID references Contract.ContractID Employee(EmpID, EmpFirstName,EmpLastName,EmpGender,EmpEmail,EmpSSN, EmpPhoneNo,EmpManager) Employee.EmpManager references EmpSSN Resident(ResID,ApartmentID,ResFirstName,ResLastName,ResGender,ResEmail,R esPhoneNo, ContractID) Resident.ContractID references Contract.ContractID ResidentApartmentID references Apartment.ApartmentID Rent(RentID,ApartmentID,ResID,RentMonth,RentYear,RentDueDate) Rent.ApartmentID references Apartment.ApartmentID Rent.ResID references Resident.ResID SocietyCharges(SCID,ApartmentID,GarbageFee,GrassCutingFee)

SocietyCharges.ApartmentID references Apartment.ApartmentID WaterBill(WaterBillID,WBCharge, WBDueDate,ApartmentID) WaterBill.ApartmentID references Apartment.ApartmentID Maintenance (MainID,EmpID,ApartmentID,MainDesc,MainDate) Maintenance.EmpID references Employee.EmpID Maintenance.ApartmentID references Apartment.ApartmentID Reference(RefID,RefFirstName,RefLastName,RefGender,RefEmail,RefPhoneNo) ReferenceProcess(ApartmentID,RefID,ResID,Discount) ReferenceProcess.ApartmentID references Resident.ApartmentID ReferenceProcess.RefID references Reference.RefID ReferenceProcess.ResID references Resident.ResID

6. Data Input Screens Forms 6.1 Main Menu

The Main Menu allows the user to view all database information.

6.2 Sample Reports
Users can quickly access a summary of important information through reports. These reports include the available apartments report , available parking sapce report, fee report, late fee report, rent history and maintenance report.

Report Homepage is a page that allows easy access to each important report.

Available apartments report page shows which apartment is currently available and information related to the apartment’s feature and unique ID. This report allows employees to view the detailed information for each apartment.

Available parking sapce report(Lotavailabe) page shows which parking sapce is currently available and information related to the lot’s unique ID and price. This report allows employees to view the detailed information for each lot.

Fee Report page displays all of the fees for each apartment and sums the amount. This report allows employees and customers to view each charge and the total on one page.

LateFee report shows apartments which did not turn in rent on time so a late fee is charged.This report allows employees and customers to view what date the late rens is paid and the amount of late fee.

Rent report provides the rent history of all the residents. The report displays the apartment number, the rent ID, the paid date and the paid amount. Employees can check detailed information from this report.

Maintaince report shows what date the maintaince is done, description of the maintaince and its unique ID. Employee ID and apartment number is also showen in this report. Employees can check details is each maintaince from this report.

6.3 Views

The Apartment View can show all the details about every apartment/room, including numbers of bedroom, numbers of bathroom, room max and min price and the square feet. User can get the whole information of the apartments easily.

The Resident View can show all the information about all the existing residents including resident name , contact information and related contract number. This view is grouped by distinct apartment number which make the whole screen clear and easy to understand by the users.

The Employee View shows all the employee information including employee names, basic information and related all these information to the contract number. The users now can easily know which employee was in charge of which contracts so that the works are assigned clearly and improve the work efficiency huge a lot.

7. Contribution
Contributions were made by Sheng Liang, Xing Jin, Chung­Jen Chen, Min Yen and Ya­Chen Chen. The following table is a breakdown of what each member worked on in this project. Task Name Company Background Information Problem Statement Database Design ERD Database Schema Access Data Building SQL Query Report Design Screenshots and Description Report Input/Documentation Editing/Formating Team Member Ya­Chen Chen Xing Jin,Min Yen The whole group The whole group Min Yen, Ya­Chen Chen Xing Jin,Sheng Liang,Chung­Jen Chen Xing Jin,Sheng Liang,Chung­Jen Chen Xing Jin,Sheng Liang,Chung­Jen Chen Xing Jin,Sheng Liang,Chung­Jen Chen The whole group Sheng Liang,Min Yen