I have two things I need to do but I'm not sure how. These problems come up in my EVALUATION_RESULTS table. The first thing I need to do is create a formula for the primary key EVALUATION_ID. I need to do this so that it is the EMPLOYEE_NUMBER followed by EMPLOYER_NUMBER followed by EVALUATION_ID. For instance, if the employee being evaluated has the number 10000, the employer has the number 1000, and the evaluation ID is 100, the evaluation number should automatically be 100001000100. How can I have this be created automatically after the user completes a form?Doing this is committing the error of overloading. This is where a single attribute contains information for more than one attribute. From a relational design point of view, the first thing you should be identifying from your list of attributes is the natural keys. In your case it may simply be the original evaluation id (100). If this is not unique, for example more than one employee or employer may share an evaluation id, then the natural key may be a combination of some or all of the attributes you have noted. In this case the natural key would be a combination of those attributes in what is called a composite key. From an implementation point of view, it may be quite acceptable to then create your Primary Key as a combination of those columns in a composite PK. Another implementation issue is that composite PK's can become unweildy to deal with, especially if they have to be referred to in different entities as Foreign Keys. In this case, you can change the composite PK to a Unique Key and create a single part PK which will be a number that is generated from a database object called a sequence. This is generally called an artificial key. If for some business reason you need to refer your original PK in the format you suggested, as in reports or business receipts, then is is a simple exercise to retrieve it from the database as you require it.
The second issue is: I need to create a function in the EVALUATION_RESULTS table that takes an average of four scores that are input in the form. So it will take (SCORE1+SCORE2+SCORE3+SCORE4)/4. How can I implement this so that the user enters the four scores, submits the form, and the AVERAGE_SCORE is added to the table.Another principle of relational design is that you don't store anything in the database that can be derived from the existing data. Things like your average score should be calculated on the fly when ever your data is presented to the user in forms or reports.