Database for the Scheduling Process, Case Study Example

Database

Before creating a database, it would be appropriate to gather data thoroughly and analyze the requirements very closely. Our new database will remove the current scheduling process of trainers and new database will be adopted.

Analysis of Data:

The system currently used is fairly inefficient because a dry-erase board is used by it. Below is the list of disadvantages:

1. Training can be scheduled at two different locations during the same time by a trainer because there isn’t anyone checking the accuracy of the data being placed on the board.
2. The trainer could lose the schedule if someone accidently deletes them or if he is pranked.
3. The trainers have the means to change the schedule to their preferred times as frequently as they want to; thus, the trainees will have difficulty with this because of the lack of a set schedule.
4. Manual logging in of the data may also change the courses assigned to trainers which will result in a loss of money for both the trainees and trainers.

After the analysis of current system, the integrity of data and error is the major issue in the scheduling process, which occurs especially in an organization that is expanding continuously. If trainers and new courses are keyed in then there will be errors and discrepancies because keeping track of the data will be very hard.

Proposed Solution:

A new process for scheduling is introduced in order to solve this issue. This new schedule will include a database for the courses and trainers because of which human errors, main reason for a decrease in profit, will be removed. Data will also be organized by this new process; therefore, keeping track of them will become easier as they are saved on the database. Some of the advantages for the proposed process are listed below:

1. The database will keep tabs on the integrity of the data.
2. Finding data will become more manageable as well as easier.
3. This process will decrease the number of human errors.
4. The method for creating reports is fast and reliable.

Even though the introduction of a new data base has the above advantages, its procurement is nonetheless hindered by some main issues.

1. The installation of computers, servers as well as other technical equipment will be needed by a database; therefore, setting it up is a difficult task.
2. Those in charge of managing and operating will need knowledge of the system.
3. One of the main areas of concern is its’ security because a company’s operations may be halted due to a breach.
4. Skillful resource will be required for the database’s maintenance.

Graphical Representation of the Scheduling System

Names for the table and column as well as the relationship they have with other tables are shown in the table relationship diagram above. Analyzing the COURSE_TABLE and the TRAINER_TABLE reveal that there are a number of one-to-many existing relationships. Therefore, one trainer is able to teach a variety of different courses. The TRAINEE_TABLE also shows this relationship; multiple courses can be taken by a single trainee.

Data/Referential Integrity

• Referential integrity is a database concept that makes sure the relationship between the different tables stays consistent.
• It is property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).
• It is how the database handles the information saved on to it with the use of Primary Keys and Foreign Keys in a table.
• The fields in a table that shouldn’t have any duplicates and must be unique are the Primary Keys in a table. If there are two Primary Keys, there won’t be any combinations of them that will be duplicated.  In each table primary key uniquely identify each record in the field. Primary key job is to prevent entering a new record with the same primary key. Sometimes in access auto number key can be use as a primary key. The primary key must identify each record, it cannot be null, when the record is created then primary key must be there. Once the primary key is established then changing is not an option. It contains compact and few attributes as possible. The TRAINER_TABLE gives an example of this. Only one EMPLID or employee ID can be assigned to a trainer; thus, no two trainers have the same ID.
• There can be duplicates for Foreign Keys. It can also be repeated. The common field in the two tables is usually the Foreign Key. Its constraint is used to link records of one table to the records of another. When you define a foreign key constraint on a column, a column with the same name must exist as a primary key in another table. For instance, the TRAINER_TABLE and the COURSE_TABLE both have the EMPLID field in common; therefore, as long as the Primary case for the COURSE_TABLE is unique, one course can have duplicate trainers (EMPLID) that are teaching one subject.
• Referential Integrity evolves into other techniques such as Cascade delete and Cascade update.  The properties given to a column in a table are used by these techniques in order to determine which other tables would be affected if the data were to change. This is how the Referential Integrity keeps mistakes from occurring when data is recorded on to the database. The data type, key, length, value and/or its relationship to other tables are checked to see if there is consistency before adding changes to the databases. The main advantage that comes with the relationships is that it ensures data integrity. If there weren’t any relationships, users would be able to add records to child tables without being concerned about entering the required parent information. After referential integrity is established, the Cascade update related fields or Cascade Delete related records are enabled, you save a lot of code when it comes to maintaining the integrity of the data within the system. When those check boxes are enabled, you don’t have to write a single line of code in order to perform these takes when needed.

Design

Below is the table design proposed for the new process of scheduling.

Field Properties in TRAINER_TABLE

The Primary key for the table is the EMPLID field; therefore, it shouldn’t have any copies.

Field Properties in COURSE_TABLE

The Primary key for this table is the COURSE_ID field. It shouldn’t have any duplicates. The Foreign key is the EMPLID field which connects this table to the trainer’s table.

This paper includes an Access Database sample for the new scheduling process.

References

Jauhari, R., (1990), Priority scheduling in database management systems. Retrieved from EBSCOhost.

Haritsa, J. R.; Haritsa, J. R., (1991), Transaction scheduling in firm real time database systems. Retreived from EBSCOhost.

Alapati, S. R., (2008), Database. Retrieved from EBSCOhost online database.