prompt PL/SQL Developer import file
prompt Created on Monday, September 17, 2007 by sim
set feedback off
set define off
prompt Dropping BRANCH...
drop table BRANCH cascade constraints;
prompt Dropping CLIENT...
drop table CLIENT cascade constraints;
prompt Dropping OWNER...
drop table OWNER cascade constraints;
prompt Dropping PROPERTY_FOR_RENT...
drop table PROPERTY_FOR_RENT cascade constraints;
prompt Dropping REGISTRATION...
drop table REGISTRATION cascade constraints;
prompt Dropping STAFF...
drop table STAFF cascade constraints;
prompt Dropping VIEWING...
drop table VIEWING cascade constraints;
prompt Creating BRANCH...
create table BRANCH
(
  BNO    CHAR(5) not null,
  STREET VARCHAR2(35),
  CITY   VARCHAR2(10),
  PCODE  VARCHAR2(10)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating CLIENT...
create table CLIENT
(
  CNO       CHAR(5) not null,
  FNAME     VARCHAR2(10),
  LNAME     VARCHAR2(10),
  TEL_NO    CHAR(15),
  PREF_TYPE VARCHAR2(10),
  MAX_RENT  INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating OWNER...
create table OWNER
(
  ONO     CHAR(5) not null,
  FNAME   VARCHAR2(10),
  LNAME   VARCHAR2(10),
  ADDRESS VARCHAR2(50),
  TEL_NO  CHAR(15)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating PROPERTY_FOR_RENT...
create table PROPERTY_FOR_RENT
(
  PNO    CHAR(5) not null,
  STREET VARCHAR2(35),
  CITY   VARCHAR2(10),
  PCODE  VARCHAR2(10),
  TYPE   VARCHAR2(10),
  ROOMS  INTEGER,
  RENT   INTEGER,
  ONO    CHAR(5) not null,
  SNO    CHAR(5) not null,
  BNO    CHAR(5)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating REGISTRATION...
create table REGISTRATION
(
  CNO   CHAR(5) not null,
  BNO   CHAR(5),
  SNO   CHAR(5),
  JDATE CHAR(9)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating STAFF...
create table STAFF
(
  SNO      CHAR(5) not null,
  FNAME    VARCHAR2(10),
  LNAME    VARCHAR2(10),
  POSITION VARCHAR2(10),
  SEX      CHAR(1),
  DOB      CHAR(9),
  SALARY   INTEGER,
  BNO      CHAR(5)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Creating VIEWING...
create table VIEWING
(
  CNO   CHAR(5) not null,
  PNO   CHAR(5) not null,
  VDATE CHAR(9),
  COMM  VARCHAR2(15)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt Disabling triggers for BRANCH...
alter table BRANCH disable all triggers;
prompt Disabling triggers for CLIENT...
alter table CLIENT disable all triggers;
prompt Disabling triggers for OWNER...
alter table OWNER disable all triggers;
prompt Disabling triggers for PROPERTY_FOR_RENT...
alter table PROPERTY_FOR_RENT disable all triggers;
prompt Disabling triggers for REGISTRATION...
alter table REGISTRATION disable all triggers;
prompt Disabling triggers for STAFF...
alter table STAFF disable all triggers;
prompt Disabling triggers for VIEWING...
alter table VIEWING disable all triggers;
prompt Loading BRANCH...
insert into BRANCH (BNO, STREET, CITY, PCODE)
values ('B005 ', '22 DEER', 'LONDON', '12345');
insert into BRANCH (BNO, STREET, CITY, PCODE)
values ('B007 ', '16 ARGYLL', 'ABERDEEN', '12346');
insert into BRANCH (BNO, STREET, CITY, PCODE)
values ('B003 ', '163 MAIN', 'GLASGOW', '12347');
insert into BRANCH (BNO, STREET, CITY, PCODE)
values ('B004 ', '32 MANSE', 'BRISTOL', '12348');
insert into BRANCH (BNO, STREET, CITY, PCODE)
values ('B002 ', '56 CLOVER', 'LONDON', '12340');
commit;
prompt 5 records loaded
prompt Loading CLIENT...
insert into CLIENT (CNO, FNAME, LNAME, TEL_NO, PREF_TYPE, MAX_RENT)
values ('CR76 ', 'JOHN', 'KAY', '02077          ', 'FLAT', 425);
insert into CLIENT (CNO, FNAME, LNAME, TEL_NO, PREF_TYPE, MAX_RENT)
values ('CR56 ', 'ALINE', 'STEWART', '01418          ', 'FLAT', 350);
insert into CLIENT (CNO, FNAME, LNAME, TEL_NO, PREF_TYPE, MAX_RENT)
values ('CR74 ', 'MIKE', 'RITCHIE', '01475          ', 'HOUSE', 750);
insert into CLIENT (CNO, FNAME, LNAME, TEL_NO, PREF_TYPE, MAX_RENT)
values ('CR62 ', 'MARY', 'TREGEAR', '01224          ', 'FLAT', 600);
commit;
prompt 4 records loaded
prompt Loading OWNER...
insert into OWNER (ONO, FNAME, LNAME, ADDRESS, TEL_NO)
values ('CO46 ', 'JOE', 'KEOGH', '2 FERHUS DR', '01224          ');
insert into OWNER (ONO, FNAME, LNAME, ADDRESS, TEL_NO)
values ('CO87 ', 'CAROL', 'FARREL', '6 ACHRAY', '01413          ');
insert into OWNER (ONO, FNAME, LNAME, ADDRESS, TEL_NO)
values ('CO40 ', 'TINA', 'MURPHY', '63 WELL ST', '01419          ');
insert into OWNER (ONO, FNAME, LNAME, ADDRESS, TEL_NO)
values ('CO93 ', 'TONY', 'SHAW', '12 PARK', '01412          ');
commit;
prompt 4 records loaded
prompt Loading PROPERTY_FOR_RENT...
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PA14 ', '16 HOLHEAD', 'ABERDEEN', '15904', 'HOUSE', 6, 650, 'CO46 ', 'SA9  ', 'B007 ');
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PL94 ', '6 ARGYLL', 'LONDON', '15903', 'FLAT', 4, 400, 'CO87 ', 'SL41 ', 'B005 ');
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PG4  ', '2 LAWRENCE', 'GLASGOW', '15902', 'FLAT', 3, 350, 'CO40 ', 'SG37 ', 'B003 ');
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PG36 ', '2 MANOR', 'GLASGOW', '15901', 'FLAT', 3, 375, 'CO93 ', 'SG37 ', 'B003 ');
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PG21 ', '18 DALE', 'GLASGOW', '15900', 'HOUSE', 5, 600, 'CO87 ', 'SG37 ', 'B003 ');
insert into PROPERTY_FOR_RENT (PNO, STREET, CITY, PCODE, TYPE, ROOMS, RENT, ONO, SNO, BNO)
values ('PG21 ', '5 NOVAR', 'GLASGOW', '15800', 'FLAT', 4, 450, 'CO93 ', 'SG14 ', 'B003 ');
commit;
prompt 6 records loaded
prompt Loading REGISTRATION...
insert into REGISTRATION (CNO, BNO, SNO, JDATE)
values ('CR76 ', 'B005 ', 'SL41 ', '2-JAN-04 ');
insert into REGISTRATION (CNO, BNO, SNO, JDATE)
values ('CR56 ', 'B003 ', 'SG37 ', '11-APR-03');
insert into REGISTRATION (CNO, BNO, SNO, JDATE)
values ('CR74 ', 'B003 ', 'SG37 ', '15-NOV-02');
insert into REGISTRATION (CNO, BNO, SNO, JDATE)
values ('CR62 ', 'B007 ', 'SA9  ', '7-MAR-03 ');
commit;
prompt 4 records loaded
prompt Loading STAFF...
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SL21 ', 'JOHN', 'WHITE', 'MANAGER', 'M', '1-OCT-45 ', 30000, 'B005 ');
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SG37 ', 'ANN', 'BEECH', 'ASSISTANT', 'F', '10-NOV-60', 12000, 'B003 ');
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SG14 ', 'DAVID', 'FORD', 'SUPERVISOR', 'M', '24-MAR-58', 18000, 'B003 ');
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SA9  ', 'MARY', 'HOWE', 'ASSISTANT', 'F', '19-FEB-70', 9000, 'B007 ');
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SG5  ', 'SUSAN', 'BRAND', 'MANAGER', 'F', '3-JUN-40 ', 24000, 'B003 ');
insert into STAFF (SNO, FNAME, LNAME, POSITION, SEX, DOB, SALARY, BNO)
values ('SL41 ', 'JULIE', 'LEE', 'ASSISTANT', 'F', '13-JUN-65', 9000, 'B005 ');
commit;
prompt 6 records loaded
prompt Loading VIEWING...
insert into VIEWING (CNO, PNO, VDATE, COMM)
values ('CR56 ', 'PA14 ', '24-MAY-04', 'TOO SMALL');
insert into VIEWING (CNO, PNO, VDATE, COMM)
values ('CR76 ', 'PG4  ', '20-APR-04', 'TOO REMOTE');
insert into VIEWING (CNO, PNO, VDATE, COMM)
values ('CR56 ', 'PG4  ', '26-MAY-04', null);
insert into VIEWING (CNO, PNO, VDATE, COMM)
values ('CR62 ', 'PA14 ', '14-MAY-04', 'NO DINING ROOM');
insert into VIEWING (CNO, PNO, VDATE, COMM)
values ('CR56 ', 'PG36 ', '28-APR-04', null);
commit;
prompt 5 records loaded
prompt Enabling triggers for BRANCH...
alter table BRANCH enable all triggers;
prompt Enabling triggers for CLIENT...
alter table CLIENT enable all triggers;
prompt Enabling triggers for OWNER...
alter table OWNER enable all triggers;
prompt Enabling triggers for PROPERTY_FOR_RENT...
alter table PROPERTY_FOR_RENT enable all triggers;
prompt Enabling triggers for REGISTRATION...
alter table REGISTRATION enable all triggers;
prompt Enabling triggers for STAFF...
alter table STAFF enable all triggers;
prompt Enabling triggers for VIEWING...
alter table VIEWING enable all triggers;
set feedback on
set define on
prompt Done.