Identifing %ttributes

The data elements that ou want to save for each entit are called -attributes-.

 %bout the products that ou sell, ou want to know, for example, what the price is, what the name of the manufacturer is, and what the tpe number is. %bout the customers ou know their customer number, their name, and address. %bout the shops ou know the location code, the name, the

address. Cf the sales ou know when the happened, in which shop, what products were sold, and the sum total of the sale. Cf the vendor ou know his staff number, name, and address. +hat will be included precisel is not of importance et it is still onl about what ou want to save.

Figure &: Entities with attributes.

Derived Data

Derived data is data that is derived from the other data that ou have alread saved. In this case the -sum total- is a classical case of derived data. 7ou know exactl what has been sold and what each product costs, so ou can alwas calculate how much the sum total of the sales is. o reall it is not necessar to save the sum total.

o wh is it saved here +ell, because it is a sale, and the price of the product can var over time. % product can be priced at 5 euros toda and at E euros next month, and for our administration ou need to know what it cost at the time of the sale, and the easiest wa to do this is to save it here. There are a lot of more elegant was, but the are too profound for this article.

#resenting ntities and 3elationships) ntit

3elationship Diagram 83D9

The ntit 3elationship Diagram 83D9 gives a graphical overview of the database. There are several stles and tpes of 3 Diagrams. % much: used notation is the -crowfeet- notation, where entities are represented as rectangles and the relationships between the entities are represented as lines between the entities. The signs at the end of the lines indicate the

tpe of relationship. The side of the relationship that is mandator for the other to exist will be indicated through a dash on the line. @ot mandator entities are indicated through a circle. *Ban* is indicated through a

-crowfeet- de relationship:line splits up in three lines.

In this article we make use of De$ign for Databases to design and present our database.

 % 5)5 mandator relationship is represented as follows)

Figure ': Man"atory one to one relationship.

 % 5)@ mandator relationship)

Figure (: Man"atory one to many relationship.

 % B)@ relationship is)

Figure ): Man"atory many to many relationship.

Figure 1*: Mo"el with relationships.

 %ssigning Fes

#rimar Fes

 % primar ke 8#F9 is one or more data attributes that uni2uel identif an entit. % ke that consists of two or more attributes is called a composite ke. %ll attributes part of a primar ke must have a value in ever record 8which cannot be left empt9 and the combination of the values within these attributes must be uni2ue in the table.

In the example there are a few obvious candidates for the primar ke.

0ustomers all have a customer number, products all have a uni2ue product number and the sales have a sales number. ach of these data is uni2ue and each record will contain a value, so these attributes can be a primar ke. Cften an integer column is used for the primar ke so a record can be easil found through its number.

'ink:entities usuall refer to the primar ke attributes of the entities that the link. The primar ke of a link:entit is usuall a collection of these reference:attributes. 4or example in the alesGdetails entit we could use

the combination of the #F-s of the sales and products entities as the #F of alesGdetails. In this wa we enforce that the same product 8tpe9 can onl be used once in the same sale. Bultiple items of the same product tpe in a sale must be indicated b the 2uantit.

In the 3D the primar ke attributes are indicated b the text -#F- behind the name of the attribute. In the example onl the entit -shop- does not have an obvious candidate for the #F, so we will introduce a new attribute for that entit) shopnr.

4oreign Fes

The 4oreign Fe 84F9 in an entit is the reference to the primar ke of

another entit. In the 3D that attribute will be indicated with -4F- behind its name. The foreign ke of an entit can also be part of the primar ke, in that case the attribute will be indicated with -#4- behind its name. This is usuall the case with the link:entities, because ou usuall link two

instances onl once together 8with 5 sale onl 5 product tpe is sold 5 time9.

If we put all link:entities, #F-s and 4F-s into the 3D, we get the model as shown below. #lease note that the attribute -products- is no longer

necessar in -ales-, because -sold products- is now included in the link: table. In the link:table another field was added, -2uantit-, that indicates how man products were sold. The 2uantit field was also added in the stock: table, to indicate how man products are still in store.

Figure 11: +rimary #eys an" foreign #eys.