1.3 Tables

Database tables are the most fundamental structure in a database. If you were asked to work on a new Oracle database, you would initially wonder how many tables there are in the application. It makes a difference whether there are six tables or 600 tables, at least in terms of how much there is for you to learn about the application before you can be a productive developer.

The examples in this text are based on a demo student data model. The model stores data for students, courses, and professors. Chapter 4 graphically illustrates this model including the SQL create scripts. A key table in this model is the STUDENTS table.

An initial plan for a STUDENTS table requires answering the question: "What attributes should be stored for each student?" An initial draft of a STUDENTS table includes these attributes:

Attribute	Column Name
A unique student number. Each student is assigned a number used to access billing and registration records.	STUDENT_ID
A student name.	STUDENT_NAME
A major field of study such as Science or History.	COLLEGE_MAJOR
A college enrollment status indicating a degree-seeking or certificate-seeking student.	STATUS

Tables have different representations. Originally, the table is a logical entity consisting of a box on an entity relationship diagram. In production, the table can be described with the SQL*Plus Describe command and rows can be selected.

1.3.1 Data Model View

The data model view of a table identifies the logical attributes of the entity being stored in the database (Figure 1-2). This includes a key attribute, which is the primary key. The primary key is unique for each instance of the entity. If the table stores students, every student will have a unique primary key. The data model view is a graphical representation.

Figure 1-2. Students Table in a Data Model.

1.3.2 Create Table Script

Once the model is complete and all attributes are defined, the entity must be created in the database. SQL is used to create a table. This step transitions the logical concept to a physical table in the database. When SQL creates objects, such as tables, this is called Data Definition Language (DDL). The following DDL creates a STUDENTS table and defines the column type of each attribute to be a variable length string with a maximum size.

 CREATE TABLE students (student_id     VARCHAR2(10),  student_name   VARCHAR2(30),  college_major  VARCHAR2(15),  status         VARCHAR2(15)); 

1.3.3 Describing the Table

The definition of a table can easily be described with the SQL*Plus command. This command retrieves information from the data dictionary in a format that conveys the information stored in the table. If the DDL in the previous section were used to create the STUDENTS table, the SQL*Plus describe command would return the following:

  SQL> desc  students  Name                           Null?    Type   ------------------------------ -------- ---------------   STUDENT_ID                     NOT NULL VARCHAR2(10)   STUDENT_NAME                   NOT NULL VARCHAR2(30)   COLLEGE_MAJOR                  NOT NULL VARCHAR2(15)   STATUS                         NOT NULL VARCHAR2(15)  

1.3.4 Table Data

SQL statements that manipulate rows in the table are called Data Manipulation Language (DML). Once the table is created, we can add students to the system with INSERT statements.

 INSERT INTO students VALUES('A101','John','Biology','Degree'); 

A key component of relational technology, emphasized in Section 1.1, is the freedom of the programmer to choose what data to query. Once the table is created and rows are inserted, there is no restriction with data access. We can query all students within a particular major. We can query all students who are degree candidates. We can query all degree-seeking students majoring in either biology or history.

 SELECT student_name FROM students WHERE college_major = 'Biology'; SELECT student_name FROM students WHERE status = 'Degree'; SELECT student_name FROM students WHERE status = 'Degree' AND (college_major='Biology' OR college_major='History'); 

In network and hierarchical database technologies, the programmer cannot, at the last minute, decide to query data in a particular order. In network and hierarchical databases, ordered query results must be built into the database structure. In relational databases we can, as developers, choose to select students and their major in alphabetical order, reverse alphabetical order, order by name, and order by major ”we have no restriction. The ordering of data is not in the database but within the SQL statement. So to pull all student names and their major in alphabetical order, we use the ORDER BY clause, which is a component of the SQL specification. Showing this ordered list, we use:

 SELECT student_name, major FROM student ORDER BY student_name; 

All rows of a table can be selected with the SELECT * syntax. The following is the SQL*Plus session output that selects all rows from this STUDENTS table after five students have been added.

  SQL>  SELECT * FROM students;  STUDENT_ID STUDENT_NAME    COLLEGE_MAJOR STATUS   ---------- --------------- -------------- -----------   A101       John            Biology        Degree   A102       Mary            Math/Science   Degree   A103       Kathryn         History        Degree   A104       Steven          Biology        Degree   A105       William         English        Degree