Relational Database Design/Relationships

Each row in a table represents a fact about the world that involves a relationship between several values. For example, the table below contains a row that relates the number 75, the string "Alice" and the Engineering department. The table works for storing this information because the business that is operating using this database table has agreed on rules that every employee has a unique number, and every employee is attached to a single department. The "shape" of the data that needs to be stored fits in the table fine.

Employee number	Name	Department
75	Alice	Engineering
84	Bob	Marketing
98	Charlie	Sales

However, all rows in the table must have the same number of columns, and not all facts about the world can be expressed as a combination of a fixed number of things. For example, suppose we have a database for a CRM application that tracks customer companies and our various contacts in each. One company might have seven contacts associated with it, while another might only have one. Worse, we can add and remove contacts for companies and potentially change this number. If all companies are going to be in the same table with a fixed number of columns, then we can't fit the contact information in.

Entity-Relationship diagrams

ER Diagrams allow mapping of simple Subject verb Object statements onto diagrams, and then mapping into a written schema definition , in for example , SQL database definition statements.

e.g. requirement statement

A corporation owns several consulting service businesses, and each consulting business is staffed by a manager, front office staff, contracted consults who are paid a percentage fee for service, and assistants to help each consultant provide extended services. All contractors and salaried assistants must have formal qualifications. The clients of the business agree to have their demographic details recorded, as the service often entails some back office processing which cannot be completed immediately during the consultation, but need to be communicated to the client by a consultant when the service product is completed.

How to begin mapping?

In ER mapping, 2 entities have a relationship, the relationship being analogous to a Verb between a Subject and an Object. For example, from the above requirement statement, several simpler statements can be generated in the Subject Verb Object format.

Corporation owns many Service_Businesses.

Service_Business operates_from Location.

Service_Business has Telephone_Contact.

Telephone_Contact is_typed.

One Manager manages one Service_Business.

Many Office Staff run Service_Business.

Office_Staff are_paid Salaries.

Office_Staff have a Roster.

One Office_Staff has a set of work_shifts.

One Office_Staff performs many worked_shifts.

Many Worked_shifts realizes one Work_shift.

Already, the brief requirement statement has generated many Sentences usable for ER Mapping.

The next step seems then to map each Subject or Object of a Sentence to be an Entity, which is later mapped to a relation or Table. The symbol for this is the name of the subject surrounded by a rectangle.

Also, the verb connecting the Subject and Object, can be mapped to a Relationship, which has its own pictogram, a diamond, with the verb inside it. The relationship may map into a table, but most often maps as a foreign key in one table, referencing the other table.

The Relationship pictogram is often marked at each end describing the cardinality of this side of a relationship. The cardinality may be 1 on one side, and many, or * on the other side, meaning that the table which matches the entity on the many side will be carrying the foreign key to the entity on the 1 cardinality side.

e.g. Corporation owns many Service Businesses.

Note there is an arrow saying which way the Subject Verb Object order is .

This would map into something like

Corporation( Corp_Id , CorpName , GovtBusinessRegoNumber ) , Service_Business ( SB_id , Address, Corp_Id)

Here the underlined attributes are the primary key columns of their owning entities. Service_Business's last column is a foreign key to corporation, and represents the information in the "Owns" relationship diamond.

Now, if Corporations can sell service_businesses to each other, then a period of ownership might apply, so the owns relationship has to make two changes: a start and an end attribute, and since one service_business can be owned by many corporations through different Owns relationship instances, the owns left cardinality becomes many.

Attributes in ER diagrams are usually modelled as an oval with the name of the attribute, linked to the entity or relationship that contains the attribute.

                                 _____            _____
 ________                      ( start)--   ---- ( end )
(CorpName)----                  """"""    \ /     """""
 """"""""      \                           V
    ------      \                          ^                                         _________
   (RegoNo)--   ----------------          / \               ------------------ ---- ( address )
    ======   \ |  Corporation   |--many--<Owns>--- many -->| Service_Business |      """""""""  
                ----------------          \ /               ------------------ ----  _____________
                                           V                        | 1             (business_name )
                                                                   has               =============
                                                                    | many         ________
                                                           ------------------ ----( number ) 
                                                          |  contact_number  |     ========
                                                           ------------------ ---- ---------
                                                                                  (  type   )
                                                                                   """""""""  

The above, has a few different ER design choices, which are controversial. Corporation.RegNo, Service_Business.Business_name, and Contact_number.Number are attributes, but they are also underlined and therefore are primary keys for their respective entities.

The Owns relationship has become a many-to-many relationship, and can be tabulated as Owns (start, end, RegNo, Business_name).

Again, a semantic primary key is used, but the foreign keys to Corporation, and Service_Business are contained within the primary key.

If Corporations can own Service_Business more than one time, what sort of multivalue dependency exists ?

If the multivalue dependency exists, then RegNo, Business_Name can occur more than once. Does that violate 4NF since RegNo, Business_Name isn't a superkey of Owns?

4NF has another criteria, what is it?