[Solved] CS307 Assignment4-Trigger

Each resident in mainland China has a unique 18-digit identity card number. When you play online game like Honor of Kings, you need to enter your ID and name when you register. The game decides how many hours user can play the game each game. However, some teenagers may enter fake ID number trying to cheat the system so he/ she can play the game all day long. Suppose you are the database administrator of Honor of Kings, you are required to write a trigger to prevent illegal ID number insert to the game database.

2. How does the China ID Card Citizen Identity Number work?

The first 6 digits is the address code which refers to a specific administrative division. It indicates where the resident was born. GB/T 2260 (a national standard) defines address codes for all administrative divisions in China. We also provide you a table _district which can help you check whether the address code in the ID number is valid or not.

The 7-14 digits is the birthday of the resident. The format is _YYYYMMDD . For example, if you was born in August 17th, 1926. The code for birthday is _19260817 . In this assignment, we assume all residents were born on or after January 1th, 1900.

The 15-17 digits is the order number. Odd number indicates the resident is male. Even number indicates the resident is female. For example, ₀₀₇ means the resident is male, while ₆₆₆ means the resident is female.

The last digit is the checksum.

Here is the algorithm for checksum (ISO 7064:1983.MOD 11-2).

3. What you need to do?

Given the database which contains two tables _district and _people ,you need to write a trigger and a function. The trigger is triggered before the data is inserted into table _people . The function for trigger check whether the ID number is valid or not.

The format of insertion statement is shown as follow.

If the ID number is not valid. The function should throw the exception and stop the insertion.

If the ID number is valid. The new inserted row need the value of _birthday and _address which are according to the ID number.

The format of _address : [province],[city],[district]

If any of province, city or district doesnt exists, you can ignore it.

The format of _birthday : substring(ID, 7, 8)

Several successful test cases:

insert into people (id, name) values (110101200402232714, name1); insert into people (id, name) values (440303210005112718, name2); insert into people (id, name) values (440300199003162929, name3); insert into people (id, name) values (540000199905203221, name3); Result:

You can try the function: string_agg