E3: Designing Relational databases with MS Access

In this project the class will be divided into two teams.
Each team will design and develop a relational database.
The choices for databases are given below, or you can choose your own database!

You first need to learn Microsoft Access 2007 or later, preferably 2010.
You can learn them by viewing the following free online courses:

  1. Lessons 1 through 12 at:

Requirements for the project:

  1. Design the database (suffix your database files with ‘T’, e.g., RiverT)
  2. Normalize the database
  3. Populate it
  4. Make a couple of queries (suffix your queries files with ‘Q’, e.g., RiverQ)
  5. Make a couple of forms (suffix your form files with ‘F’, e.g., RiverLengthQ)
  6. Make a couple of reports (suffix your report files with ‘R’, e.g., RiversR)
  7. Publish your database and send me the database file and the pdf of your documents

After learning Microsoft Access, go ahead and build a database from the following two options:

  1. AlumniDB Database

Description:After graduation, students from GSU either find jobs, internship, or enter graduate programs. We want to keep track of the status of the Alumni here in the Department.
These alumni have address and other information about their employer or graduate school. The students are in contact with the Department, and sometimes make monetary contribution.

Make a relational database to allow the department to manage their alumni data.

The draft design for the Alumni database is discussed in the ‘Normalization’ Powerpoint slides in the ‘Lectures’ page. This is a good start for you.

You first need to normalize the tables listed below (and other required tables).
Please make sure to normalize the tables by October 15.

The AlumniDB Database must have the following tables (relevant field are given under each table):

AlumniT

alum_first, alum_last, alum_grad_yr, alum_phone, alum_email, alum_street_number, alum_street_name, alum_zip, alum_alum_current_employer, graduation_yr, alum_current_school, was_intern, internship_place

ZipCodesT

zip_code, city_id

CitiesT

city_id, city_name, state_id

StatesT

state_id, state_name, state_abrev

EmployerT

emp_id, emp_name, emp_contact, emp_street, emp_zip, job_skills_required, etc.

JobT

job_id, job_title, etc.

GradSchoolT

sch_name, dept_name, dept_phone, degree_program, sch_street, sch_zip, etc.

CommentsT

comment_id, comment_desc, etc., Fk (to alum_id)

ContributionT

Contrib_id, contrib_amount, contrib_date, FK (alum_id)

Do the following after the list above is completed:

  • Go through the normalization process through the third normal form (3NF) for each table
  • Create new tables as they become necessary through your design (e.g., contribution).
  • Assign a proper data type to each field
  • Identify the PK and FK in each table
  • Assign constraints
  • Assign indexes to the fields if necessary (depends on the type of queries, e.g., select, sort by, join
  • Add more field to each table if required
  • Think about cardinality (1:1, 1:M, and M:M)
  • Identify and model generalization, specialization, aggregation, composition, recursive relationships if needed
  • Other things as they become needed
  1. Rental property database

Description: You are the manager of a couple of rental properties for landlords. The properties are apartment, house, or townhouse. Tenants rent these properties through a lease and make a monthly payment for it when they occupy it. The properties need services (cutting lawn, painting, plumbing, etc.) which are provided by Service providers.

Please read the instruction given above for the Alumni database regarding normalization and the selection of the cardinalities (1:1, 1:m, m:n) and the primary and foreign keys.

Make the database and call it RentalDB.
Use the preliminary structure of the tables given below:
Notice that you may need to move the attributes from some tables to other tables.

For example: ther may be several OtherExpenses for each property.
Also, the RentalMonth may not belong to the LandlordT table (Does it belong to it?).

LandlordT

LandlordID, FirstName, LastName, PropertyAddress, RentalAmount, RentalMonth, DepositAcct, MgmetFee, HOA, OtherExpense, CreditToLandlord,CommentToLandlord, Notes.

PropertyT

PropertyID, PropertyType, Address, City, State, Zip, Country, Area, Bedrooms, Bathrooms, Garage, Mortgage, HousingDues, Tax, Insurance, Downpayment, Webpage, LoanAmount, AssessedValue, CurrentValue, Notes.

TenantT

TenantID, Lastname, FirstName, Email, HomePhone, CellPhone, FaxNumber, Address, City, State, Zip, Country, Company, JobTitle, BusinessPhone, Webpage, Notes.

LeaseT

LeaseID, PropertyID, TenantID, StartDate, EndDate, Monthly LeaseAmount, SecurityDeposit, PetDeposit, Notes.

PropertyTypeT

PropertyTypeID, PropertyType

ServiceT

ServiceID, PropertyID, ServiceDate, ServiceTypeID, ServiceProviderID, Cost.

ServiceProviderT

ServiceProviderID, ServiceProvider, Address, City, State, Zip, Country, Email, BusinessPhone, CellPhone, Fax, WebPage, Notes.

PaymentT

LeaseID, DateDue, DatePaid, ReferenceNum, PaymentTypeID, Amount, PaymentCategory, Notes.

Do we need the OtherExpensesT table? If yes, build it.

How about other tables?