Assignment 4 : Trigger Solution

1. Introduction
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, 007 means the resident is male, while 666 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.INSERT INTO people values (ID, Name);
ID and name are string. ID is the ID number. name is the name of user.
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 doesn't 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
select string_agg(x.name, ',')
from (
select name
from district
where code in ('110000', '110100', '110101')
order by code) x;
4. What to submit?
You need to submit two files:people_trigger.sql : it creates trigger for given database.
valid_check.sql : it defines the function for trigger.
5. Rules
1. This assignment is based on PostgreSQL.
2. The file name of procedure should be 'valid_check.sql', the file name of trigger should be
'people_trigger.sql' and the name of table for trigger must be 'people'.
3. You need to make sure that if we execute those files in this order 'valid_check.sql',
'people_trigger.sql', valid data can be imported successfully.
4. You need to make sure that there is only one function in valid_check.sql
5. Your insert results of trigger must be strictly followed the description and the data in result.
6. Submit those two .sql files are enough, do not compress them into one document.
7. Please submit those queries into sakai website as soon as possible, so that you can get
chance to receive feedback before deadline. After the deadline, we will check the assignment
automatically by a script and then given your grade, at that time, any argument about your
grade of this assignment will not be accepted.