This models a Photographic Data Bank with images that can be bought over the internet. Every photo image can only be bought once so actually what is being sold are the rights to use that image (a fact expressed in the E/R diagram as the Many-One relationship Includes between Photos and Transactions).
The model has two very distinct parts, on one hand there is a set of entities that support the catalog of photos, models, and photographers and on the other hand a set of entities to support the transactions and customers.
We store some technical information for every photo, like the type (color or black and white), type of film, shutter speed, f-stop, price, etc...
The photos are grouped in several categories: Landscapes, Portraits and Abstracts (everything else). This can be easily extended to many more like Underwater, Nature, Animals, Sports, War, but I didn’t want to clutter the design at this stage.
We store some biographical information of the authors/photographers, and of the models (in the case of portraits) and information about the location the photo was taken in (for landscapes).
Although the customers can browse the catalog without registering, in order to buy one or more photos they need to have an account/profile and they need to register with a user name and password so the user name can be used as a key for Customers.
In the same transaction (or shopping cart) a customer can buy several photos. the attribute TotalAmount can be calculated from the individual price of every photo. Although this may, apparently, introduce some redundancy it gives us the flexibility to adapt the total price based on the customer or promotions or any other business rules (not included in the model)
Attributes like Name and Address will be extended to their various components First Name, Last Name, Street Address, State, Zip Code and the like when creating the tables but we have decided to keep them grouped in the E/R model.
- Queries, Insertions, Deletions and Updates
- Views
