The current authentication and verification process for CTPL COCs uses a centralized database system called COCAS (COC Authentication System). COCAS was developed by SQL Wizard and is owned by the Philippine Insurers and Reinsurers Association (PIRA).
When CTPL insurance is sold to the registrant, the insurance company encodes insurance and vehicle data into COCAS using the Internet. The registrant then passes through a verification step at a station (handled by DTECH Management Inc) located in LTO District Office premises, where the authenticity of the COC is verified.
At this stage, a DTECH representative connects (using the internet or SMS) to COCAS, encodes COC and vehicle information, and affixes a sticker on the COC if verification is received.
The registrant then submits documents to the LTO evaluator, where the evaluator checks whether the COC has indeed been verified by ensuring that a sticker is present, before proceeding with the registration transaction.
The insurance firms eventually declare the number of COCs they have issued to the IC and pay the corresponding taxes to the BIR.
Consider the Insurance database given below. The primary keys are underlined and the data types are specified:
PERSON( driver-id :string,name:string,address:string)
CAR( Regno :string,model:string,year:int)
ACCIDENT( report-number :int,date:date,location:string)
OWNS( driver-id :string,regno:string)
PARTICIPATED( driver-id :string, regno :string, report-number :int,damage-amount:int)
1) create the above tables by properly specifying the primary keys and the foreign keys
2) Enter atleast five tuples for each relation
3) Demonstrate how you
a) update the damage amount for the car with a specific regno in accident with report number 12 to 25000
b) add a new accident to the database
4) Find the total number of people who owned cars that were involved in accidents in 2002.
5) Find the number of accidents in which cars belonging to a specific model were Involved.
6) Generation of suitable reports
7) Create suitable front end for querying and display the results