spool crea_sailors.txt --------------------------------------------------------------------------- -- Script file to generate the tables Sailors, Boats, and Reserves -- used in Chapter 5 of the book Database Management Systems by -- Raghu Ramakrishnan and Johannes Gehrke. -- -- Hjalmtyr Hafsteinsson, september 2010. -- Adapted for Oracle database. Felipe Manriquez 2013 --------------------------------------------------------------------------- connect / as sysdba prompt dropping user Sailors -- Drop schema Sailors DROP USER sailors cascade / prompt dropping TABLESPACE sailors_data -- Drop Tablespaces DROP TABLESPACE sailors_data including contents and datafiles / prompt dropping TABLESPACE sailors_index DROP TABLESPACE sailors_index including contents and datafiles / prompt Creating tablespace sailors_data -- Creacion de tablespaces de tablas CREATE TABLESPACE sailors_data DATAFILE '/u02/oradata/DUOC/sailors_data01.dbf' SIZE 16M AUTOEXTEND ON NEXT 4M MAXSIZE 1G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO / prompt Creating tablespace sailors_index -- Creating index tablespace sailors_index CREATE TABLESPACE sailors_index DATAFILE '/u02/oradata/DUOC/sailors_index01.dbf' SIZE 16M AUTOEXTEND ON NEXT 4M MAXSIZE 1G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO / prompt Creating user (schema) Sailors -- Creating user (schema) sailors create user sailors identified by sailors default tablespace sailors_data temporary tablespace temp / -- Grants roles connect and resource to user sailors prompt Granting roles connect and resource to user sailors grant connect, resource to sailors / -- Granting system privilege CREATE VIEW to user Sailors prompt Granting system privilege CREATE VIEW to user Sailors grant create view to sailors / -- Connects to user sailors prompt Connecting to user sailors connect sailors/sailors prompt altering the default date format for the Sailors session -- altering the default date format for the Sailors session ALTER SESSION SET NLS_DATE_FORMAT='mm/dd/yy' / -- Drop any existing table. prompt droppping tables (if they exist) drop table sailors cascade constraints purge / drop table boats cascade constraints purge / drop table reserves cascade constraints purge / -- Add the three tables. prompt creating table Sailors create table sailors( sid integer, sname varchar(30), rating integer, age real, constraint sailors_pk primary key(sid) using index tablespace sailors_index ) tablespace sailors_data; prompt creating table Boats create table boats( bid integer, bname varchar(30), color varchar(10), constraint boats_pk primary key(bid) using index tablespace sailors_index ) tablespace sailors_data; prompt creating table Reserves create table reserves( sid integer, bid integer, day date, constraint reserves_pk primary key(sid,bid,day) using index tablespace sailors_index, constraint reserves_sailors_fk foreign key(sid) references sailors, constraint reserves_boats_fk foreign key(bid) references boats ) tablespace sailors_data; prompt Inserting sailors rows -- Insert sample data insert into sailors (sid, sname, rating, age) values (22, 'Dustin', 7, 45.0); insert into sailors (sid, sname, rating, age) values (29, 'Brutus', 1, 33.0); insert into sailors (sid, sname, rating, age) values (31, 'Lubber', 8, 55.5); insert into sailors (sid, sname, rating, age) values (32, 'Andy', 8, 25.5); insert into sailors (sid, sname, rating, age) values (58, 'Rusty', 10, 35.0); insert into sailors (sid, sname, rating, age) values (64, 'Horatio', 7, 35.0); insert into sailors (sid, sname, rating, age) values (71, 'Zorba', 10, 16.0); insert into sailors (sid, sname, rating, age) values (74, 'Horatio', 9, 35.0); insert into sailors (sid, sname, rating, age) values (85, 'Art', 3, 25.5); insert into sailors (sid, sname, rating, age) values (95, 'Bob', 3, 63.5); prompt Inserting boats rows insert into boats (bid, bname, color) values (101, 'Interlake', 'blue'); insert into boats (bid, bname, color) values (102, 'Interlake', 'red'); insert into boats (bid, bname, color) values (103, 'Clipper', 'green'); insert into boats (bid, bname, color) values (104, 'Marine', 'red'); prompt Inserting reserves rows insert into reserves (sid, bid, day) values (22, 101, '10/10/98'); insert into reserves (sid, bid, day) values (22, 102, '10/10/98'); insert into reserves (sid, bid, day) values (22, 103, '10/8/98'); insert into reserves (sid, bid, day) values (22, 104, '10/7/98'); insert into reserves (sid, bid, day) values (31, 102, '11/10/98'); insert into reserves (sid, bid, day) values (31, 103, '11/6/98'); insert into reserves (sid, bid, day) values (31, 104, '11/12/98'); insert into reserves (sid, bid, day) values (64, 101, '9/5/98'); insert into reserves (sid, bid, day) values (64, 102, '9/8/98'); insert into reserves (sid, bid, day) values (74, 103, '9/8/98'); prompt commiting changes commit; prompt Restablishing default date format for the session -- Restablishing default date format for the session -- Warning!! Spanish format ALTER SESSION SET NLS_DATE_FORMAT='dd/mm/yyyy' / prompt Adds new column email to table sailors -- Adds new column email to table sailors ALTER TABLE sailors ADD (email varchar2(20)) / prompt Adds new cONSTRAINT sailors_email_UK to table sailors -- Adds new cONSTRAINT sailors_email_UK to table sailors ALTER TABLE sailors ADD CONSTRAINT Sailors_email_UK UNIQUE (EMAIL) / prompt From the oracle data dictionary, retrieving integrity constraints select substr(OWNER,1,10) owner, CONSTRAINT_NAME, CONSTRAINT_TYPE, substr(TABLE_NAME,1,15) table_name from user_constraints / prompt retrieving indexes tablespaces select index_name, tablespace_name from user_indexes / -- It is observed that the UK index is not in the right tablespace prompt Moving the UK index Sailors_email_UK from the current tablespace to the new one ALTER INDEX Sailors_email_UK REBUILD TABLESPACE sailors_index / -- Creating addional indexes prompt Creating index idx_reserves_bid on reserves (bid) CREATE INDEX idx_reserves_bid on reserves (bid) TABLESPACE sailors_index / prompt Creating index idx_reserves_sid on reserves (sid) CREATE INDEX idx_reserves_sid on reserves (sid) TABLESPACE sailors_index / prompt Creating index idx_sailors_rating on sailors(rating) CREATE INDEX idx_sailors_rating on sailors(rating) TABLESPACE sailors_index / -- Test that the new indexes are been used by the oracle engine access path -- Analyze execution plans spool off