MCS-170 The Nature of Computer Science

Fall 2006

---

1. Review - Database

• Structured collection of Data
• MetaData – describes type of info held in table, not data itself
• Fields – represent column information (attributes)
• (e.g., ‘First name’)
• Values – specific instance of a field
• (e.g., ‘Michael’)
• Entities = Rows = Tuples = Records – represent particular sets of fields

• Field Specification
• Field_name data_format optional_comment
• First_name text 20 customer’s first name

• Primary Key – A field that can be used to uniquely identify a row of table

• Query -- Database operation that selects certain data from a list of tables and creates a new ouput table
  
• Example: SELECT {field_names} FROM {table_names} WHERE {row_constraints}
• Selects columns from the tables subject to the constraints on the rows

2. Designing a Database

• Avoid Redundancy -- Never store duplicate information
• Duplicates cause inconsistency, We might change one place, but not another
• Inconsistent Data = Garbage Data
  
• How do we store single instances of Data?
• Example -- Addresses
• Adresses probably needed in Multiple Tables
• Solution: Create separate Address Table, keyed with a unique ID (key)
• In each table where address is needed, store the key
• When address is needed in table, simply look up key

• Database Scheme (Schema) - Description of entities of database

• Example - College Database of student records
• Need Two Types of Entities: Student, Home_Address
• Schema:
  

Student
    Student_ID    Number        // 8 digits
    First_Name    Text 25       // First name
    Middle_Name   Text 2        // Middle Initials
    Last_Name     Text 25       // Last Name
    Birthdate     Date    
    On-Probation  Boolean       // 0 = Good Standing, 1 = Trouble

Home_Address
    Student_ID    Number        // 8 digits
    Street_Addr   Text 100      // street address
    City          Text 25       // city
    State         Text 25       // Or province, canton, etc
    Country       Text 25      
    Post_Code     Text 10       // Many are not numbers (e.g., Canadian)

• Note: Addresses are not put in Student table. Why Not?

    Ease of use - allows access to student's address without extraneous info.
    Two tables are different, but linked through Student_ID key.
    Two tables have this Relationship

• A Relationship is a correspondence between two tables
• Example - Two relationships:
  
• Lives_At -- Given Student_ID in Student table, can find matching address
• Home_Of -- Given Student_ID in Home_Address table, can find Student record

Lives_At ------->
Student Table Home_Address Table
                                        <---------- Home_Of

• Joining Tables -- Given the close relationship between our tables, it would be nice to create one super table with all info.
  
• Join Operation -- creates a new table from two tables by joining together rows that match with a common primary key.
  
• Example:

Master_Record = Student JOIN Home_Base
            WHERE Student.Student_ID = Home_Base.Student_ID

Creates
Master_Record
    Student_ID
    First_Name
    Middle_Name
    Last_Name
    Birthdate
On-Probation
Street_Addr
    City
    State
    Country
    Post_Code

3. SQL – Structured Query Language

• MySQL – A public domain version of SQL, used by many web sites
• Yahoo, AP Network, NASA
• Structured as a Client-Server Application

• The Gustavus Math/Computer Science Department has a MySQL server.
• The MCS department Linux machines have MySQL client software installed.

• To connect to the server and start a MySQL session (as a client):
• mysql -h mcs-mysql.gac.edu -u username -p

• Example: Create the two tables above.  Here is the SQL file.
  

Demo of session in class.