vb.net 2d barcode dll OBJECT-RELATIONAL FEATURES in Java

Maker Data Matrix 2d barcode in Java OBJECT-RELATIONAL FEATURES

For the examples mentioned so far in this chapter, you could argue that you could implement them very well with standard relational techniques, as discussed in previous chapters of this book. You could separate various phone numbers into separate columns, you could create a separate ERRATA table with a foreign key constraint referring to the COURSES table, and so on. So when should you choose an object-relational approach rather than a pure relational approach It might be a matter of taste, and discussions about taste are probably a waste of time in a technical book like this one. As the Romans said, De gustibus non disputandum est... (That phrase translates to: There is no disputing about tastes ) It might be the case that you have a powerful object-oriented design and development environment. You may find that Oracle s object-relational features enable you to maintain an intuitive and straightforward mapping between that development environment and the Oracle database structures. In any case, this book does not speculate about when one approach is better than the other. The examples in this chapter have a single purpose: to illustrate the object-relational features of the Oracle DBMS. As you read about the techniques described in this chapter, you may wonder whether they violate the first normal form as one of the foundations of the relational model. That is not the case. The relational model does not forbid in any way storing complex or set-valued attributes in your rows. Data atomicity is a rather slippery concept. For example, if you consider DATE values, aren t you looking at a compound datatype A DATE value has meaningful subcomponents, such as year, month, and day. For a thorough treatment of this subject, see An Introduction to Database Systems, 8th Edition by Chris Date (Addison Wesley, 2003).

We will begin to explore varrays by implementing the phone list example introduced in the previous section. To keep our EMPLOYEES table unimpaired, we create a copy of the EMPLOYEES table for our experiments in this final chapter of the book. We also leave out some of the columns of the original EMPLOYEES table. See Listing 12-1. Listing 12-1. Creating a Copy of the EMPLOYEES Table create table e as select empno, ename, init, mgr, deptno from employees;

Before we can add a list of phone numbers for every employee in the E table, we must create a corresponding type first, as shown in Listing 12-2.

Listing 12-2. Creating and Describing a Type create or replace type numberlist_t as varray(4) of varchar2(20); / describe numberlist_t numberlist_t VARRAY(4) OF VARCHAR2(20) select type_name, typecode from user_types; TYPE_NAME TYPECODE ------------------------ -----------------------------NUMBERLIST_T COLLECTION Note that you must end the CREATE TYPE command in Listing 12-2 with a slash (/) in the third line, although you ended the second line with a semicolon. The reason is that you are not entering an SQL or an SQL*Plus command; you re entering a PL/SQL command. Note also that from now on, you can use this NUMBERLIST_T type as often as you like. It is known to the database, and its definition is stored in the data dictionary. You can query the USER_TYPES data dictionary view to see your own type definitions.

Note To allow other database users to use your type definitions, you must grant them the EXECUTE privilege on those types.

In Listing 12-3, we add a column to the E table, using the NUMBERLIST_T type we created in Listing 122. Then, we execute a query. Listing 12-3. Adding a Column Based on the NUMBERLIST_T Type alter table e add (numlist numberlist_t); describe e Name ------------------------EMPNO ENAME INIT MGR DEPTNO NUMLIST Null Type -------- --------------NUMBER(4) NOT NULL VARCHAR2(8) NOT NULL VARCHAR2(5) NUMBER(4) NUMBER(2) NUMBERLIST_T

select empno, numlist from e;