Elimination of cycle between relationships in the proposed ER diagram

user_18396935 0
Viewed 112

In making an ER schema for a simple database, I have encountered the following problem:

  • I get a cycle in the diagram, which I don't know if it is redundant or I could eliminate it somehow.

I present the problem on a large scale:

  1. The visit entity records visits to London by a vehicle. This entity contains information on their arrival, departure and total visit time.
  2. The vehicle entity contains information on the vehicle's place of origin, CO2 emissions and its number plate.
  3. The entity date contains information for each date of the day of the week to which it corresponds and the name of the holiday for any region added.

The region of the entity Date is matched to the Vehicle region. The entry_date/end_date of the entity Visit is matched to the date of the entity Date. Finally, the number plate of the entity Vehicle is matched to the number plate of the entity Visit. In this way, the cycle that I mentioned at the beginning appears.

The ER diagram is as follows:

ER diagram of the problem

If there are any questions about the problem that I have not explained, please do not hesitate to ask me. I welcome suggestions for improving the ER diagram, either to remove the cycle or to simply keep it as it is if you think it is correct.

entity-relationship diagram database
2 Answers

My two cents -

"Date" is really not a good name for entity or table. First, it is too general to convey what you really refer to. Second, it is a reserved key word in most common languages. You just cause unnecessary trouble for programming.

You use "Date" to get holiday name (for particular region) and week day, right? My suggestion is that you only need to save holidays in this table because weekday can be figured out in most common programming language.

This "Date" table is just a lookup table to help you find out holiday, you do not need to enforce relation between "Date" and Visit

I'd also suggest you add Region table to enforce consistent naming.

Here is the DB diagram, I renamed "date" to "holiday" enter image description here

Here is the SQL server implementation -

create table region (
  region_code varchar(100) primary key
 ,region_name varchar(100)
)

create table holiday (
  holiday_date date not null
 ,region_code varchar(100) not null
 ,holiday_name varchar(100) not null
)

alter table holiday add primary key (holiday_date, region_code)
alter table holiday add foreign key (region_code) references region (region_code)

create table vehicle (
  number_plate varchar(100) primary key
 ,region_code varchar(100) not null
 ,CO2_emission varchar(100)
)

alter table vehicle add foreign key (region_code) references region (region_code)

create table visit (
  number_plate varchar(100) not null
 ,entry_date date not null
 ,end_date date
)

alter table visit add primary key (number_plate, entry_date)
alter table visit add foreign key (number_plate) references vehicle (number_plate)
user_25820500

The Relate relationship between Vehicle and Date entity is redundant here. You can still find the dates for each visit a vehicle completed from the existing relationships. If you convert the ER diagram to DB tables, it'll be more clear.

Why are entry_date and exit_date two attributes of the Visit entity? These are already considered by the many-to-2 relationship with the Date entity. Remove these two attributes along with number_plate from Visit. Lastly, add an unique id to the Visit entity.

user_136799030
Related