0

Sorry in advance for the large question, but I'm working on a piece of coursework for a data management module using Oracle 12c, I don't have any prior experience in anything like this and I'm very confused as to what to do. The objective is to create a database for a fictional shipping company, I have created an ERD and exported that into a DDL to use for my database queries. I have been able to input data into the Ship table, however when I try to input data into any other tables it come up with Error ORA-00942: table or view does not exist, and I am unsure why. Below is the code exported from the DDL and some sample statements. Thank you in advance for any help that you can give.

CREATE TABLE Consignments
(
Consignment_Number CHAR (6 CHAR) NOT NULL ,
Supplier_Address   VARCHAR2 (40 CHAR) ,
Delivery_Address   VARCHAR2 (40 CHAR) ,
Volume             NUMBER ,
Weight             NUMBER ,
Ship_Ship_Number   CHAR (4 CHAR) NOT NULL
) ;
ALTER TABLE Consignments ADD CONSTRAINT Consignments_PK PRIMARY KEY ( 
Consignment_Number ) ;

CREATE TABLE Officers
(
Officer_Number            CHAR (6 CHAR) NOT NULL ,
Officer_Name              VARCHAR2 (20 CHAR) ,
Officer_Address           VARCHAR2 (40 CHAR) ,
Officer_DoB               DATE ,
Officer_Rank              VARCHAR2 (20 CHAR) ,
Ship_Ship_Number          CHAR (4 CHAR) NOT NULL ,
Officers_Officer_Number   CHAR (6 CHAR) ,
Officers_Ship_Ship_Number CHAR (4 CHAR)
) ;
ALTER TABLE Officers ADD CONSTRAINT Officers_PK PRIMARY KEY ( 
Officer_Number, Ship_Ship_Number ) ;

CREATE TABLE Port
(
Port_Number    CHAR (3 CHAR) NOT NULL ,
Port_Name      VARCHAR2 (20 CHAR) ,
Agent_Name     VARCHAR2 (20 CHAR) ,
Arrival_Date   DATE ,
Departure_Date DATE
) ;
ALTER TABLE Port ADD CONSTRAINT Port_PK PRIMARY KEY ( Port_Number ) ;

CREATE TABLE Relation_1
(
Voyage_Voyage_Number CHAR (6 CHAR) NOT NULL ,
Port_Port_Number     CHAR (3 CHAR) NOT NULL
) ;
ALTER TABLE Relation_1 ADD CONSTRAINT Relation_1_PK PRIMARY KEY ( 
Voyage_Voyage_Number, Port_Port_Number ) ;

CREATE TABLE Ship
(
Ship_Number          CHAR (4 CHAR) NOT NULL ,
Ship_Name            CHAR (20 CHAR) ,
Voyage_Voyage_Number CHAR (6 CHAR) NOT NULL
) ;
CREATE UNIQUE INDEX Ship__IDX ON Ship
(
Voyage_Voyage_Number ASC
)
;
ALTER TABLE Ship ADD CONSTRAINT Ship_PK PRIMARY KEY ( Ship_Number ) ;

CREATE TABLE Voyage
(
Voyage_Number    CHAR (6 CHAR) NOT NULL ,
Duration         NUMBER ,
Ship_Ship_Number CHAR (4 CHAR) NOT NULL
) ;
CREATE UNIQUE INDEX Voyage__IDX ON Voyage
(
Ship_Ship_Number ASC
)
;
ALTER TABLE Voyage ADD CONSTRAINT Voyage_PK PRIMARY KEY ( Voyage_Number ) ;

ALTER TABLE Consignments ADD CONSTRAINT Consignments_Ship_FK FOREIGN KEY ( 
Ship_Ship_Number ) REFERENCES Ship ( Ship_Number ) ;

ALTER TABLE Relation_1 ADD CONSTRAINT FK_ASS_1 FOREIGN KEY ( 
Voyage_Voyage_Number ) REFERENCES Voyage ( Voyage_Number ) ;

ALTER TABLE Relation_1 ADD CONSTRAINT FK_ASS_2 FOREIGN KEY ( 
Port_Port_Number ) REFERENCES Port ( Port_Number ) ;

ALTER TABLE Officers ADD CONSTRAINT Officers_Officers_FK FOREIGN KEY ( 
Officers_Officer_Number, Officers_Ship_Ship_Number ) REFERENCES Officers ( 
Officer_Number, Ship_Ship_Number ) ;

ALTER TABLE Officers ADD CONSTRAINT Officers_Ship_FK FOREIGN KEY ( 
Ship_Ship_Number ) REFERENCES Ship ( Ship_Number ) ;

ALTER TABLE Ship ADD CONSTRAINT Ship_Voyage_FK FOREIGN KEY ( 
Voyage_Voyage_Number ) REFERENCES Voyage ( Voyage_Number ) ;

ALTER TABLE Voyage ADD CONSTRAINT Voyage_Ship_FK FOREIGN KEY ( 
Ship_Ship_Number ) REFERENCES Ship ( Ship_Number ) ;




INSERT INTO Officers (Officer_Number, Officer_Name, Officer_Address, 
Officer_DoB, Officer_Rank, Ship_Ship_Number)
VALUES (000000, 'J. Cena', ('1 Elder Row, Glasgow'), 1970-01-01, 'Captain', 
1111)
  • What insert statements are you running? Create looks fine from a glance, are the tables actually being created? – Twelfth Dec 07 '17 at 17:20
  • The last insert statement I used was INSERT INTO Voyage (Voyage_Number, Duration) VALUES (123450, 103); – tony brazier Dec 07 '17 at 17:21
  • That would error on the not null constraint...Voyage has : Ship_Ship_Number CHAR (4 CHAR) NOT NULL – Twelfth Dec 07 '17 at 17:24
  • ahh okay, as mentioned in the question, I really don't know what I'm doing, we never really moved past basic queries and modifying tables in the lectures. I tried a new insert statement adding in Ship_Ship_Number as a value, not sure if that was a possible solution, but it came up with the same error message – tony brazier Dec 07 '17 at 17:28
  • please add this sample insert statement to your question ( for easier readability of future references ) – Barbaros Özhan Dec 07 '17 at 17:31
  • You've got a decent amount of foriegn keys, so you will only be able to enter data in the correct order or those references will start to fail and block your inserts as well. Edit your question with a few inserts you are trying to run, then put the error. It'll help us hep you. – Twelfth Dec 07 '17 at 17:32
  • awfully sorry the insert statement was INSERT INTO Voyage (Voyage_Number, Duration, Ship_Ship_Number) VALUES (123450, 100, 1111) – tony brazier Dec 07 '17 at 17:32
  • may yield ORA-02291 problem due to HR.VOYAGE_SHIP_FK(since we don't know the datas) – Barbaros Özhan Dec 07 '17 at 17:33
  • 1
    I don't see any insert statement. No wonder you can't input data into table. – Eric Dec 07 '17 at 17:33
  • When tables were created, which username was used to logon and run the script on Oracle ? This is because Oracle uses "schemas" that are usually tied to the object owner. So if you created tables using user "myuser", you should query using "myuser" as schema,.. like "select * from myuser.voyage" – Cristian Meneses Dec 07 '17 at 17:45
  • 2
    By the way `CHAR` is the datatype we keep locked in the attic and don't talk about. https://stackoverflow.com/a/42165653/230471 – William Robertson Dec 07 '17 at 18:27
  • add this: COMMIT; – tbone Dec 07 '17 at 19:59

0 Answers0