Suppose you are given the task to create roles and assign users to them for Sohar college. Write a detailed description with examples on the roles, their privileges and users.
Create the following tables in SQL and populate them with the data of all group members for this semester. You need to show both the SQL queries and the populated tables. Please make sure to apply various constraints that are applicable.