$2$ Supporting Advanced Constraints

The requirement analysis also revealed certain behaviors and integrity the database must uphold when deployed. For this, you are required to create appropriate views, functions and procedures, and triggers that allow the database to behave as expected.

$2\mathrm{.}1$ Modifications to Student Table

When a new student is registered, a unique student$\_$id is assigned to each student. A student$\_$id is a $10-$digit unique code, with the first four digits being entry$\_$year, the next three characters are dept$\_$id$,$ and the last three digits are seq$\_$number. When a new student is registered, your schema must validate this entry$\_$number with the below conditions:

The entry$\_$year and dept$\_$id in student$\_$id should be a valid entry in valid$\_$entry table.

The sequence number should start from $001$ for each department $($maintained in valid$\_$ent ry table$).$ Thus, the current sequence number is assigned when a new student is registered in a department.

Create a trigger with the name of validate$\_$student$\_$id to validate the student$\_$id$.$ If the entry$\_$number assigned to a student is not valid, then raise an "invalid" message; else, successfully insert the tuple in the table.

Example: Below is an instance of the valid$\_$entry and student table.

$\backslash$table$[[$dept$\_$id$,$entry$\_$year,seq$\_$number$],[$CSZ$,2020,1],[$CSY$,2024,3]]$

Table $16$: Entry in valid$\_$entry table

$\backslash$table$[[$first$\_$name,last$\_$name,student$\_$id$,$address,contact$\_$number,email$\_$id$,$tot$\_$credits,dept$\_$id$],[,,2024$CSY$001,,$XXXXXXXXXX$,"2024$CSY$001$@CSY$.$iitd.ac$.$in$,"0,$CSY$],[,,2024$CSY$002,,,2024$CSY$002$@CSY$.$iitd.ac$.$in$,10,$CSY$]]$

Table $17$: Valid student table

Below are the valid $18$ and invalid $19$ tuples for the student table. The correct entry should be inserted in the table, while the invalid entry should return the "invalid" message.

$5$

$\backslash$table$[[$first$\_$name,last$\_$name,student$\_$id$,$address,contact$\_$number,email$\_$id$,$tot$\_$credits,dept$\_$id$],[$XXxx$,$XXxx$,2020$CSZ$001,$XXXX$,$XXXXXXXXX$,2020$CSZ$001$@CSZ$.$iitd.ac$.$in$,0,$CSZ$]]$

Table $18$: Valid entry in student table

$\backslash$table$[[$first$\_$name,last$\_$name,student$\_$id$,$address,contact$\_$number,email$\_$id$,$tot$\_$credits,dept$\_$id$],[$xxxx$,$xxxx$,2021$CSZ$001,$xxxx$,$xxxxxxxxxx$,2020$CSZ$001$@CSZ$.$iitd.ac$.$in$,0,$CSZ$],[$xxxx$,$xxxx$,2020$CSZ$002,$xxxx$,$xxxxxxxxxx$,2020$CSZ$001$@CSZ$.$iitd.ac$.$in$,0,$CSZ$],[$xxxx$,$xxxx$,2020$CS$001,$xxxx$,$xxxxxxxxxx$,2020$CSZ$001$@CSZ$.$iit.ac$.$in$,0,$CSZ$],[$xxxx$,$xxxx$,2020$CSY$005,$xxxx$,$xxxxxxxxxx$,2020$CSZ$001$@CSZ$.$iitd.ac$.$in$,0,$CSZ$]]$

Table $19$: Invalid entry in student table $[$incorrect student$\_$id$]$

$2.$ If the above student$\_$id is a valid id$,$ you add that student detail in the student table. But do not forget to increase the counter, i$.$e$.,$ seq$\_$number in valid$\_$entry table after each insert in the student table. Thus, create a trigger with the name, update$\_$seq$\_$number, which will update the seq$\_$number in valid$\_$entry table.

Example: Once the valid student is inserted in the student table as shown in there should be an update in valid$\_$entry table. The correct update based on the above$-$given instance for valid$\_$entry table is shown below:

$\backslash$table$[[$dept$\_$id$,$entry$\_$year,seq$\_$number$],[$CSZ$,2020,2],[$CSY$,2024,3]]$

Table $20$: Valid update in valid$\_$entry table