Oracle has quite a few built-in functions specifically for manipulating DATE values and expressions. You'll see some examples of these functions on Day 7. The DATE datatype enables you to store dates in the range of January 1, 4712 B.C., to December 31, 4712 A.D. Oracle uses the default format of DD-MMM-YY for entering and displaying dates.

Large Strings

As mentioned earlier, you must use Oracle's LONG datatype to store more than 2,000 characters in a single column. A LONG column can accommodate up to 2GB of characters. As with the VARCHAR2 datatype, the Oracle RDBMS is also efficient in its use of the LONG datatype; it will allocate only the storage that is needed to store each column value. However, you will face a number of restrictions on the use of LONG columns in SQL. You can't use Oracle functions or operators to search or modify the contents of a LONG column. If you try to search a LONG column, Oracle returns an error. In a sense, you can think of a LONG column as a large container into which you can store or retrieve data—but not manipulate it.

BLOBs

As you're probably aware, most databases provide for the storage of binary large objects (BLOBs). BLOBs include documents, graphics, sound, video—actually, any type of binary file you can think of. The Oracle LONG RAW datatype is designed for BLOB storage.

When you want to associate a BLOB with a "normal" row, two choices are available to you:

• Store the BLOB in an operating system file (such as an MS-DOS file) and store the directory and filename in the associated table
• Store the BLOB itself in a LONG RAW column

Some developers are more comfortable with the first method. They think that the BLOB is more readily available if it's stored in the file system, instead of in the Oracle database. They reason that they gain very little by storing a BLOB in a table column if the Oracle database can't (or shouldn't) manipulate the BLOB.

However, other developers see an advantage in centralizing all data storage in the Oracle database. They argue that this approach provides greater portability; removing references to a directory and filename leaves fewer OS-specific issues to deal with.

A column defined as LONG RAW can accommodate up to 2GB for each row. Like LONG columns, LONG RAW columns have a number of limitations. For example, you cannot use any of the built-in functions with a LONG RAW column.

The RAW Datatype

Oracle also provides the RAW datatype, which can accommodate up to 255 bytes of binary data. Because of this storage restriction, a RAW column is less useful than a LONG RAW column.

Summary

As discussed in this lesson, try to follow these steps when designing a logical data model:

1. Begin with a logical data model.
2. Identify each entity in the system that you're modeling.
3. Identify a primary key for each entity.
4. Determine the foreign keys you need to create.

Keep the following concepts in mind when you work with Oracle datatypes:

• Every column in a table must have an Oracle datatype.
• Use NUMBER to store numeric data.
• Use CHAR to store single characters.
• Use VARCHAR2 to store strings that contain up to 2,000 characters. VARCHAR2 supports Oracle built-in functions and operators.
• Use LONG when you must store very large text strings.
• Use DATE to store all date and time information.
• Use LONG RAW to store BLOBs of up to 2GB.

What Comes Next?

On Day 4, you learn how to actually build the tables and other structures that implement a logical data model.

Q&A

Q Won't performance be a problem if all of the tables of an application are in Third Normal Form?

A It is a mistake to assume that the performance of a database application will always be substandard if its tables are in Third Normal Form. It's better to start out with a design in Third Normal Form—or higher—and then identify any areas where performance may be a problem.

Q In an Oracle database, how many columns can a table contain?

A A table can contain up to 254 columns.

Workshop

The purpose of the Workshop is to enable you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercises before you continue with tomorrow's lesson.

Quiz

1. True or false? If an attribute that isn't part of the primary key is a foreign key, it must be mandatory; it cannot allow a null value.
2. Will the Student Schedule table handle the situation in which a student needs to repeat a course? Why or why not?
3. What characteristic of a relational database will prevent a class from being deleted from the Class table if there are rows in the Student Schedule table that contain the Class ID to be deleted?
4. From an entity-relationship perspective, there is a relationship between the Class and Instructor tables. Is it identifying or non-identifying?
5. True or false? If you create the proper index for a table, its rows will always be retrieved in the ascending order of the indexed columns.

Exercises

1. Suppose you want to identify the instructor who is the head of a department. Can you think of at least two ways of doing this? What are the strengths and weaknesses of each approach?
2. The Student Schedule table currently holds both current classes and previous classes that a student has taken. Propose an alternate design using two tables—one table contains the current schedule and the second table contains classes that the student has previously taken. Identify each attribute in the two tables. What are the advantages of this design?