Outline:
We saw some data structures which might help do this efficiently. However, as we described it then, this was one-dimensional. That is, we were only allowed to search for information based on one particular field of information (the name).
More generally, we want databases to handle information about many different items, each of which may have many different fields of data.
Employee Record:
If efficiency important, perhaps I should keep two copies of all of the records, with one version sorted by name and another version sorted by birthdate.
For instance, payroll should have access to my salary and home address, but general employees should probably only see my name, office, and phone number. My manager might know my salary, but not my social security number.
The database portrays the data as if it were stored in rectangular tables, called relations.
Example (from Fig. 9.3):
We call one row of this table a tuple.
Though this may contain the information that we want, there are several problems with such a structure.
A better way: We can instead represent our database using a combination of three relations, as follows:
Query: Find all Capricorns
First approach (Fig. 9.4 of text):
Again, this suffers from the same pitfalls
An alternative solution: Use three separate relations. (Fig. 9.5)
Query: Find all people in Accounting
Imagine that you had the following relation:
and that you tried to replace it with a combination of the following two relations:
Is this new formation equivalent? No!
Example: How do you determine the employees in the Sales department?