The maximum length of an Ingres object name is 24 characters.

Ingres Data Types.

Notation              Type           Range                             

char(1) - char(2000)  character      A string of 1 to 2000 characters  

c1 - c2000            character      A string of 1 to 2000 characters  

varchar(1) -          character      A string of 1 to 2000 characters  
varchar(2000)                                                          

text(1) - text(2000)  character      A string of 1 to 2000 characters  

integer1              1-byte         -128 to +127                      
                      integer                                          

smallint              2-byte         -32,768 to +32,767                
                      integer                                          

integer               4-byte         -2,147.483,648 to +2,147,483,647  
                      integer                                          

float4                4-byte         -1.0e+38 to +1.0e+38              
                      floating       (7 digit precision)               

float                 8-byte         -1.0e+38 to +1.0e+38              
                      floating       (16 digit precision)              

date                  date (12       1-jan-1582 to 31-dec-2382 (for    
                      bytes)         absolute dates) and -800 to +800  
                                     years (for time intervals)        

money                 money (8       $-999,999,999,999.99 to           
                      bytes)         $999,999,999,999.99               

table_key             character      no range: stored as 8 bytes       

object_key            character      no range: stored as 16 bytes      

5.1.2 Copying Tables.

It is also possible with the following variation of the CREATE statement to create a new table definition and copy rows into the new table with a single statement.

CREATE TABLE table-name ( column-definition-list )

AS SELECT column-list

FROM table-list

WHERE conditional-expression ;

Example 5.1.2 - Let us suppose that want to create an exact copy of the Ticket table which we will call Ticket2.

CREATE TABLE Ticket2 (TicketNo, TicketDate, Pid)

AS SELECT TicketNo, TicketDate, Pid

FROM Ticket ;

As Ticket2 is inheriting the same number of columns, the same order of columns, and the same column names as Ticket, the column-lists used in this example could have been omitted.

If we had wanted to create Ticket2 containing only a sub-set of the rows from the Ticket table then we would have used the WHERE clause to specify the required selection conditions.

5.1.3 Full Ingres Create Table Syntax.

Create a new base table owned by the user who issues the statement:

create table tablename

( columnname format {, columnname format} )

[with_clause]

To create a table and load from another table:

create table tablename

[( columnname {, columnname} )] as subselect {union [all] subselect}

[with_clause]

Where the with_clause consists of the word with followed by a comma-separated list of one or more of the following:

structure = hash | heap | isam | btree /*default is heap */

location = (locationname {, locationname}) /*default is ii_database */

[no ]journaling /* default without journaling */

[no ]duplicates /* default duplicates allowed */

key = (columnlist)

fillfactor = n /* defaults - 50%hash,

80%isam, 80%btree */

minpages = n /* No of hash primary pages - default 16 */

maxpages = n /* No of hash primary pages - default no limit */

leaffill = n /* %fill - btree leaf index pages - default 70% */

nonleaffill = n /* %fill - tree nonleaf indexpages - default 80%*/

compression[ = ([[no]key] [,[no]data])] | nocompression

A table can have a maximum of 300 columns and a table row may be a maximum of 2000 bytes wide. (Note nullable columns require an additional byte of storage for the null indicator.)

The name and data type of each column in the new table are specified by columnname and format arguments respectively.

Columnname can be any valid Ingres name - must me unique within the table.

Format specifies the datatype and length of the column and uses the following syntax:

data type [not null [with | not default] | with null] [not | with system_maintained]

with null (assumed if [not null] omitted)

The column accepts nulls. (Ingres inserts a null if no value supplied.)

not null with default

The column does not accept nulls.(Ingres supplies a default value if no value given.)

not null (= not null not default)

The user must supply a value.

with system_maintained

Used in conjunction with a logical key data types (ie table_key or object_key). Ingres automatically assigns a unique value to the column when a row is appended to the table.

Only compatible with not null with default.

System_maintained columns cannot be changed by users or applications.

5.1.4 Removing Unwanted Tables.

When a database table becomes redundant it may be dropped from the database as follows.

DROP TABLE Table2 ;

Exercise 6. (This exercise must be followed in sequence.)

Give the appropriate SQL to following:

1) Create the archive table APassenger with the same column names and data types as the Passenger table. Reference Appendix A6.2 or use the help statement to see the Passenger table definition.

2) Copy rows to the Apassenger table from the Passenger table according to the SQL specification given in Example 4.6.4. Use appropriate SQL to confirm that you have copied the required rows into the APassenger table.

3) Create using a single statement a new table called People which is an exact copy of (ie contains the same rows of data) the Passenger table.

4) Issue a COMMIT after creating the People table.

5) Delete those rows (passenger records) in the People table which appear in the Apassenger table. Use appropriate SQL to confirm that you have deleted the required records.

6) Issue a ROLLBACK. Use appropriate SQL to confirm the results.

7) Create using a single statement a new table called Craft which is an exact copy of (ie contains the same rows of data) as the Aircraft table.

8) Add the two new aircraft types given in Example 4.6.3 to the Craft table. Use appropriate SQL to confirm that the two new aircraft have been inserted correctly.

9) Reduce by 4 the seating capacity of all aircraft recorded in the Craft table. Use appropriate SQL to confirm that you have updated the table correctly.

10) KEEP the Craft table you will need it later! However, make a note of how you could have removed this table at this point without using the Drop statement.

Drop the APassenger and People tables created in this exercise.

fromai   flightn  deptime   arrtime    

BIRM     BD655    15:00     17:05      
BIRM     BD657    17:30     19:35      
BIRM     BD659    18:25     20:30      

5.2.2 Views Providing Database Security.

There are many instances where access to private data within a database needs to be restricted to specific users.

It is possible to create such access restrictions using views. In this section we will consider just one example of how access may be controlled by creating a view which consists of different rows depending on the user who is querying the view.

Example 5.2.2 - Let us suppose that we want to allow all passengers to view their itineraries from a visual display at the airport by logging on to the Airline's DBMS under their Passenger ID as held in the Passenger table (the Pid column).

The DBMS provides a pseudo-column user which holds the name (login) of the person currently querying the database. The pseudo-column user may then be used in the WHERE clause like any other table column. Hence the following view will automatically return the itinerary belonging to the enquiring passenger.

CREATE VIEW PassengerItinerary

AS SELECT I.LegNo, I.TicketNo, I.FlightNo, I.FlightDate

FROM Passenger P, Itinerary I

WHERE P.Pid = user

AND P.TicketNo = I.TicketNo

ORDER BY TicketNo, LegNo ;

This view would need to be accessible to all, so we would need to assign read-only privileges to the view as follows. We would do this with the grant statement as follows:

grant select on PassengerItinerary to public;

The following SQL would then list only the itinerary belonging to the enquiring passenger.

SELECT * FROM PassengerItinerary ;

Exercise 7.

Give the SQL required to

1. Passengers travelling from Heathrow to Paris must pick up a link flight from East Midlands. Create a View of the interconnecting flights between HROW (Heathrow) and PARI (Paris).

2. List, based on an appropriate join with the view created in (1), the possible arrival times at Paris based upon a departure from Heathrow on flight BD224.

3. Remove the View created in (1) from the database.