Databases, Tables, Rows, Etc.

This document was written by CS 290W TA David Corcoran and was last modified

Background Information

We will be using Oracle 8 for our projects. Oracle, like other database servers has user accounts. In those accounts a user may create a database as well as modify and perform most administrative functions to it. Some accounts, though, can be made with only read access, or access to just certain administrative functions.

An Oracle Database contains the following:

Databases are grouped tables which are grouped columns which are grouped rows. Got that? Actually, let's go into more depth.

Databases and Tables

Databases are actually large containers which store numerous tables. Databases generally do not actually store values. They are just groups of different tables of data. For example, let's say you wanted to create a database of student grades where the students used a login and password to access their grades. You might call the database StudentGrades. Refer to the following diagram for more explanation.

Database: StudentGrades

Table: Access
StudentID FullName Login Password
302-01-1341David Corcoran corcorandave123
404-09-2323James Billy billyjcool
234-02-2333Jim Bob jimbobschool

Table: Grades
StudentID Homework1 Homework2 Exam1
302-01-1341 784798
404-09-2323 726493
234-02-2333 382221

The database StudentGrades contains two tables. The first one is Access and the second is Grades. Both are related because they both contain the student ID. This makes the tables relational. We could verify the username and password by using table Access and use the corresponding student ID to query the table Grades. Both tables are related to one another and belong to database StudentGrades.

Columns and Rows

Columns are just like they are in a table. Take the example above. The column StudentID contains all of the student ID's. Homework1 contains all of the grades for Homework 1. The actual ROW is the data element that you insert into the table. A row is related because 302-01-1341 is the ID for David Corcoran whose login is corcoran and password is dave123. (Do NOT use these passwords as examples for good passwords!)

All of this will become relevant when we discuss doing actual queries of database tables using Perl. For example with the SELECT one can query a database. But you can only have it return certain rows or just have it do comparisons with certain rows. The syntax of the SELECT statement is: 

SELECT fields FROM table WHERE criteria;
Much more information about the SQL SELECT statement may be found in Introduction to Structured Query Language.

Consider the following example: 

SELECT Homework1 FROM Grades WHERE 
   StudentID = '302-01-1341';
We are asking to return only column Homework1 from the table Grades where the Column StudentID contains 302-01-1341. The result of this query will be the number 78. See if you can understand the following: 
SELECT Exam1, Homework2 FROM Grades WHERE 
   StudentID = '404-09-2323';

SELECT * FROM Grades WHERE Homework2 < 50;

SELECT * FROM Grades WHERE 
   Homework1 = 78 AND Homework2 = 47;

SELECT FullName, Login FROM Access WHERE
   Password LIKE '%ool%';

SELECT Homework1, Homework2, Exam1 FROM Grades 
   WHERE StudentID =
  (SELECT StudentID FROM Access WHERE 
   Login = 'corcoran' AND Password = 'dave123');

[ Back to Main ]