I started to learn SQL recently. I downloaded some kind of assignment, where the goal is to design small database. I have two tables listed below:

create table SEA (
	SEA_NAME varchar(50)constraint sea_prj_key primary key,
	AREA numeric(18, 0),
);

and:

create table NATION (
	CTRY_NAME varchar(50)constraint sea_prj_key primary key,
	AREA numeric(18, 0),
);

Since more than one country can lie by one sea, and also one coutry can lie by more than one sea, I've created the third table:

create table SEA_BORDER (
	CTRY_NAME varchar(50)references NATION(CTRY_NAME),
	SEA_NAME varchar(50)references SEA(SEA_NAME),
	BORDER numeric(18, 0)
);

The problem is that there can be a Country that doesn't lie by the sea, but there can't be sea, that doesn't create a border of any country. I have completly no idea how to solve that problem. Dont know if I should create another column/table or create some constraint. So I need a guide where I should look for solution.

I feel current structure is enough, if a country has no sea-border, there will be no record in SEA_BORDER for that country. As far as database is concerned your current structure is perfect.

When finding countries with/without border with sea etc, you need to write correct SQLs (selecs).

The problem is not about country with no sea-border. I'm concerned about second condition - how can I be sure that there won't be sea, that doesn't create a border with any country? (every sea has to create a border with one country at least).

Database cannot really force this (nothing simple or standard way); your programming logic should take care of this by not allowing add sea not possible unless there is country, edit sea record invalid if no country relation is given by user.
As far as database is concerned, this is good.

Ok, thx a lot for the answer.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.