Question

A local credit union offers car loans to customers on a regular basis. Unfortunately, the credit union ends up repossessing an average of 20 cars per month. Once the cars are repossessed, the credit union sells them to individual buyers to recoup some of their losses.

The cars are placed in a secure parking lot; where potential buyers are invited to inspect the cars. To find out which cars are available each month, potential buyers subscribe to a mailing list. If the car looks good, the buyer puts in a bid to the credit union via email. The highest bid wins the car.

The credit union has been doing the paperwork for these transactions by hand, but now want a computerized system. Because you have knowledge in database development and Microsoft Access, you have been hired to create a database for the monthly auction.

Using the database design rules covered in class, list each rule. Then provide specific details when appropriate as you develop the auction database. When you are finished, I should be able to see the tables, fields, datatypes, primary keys, foreign keys, and relationships.

Consider the following.

- When the bank repossesses a car, an employee records the year, make and model of the car, the mileage, color and VIN number

- Potential customer information is also recorded; name, address, gender, phone number and email address.

- Finally, the bank needs to have some way of recording the bids that come in. Customers send in their bids on specific cars. The bids are recorded and the date is noted.

Answer 1

Tables and the columns names(fields) are as below for Auction database:

REPOSSESS_CARS(reposs_car_id, description, manufacture_year, make, model, colour, milage, VIN number)

POTENTIAL_CUSTOMER(cust_id, cust_name, address, gender, phone_number, email, city)

CUSTOMER_BID(bid_id, cust_id, reposs_car_id, bid_amount, bid_date, rank)

RESELLING_HISTORY(resale_id, bid_id, loss_id, highest_bid_amount, sale_date)

LOSSES(loss_id, loan_id, amount_recovered, amount_pending, loss_amount)

LOAN_DETAILS(loan_id, loan_amount, loan_issued_date, interest, amount_to_be_recovered)

LOSSES_RECOVERED(recovery_id, resale_id, resale_amount, loss_amount, recovered_amount)

The relations and the datatypes are shown in the below picture:

PK is the primary key of that table.

FK is the foreign key used to join to other table in database

In the below picture we have one-to-many and one-to-one relations shown: