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?
SELECT from EMPLOYEE where EmplID="34Y70"
PROJECT Name,Address from EMPLOYEE
Relation B
JOIN A and B
The attribute names from each of the original relations do not necessarily need to have the same names, and if they do, it may be a coincidence. However, we often do not want to join all possible pairs of tuples, but only those in which some particular attributes match.
JOIN A and B where A.W = B.X
Example, NEW1 <- JOIN ASSIGNMENT and JOB where ASSIGNMENT.JobID=JOB.JobID NEW2 <- SELECT form NEW1 where ASSIGNMENT.TermDate="present" LIST <- PROJECT ASSIGNMENT.EmplID,JOB.Dept from NEW2
Also, it should be noted that the user interface for many databases will present these same concepts, however using different syntax or graphical interaction.
Though its terminology differs somewhat from what we have introduced above, a single SQL statement may be used to express a combination of SELECT, PROJECT and JOIN operations.
Format:
select <list of attributes> from <list of tables> where <list of conditions>
PROJECT Name, Address from EMPLOYEE
In SQL:
select Name, Address from EMPLOYEE
select EmplID, Name, Address, SSN from EMPLOYEE where EmplID = '34Y70'
select * from A, B where A.W = B.X
select EmplID, Dept from ASSIGNMENT, JOB where ASSIGNMENT.JobID = JOB.JobID and ASSIGNMENT.TermDate = "present"
SQL statements can use additional features not mentioned in the text. Just a few such examples are:
Information on all countries, according to the "CIA World Factbook" from 1995. This information is in a single table titled cia as follows: table 'cia' nameregionareapopulationgdp Afghanistan Asia 652000 25838797 21000000000 Albania Europe 28748 3490435 5600000000 Algeria Africa 2381740 31193917 147600000000 . . . . . . . . . . Here are some practice problems. Here is a self-test tutorial you can try. Here are the answers to the tutorial. Information on movies and their stars, according to the Internet Movie Database from 1997. To avoid duplicaiton, this database design uses three separate tables table 'movie' idtitleyrscorevotes 1 Star Wars 1977 8.8 53567 2 Shawshank Redemption, The 1994 9 44974 3 Pulp Fiction 1994 8.6 43993 4 Titanic 1997 7.2 43371 . . . . . . . . . . table 'actor' idname 1 Woody Allen 2 Clint Eastwood 3 Robert De Niro 4 Sean Connery . . . . table 'casting' movieidactoridord 972 588 1 849 588 2 1575 588 3 47 590 4 . . . . . . A more complete explanation of these fields can be found here. Here is a self-test tutorial you can try. (Please note, we have only covered enough to get you through queries 1a-1b, 2a-2c, 3a-3e. Queries 4a-4e involve operations we have not covered.) Here are the answers to the tutorial.
Information on movies and their stars, according to the Internet Movie Database from 1997. To avoid duplicaiton, this database design uses three separate tables table 'movie' idtitleyrscorevotes 1 Star Wars 1977 8.8 53567 2 Shawshank Redemption, The 1994 9 44974 3 Pulp Fiction 1994 8.6 43993 4 Titanic 1997 7.2 43371 . . . . . . . . . . table 'actor' idname 1 Woody Allen 2 Clint Eastwood 3 Robert De Niro 4 Sean Connery . . . . table 'casting' movieidactoridord 972 588 1 849 588 2 1575 588 3 47 590 4 . . . . . . A more complete explanation of these fields can be found here. Here is a self-test tutorial you can try. (Please note, we have only covered enough to get you through queries 1a-1b, 2a-2c, 3a-3e. Queries 4a-4e involve operations we have not covered.) Here are the answers to the tutorial.