Talk:Bklein7 Week 6

Lecture on 9/29

• Relational Databases- most common type of database
  • PostgreSQL Tutorial
  • Databases History
    • Prior- Network Databases; ended up getting blown out of the water and unified by the relational database
    • Future- No SQL Databases
  • Structure- series of tables
    • Tables are collections of rows that give specific instances of information that complies with an overall structure. The domain (set of accepted values) for each column is specified. Some possible domain options include marker, number, and text.
      • The table is the standard unit of the relational database.
    • Became popularized due to its strong mathematical foundations.
    • A relational database is a collection of several tables. Coordinated information.
      • The particular plan for a database that describes what's in it and where everything is located is reffered to as a schema.
  • Key- singular value that serves to identify the rest of the data in a particular row (e.g. id column)
    • Primary Key- the chosen value that it is agreed upon for everyone to use as an identifier; typically easy or conveint
    • Foreign Key- storing information in one table using the primary key of another table; primary use of keys is to connect tables
      • foreign keys can link together dozens of tables in unique ways; establish many to many relationships (tables that connect other tables)
• SQL (can use pgAdmin III)
  • 2 Conceptual Parts
    1. Data definition language (DDL)
    2. Data manipulation language (DML)
  • DDL Commands
    • create table
      • table name, column name & data type, column constraint, primary key, references, etc.
  • In class example
    • Create Database
    • create table movie (id int primary key, year int, title varchar)
    • (review)

Lecture on 10/1

• Live session of PostgreSQL Tutorial.
• 4 fundamental functions of a database
  1. Create
     • insert...
  2. Retrieve
     • select...
     • select * from movie
     • select (column) from movie where (ex. year > 1990) order by (column)
     • ~ for sed-like commands
     • desc= descending order
     • *=show me everything
     • select count(*) from...
     • select year, count(*) from movie where year > 1985 and year <1996 group by year
  3. Update
     • update...set...where
     • update (table) set (column = new value) where (id = #)
  4. Delete
     • delete from...where
     • delete from movie (where...)
• Syntax Notes
  • Any apostrophes in data needs to be typed in as two apostrophes
• cat movie_titles.txt | sed "s/'//g" | sed "s/$/');/g" | sed "s/,/,'/2" | sed "s/^/insert into movie (id, year, title) values (/g"
• Individual Web Directory
  • Can be found at http://my.cs.lmu.edu/~bklein7/
  • Can use browser to get files placed in this "mini web page"
  • Placing files here
    • > symbol places a command output into a file
    • personal directory: bklein7/public_html
    • command: > bklein7/public_html?movie.sql.txt
• Creating a new table with a reference to a foreign key
  • add reference table(column) where you would place a primary key term when creating a table
  • select * from movie, performer where movie.id=performer.movie_id