Problem Description

The University Housing office receives many applications from graduate and married students requesting an apartment on campus. There are five housing villages in campus, and each village has about 500 apartments. Each apartment falls into one of the categories given in the table below. Village 1 has apartments in categories 1 and 3 only. The apartments in villages 2 and 3 are typically in categories 2 and 4. The other two villages have apartments in categories 5-12.

To be eligible to apply, students must be graduate students or married undergraduate/graduate students. Priorities in assigning an apartment are given based on marital status, degree pursued, and application date. Married students have the highest priority whether they are graduate or undergraduate students. Doctoral (PhD) students have higher priority than masters (MS) students.

Applicants can mark three preferences for the village and apartment type desired. Based on availability, they will be offered an apartment. Applicants can reject the offer. After three rejections, they will have to apply again. A non-refundable fee is required for each application. Only one application is permitted for each student. Married couples, however, can have two separate applications if they are both students. Single students are not allowed to share apartments. A refundable fee is required upon moving in. Notification for vacating premises is required one month prior to the move-out date. After graduation, students are allowed to stay in the apartment at most one more semester.

Database Design

The following are the main entity types of the Housing department database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. Identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the relations created, indicate its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.

1. Resident: The main attributes are identification number, name, gender, marital status, college, and department of the family head; address, telephone number, etc.

2. Applicant (a student who has applied for on-campus housing but has not yet been assigned an apartment): The main attributes are identification number, name, gender, address, telephone number, marital status, college, department, preference about the village (the name of the village they want to stay in), preference about the apartment (single bedroom, double bedroom, furnished, unfurnished, etc.), etc.

3. Maintenance Request: The main attributes are address (this includes building and apartment numbers), description of the maintenance problem, name of the resident, submission date, date the maintenance problem was fixed, name of the employee responsible, etc.

4. Apartment: The main attributes are address (it consists of village name, building number, and apartment number), number of bedrooms, air-conditioning status (central AC, window unit AC, or no AC), furniture status (furnished or unfurnished), dish washer status (whether it has a dish washer), etc.

Access Application Development

The following are some of the queries, forms, and reports one can create to increase the functionality of the database.
Queries:

1. At the end of each semester, the Housing department prepares a list of all the apartments that will be available for the next semester. The list provides details about the address, air conditioning status, furniture status, etc. of all the apartments available.

2. In assigning an apartment, the Housing department prioritizes married couples (versus unmarried), PhD students (versus MS students), and early applications. List the identification number, name, address, and telephone number of the applicants who need an apartment next semester. Sort the information about the applicants based on their priority level.

3. Most of the apartments are vacated at the end of the semester. On the checkout date, an employee should visit the apartment and check its status and inventory. The Housing department has a limited number of employees, and, therefore, for scheduling purposes it is necessary to know in advance which apartments will be vacant and when the checkout date is. List the addresses of the apartments that will be vacated at the end of the semester together with the checkout dates.

4. In order to help the department with scheduling for apartment maintenance service, prepare a list of the apartments that have submitted a maintenance order for a particular date.

Forms:

1. Create a user sign-in form together with a registration form for new users.

2. Create the following data entry forms that are used for database administrative functions: residents, applicants, maintenance orders, etc. These forms allow the user to add, update, and delete information about residents, applicants, maintenance orders, etc.

3. Create a form that allows the user to check the availability of apartments in a particular category. One way that can be followed to build such a form is by using a combo box to present all the apartment categories. Once a category is chosen from the combo box, a subform (included in this form) presents the address of all the available apartments in this category.

4. Create a form that allows the user to browse through the table that contains information about the residents. Create a subform that presents for each resident the corresponding billing information. Present for each resident the total amount of money paid to the Housing department so far.

5. Create a form that allows the user to browse through the table that contains information about the applicants. Create a subform that presents for each applicant his/her marital status, degree pursued, application date, and apartment preferences.

6. The Housing department is performing a demographical study of their residents. They are interested to know about the changes in the following:

a. The number of married residents (per year) during the last 10 years

b. The number of undergraduate married residents (per year) during the last 10 years

c. The number of unmarried residents (per year) during the last 10 years

d. The number of residents pursuing a PhD degree (per year) during the last 10 years

e. The number of residents pursuing an MS degree (per year) during the last 10 years

f. The number of female residents (per year) during the last 10 years

7. Create a form that allows the user to browse through the apartments' table. Create a subform that presents for each apartment the maintenance orders that are still due.

8. Create a form that allows the user to check the status of maintenance requests for a particular day.

Reports:

1. Every day, a report with the maintenance requests due and the requests that were not handled (overdue) is presented to the maintenance department. The report contains the name of the resident, apartment address, submission date, due date, and a short description of the problem to be handled.

2. The Housing department is considering remodeling some of the apartments. Priority will be given to the apartments and villages that have had the largest number of maintenance problems during the last year. Prepare charts that present the number of maintenance requests placed by each village and by each apartment type per month during the last year.

3. Prepare a report consisting of the address and characteristics (such as, number of bedrooms, AC availability, etc.) of all the apartments that will be available next semester.

4. Report the name and current address of the applicants that need an apartment next semester. Rank the applicants based on marital status and degree pursued.

5. Every month, the Housing department mails to its residents a statement of their financial obligations (rent payments, electricity payments, etc.).

a. Create a report that presents the monthly financial obligations of each resident.

b. Use the label wizard to create a report that contains labels with the addresses of the residents.

Visual Basic.NET Application Development

This database will mainly be used by the Housing department employees. Users are asked to enter a user name and password to log in to the database. The new users are allowed to sign up. After successful login, users choose from a list the activity that they would like to perform. It is up to you to group the activities together. The following is an example of a list similar to what you will create. The list of activities consists of Resident, Applicant, Maintenance, Apartment, and Demographical Studies.

- If the option "Resident" is chosen, a new form opens that provides a list of activities that involve residents, such as add a new resident, update the information about a current resident, delete the information about a resident from the database, check a resident's financial status, maintenance requests submitted, etc.
- If the option "Applicant" is chosen, a new form opens that provides a list of activities that involve applicants, such as add a new applicant, update the information about a current applicant, delete the information about an applicant from the database, check the application status, etc.
- If the option "Maintenance" is chosen, a new form opens that provides a list of activities that involve maintenance services, such as add a new maintenance request, update the status of a request, list the requests submitted or due on a particular date, etc.
- If the option "Demographical Studies" is chosen, a new form opens that allows the user to choose the chart to be displayed (list the charts already built in part 6 of the section about the reports).

Web Extension

The Housing department allows the residents and applicants to browse through their database on-line from their PC at home or in the office. The residents/applicants can use the member identification number to log in to the database. The residents should be able to check their financial status, update their account information, and submit maintenance requests on-line. The applicants should be able to apply and check their application status on-line.

Develop an ASP.NET web application that will enable the users to access the database and perform.

Below are the instructions as to what the project report should include:

1. The complete E-R model in an E-R diagram

2. Any assumptions that you make and anything else that you think helps explain. clarify and validate your E-R model.

For each project, students need information in the project description from the beginning to the end of the Queries section. Students should discard all information after the Queries section in the project description.

To develop the E-R model for the project, students need to read information from the beginning of the project description to the end of the Queries section in order to determine what data need to be represented in the E-R model. Students cannot design the queries in the Queries section at this stage, but should read the list of queries to understand what data need to be included in the E-R model.