MCS-170 The Nature of Computer Science

Fall 2006

---

1. Database Relationships

• Database Relationship -- Connection between data in one or more tables

• Example –  Movie Database has three tables: persons, movies, acts_in

These are linked (connected) by two ids, the id in the movies table and the id in the persons table:

persons -> firstnames 
                    lastname 
		    id   (primary key)

movies -> title 
                   director   {references persons(id)}
                   year_made 
                   id  (primary key)

acts_in  -> actor {references persons(id)}
                    movie {references movies(id)}

• Note how the movies and acts_in tables use these id's. For example, the data for the movie Citizen Kane consists of a director reference, which is a relationship to the persons table, using the persons id field.
  

movies -> 'Citizen Kane'
                   181  (reference to the person whose id is 181 in persons table)
                   1941
                   8
persons -> 'Orson'
                    'welles' 
		    181

2. Joining Tables

• Syntax:

• Reading:   Bowtie operator is used to signify the join of two tables. IF our two tables are table1 and table2 we would write
  
• table1 table2
• MySQL:  We would write
  
• select * from table1, table2
  

• Effect? With either type of syntax, the net effect of joining the tables is to create a new table with all the fields of the two separate tables (table1 and table2) and all possible rows of information taken from table1 and table2.. 
  
• Example: Table1 has fields -- name, address, id -- and has 10 rows
  

       Table2 has fields -- title, url -- and has 20 rows

• The join of  Table1 and Table2 results in a new table with 200 rows and fields of name, address, id, title, url

3. Row Constraints

• Syntax:

• Reading:  Row Constraints are specified using the ON command
  
• table1 t1 table2 t2 ON t1.name = 'Bill Johnson'
  
• MySQL:  We would write
  
• select * from table1 t1, table2 t2 where t1.name = 'Bill Johnson'
  

• Effect? Row constraints reduce multiplicities in a joined table and select out specific relationships that are desired.
  

• Example: On page 392 of the reading, we have 4 tables in a database:
  
• Personal, Payroll, HR, and Softball
• What key Field connects all of these tables? (That is, creates relationships between these tables?)
  
• Query Construction: Human Resources needs a list of all employees and their health coverage status - what query would we use to get this info?
• The tables we would need to use would be Personal and Payroll. The fields we would like to display are First_Name, Last_Name, Health.
  
• The SQL query would be
  
• select First_Name, Last_Name, Health
  

from Personal p, Payroll pr where p.Emp_ID = pr.Emp_ID 

• In-Class: Construct the following Queries (test out on movies ones on computer in class)
• Get info on all employees whose supervisor is 'Bob Jones" and who bat left-handed
• Find all movies having actor 'Burt Lancaster'
• Find all movies made in 1974 or directed by 'Orson Welles'