Relation
R(subject_ID, subject_name, user_ID, user_name, user_surname, user_email, user_role, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, subtask_ID,subtask_name, subtask_completion)
Functional Dependencies
FD = {
subject_ID → subject_name,
user_ID → user_name, user_surname, user_email, user_role,
module_ID → module_name, module_code, subject_ID,
module_ID, module_year, module_term, module_group → module_timetable,
task_ID → task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group,
subtask_ID → subtask_name, subtask_completion, task_ID,
}
- Because each module can take place in multiple years, in multiple terms and can potentially have different student groups, each timetable is unique to these 3 attributes.
Normalization
Closures
- (subject_ID) = {subject_ID, subject_name}
- (user_ID) = {user_ID, user_name, user_surname, user_email, user_role}
- (module_ID) = {module_ID, module_name, module_code, subject_ID, subject_name}
- (module_ID, module_year, module_term) = {module_ID, module_name, module_code, subject_ID, subject_name, module_year, module_term, module_timetable}
- (task_ID) = {task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name}
- (subtask_ID) = {subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name}
Key
((user_ID) + (subtask_ID)):
(user_ID, module_ID, module_year, module_term, subtask_ID) = {user_ID, user_name, user_surname, user_email, user_role, subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_timetable, subject_ID, subject_name}
Second Normal Form (2NF)
Every non-primary-key attribute is fully functionally dependent on the primary key.
R(user_ID, user_name, user_surname, user_email, user_role, subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_timetable, subject_ID, subject_name)
user_ID
→ user_name
, user_surname
, user_email
, user_role
Partial Functional Dependency
subtask_ID
→ subtask_name
, subtask_completion
, task_ID
, task_name
, task_description
, task_deadline
, task_date_created
, task_type
, task_importance
, task_completion
, module_ID
, module_name
, module_code
, module_year
, module_term
, module_timetable
, subject_ID
, subject_name
Partial Functional Dependency
R(user_ID, subtask_ID)
R1(user_ID, user_name, user_surname, user_email, user_role)
R2(subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name)
Relations
- R(user_ID, subtask_ID)
- R1(user_ID, user_name, user_surname, user_email, user_role)
- R2(subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name)
Third Normal Form (3NF)
There is no transitive dependency for non-prime attributes.
R(subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name)
subtask_ID
→ task_ID
→ task_name
, task_description
, task_deadline
, task_date_created
, task_type
, task_importance
, task_completion
, module_ID
, module_year
, module_term
, module_group
, subject_ID
R(subtask_ID, subtask_name, subtask_completion, task_ID)
R1(task_ID, task_ID*, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name)
R(task_ID, task_ID*, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_name, module_code, module_year, module_term, module_group, module_timetable, subject_ID, subject_name)
task_ID
→ module_ID
, module_year
, module_term
, module_group
→ module_name
, module_code
, module_timetable
, module_year
, module_term
, module_group
, subject_ID
R(task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group)
R1(module_ID, module_year, module_term, module_group, module_name, module_code, subject_ID, subject_name)
R1(module_ID, module_year, module_term, module_group, module_name, module_code, subject_ID, subject_name)
module_ID
, module_year
, module_term
, module_group
→ module_name
, module_code
, module_timetable
, subject_ID
, subject_name
Full Functional Dependency
module_ID
→ module_name
, module_code
, subject_ID
, subject_name
Partial Functional Dependency
R(module_ID, module_year, module_term, module_group, module_timetable)
R1(module_ID, module_name, module_code, subject_ID, subject_name)
R(module_ID, module_name, module_code, subject_ID, subject_name)
module_ID
→ subject_ID
→ subject_name
R(module_ID, module_name, module_code, subject_ID)
R1(subject_ID, subject_name)
Relations
- R(user_ID, user_name, user_surname, user_email, user_role)
- R(subject_ID, subject_name)
- R(module_ID, module_name, module_code, subject_ID)
- R(module_ID, module_year, module_term, module_group, module_timetable)
- R(task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group)
- R(subtask_ID, subtask_name, subtask_completion, task_ID)
Boyce Codd Normal Form (BCNF)
For every functional dependency X → Y, X must the super key of that relation.
R1(user_ID, user_name, user_surname, user_email, user_role)
R2(subject_ID, subject_name)
R3(module_ID, module_name, module_code, subject_ID)
R4(module_ID, module_year, module_term, module_timetable)
R5(task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group)
R6(subtask_ID, subtask_name, subtask_completion, task_ID)
R1(user_ID, user_name, user_surname, user_email, user_role)
(user_ID) = {user_ID, user_name, user_surname, user_email, user_role}
R2(subject_ID, subject_name)
(subject_ID) = {subject_ID, subject_name}
R3(module_ID, module_name, module_code, subject_ID)
(module_ID) = {module_ID, modules_name, module_code, subject_ID, subject_name}
R4(module_ID, module_year, module_term, module_timetable)
(module_ID, module_year, module_term) = (module_ID, module_year, module_term, module_timetable, modules_name, module_code, subject_ID, subject_name)
R5(task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID)
(task_ID) = {task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group, modules_name, module_code, module_timetable, subject_ID, subject_name}
R6(subtask_ID, subtask_name, subtask_completion, task_ID)
(subtask_ID) = {subtask_ID, subtask_name, subtask_completion, task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, modules_name, module_code, subject_ID, subject_name}
Relations
- user(user_ID, user_name, user_surname, user_email, user_role)
- subject(subject_ID, subject_name)
- module(module_ID, module_name, module_code, subject_ID)
- module_timetable(module_ID, module_year, module_term, module_timetable)
- task(task_ID, task_name, task_description, task_deadline, task_date_created, task_type, task_importance, task_completion, module_ID, module_year, module_term, module_group)
- subtask(subtask_ID, subtask_name, subtask_completion, task_ID)