Overview

In this assignment you will logically design a relational database for a budding boarding-house company that is located in the "Motor" City. You will have two deliverables. This is an INDIVIDUAL ASSIGNMENT. DO NOT BUILD THIS DATABASE, ONLY DO THE LOGICAL DESIGN. Pay close attention to the Background information and the Logical Design Criteria, because this is where you will find everything necessary to complete the logical design.

Background

The Alpha Boarding House Company was started by Mrs. Mabel Alpha in Detroit, Michigan. She began with a single rental house, but now owns two small apartment buildings. Mrs. Alpha has just signed major contracts with the three major Automakers in Michigan to house the various college students that are newly hired at each company who come in for various new employee training sessions. The newly hired employees usually come to Detroit from all of the other states across the United States, including Hawaii. A given Employee/Trainee may attend 1 or more training sessions during their first year of employment and may stay a couple of days or even a couple of weeks.

The first apartment building contains 25 apartments of different types spread between 3 floors and the second apartment building contains 35 apartments of different types spread between 4 floors. The different types of apartments are: One, Two or Three Bedrooms. There is also a different flat nightly charge for each type of apartment as well. For Example: Employers are charged the same flat nightly rate for a Three Bedroom apartment rental, regardless if they have 1, 2 or 3 employees staying in there. So, Employers save money by having their employee/trainees room together, but they loose money if they have 3 trainees in three one bedroom apartments, because the flat nightly rate for three one bedroom rentals is greater than the nightly flat rate for one three bedroom rental.

Lastly, each of the three major automakers also have negotiated a different % discount for their employee/trainees who stay longer than 7 days.

Logical Design Criteria

The logical and relational requirements for the database that you are designing for the Alpha Boarding House Company are as follows:

Keep track of the new Employees/Trainees and their permanent (Home) addresses and contact information who stay at each of the Alpha Apartment Buildings during a given training session.

Keep track of the new Employee's/Trainee's current address and contact information. (What apartment are they residing in?)

Keep track of the length of stay for an Employee/Trainee in a specific apartment. This should be based on a Check In and Check Out date.

Keep track of all the individual apartments a given Employee/Trainee has ever stayed in. Note: This is historical data. An Employee/Trainee that stays at the one of the Boarding Houses, may get the same apartment or a different one each time that they come to Detroit for training.

Keep track of the different apartments that are in each apartment building, along with each apartment number and floor. Example: Apartment A, Floor 1 or Apartment P Floor 3.

Keep track of the different nightly charges for each of the different apartment types.

Keep track of the name, location, address and contact information of each of the apartment buildings that Mrs. Alpha owns.

Keep track of the address and contact information of the Big Three Automakers in "Motor" City.

Keep track of which Employee/Trainee works at which auto company for billing purposes.

keep track of which apartments at each building are available for rental.

Sample Reports

Below you will find examples of some of the Reports that are currently being generated. Use them as another tool to help with your analysis and creation of your logical design.

Deliverables

Entity Relationship Logical Data Model: A visual model which represents each table (entity) and the relationships between the tables. This diagram can be completed with MS. Word, Visio, Smartdraw or some other graphics program of your choice.

Field List: Shows the fields (attributes) for each table (entity) and identify the primary key (by underlining it). Identify any Alternate Keys, Secondary Keys and all Foreign Keys. The field list should include all fields which are found in the problem. We will use the Relational Database Shorthand from the Power Power Point Presentation "Database Design - Using ER Diagrams"

Technical Summary: The technical summary provides verbal support for the relational data model. The technical summary should answer the question, "Why this logical data design and not some other?" or "Why are these relationships one-to-many or many-to-many?" This is the portion where you get to talk the database language and explain/support your Entity Relationship Logical Data Model.