# ****STAFF MANAGEMENT SYSTEM DATABASE DOCUMENTATION**** ## ****1) INTRODUCTION****

Database documentation serves as a comprehensive guide outlining the structure, organization, and functionality of a database system. This documentation aims to focus on describing the thought process behind how the Staff Management System Database is being constructed by showing steps taken, methods used and providing explaination where appropriate. There will be a coverage of Entity Sets, Relationships, Cardinality Constraints, Participation Constraints, Entity Relationship diagram, Relational Schema, Normalization, Final Schema for Implementation and Data Description. Each of the sections will contain meaningful descriptions and make use of tables or diagrams.

Practice of effective database documentation allows stakeholders such as developers and database administrators to quickly grasp the overall design of the database and improves maintainability.

## ****2) DATABASE DESIGN**** I. Entity Sets

This section will cover Entity sets. It will help map out tables to create as we break down their needs through understanding keys, attributes and details. The contents listed under column "Entity Sets" are tables that are deemed required for the construction of the basic structure of the database.



|ENTITY SETS|KEYS|OTHER ATTRIBUTES|DETAILS| |-----------|----|----------------|-------| |sms_users|users_id|users_username
users_password
users_contactNo
users_emailAdd
users_status|sms_users table is used to store simple information on users account that can be accessed for immediate use| |sms_user_type|usersType_id|usersType_name
usersType_authority|sms_user_type table is used to specify the position a particular user will have and their access to data through authority levels| |sms_personal_detail|personDet_id|personDet_firstName
personDet_lastName
personDet_givenName
personDet_ICNo
personDet_gender
personDet_fullAddress
personDet_contactNo2
personDet_photo
personDet_facebookID
personDet_joinedDate
personDet_emailAdd3|sms_personal_detail table is used to hold information that is more personal relating to each user| |sms_attendance|attend_id|attend_date
attend_checkInTime
attend_checkOutTime
attend_lateReason
|sms_attendance table is used to keep track of user attendance after a "check-in" or "check-out" has been made| |sms_log|log_id|log_data_action
log_date
log_time
|sms_log table is used to keep track of actions done on records within any other tables| |sms_position|position_id|position_name|| |sms_auth_token|token_id|selector
hashed_validator
expire_date
expire_time||
Figure A1. Entity Sets table













Example:
![entity-sets](https://www.dropbox.com/scl/fi/9airnmjv89q269ikye028/entity-sets.drawio-1.png?rlkey=7s632c9w1r9c85gux1h02amrg&dl=1)
Figure A2. Definition of terminologies used


II. Relationships

This section will contain Relationships which is used to outline meaningful association between two or more entity sets. This helps to improve overall table structures and reduce redundant data by establishing usage of shared data or keys among the tables.

|RELATIONSHIP SETS|BETWEEN WHICH ENTITIY SETS|ATTRIBUTE OF RELATIONSHIP SETS|DETAILS| |-----------|----|----------------|-------| |classified_as|sms_users & sms_users_type|/|sms_users can be classified as a certain sms_users_type| |holds|sms_users & sms_personal_detail|/|sms_users will each hold an sms_personal_detail| |mark|sms_users & sms_attendance|/|sms_users will each mark their attendance through sms_attendance| |logs|sms_log & sms_users & sms_users_type & sms_personal_detail & sms_attendance|/|whenever any new record is added to a table, sms_log will log its relevant information| |log_by|sms_log & sms_users|/|to keep track of which user in sms_users made changes to which record in sms_log| |under|sms_personal_detail & sms_position|/|to store the position that each user is under in their personal detail record| |refers_to|sms_auth_token & sms_users|/|to store authentication token if the user ticks the checkbox "remember me" in the login page|

III. Cardinality Constraints

This section will contain Cardinality Constraints which is used to specify the maximum number of occurrences of one entity that can be associated with another entity in a relationship.

|RELATIONSHIP SETS|CARDINALITY CONSTRAINTS|DETAILS| |-----------|----|----------------| |classified_as|One-to-Many|- A user can only be classified as one userType
- A userType can be used to classify many users

- Eg. A user named "Ali" may only possess "admin" userType, but userType "admin" can be used to classify many users like "Ali", "Jack" or "John"| |holds|One-to-One|- A user may only hold one personalDetail information at one time
- A personalDetail information should only refer to one user| |mark|Many-to-one|- A users may be able to mark their attendance many times throughout their time within the company
- A record of attendance may only refer to one user| |logs|One-to-One-to-One-to-One-to-Many|-A single log should only refer to a single record from any table
- A single record of any table may be referred by sms_log several times if many changes for one particular record is made| |under|One-to-many|| |refers_to|One-to-many||

IV. Participation Constraints

This section will contain Participation Constraints which refers to the minimum number of instances that an entity can be associated with another entity.

|RELATIONSHIP SETS|PARTICIPATION CONSTRAINTS/ ANY OTHER CONSTRAINTS|DETAILS| |-----------|----|----------------| |classified_as|sms_users - Partial participation

sms_users_type - Partial participation|- A user can still exist without first knowing their level of authority within the company
- A userType can still exist without first being labelled under a user| |holds|sms_users - Partial participation

sms_personal_detail - Total participation| - A user account can be created without having information on their personal details
- Information on a user's personal detail cannot exist unless the user has already created an account| |mark|sms_user - Partial participation

sms_attendance - Total participation|- A user may still exist even without attendance marked
- An attendance record cannot exist without the presence of a user| |logs|sms_log - Total participation
sms_user - Partial participation
sms_attendance - Partial participation
sms_users_type - Partial participation
sms_personal_detail - Partial participation|- A log cannot exist without the presence of other tables
- A table can still exist without the presence of a log record| |log_by|sms_log - Total participation
sms_users - Partial participation|- A log cannot exist without having a user first make changes to data in the table
- A user can still exist without having made any changes to the tables|

V. Entity Relationship Diagram
![entity-relationship-diagram](https://www.dropbox.com/scl/fi/iavm09ncw0mie59f35lsp/entity-relationship-diagram.drawio-4.png?rlkey=7vw7jh8uw6sm3kr1pvnfizo19&dl=1)

VI. Relational Schema

sms_users(users_id, users_username, users_password, users_contactNo, users_emailAdd, users_status)

sms_user_type(usersType_id, usersType_name, userType_authority)

sms_personal_detail(personDet_id,personDet_firstName,personDet_lastName,personDet_givenName,personDet_ICNo,personDet_gender, personDet_fullAddress, personDet_contctNo2, personDet_photo, personDet_photo, personDet_facebookID, personDet_joinedDate, personDet_emailAdd3)

sms_users(users_id, users_username, users_password, users_contactNo, users_emailAdd, users_status, usersType_id, personDet_id)
FK usersType_id REF sms_user_type(usersType_id)
FK personDet_id REF sms_personal_detail(personDet_id)

sms_attendance(attend_id, attend_date, attend_checkInTime, attend_checkOutTime, attend_lateReason)

sms_attendance(attend_id, attend_date, attend_checkInDateTime, attend_checkOutDateTime, attend_lateReason, users_id)

FK users_id REF sms_users(users_id)

sms_log(log_id, log_data_action, log_date, log_time)

sms_log(log_id, log_data_action, log_date, log_time, users_id)

FK users_id REF sms_users(users_id)

logs(log_id, users_id, usersType_id, personDet_id, attend_id)

FK users_id REF sms_users(users_id)
FK usersType_id REF sms_user_type(usersType_id)
FK personDet_id REF sms_personal_detail(personDet_id)
FK attend_id REF sms_attendance(attend_id)


VI. Normalization

sms_users(users_id, users_username, users_password, users_contactNo, users_emailAdd, users_status, usersType_id, personDet_id)

----> sms_users(users_id, users_username, users_password, users_contactNo, users_emailAdd, users_status)
----> sms_users_info(users_id, personDet_id, usersType_id)
FK users_id REF sms_users(users_id)
FK personDet_id REF sms_personal_detail(personDet_id)
FK usersType_id REF sms_user_type(usersType_id)

logs(log_id, users_id, usersType_id, personDet_id, attend_id)

logs(log_id, users_id, usersType_id, personDet_id, attend_id)

#removed candidate key authority

----> sms_log_users(log_id, users_id)
FK users_id REF sms_users(users_id)
----> sms_log_users_type(log_id, usersType_id)
FK usersType_id REF sms_users_type(usersType_id)
----> sms_log_personal_detail(log_id, personDet_id)
FK personDet_id REF sms_personal_detail(personDet_id)
----> sms_log_attendance(log_id, attend_id)
FK attend_id REF sms_attendance(attend_id)


VII. Final Schema for Implementation

sms_users(users_id, users_username, users_password, users_contactNo, users_emailAdd, users_status)

sms_users_info(users_id, personDet_id, usersType_id)

FK users_id REF sms_users(users_id)
FK personDet_id REF sms_personal_detail(personDet_id)
FK usersType_id REF sms_users_type(usersType_id)

sms_users_type(usersType_id, usersType_name, usersType_authority)

sms_personal_detail(personDet_id,personDet_firstName,personDet_lastName,personDet_givenName,personDet_ICNo,personDet_gender, personDet_fullAddress, personDet_contctNo2, personDet_photo, personDet_facebookID, personDet_joinedDate, personDet_emailAdd3)

sms_attendance(attend_id, users_id, attend_date, attend_checkInTime, attend_checkOutTime, attend_lateReason)

FK users_id REF sms_users(users_id)

sms_log(log_id, log_actionBy_id, log_data_action, log_date, log_time)

FK log_actionBy_id REF sms_users(users_id)

sms_log_users(log_id, users_id)

FK users_id REF sms_users(users_id)

sms_log_users_type(log_id, usersType_id)

FK usersType_id REF sms_users_type(usersType_id)

sms_log_personal_detail(log_id, personDet_id)

FK personDet_id REF sms_personal_detail(personDet_id)

sms_log_attendance(log_id, attend_id)

FK attend_id REF sms_attendance(attend_id)


VIII. Data Description

This section holds tables that specifies the datatype, size and null-viability of each attributes when an entity is created within the database.


|sms_users| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |users_id|INT||NO|user_id primary key used to uniquely identify each record| |users_username|VARCHAR|40|NO|Holds each user account's username| |users_password|VARCHAR|255|NO|Holds each user account's password| |users_contactNo|INT|20|NO|Holds each user account's contact Number| |users_emailAdd|VARCHAR|60|NO|Holds each user account's email address| |users_status|VARCHAR|20||Hold each user account's status|
|sms_usersInfo| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |users_id|INT||NO|Foreign key from table sms_users used to maintain relationship between tables sms_usersType and sms_personalDetail| |personDet_id|INT||NO|Foreign key from table sms_personalDetail used to maintain relationship between tables sms_users and sms_usersType| |usersType_id|INT|||Foreign key from table sms_usersType used to maintain relationship between tables sms_users and sms_personalDetail|
|sms_usersType| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |usersType_id|INT||NO|usersType_id primary key used to uniquely identify each record| |usersType_name|VARCHAR|40|NO|Holds each usersType's position name| |usersType_authority|INT|4||Holds each usersType's authority level|
|sms_personalDetail| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |personDet_id|INT||NO|personDet_id primary key used to uniquely identify each record| |personDet_firstName|VARCHAR|40||Holds each user's personal detail first name information| |personDet_lastName|VARCHAR|40||Holds each user's personal detail last name information| |personDet_givenName|VARCHAR|40||Holds each user's personal detail given name information| |personDet_ICNo|INT|20||Holds each user's personal detail IC number| |personDet_gender|CHAR|6||Holds each user's personal detail gender information| |personDet_fullAddress|VARCHAR|50||Holds each user's personal detail full address information| |personDet_contctNo2|INT|20||Holds each user's personal detail 2nd contact number information| |personDet_photo|BOOL|||Holds each user's personal detail photo presence information| |personDet_facebookID|VARCHAR|30||Holds each user's personal detail facebook ID information| |personDet_joinedDate|DATE|||Holds each user's personal detail date of entry into company information| |personDet_emailAdd3|VARCHAR|60||Holds each user's personal detail email address information|
|sms_attendance| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |attend_id|INT||NO|attend_id primary key used to uniquely identify each record| |users_id|INT||NO|Foreign key from table sms_users used to maintain relationship between tables sms_users and sms_attendance| |attend_Date|DATE|||Holds each user's date of attendance| |attend_checkInTime|TIME|||Holds each user's check in time| |attend_checkOutTime|TIME|||Holds each user's check out time| |attend_lateReason|VARCHAR|100||Holds each user's reason for late attendance|
|sms_log| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |log_id|INT||NO|log_id primary key used to uniquely identify each record| |log_actionBy_id|INT||NO|Foreign key from table sms_users used to maintain relationship between tables sms_users and sms_log; used to identify which user changed what record| |log_data_action|BOOL||NO|Holds the action that was performed on each log; can only be of 2 types, create or update| |log_date|DATE||NO|Holds the date when a log is created| |log_time|TIME||NO|Holds the time when a log is created|
|sms_log_users| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |log_id|INT||NO|log_id primary key used to uniquely identify each record; also a foreign key from table sms_log used to maintain relationship between sms_log and sms_log_users| |users_id|INT||NO|Foreign key from table sms_users used to maintain relationship between tables sms_users and sms_log_users|
|sms_log_users_type| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |log_id|INT||NO|log_id primary key used to uniquely identify each record; also a foreign key from table sms_log used to maintain relationship between sms_log and sms_log_users_type| |usersType_id|INT||NO|Foreign key from table sms_users_type used to maintain relationship between tables sms_users_type and sms_log_users_type|
|sms_log_personal_detail| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |log_id|INT||NO|log_id primary key used to uniquely identify each record; also a foreign key from table sms_log used to maintain relationship between tables sms_log and sms_log_personal_detail| |personDet_id|INT||NO|Foreign key from table sms_personal_detail used to maintain relationship between tables sms_personal_detail and sms_log_personal_detail|
|sms_log_attendance| |-----------| |COL NAME|TYPE|SIZE|NULL|DESCRIPTION| |--------|----|----|----|-----------| |log_id|INT||NO|log_id primary key used to uniquely identify each record; also a foreign key from table sms_log used to maintain relationship between tables sms_log and sms_log_attendance| |attend_id|INT||NO|Foreign key from table sms_attendance used to maintain relationship between tables sms_attendance and sms_log_attendance|