[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