Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

How to resolve the errors!? DROP TABLE IF EXISTS Country; create table Country(

ID: 3831129 • Letter: H

Question

How to resolve the errors!?

DROP TABLE IF EXISTS Country;
create table Country(
  code char(2) NOT NULL PRIMARY KEY,
  name varchar(100) NOT NULL
);

    DROP TABLE IF EXISTS Member;
create table Member(
  member_id INTEGER PRIMARY KEY,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  title varchar(100)  NOT NULL,
  live_in varchar(50)   REFERENCES Accommodation(name),
  Country_code char(2) NOT NULL REFERENCES Country(code)
        ON DELETE CASCADE
ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Athlete;
create table Athlete(
    id integer   PRIMARY KEY NOT NULL REFERENCES Member(member_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
        
);

DROP TABLE IF EXISTS Official;
create table Official(
    id integer   PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE CASCADE
      ON UPDATE CASCADE
);

DROP TABLE IF EXISTS Staff;
create table Staff(
    id integer   PRIMARY KEY NOT NULL REFERENCES Member(member_id)
     ON DELETE CASCADE
      ON UPDATE CASCADE     
);

DROP TABLE IF EXISTS Books;
Create table Books(
    book_time timestamp   NOT NULL,
    member_id integer  NOT NULL REFERENCES Member(member_id),
     
    start_time varchar(100) NOT NULL REFERENCES Journey(start_time),
    start_date date  NOT NULL REFERENCES Journey(start_date),
    byStaff integer  NOT NULL REFERENCES Staff(id),
    vehicle char(8)  NOT NULL REFERENCES Journey(code),
    PRIMARY KEY(member_id, start_time, start_date, vehicle)
);

DROP TABLE IF EXISTS Journey;
create table Journey(
  start_time time  NOT NULL,
  start_date date  NOT NULL,
   nbooked integer  NOT NULL,
  departure varchar(100) NOT NULL REFERENCES Place(name),
  arrival varchar(100) NOT NULL REFERENCES Place(name),
  code char(8)  NOT NULL REFERENCES Vehicle(code),
    PRIMARY KEY( start_time, start_date, code)
);

DROP TABLE IF EXISTS Vehicle;
create table Vehicle(
  code char(8)   PRIMARY KEY,
  capacity varchar(100) NOT NULL
);

DROP TABLE IF EXISTS Place;
create table Place(
  name varchar(50)  PRIMARY KEY,
  address varchar(100) NOT NULL,
  longitude varchar(100)  NOT NULL,
  latitude varchar(100)  NOT NULL
);

DROP TABLE IF EXISTS SportVenue;
create table SportVenue(
    name varchar(50)   NOT NULL REFERENCES Place(name)
);

DROP TABLE IF EXISTS Accommodation;
create table Accommodation(
    name varchar(50)   NOT NULL REFERENCES Place(name),
  PRIMARY KEY(name)
);

DROP TABLE IF EXISTS Event;
create table Event(
  name varchar(100) NOT NULL PRIMARY KEY,
  result_type FLOAT NOT NULL,
  event_time varchar(50)  NOT NULL,
  event_date varchar(50)   NOT NULL,
  sport_name varchar(50) NOT NULL REFERENCES Sport(name)   
);

DROP TABLE IF EXISTS Participates;
create table Participates(
    medal varchar(6)  DEFAULT NULL CHECK(medal IN ('gold', 'silver', 'bornze', 'NULL')),
    result varchar(100)  NOT NULL,
    id integer    REFERENCES Athlete(id),
    sport_name varchar(50)  REFERENCES Event(name),
   PRIMARY KEY( id, sport_name)   
);

DROP TABLE IF EXISTS Runs;
create table Runs(
    role varchar(100)  NOT NULL,
    id integer    REFERENCES Official(id),
    sport_name varchar(50) REFERENCES Event(name),
    PRIMARY KEY( id, sport_name)   
);

DROP TABLE IF EXISTS Sport;
create table Sport(
    name varchar(50)  PRIMARY KEY
);

INSERT INTO Country(code,name) VALUES('AU', 'Australia');
INSERT INTO Country(code,name) VALUES('BR','Brazil');
INSERT INTO Country(code,name) VALUES('CA','Canada');
INSERT INTO Country(code,name) VALUES('CN','China');
INSERT INTO Country(code,name) VALUES('DE','Germany');
INSERT INTO Country(code,name) VALUES('IT','Italy');
INSERT INTO Country(code,name) VALUES('KR','Korea');
INSERT INTO Country(code,name) VALUES('RU','Russia');
INSERT INTO Country(code,name) VALUES('UK','United Kingdom');
INSERT INTO Country(code,name) VALUES('US','United States');


INSERT INTO Vehicle VALUES ('BUS','25');
INSERT INTO Vehicle VALUES ('CAR','4');
INSERT INTO Vehicle VALUES ('TEMPO','25');

INSERT INTO Place(name, address,longitude,latitude)
VALUES('ANZ Stadium','Sydney Olympic Park','33.8471° S', '151.0634° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('Ryde Sports Center','109 Kent Rd','33.7873° S', '151.1127° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('ICC','14 Darling Dr','33.8734° S', '151.1994° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('Sydney Harbour Marriott Hotel' ,'30 pitt st','33.8627° S', '151.2093° E');

INSERT INTO SportVenue(name)
VALUES('ANZ Stadium');
INSERT INTO SportVenue(name)
VALUES('Ryde Sports Center');

INSERT INTO Accommodation(name)
VALUES('ICC');
INSERT INTO Accommodation(name)
VALUES('Sydney Harbour Marriott Hotel');


INSERT INTO Sport(name)
VALUES('Swimming');
INSERT INTO Sport(name)
VALUES('Football');
INSERT INTO Sport(name)
VALUES('Diving');


INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('50m free style', 5.62, '10:30', '18 Dec 2009', 'Swimming');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('100m free style', 4.67, '11:30', '30 Jul 2009', 'Swimming');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('16 Team Tournaments MEN', 76.25 , '15:00', '17 Jul 2009', 'Football');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('12 Team Tournaments WOMEN', 63.85, '19:00', '18 Jul 2009', 'Football');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('10M PLATFORM MEN', 5.32, '19:30', '5 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('10M PLATFORM WOMEN', 6.70, '21:00', '5 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('3M SPRINGBOARD MEN', 3.50, '13:00', '6 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('3M SPRINGBOARD WOMEN', 3.25, '15:00', '6 MAY 2009', 'Diving');


INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000001,'shoujian','Dong','Mr','ICC','CN');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000002,'kaichun','Zhang','Miss','ICC','RU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000003,'Brenton','Cheung','Mr','Sydney Harbour Marriott Hotel','AU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000004,'Siwei','Li','Miss','ICC','AU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000005,'Qiang','Zeng','Miss','Sydney Harbour Marriott Hotel','BR');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (0000000006,'Shuhao','Zhang','Mr','Sydney Harbour Marriott Hotel','AU');


INSERT INTO Official VALUES(1);
INSERT INTO Official VALUES(2);
INSERT INTO Official VALUES(3);

INSERT INTO Staff VALUES (4);
INSERT INTO Staff VALUES (6);
INSERT INTO Staff VALUES (7);

INSERT INTO Books VALUES ('18-MAR-17 11.00.00.000000',1,'18-MAR-17 11.00.00.000000', '18-03-2017',4,'CAR');
INSERT INTO Books VALUES ('18-APR-1711.00.00.000000',3,'18-APR-17 11.00.00.000000', '18-04-2017',5,'BUS');
INSERT INTO Books VALUES ('18-APR-1711.00.00.000001',3,'18-APR-17 11.00.00.000001','18-04-2017',6,'BUS');

INSERT INTO Journey VALUES('18-MAR-17 11.00.00.000000','18-03-2017',1,'ABCD','CDEF','CAR');
INSERT INTO Journey VALUES('18-MAR-17 11.00.00.000001','18-04-2017',1,'ABCD','QRST','BUS');
INSERT INTO Journey VALUES('18-MAR-17 11.00.00.000002','18-04-2017',1,'ABCD','CDEF','TEMPO');

INSERT INTO Participates VALUES ('gold','1',0000000001, '50m free style');
INSERT INTO Participates VALUES ('silver','2',0000000004,'16 Team Tournaments MEN');

INSERT INTO Runs VALUES ('goal keeper',1,'Football');
INSERT INTO Runs VALUES ('striker',2, 'Football');

INSERT INTO Athlete VALUES (0000000001);
INSERT INTO Athlete VALUES (0000000002);
INSERT INTO Athlete VALUES (0000000003);
INSERT INTO Athlete VALUES (0000000004);
INSERT INTO Athlete VALUES (0000000005);
INSERT INTO Athlete VALUES (0000000006);

1. Why is the "relation <Table> does not exist" errors occur?

2. How should I test that each table exists?

I get errors when compiling and excecuting the ddl and dml above through pgadmin program...

Explanation / Answer

You will have to make sure that if a table declaration refrences another table, then they should be created first. I've reordered the CREATE TABLE statements.

Secondly, You should populate the tables as soon as they are created as other tables having a foreign key has to refer the values. I've done the same for you according to the tables.

The Insert statements for journey were not correct, i've corrected it.

There is still one error 'there is no unique constraint matching given keys for referenced table 'Journey'.

This error is due ot the fact that Journey table has a composite key and doesn't have unique constraint on the columns which are referenced in Books table. You'll have to refer to the ER diagram and restructure your books/journey table.

DROP TABLE IF EXISTS Sport;
create table Sport(
name varchar(50) PRIMARY KEY
);

INSERT INTO Sport(name)
VALUES('Swimming');
INSERT INTO Sport(name)
VALUES('Football');
INSERT INTO Sport(name)
VALUES('Diving');

DROP TABLE IF EXISTS Vehicle;
create table Vehicle(
code char(8) PRIMARY KEY,
capacity varchar(100) NOT NULL
);


INSERT INTO Vehicle VALUES ('BUS','25');
INSERT INTO Vehicle VALUES ('CAR','4');
INSERT INTO Vehicle VALUES ('TEMPO','25');

DROP TABLE IF EXISTS Place;
create table Place(
name varchar(50) PRIMARY KEY,
address varchar(100) NOT NULL,
longitude varchar(100) NOT NULL,
latitude varchar(100) NOT NULL
);

INSERT INTO Place(name, address,longitude,latitude)
VALUES('ANZ Stadium','Sydney Olympic Park','33.8471° S', '151.0634° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('Ryde Sports Center','109 Kent Rd','33.7873° S', '151.1127° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('ICC','14 Darling Dr','33.8734° S', '151.1994° E');
INSERT INTO Place(name, address,longitude,latitude)
VALUES('Sydney Harbour Marriott Hotel' ,'30 pitt st','33.8627° S', '151.2093° E');

DROP TABLE IF EXISTS Country;
create table Country(
code char(2) NOT NULL PRIMARY KEY,
name varchar(100) NOT NULL
);
INSERT INTO Country(code,name) VALUES('AU', 'Australia');
INSERT INTO Country(code,name) VALUES('BR','Brazil');
INSERT INTO Country(code,name) VALUES('CA','Canada');
INSERT INTO Country(code,name) VALUES('CN','China');
INSERT INTO Country(code,name) VALUES('DE','Germany');
INSERT INTO Country(code,name) VALUES('IT','Italy');
INSERT INTO Country(code,name) VALUES('KR','Korea');
INSERT INTO Country(code,name) VALUES('RU','Russia');
INSERT INTO Country(code,name) VALUES('UK','United Kingdom');
INSERT INTO Country(code,name) VALUES('US','United States');
  
DROP TABLE IF EXISTS Accommodation;
create table Accommodation(
name varchar(50) NOT NULL REFERENCES Place(name),
PRIMARY KEY(name)
);

INSERT INTO Accommodation(name)
VALUES('ICC');
INSERT INTO Accommodation(name)
VALUES('Sydney Harbour Marriott Hotel');

DROP TABLE IF EXISTS Journey;
create table Journey(
start_time time NOT NULL,
start_date date NOT NULL,
nbooked integer NOT NULL,
departure varchar(100) NOT NULL REFERENCES Place(name),
arrival varchar(100) NOT NULL REFERENCES Place(name),
code char(8) NOT NULL REFERENCES Vehicle(code),
PRIMARY KEY( start_time, start_date, code)
);
INSERT INTO Journey VALUES('01:00:00','2012-02-02',1,'ANZ Stadium','ICC','CAR');
INSERT INTO Journey VALUES('02:00:00 ','2013-02-02',1,'Ryde Sports Center','ICC','BUS');
INSERT INTO Journey VALUES('03:00:00 ','2014-02-02',1,'ICC','ICC','TEMPO');

DROP TABLE IF EXISTS Member;
create table Member(
member_id INTEGER PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
title varchar(100) NOT NULL,
live_in varchar(50) REFERENCES Accommodation(name),
Country_code char(2) NOT NULL REFERENCES Country(code)
ON DELETE CASCADE
ON UPDATE CASCADE
);

INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (1,'shoujian','Dong','Mr','ICC','CN');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (2,'kaichun','Zhang','Miss','ICC','RU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (3,'Brenton','Cheung','Mr','Sydney Harbour Marriott Hotel','AU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (4,'Siwei','Li','Miss','ICC','AU');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (5,'Qiang','Zeng','Miss','Sydney Harbour Marriott Hotel','BR');
INSERT INTO Member( member_id ,first_name,Last_name,title ,live_in,Country_code)
VALUES (6,'Shuhao','Zhang','Mr','Sydney Harbour Marriott Hotel','AU');

DROP TABLE IF EXISTS Athlete;
create table Athlete(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE CASCADE
ON UPDATE CASCADE
  
);

INSERT INTO Athlete VALUES (0000000001);
INSERT INTO Athlete VALUES (0000000002);
INSERT INTO Athlete VALUES (0000000003);
INSERT INTO Athlete VALUES (0000000004);
INSERT INTO Athlete VALUES (0000000005);
INSERT INTO Athlete VALUES (0000000006);

DROP TABLE IF EXISTS Official;
create table Official(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO Official VALUES(1);
INSERT INTO Official VALUES(2);
INSERT INTO Official VALUES(3);

DROP TABLE IF EXISTS Staff;
create table Staff(
id integer PRIMARY KEY NOT NULL REFERENCES Member(member_id)
ON DELETE CASCADE
ON UPDATE CASCADE   
);

INSERT INTO Staff VALUES (4);
INSERT INTO Staff VALUES (6);
INSERT INTO Staff VALUES (1);

DROP TABLE IF EXISTS Books;
Create table Books(
book_time timestamp NOT NULL,
member_id integer NOT NULL REFERENCES Member(member_id),

start_time varchar(100) NOT NULL REFERENCES Journey(start_time),
start_date date NOT NULL REFERENCES Journey(start_date),
byStaff integer NOT NULL REFERENCES Staff(id),
vehicle char(8) NOT NULL REFERENCES Journey(code),
PRIMARY KEY(member_id, start_time, start_date, vehicle)
);

INSERT INTO Books VALUES ('18-MAR-17 11.00.00.000000',1,'18-MAR-17 11.00.00.000000', '18-03-2017',4,'CAR');
INSERT INTO Books VALUES ('18-APR-1711.00.00.000000',3,'18-APR-17 11.00.00.000000', '18-04-2017',5,'BUS');
INSERT INTO Books VALUES ('18-APR-1711.00.00.000001',3,'18-APR-17 11.00.00.000001','18-04-2017',6,'CAR');

DROP TABLE IF EXISTS SportVenue;
create table SportVenue(
name varchar(50) NOT NULL REFERENCES Place(name)
);

INSERT INTO SportVenue(name)
VALUES('ANZ Stadium');
INSERT INTO SportVenue(name)
VALUES('Ryde Sports Center');


DROP TABLE IF EXISTS Event;
create table Event(
name varchar(100) NOT NULL PRIMARY KEY,
result_type FLOAT NOT NULL,
event_time varchar(50) NOT NULL,
event_date varchar(50) NOT NULL,
sport_name varchar(50) NOT NULL REFERENCES Sport(name)   
);

INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('50m free style', 5.62, '10:30', '18 Dec 2009', 'Swimming');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('100m free style', 4.67, '11:30', '30 Jul 2009', 'Swimming');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('16 Team Tournaments MEN', 76.25 , '15:00', '17 Jul 2009', 'Football');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('12 Team Tournaments WOMEN', 63.85, '19:00', '18 Jul 2009', 'Football');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('10M PLATFORM MEN', 5.32, '19:30', '5 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('10M PLATFORM WOMEN', 6.70, '21:00', '5 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('3M SPRINGBOARD MEN', 3.50, '13:00', '6 MAY 2009', 'Diving');
INSERT INTO Event(name, result_type, event_time, event_date, sport_name)
VALUES('3M SPRINGBOARD WOMEN', 3.25, '15:00', '6 MAY 2009', 'Diving');


DROP TABLE IF EXISTS Participates;
create table Participates(
medal varchar(6) DEFAULT NULL CHECK(medal IN ('gold', 'silver', 'bornze', 'NULL')),
result varchar(100) NOT NULL,
id integer REFERENCES Athlete(id),
sport_name varchar(50) REFERENCES Event(name),
PRIMARY KEY( id, sport_name)   
);


INSERT INTO Participates VALUES ('gold','1',0000000001, '50m free style');
INSERT INTO Participates VALUES ('silver','2',0000000004,'16 Team Tournaments MEN');


DROP TABLE IF EXISTS Runs;
create table Runs(
role varchar(100) NOT NULL,
id integer REFERENCES Official(id),
sport_name varchar(50) REFERENCES Event(name),
PRIMARY KEY( id, sport_name)   
);

INSERT INTO Runs VALUES ('goal keeper',1,'Football');
INSERT INTO Runs VALUES ('striker',2, 'Football');

Related Questions

How to make project management work in your organization, chapter 16. Throughout
Question #422800
How to make the computer generate a random number for a move for \'O\' in tic ta
Question #3539598
How to make the max number of vertices is 6000? The following code works 100% us
Question #3776469
How to make this look like a decent resturant hompage using the following - in h
Question #3600719
How to make this program ask for input again if invalid input is entered (in C p
Question #3798527
How to make this program for c++ visual studio? Please show your work and show r
Question #3789838
How to make this program generate a random 20000 array\'s elements with values b
Question #3804192
How to make this program output sent it to the file. import java.util.Scanner; p
Question #3737580
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
How to reset Command-C for copy not for \"Copy to Scrapbook\" For some reason in
Next
How to respond to this discussion reply. Forecasting is an essential tool making

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.