I have this schema:
create table hall(
building-id int,
hall-id int,
capacity int,
primary key(building-id, hall-id)
);
create table semester(
sem-id char(5),
start-date date,
eend-date date,
primary key(sem-id)
);
create table weekly-event(
weid int,
course-id numeric(7,0),
sem-id char(5),
weekday varchar(10),
start-time time,
duration interval,
registered int,
primary key(weid),
foreign key(sem-id) references semester
);
create table one-time-event(
oeid int,
event-name varchar(50),
end-date date,
start-time time,
duration interval,
registered int,
primary key(oeid)
);
create table timetable(
start-date date,
start-time time,
hall-id int,
building-id int,
event-id int,
duration interval,
primary key(start-date, start-time, hall-id, building-id, event-id),
foreign key(hall-id, building-id) references hall
);
where either events that occur on a weekly basis or one-time events are scheduled on some campus. I want to draw entity-relationship diagram (ERD) for this schema and first of all I don't have much experience in drawing ERDs.
Also, I'm wondering if I should use some kind of "IS A" relationship for the weekly and one-time events and unite some attribute under event table which would have children which would contain only the attributes specific to them.
This is the ERD I have so far:
As far as I see it, timetable is dependent on other tables that's why I chose rhombuses with double lines. I also think that the relationship between timetable and hall and weekly-events is one-to-many while the relationship between timetable and one-time-event is one-to-one.
Because weekly-event references semester it has total participation.
Also logical timetable cannot exist without at least one hall, one-time-event or weekly-event that's why it also has total participation in those.
