[ Team LiB ] |
1.3 TablesDatabase 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:
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 ViewThe 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 ScriptOnce 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.
1.3.3 Describing the TableThe 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:
1.3.4 Table DataSQL 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.
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.
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:
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.
|
[ Team LiB ] |
No comments:
Post a Comment