CMPS 002 - Computer Literacy

Prof. Paulo Franca

CyberSlug Rules!

Classes

Class Syllabus

Grading Policy

Labs

Interact

Login

  • enter system
  • get attendance
  • submit work
  • take lab quiz
  • check scores

Help

Help me!

Links

Staff only

 

 

 

Datacare   Ahshay

 

Definitions

  • Database: Collection of information stored on a compute
  • Database program: software tool for organizing and retrieving database information
  • File: a collection of records - also called a "Table"
  • Record: a collection of information about one entity (collection of fields)
  • Field: an individual component of a record; an attribute of one entity
    • (all fields are related to the same entity)
  • View: way in which user sees information; a list, a form, a report, etc.

Database and Files

filecabinet.jpg (7619 bytes)

A Record (folder) and its fields:

recsfields.jpg (22962 bytes)

A Table:

People:

Name id Birthdate Street City State Zip
Newman, Alfred E. 000 12/01/1952 AnyPlace Ct Santa Cruz CA 95064
Schwarzenegger, Arnold 222 01/01/1952 Capitol Building Sacramento CA 91000
Bush, Jeffrey 342 06/05/1956 123 Main St Coral Gables FL 11221
Madonna 223 12/08/1960 121 Palm Drive Beverly Hills CA 92000
  • Each row in the above table is a "record"
  • Each column represents a field

Another Table:

Vehicles:

License Plate Owner Id Make Model Year
TRMIN8 222 Porsche Carrera 1972
SHORT 342 Toyota Prius 2005
MATGRL 223 Mazda Myiata 2004
NOSHW 222 Nissan Maxima 2006
2424 342 Lincoln Continental 1998
         

Then, if the second table represents vehicles whose owner id is the id on the first table...

You can "Virtually" create a table that joins the above together on the "ID" field:

People join Vehicles on People.id=Vehicles.OwnerId

Name id Birthdate Street City State Zip License Plate Ownwer Id Make Model Year
Schwarzenegger, Arnold 222 01/01/1952 Capitol Building Sacramento CA 91000 TRMIN8 222 Porsche Carrera 1972
Schwarzenegger, Arnold 222 01/01/1952 Capitol Building Sacramento CA 91000 NOSHW 222 Nissan Maxima 2006
Bush, Jeffrey 342 06/05/1956 123 Main St Coral Gables FL 11221 SHORT 342 Toyota Prius 2005
Bush, Jeffrey 342 06/05/1956 123 Main St Coral Gables FL 11221 2424 342 Lincoln Continental 1998
Madonna 223 12/08/1960 121 Palm Drive Beverly Hills CA 92000 MATGRL 222 Mazda Myiata 2004

The benefit of joining tables instead of using one big table:

Note: If there is no match on the second table, the record is ommited in a "join" but if you use "left join" the record will show.

If Schwarzenegger moves, you only have to update the people table; not each of the vehicle's records

Common operations you can do in a database are called "Queries"

  • Select - use select to search for information that you want
  • Update - use update to change the contents of a field
  • Insert - use insert to insert new records in a table
  • Delete - use delete to remove records from a table.

We will only study the "Select"

Select <list of fields> from <table> where <condition> order by <list of fields>

examples:

  • select name from people where state='CA' -> results: Alfred E. Newman, Arnold Schwarzenegger, Madonna
  • select name,city,make from people join vehicles on people.id=vehicle.ownerid  order by city

Result:

name city make
Madonna Beverly Hills Mazda
Bush, Jeffrey Coral Gables Toyota
Bush, Jeffrey Coral Gables Lincoln
Schwarzenegger, Arnold Sacramento Porsche
Schwarzenegger, Arnold Sacramento Nissan