The following schema, which is not the most efficient
one due to educational purposes, describes a shop of electronic articles that
operates through Internet. Therefore, the catalog of available products is extracted
from a query of the DB and a HTML page is built with the information. Besides,
it is supposed that a set of forms and processes to store baskets and orders
have been implemented. Also, the information of the registered
users is required.
The articles are identified by a code (“cod”) and
there is some extra information such as the retail price of the product (“rrp”).
This price is shown in the catalog to the user but it is not necessary the same
price in which the product was sold at any moment. Suppose that there is an
increase of the prices of the products in March. However, this product was already sold in February
too. There is also information that
describes the article including a miniature image (“image”,”urlimage”). Both are
two possible methods to access the same image, the folder with the set of
images is in the server, for instance.
The articles have also a brand, as it is shown in the relationship
“manufacturer”. The articles has always a brand and only one. The objective of
this relationship is storing extra information about the brand, such as the name
of the company that manufactures the brand, and the logo, that can be an image
shown in the catalog.
Some articles are available in the warehouse and some are due to renewal. Available articles are “stock” type, they will be part of the
specialization “stock”. This must be understood as if the article is not in
“stock” table, it is not shown in the catalog, or, the user is informed that
the product is not available.
The articles are grouped together by categories. In the complete list of
articles we find that some of them are cameras, some lenses, some tvs and some
memories (SD memory cards, Compact Flash, etc.). This part of the E-R schema is
showing that all the articles have code, name, rrp, image, urlimage and
specifications. However, if the article is a “lense”, we have also information
about the type of lense, the frame, the minumum and maximum aperture and the
special elements. These attributes are specific of the lenses, but they are not part of another types of articles. For example, they does not exist in the tvs that the
tvs have its own categories like panel, resolution, etc.
A special type of category is the “pack”. The “pack” articles are not real
products, but they represent a set of articles that are sold together with a special
price (a "pack" is a type of article that has code, name and rrp like
any other article). Think about a reflex camera (1000 €) that is sold together
with a lense (500 €) and a memory card (30 €): the total amount of these
articles separately is 1530 € but if the client buys the pack
"A001", for instance, the company can offer it at a different price,
for instance 1250 €, and it will buy the three products together.
Therefore, one thing is the pack article, that is stored in “pack” entity, and
another thing are the articles that are part of a pack: this list of articles
grouped together is represented and queried from the relationship “PART_OF”; a
pack can have various articles and the same article can be part of various
About the users we will stored the typical information. Specially important are
the “email”, that identify the users, the name, surnames and DNI, that are
compulsory attributes, all the users have to provide this information. Besides, we store the address and birthday.
users will have an alternative delivery address, that means that the products
will be delivered to this second address but the invoicing will be done to the
In order to avoid confusions, the town that is referred in the main and
alternative address is stored in another entity. Therefore, both in the “user”
and “deliveryaddress”, it is know the place of the postal addresses. For this
reference, the “codm” is used, together with the name of the town: ('1225',
'San Vicente del Raspeig"), for instance.
However, the identification of places needs also the code of the provinces. This
data is provided by the identification dependency of “belongs”. Therefore, San
Vicente del Raspeig is identified by ('03','1225') what is obviously different
to ('45','1225'), Olías del Rey, that is located in Toledo.
Finally the orders. It is supposed that the users can used the tool through the
“basket”. This is a list of
articles in which the user is interested in and they can be ordered or not. In
fact, the basket is a set of pairs email, code of the article, plus a date when
the article was chosen. This information is not related with the final orders.
The orders have a heading (“ORDER”) that informs about the number, date and
user that makes the order (relationship "buy"). An order only, and
always, has a client, and a client can make as many orders as he/she wants.
The detail of the order, the list of articles to be bought and their prices are stored in “LINEORDER”. For example, the order 1 can contain 3 lines (1-1, 1-2,
1-3) and the order 2 can contain 2 (2-1, 2-2). Again, we need the
identification dependency: the lines of the order need the number of the order
they belong to.
Each line if always referring to an article (relationship "ordering")
and it always specifies a retail price. Again, we focus on the differences
between the price of the article and the rrp (ARTICLE.rrp) because they can be different.