Is my ER-Diagram for yearly data on trade and transportation OK?

Question

As part of my school project, I'm supposed to design/make a database where one can store/update/retrieve yearly data about international trade and transportation. To begin with, I isolated a small part of the database in order to start small.

• Firstly I tried to design a diagram that would store the number of passengers (not individual passengers) that embarked/disembarked on/off ships in each port of every country every year and how many local and foreign passengers there were (I don't need those two to interact).

  

  (Ignore the Passengers on the top.) and the inwards_outwards entity would give me a table in the database that would look like this:

  

• Secondly I tried to design the diagram of a table where I could store Origin-Destination data (e.g. of the passengers that arrived in (or left from ) a country, how many came from (went to) each other country etc.

  For instance in 2011, from England 20 passengers flew to France, 10 to Germany, etc. and in 2011, in England arrived 23 from France, 19 from Germany, etc.

  

  and the od_hellas entity would give me a table like this:

  

Questions:

1. Do the above look OK to you?

2. Is there a more efficient way to store yearly data?

3. Is what I'm trying to make doable in the context of a project? Any advice in general?

Answer 1

You can do this with three tables as shown below. If you want to add data about Passengers then you would need a fourth table "Passenger"

The value in your "Numbers" column can be calculated from the base data by using SQL COUNT something like this:

SELECT COUNT(passengerNr)
FROM Departure
WHERE portCode = "EL_OGRPIR";

To get the data by year, you just add something like [AND date = "2011"] (depends on how you choose to store your date data.)

If my solution helps, please click on the vote icon.

Here is the logical view of the tables.

Here is the SQL DDL that you would use to generate the tables in a database. (e.g. you could cut and paste this SQL into the "New Query" panel in SQL Server Management Studio.)

CREATE SCHEMA Trade
GO

CREATE TABLE Trade.Port
(
    portCode nchar(15) NOT NULL,
    countryCode nchar(2) NOT NULL,
    portName nchar(50) NOT NULL,
    type nchar(10) CHECK (type IN (N'SeaPort', N'AirPort', N'LandBorder')) NOT NULL,
    CONSTRAINT Port_PK PRIMARY KEY(portCode)
)
GO


CREATE TABLE Trade.Departure
(
    passengerNr int NOT NULL,
    portCode nchar(15) NOT NULL,
    "date" datetime NOT NULL,
    isInternational bit,
    CONSTRAINT Departure_PK PRIMARY KEY(passengerNr, portCode)
)
GO


CREATE TABLE Trade.Arrival
(
    passengerNr int NOT NULL,
    portCode nchar(15) NOT NULL,
    "date" datetime NOT NULL,
    isInternational bit,
    CONSTRAINT Arrival_PK PRIMARY KEY(passengerNr, portCode)
)
GO


ALTER TABLE Trade.Departure ADD CONSTRAINT Departure_FK FOREIGN KEY (portCode) REFERENCES Trade.Port (portCode) ON DELETE NO ACTION ON UPDATE NO ACTION
GO


ALTER TABLE Trade.Arrival ADD CONSTRAINT Arrival_FK FOREIGN KEY (portCode) REFERENCES Trade.Port (portCode) ON DELETE NO ACTION ON UPDATE NO ACTION
GO