Help with my first database design

[yugoza]

Hi all,

I would really appreciate some feedback on my first database design which is depicted in a UML diagram attached. Does it make sense to you? Is it clear enough? Can you spot any immediate problems with it?

I'll very grateful for any comments on the representation as such, data types chosen, cardinalities, etc., but I'm especially unsure about the two ternary relationships in it - any opinions on this?

Many thanks to everyone!!!

[dickersonka]

its pretty clear

some recommendation i would make, is to try to get it 3nf

for example project->areas, this is a varchar, might consider making a separate table, but if its not that big of a concern then keep it the way you have it

i think you will need a usertype column on the user table
otherwise, how will you be able to tell who is student, supervisor, coordinator

[yugoza]

Hello & thank you for your comments!

Yes, I'm currently working on transforming this design into SQL tables and indeed, I map multivalued attributes into separate tables. And it is in these tables that e.g. each project->area or supervisor->research_area will be of type VARCHAR. Obviously, this is not clear from the UML diagram - is there a way of representing this in the UML somehow? Should UML show as much detail? I'm already trying to show that some of the attributes are multivalued by including [0..*] after their name. Just realised that I'm not showing what attributes are primary keys - although that is rather obvious - will need to fix this anyway.

Is there anything else I should do to get it 3nf?

Regarding the inheritance bit - it is disjoint because all users must be either students or supervisors (one of the supervisors is a coordinator with administrator privileges in the system). Because of this, my current idea is to have 3 tables - for students, supervisors and coordinators (I'm allowing for more than one coordinator, which might be useful under some special circumstances) because in case of disjoint generalisation there shouldn't be a table for the super-entity, should there?

See, I'm trying to follow that I believe are the rules, but it seems to complicate things for me. E.g. because I don't have that table for 'Users', I find it hard to transform those ternary relationships where I need to be able to refer to 2 sub-entities at the same time (student & supervisor and student & coordinator). So, shall I just create a table for Users even though it is not a proper way of implementing disjoint generalisation?