[RESOLVED] Oracle Train Assignment

[Nightwalker83]

Hi,

I have been given the task by my lecturer to design a train information system. Below is the diagram I need to follow and also my attempt at writing the code.

create or replace type discount_type as object
(discountID integer,
discountName varchar2(25),
discountPercent integer,
startDate date,
endDate date)
/

create table discount_table of discount_type ;
/

alter table discount_table
add (constraint discountID_pk primary key (discountID));


Create or replace type person_type as object
(personID integer,
firstName varchar2(25),
lastName varchar2(25),
dob date,
email varchar2 (25),
phone varchar2 (10))
not final;
/

create table person_table of person_type;
/

alter table person_table
add (constraint personID_pk primary key (personID));

create or replace type staff_type under person_type
(staffID integer, password varchar2(8));
/

create or replace type customer_type under person_type
(customerID integer, gender varchar2(1), title varchar2(4),  dob date);
/

create table staff_table of staff_type;
/
create table customer_table of customer_type;
/
------
create or replace type address_type as object
(addressID integer,
street varchar(25),
suburb varchar2(25),
postcode varchar2(4),
cstate varchar2(4),
addressType varchar2(25),
personRef REF person_type);
/

create table address_table of address_type;
/

alter table address_table
add (constraint AddressID_pk primary key (AddressID));
--------
create or replace type station_type
(stationID integer, stationName varchar2(25));
/

create table station_table of station_type;
/

alter table station_table
add (constraint stationID_pk primary key (stationID));

Create or replace type ticket_type as object
(TicketID integer,
TravellerName varchar2(25),
resavationID integer,
stationStart integer,
stationEnd integer,
discountID integer,
carriageID integer,
seatID integer,
carriageID1 integer, serviceID integer,
dateOfService date,
stationRef REF station_type);
/

create or replace type resavation_type as object
(resavationID integer,
customerID integer,
resavationDate date,
resavationStatus varchar2(4),
carriageID integer,
serviceID integer,
dateofService date,
customerRef REF customer_type)
nested table ticket store as ticket_tab;
/


create or replace type table_ticket_type as table of ticket_type;
/
create table resavation_table of resavation_type;
/

alter table resavation_table
add (constraint resavationID_pk primary key (resavationID));

insert into resavation_table
values(, , 
table_ticket_type(ticket_type(),
ticket_type(),
ticket_type()));

create or replace type payment_type as object
(paymentID integer,
amount decimal,
paidDate date,
paidType varchar2(25),
resavationID integer,
resavationRef REF resavation_type)
/

create table payment_table of payment_type ;
/

alter table payment_table
add (constraint paymentID_pk primary key (paymentID));

create or replace type class_type as object
(classID integer,
className varchar2(25))
/

create table class_table of class_type;
/

alter table class_table
add (constraint classID_pk primary key (classID));

create or replace type carriage_type as object
(carriageID integer,
classID integer,
numSeats integer,
classRef REF class_type)
/

create table carriage_table of carriage_type;
/

alter table carriage_table
add (constraint carriageID_pk primary key (carriageID));

create or replace type seat_type as object
(seatID integer,
seatID integer,
columnno integer,
rowno integer,
carriageRef REF carriage_type)
/

create table seat_table of seat_type;
/

alter table seat_table
add (constraint seatID_pk primary key (seatID));

create or replace type seatservicedatecarriage_type as object
(carriageID integer,
seatID integer,
carriageID1 integer,
serviceID integer,
dateOfService date,
seatRef REF seat_type)
/

Now in the assignment brief it says:

For simplicity, you only require to define the following tables of object types:
Person, Staff, Customer, Reservation, Ticket. Although, as you can probably see in the picture I have attached there a several classes being referenced by the ticket table which in-turn reference other tables. I have a couple of questions:

1. How far do I need to go is the assignment only requires the information for those five tables mentioned above?

2. Once finished, which tables do I populate with the data?

Thanks,


Nightwalker

[jmcilhinney]

It seems to me that you've already gone overboard if that instruction is to be believed. If the assignment specifies only those five tables then you apparently don't need anything about stations, etc. You would just define specific ticket types and the user can then buy one of those.

[Nightwalker83]

Yeah, I know! However, if you look at the "Ticket" class it references other classes which have there own references. This is what confuses me.

[jmcilhinney]

Ah, so you were actually given that diagram rather than having created it yourself. In that case, I assume that this (rather grammatically incorrect) sentence:

For simplicity, you only require to define the following tables of object types:
Person, Staff, Customer, Reservation, Ticket.

means that you only have to create classes relating to those tables and, therefore, also omit properties that relate to other tables.

[Nightwalker83]

Ah, so you were actually given that diagram rather than having created it yourself. In that case, I assume that this (rather grammatically incorrect) sentence:means that you only have to create classes relating to those tables and, therefore, also omit properties that relate to other tables.

Yes, that is what I thought too. We weren't given the class diagram but an incomplete .mwb file which, contained all the classes apart from those you mentioned. I had to added those you mentioned but it doesn't make make sense if I am only suppose to create the Person, Staff, Customer, Reservation, Ticket tables. There would be no reason to include the whole diagram in the first place.

[jmcilhinney]

These are really questions for your lecturer because we can only speculate. With that in mind, I would guess that they built that diagram at some point based on what they thought they wanted, then discovered that it was just too much for some or all of the students.

[Nightwalker83]

Damn, it appears as if I only need to figure out how to get the following code working before I have finished.

create type resavation_type as object
(resavationID integer,
customerID integer,
resavationDate date,
resavationStatus varchar2(4),
carriageID integer,
serviceID integer,
dateofService date,
customerRef REF customer_type,
stationRef REF station_type,
MEMBER FUNCTION totalTickets RETURN NUMBER);
/

create type body resavation_type as
member function totalTickets return 
number is x number;
BEGIN
	SELECT count(ticketID) INTO x
	FROM type ticket_type ti, TABLE(ti.ticketID) tid 
	RETURN x;
END totalTickets,
END; 
/
create table resavation_table of resavation_type;
/
alter table resavation_table
add (constraint resavationID_pk primary key (resavationID));

I seem to have two problems.

1. I receive "Type created with compilation errors" when creating both the reservation type and body.

2. Is the body suppose to be separate (as above) or part of the existing type?

[abhijit]

Where did you create station_type, customer_type and ticket_type?

[Nightwalker83]

Here is an updated version of the code:

create type person_type as object
(personID integer,
firstName varchar2(25),
lastName varchar2(25),
dob date,
email varchar2 (25),
phone varchar2 (10))
not final;
/

create table person_table of person_type;
/

alter table person_table
add (constraint personID_pk primary key (personID));

create type staff_type under person_type
(staffID integer, password varchar2(8));
/

create type customer_type under person_type
(gender varchar2(1), title varchar2(4));
/

create table staff_table of staff_type;
/
create table customer_table of customer_type;
/
------
create type address_type as object
(addressID integer,
street varchar(25),
suburb varchar2(25),
postcode varchar2(4),
cstate varchar2(4),
addressType varchar2(25),
personRef REF person_type);
/

create table address_table of address_type;
/

alter table address_table
add (constraint AddressID_pk primary key (AddressID));
--------
create type resavation_type as object
(resavationID integer,
customerID integer,
resavationDate date,
resavationStatus varchar2(1),
carriageID integer,
serviceID integer,
dateofService date,
customerRef REF customer_type,
member function totalTickets return number);
/

create table resavation_table of resavation_type;
/

create type station_type as object
(stationID integer, stationName varchar2(25), resavationRef REF resavation_type);
/

create table station_table of station_type;
/

alter table station_table
add (constraint stationID_pk primary key (stationID));

create or replace type ticket_type as object
(TicketID integer,
TravellerName varchar2(25),
resavationID integer,
stationStart integer,
stationEnd integer,
discountID integer,
carriageID integer,
seatID integer,
carriageID1 integer, 
serviceID integer,
dateOfService date,
stationRef REF station_type);
/

create table table_ticket of ticket_type;
/

alter table table_ticket
add (constraint TicketID_pk primary key (TicketID));

create or replace type body resavation_type is
   member function num_of_tickets return 
   number is N number;
   begin
       select count(T.TicketID) into N from table_ticket T where
	   deref(T.resavationID) = self;
       return N;
   end num_of_tickets;
end;
/

alter table resavation_table
add (constraint resavationID_pk primary key (resavationID));

INSERT INTO customer_table VALUES (1, 'Bob', 'Smith', '12-April-71', 'bobsmith@optus', '86754345', 'M', 'Mr');
INSERT INTO customer_table VALUES (2, 'Jane', 'Jones', '11-May-66', '[email protected]', '23145678', 'F', 'Mrs');
INSERT INTO customer_table VALUES (3, 'Brian', 'Jones', '07-July-50', '[email protected]', '12345678', 'M', 'Mr');

INSERT INTO staff_table VALUES (1, 'Thomas', 'Travis', '22-Feb-82', '[email protected]', '22278889', 78656, 'tfrsewrd');
INSERT INTO staff_table VALUES (2, 'Joann', 'Smith', '22-Feb-82', 'jsmith@optus', '8545332', 56454, '756456uy');
INSERT INTO staff_table VALUES (3, 'Aaron', 'Spehr', '26-Nov-83', '[email protected]', '87654321', 1223, '9786jgft');

INSERT INTO address_table select 1, '1/34 Wilson St', 'Prospect', '5082', 'SA', 'Unit', REF(A) from customer_table A where A.personID = 1;
INSERT INTO address_table select 2, '2 some Ave', 'Hampsted Gardens', '5047',  'Vic', 'House', REF(A) from customer_table A where A.personID = 2;
INSERT INTO address_table select 3, '334 Long Way', 'somewhere', '5194','SA', 'House', REF(A) from customer_table A where A.personID = 3;

INSERT INTO resavation_table select 1, 1, '1-Jan-2000', 'T', 0965, 276, '27-Jul-2011', REF(A) from customer_table A where A.personID = 1;
INSERT INTO resavation_table select 2, 3, '27-Jul-2011', 'T', 222, 666, '27-May-2011', REF(A) from customer_table A where A.personID = 2;
INSERT INTO resavation_table select 3, 2, '27-Dec-2011', 'C', 911, 592,  '27-Jul-2011', REF(A) from customer_table A where A.personID = 3;

INSERT INTO station_table select 1, 'Adelaide', REF(R) from resavation_table R where R.resavationID = 1;
INSERT INTO station_table select 2, 'Melbourne', REF(R) from resavation_table R where R.resavationID = 2;
INSERT INTO station_table select 3, 'Sydney', REF(R) from resavation_table R where R.resavationID = 3;

INSERT INTO  table_ticket select 1, 'Jeff Healy', 297,  1, 3, 2,   5, 27, 1, 7, '05-Dec-2000',  REF(S) from station_table S where S.stationID = 3;

[abhijit]

Here is an updated version of the code:

create type person_type as object
(personID integer,
firstName varchar2(25),
lastName varchar2(25),
dob date,
email varchar2 (25),
phone varchar2 (10))
not final;
/

create table person_table of person_type;
/

alter table person_table
add (constraint personID_pk primary key (personID));

create type staff_type under person_type
(staffID integer, password varchar2(8));
/

create type customer_type under person_type
(gender varchar2(1), title varchar2(4));
/

create table staff_table of staff_type;
/
create table customer_table of customer_type;
/
------
create type address_type as object
(addressID integer,
street varchar(25),
suburb varchar2(25),
postcode varchar2(4),
cstate varchar2(4),
addressType varchar2(25),
personRef REF person_type);
/

create table address_table of address_type;
/

alter table address_table
add (constraint AddressID_pk primary key (AddressID));
--------
create type resavation_type as object
(resavationID integer,
customerID integer,
resavationDate date,
resavationStatus varchar2(1),
carriageID integer,
serviceID integer,
dateofService date,
customerRef REF customer_type,
member function totalTickets return number);
/

create table resavation_table of resavation_type;
/

create type station_type as object
(stationID integer, stationName varchar2(25), resavationRef REF resavation_type);
/

create table station_table of station_type;
/

alter table station_table
add (constraint stationID_pk primary key (stationID));

create or replace type ticket_type as object
(TicketID integer,
TravellerName varchar2(25),
resavationID integer,
stationStart integer,
stationEnd integer,
discountID integer,
carriageID integer,
seatID integer,
carriageID1 integer, 
serviceID integer,
dateOfService date,
stationRef REF station_type);
/

create table table_ticket of ticket_type;
/

alter table table_ticket
add (constraint TicketID_pk primary key (TicketID));

create or replace type body resavation_type is
   member function num_of_tickets return 
   number is N number;
   begin
       select count(T.TicketID) into N from table_ticket T where
	   deref(T.resavationID) = self;
       return N;
   end num_of_tickets;
end;
/

alter table resavation_table
add (constraint resavationID_pk primary key (resavationID));

INSERT INTO customer_table VALUES (1, 'Bob', 'Smith', '12-April-71', 'bobsmith@optus', '86754345', 'M', 'Mr');
INSERT INTO customer_table VALUES (2, 'Jane', 'Jones', '11-May-66', '[email protected]', '23145678', 'F', 'Mrs');
INSERT INTO customer_table VALUES (3, 'Brian', 'Jones', '07-July-50', '[email protected]', '12345678', 'M', 'Mr');

INSERT INTO staff_table VALUES (1, 'Thomas', 'Travis', '22-Feb-82', '[email protected]', '22278889', 78656, 'tfrsewrd');
INSERT INTO staff_table VALUES (2, 'Joann', 'Smith', '22-Feb-82', 'jsmith@optus', '8545332', 56454, '756456uy');
INSERT INTO staff_table VALUES (3, 'Aaron', 'Spehr', '26-Nov-83', '[email protected]', '87654321', 1223, '9786jgft');

INSERT INTO address_table select 1, '1/34 Wilson St', 'Prospect', '5082', 'SA', 'Unit', REF(A) from customer_table A where A.personID = 1;
INSERT INTO address_table select 2, '2 some Ave', 'Hampsted Gardens', '5047',  'Vic', 'House', REF(A) from customer_table A where A.personID = 2;
INSERT INTO address_table select 3, '334 Long Way', 'somewhere', '5194','SA', 'House', REF(A) from customer_table A where A.personID = 3;

INSERT INTO resavation_table select 1, 1, '1-Jan-2000', 'T', 0965, 276, '27-Jul-2011', REF(A) from customer_table A where A.personID = 1;
INSERT INTO resavation_table select 2, 3, '27-Jul-2011', 'T', 222, 666, '27-May-2011', REF(A) from customer_table A where A.personID = 2;
INSERT INTO resavation_table select 3, 2, '27-Dec-2011', 'C', 911, 592,  '27-Jul-2011', REF(A) from customer_table A where A.personID = 3;

INSERT INTO station_table select 1, 'Adelaide', REF(R) from resavation_table R where R.resavationID = 1;
INSERT INTO station_table select 2, 'Melbourne', REF(R) from resavation_table R where R.resavationID = 2;
INSERT INTO station_table select 3, 'Sydney', REF(R) from resavation_table R where R.resavationID = 3;

INSERT INTO  table_ticket select 1, 'Jeff Healy', 297,  1, 3, 2,   5, 27, 1, 7, '05-Dec-2000',  REF(S) from station_table S where S.stationID = 3;

This is all working code. So it looks like you have resolved the issue.

[Nightwalker83]

That works for you, no problems with the sections mentioned in post #7? It didn't for me.

[abhijit]

That works for you, no problems with the sections mentioned in post #7? It didn't for me.

Depends on the version of Oracle, you're currently using. I am on 9.2.0.7.0

[Nightwalker83]

Well, according to the command prompt I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.

[abhijit]

Well, according to the command prompt I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0.

I do not have that version to test this. However from my experience, oracle has always supported backward compatibility.

[Nightwalker83]

I do not have that version to test this. However from my experience, oracle has always supported backward compatibility.

I suppose I could ask my lecturer whether or not the above code worked for him because from what I know is using the same version of Oracle as I am.

Since, you managed to get it working do you mind posting the result of when you run the code, query the database?

[abhijit]

Here you go:
http://imgur.com/a/8O3wk#.TsV3Rz1h2nE.bitly


You do realize that to view the contents of REF column require more than this tool I am using.

EDITED TO ADD: OR A SIMPLE DEREF() SHOULD DO THE TRICK.

[abhijit]

Deref - PNG Image.

[Nightwalker83]

No, that's not it! The ticket count is suppose to be there, that is what the code in post #7 is suppose to do.