Users - non-sono-bello-ma-patcho/BD_LAB GitHub Wiki

Each user have an univocal username, a password, name, surname, year and place of birth, phone number, register number and the name of the course it is attending.

User is the most complex entity in the schema, it is referenced by many other entities and it references few entities as well. Premium users have a pivotal role in the schema since of confirmation depends on them. This is how we design the user entity(As usual all the attributes that are not primary or external are omitted):

In the initial schema basic and premium users were pictured as two different entities, specialization of the father entity user, we than translated the hierarchy by adding an additional privilege field, which specify wether the user is basic or premium. But as you can see lots of association referred to premium users, as team/player/referee confirmation, are missing: this is due to the fact that we preferred to keep the two representation separated for matters of clearness. Here the second part of the user entity:

This is how we translated the user entity:

create table Users (
  username varchar(64) primary key,
  password varchar(64),
  name varchar(64),
  surname varchar(64),
  birthDate date not null,
  birthPlace varchar(64) not null,
  photo bigserial references Photos on delete cascade on update cascade,
  regNumber int,
  uprivilege privilege default 'base',
  studyCourse varchar(64) references StudyCourses on update cascade on delete restrict,
  unique(name, surname, regNumber)
);

One thing that you can notice is that non of the all 0-n associations have been translated as user attributes, this is due to the impossibility to translate a multi-value attribute into sql script. We added constraint on studycourse since this field can't be empty.