I d go about this a bit differently. First, is there a real reason to treat a Movie and a Game as separate entities? Why not have a RentableItem that can be either a movie, a game, a game machine, a Blue-Ray player, or whatever? You d key it by an item_id field, and it would have the expected metadata (title, type, genre, rental_class, and so on). Then you need to model the fact that a Registrant rents one or more RentableItems. This can be done with a Rental table, whose rows each connect one rented RentableItem with a particular Registrant (that is, the Rental is keyed by a rental_id and it has a foreign key to RentableItem.item_id and a foreign key to Registrant.registrant_id. The Rental would also have the due date, a "returned" flag, the price of the rental, etc.

Then you know a RentableItem is not in the store if there is a Rental record whose item_id is the same as the RentableItem s and whose "returned" flag is false. You never have to modify the RentableItem table itself, just the Rental table.

You re right to create separate tables for rentedGames and rentedMovies, since this model now allows for more than one movie or game of the same type being rented at the same time, which is surely more realistic than having only one instance of a particular movie or game.

This will prevent the deletion of the parent record, when the link record (rentedMovie, say) is deleted. But this deletion of the parent movie should not be happening anyway if you ve set up your relationship to not cascade delete , and you allowed the registrantID field in the original Movies or Games tables to be nullable.

To answer your second question (which I realise assumes only one movie/game for any particlar title): the way this is normally done, if you re using link tables, which is what you want to do, is simply to delete the rentedMovie/Game record. The absence of a link record for any Movie or Game is all your code needs to determine in order to know that that movie or game is now rentable (again).

I know you re doing this for a class / practice, so this may not be relevant, but consider that having the rental history for things is often very useful. Because of this, you may not want to delete the rented records, but instead just mark the item as returned.

Consider:

TABLE RentalTransaction:
RentalTransactionID integer PK NOT NULL
CustomerID integer FK NOT NULL
RentedOn datetime NOT NULL
DueDate datetime NOT NULL
<..any other fields you may need..>

TABLE RentalItems:
RentedID integer PK NOT NULL
RentalTransactionID integer FK NOT NULL
RentedItemID integer FK NOT NULL
RentedQty integer NOT NULL
RentalRetuned datetime NULL

You can see if any individual item is out or not by if it s RentalReturned field is null or not. If it is nonull, then you know the item is back, and now you can aggregate rental data to see how often it goes out, what the average length of rental is, etc, etc. You would have to build in some checks to make sure you weren t renting more copies of an item than you actually have and other such things, but I think this is overall a more flexible start to a schema. It may also be overly complicated for what you re doing, but I wanted to at least bring the idea up.

Do you really want to delete the rentedMovie instance? How will you report on how many movies a person has rented etc?

I d suggest rethinking your model slightly. You need somewhere to store people data, somewhere to store item data and somewhere to store people/item data as a first step.

Ignore the difference between movies and games for now - that becomes a process of normalisation once you ve defined your underlying structure.

As a simple starting point you should have:

Persons 1..1 ---- 1..* Hires 0..* ---- 1..1 Items

where the Hires table is a linking table between the two others with a combined key made up of personID, ItemID and a time-stamp of some description (to allow re-renting of the same movie).

You can then look at having a separate table for item types etc.

First thing to consider is that a movie is actually two entities, title and media. Title is "Lord of the Rings", while media is a DVD you take home. One title can have many media (copies), while one media has one title. Rental table has a row for each media-rental, this table gets a new row each time a bar code is scanned on rental, while DateReturned is populated upon return. Status field in the Media table tracks the in/out status for each disc/game. If you feel that you need to track which movies were rented together to a customer, you may find that by DateRented (datetime) or add a ReceiptNumber or ShoppingBasketID to the Rental table.