Database Structure July, 05th 2021

Database Structure July, 05th 2021.

Satellite tables. ACP Database overview. 1|. 2|. SQL Concepts.

ACP Database overview. 1.

ACP Database overview. DBO : Dedicated for ACL core database objects and the parametrization database objects (tables, stored procedures, functions, etc.). INTER : Related to staging & interfaces database objects. DTC : Dedicated for new tables which are not already normalized (dbo). RPT : Related to reports and document generation database objects. PARAM : Related to parametrization database objects (Policy Rules, etc.)..

ACP Database overview. Schema Object type Nb objects Total objects dbo Tables 378 3117 Stored Procedures 1023 Tabular Functions 9 Scalar Functions 20 Primary Keys 374 Foreign Keys 1313 inter Tables 16 58 Stored Procedures 26 Tabular Functions 2 Scalar Functions 3 Primary Keys 11 param Tables 1 226 Stored Procedures 155 Tabular Functions 66 Scalar Functions 3 Primary Keys 1 rpt Stored Procedures 110 119 Tabular Functions 3 Scalar Functions 6 wfe Stored Procedures 62 77 Tabular Functions 9 Scalar Functions 6.

ACP Database overview. Table’s Technical columns.

Satellite tables. 2.

Satellite tables. The main tables of Axe_Credit database are: Counterparty Transaction Mitigant Project Covenant Pricing Users Parametrization.

Satellite tables. [image].

Satellite tables. Counterparty. screen 127 128 00006415 130 131 132 29545 29546 29548 29549 29551 10 29555 11 29565 33 29 3 29547 12 48 I-tigad_d 12 cou-tgparty id 6417 129 [6415 6414 6413 6412 transaction_id COJNerpMy shatnane 00006417 )0006416 00006413 00006412 réerence courtevaty Republic PLSDONOTTO„ of den cot-nterparty name2 NULL NULL PLSDONOTTOU... NULL namk coutnarty_type NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL dae NULL NULL NULL NULL NULL NULL NULL NULL SC-omg tendate_id NULL NULL 18 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Ill Hemal NULL NULL NULL NULL NULL ettty_id Hernal segmett_id 15 15 11 üt_dae co'S re"nce id screen_referrce layer das fade,' facity uyer_dds facilty COG CREDIT MEMO SCREEN trnsæjm_zne C0029547 00029551 trnsadiM id 29547 29547 29547 NULL NULL NULL 47 NULL NULL NULL mitgatOn_cuætcy NULL NULL NULL NULL NULL NULL NULL 6415 6415 6415 6415 6415 6415 6415 6415 20B024€oomooooo 201+02-06000000000 20B024600WOOOOO 2019-0206 0000 00.000 201N2ÆOOOO:OOOOO MI q.m.lnrnmmrm nngäionrtettage migatOn amot-nt NULL NULL NULL NULL NULL NULL NULL NULL 25000000 20.000000 NULL NULL NULL 20000000 NULL NULL NULL NULL NULL NULL NULL NULL Nil' I release dae NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ntig&lt shortname screen 00000012 start dae NULL NULL NULL 2019-01-27 0000000) date id NULL NULL sequetce_id 195 iuser NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NIJLL NULL NULL NULL liqLi&y NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL erZ_dae NULL NULL NULL NULL NULL NULL NULL date NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL Caya Project transaction type_id 396 451 397 2018-1721 wmooooo NULL NULL NULL 2013009 coo NULL NULL NULL NULL NULL tar-goo NULL NULL NULL NULL NULL regulatory ntigmt_type NULL NULL 10 NULL NULL.

Satellite tables (counterparty tables). [image].

Satellite tables (counterparty tables). Table Name ACP Tab Name Definition and Usage Counterparty Customer Identification (BIR) Main Customer Information (CIC, Name, RM, etc.) Counterparty_address Customer Address All Address types of the Customer Counterparty_contacts Management & Shareholders Stores contacts relationship with the Customer (Shareholders, Contacts, Management, BoD, etc.) Counterparty_internal_rating MRA - Rating Stores MRA Rating with appropriate dates and status Financial_analysis MRA - Financial Data Stores Customer financials (Audited, In-house, Projected) Counterparty_parent Group Structure Stores the Relationship between the customer and different entities (Subsidiary, Affiliate, etc.) Counterparty_generic Buyer / Supplier Stores data related to extra data for a Customer. E.g. Buyer & Supplier information..

counterp arty_id axnerpatty_shortname 00000001 saeen _reference corporate corporate corporate cmdepatty_n ame2 CO rating _date _templat 18 18 18 rating—hod comment _entüy_id NULL NULL NULL 2 3 a 6 7 *eer co Euhg_session _id NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL counterp arty_irtemdßing _id 1001 1002 1004 1007 1008 courterpaty_id rating_d 74 74 1010 1011 1011 1010 2018-11-01 ocoo:oo.ooo 2018-11-300&0000000 2018-11-300&0000.000 2018-11-1400:ocoo.ooo 2018-11-19 ocoo:ocooo 3. 2. Comment I dghadgh Descnption Description This is the fnal ORR NULL rtemd_segment _id 11 status_dd NULL NULL NULL NULL NULL NULL NULL.

Satellite tables (Transaction tables). [image].

Satellite tables (Transaction tables). Table Name ACP Tab Name Definition and Usage Transaction Main details Contains general information about the CA Transaction_condition Pricing & Total Limit amount Contains information about the limit pricing Transaction_generic Cashflow Projection, SIMAH, etc. Contains extra data related to the transaction Transaction_action Call Memo Defines information about customer meetings Transaction_covenant CCRT Contains the relationship between the CA and Covenant Transaction_comment Write ups Contains the write ups related to the CA Transaction_Hierarchy Limit Structure Contains the relationship between all facility layers (Category, Sub Category, etc. & facilities).

transäjcn_id i 330 transutjm_d | 330 330 transaction_nne _id 15 ca_cb9 NULL NULL NULL NULL NULL NULL NULL mmawrrt_fee NULL NULL use dart_date use NULL _pencd NULL NULL udate nd_dde NULL NULL NULL net _vew_dde NULL NULL camM NULL NULL NULL adian date NULL NULL trans.:tjcn actan id 71 72 45 54 transztjm_id 330 55 NULL trrudøn_id 330 330 NULL NULL NULL CC Snuy cf rri-utes cf r, sss 1074 NULL NULL NULL NULL NULL 2018-12-31 034218703 2018-1&31 235037833 duser NULL NULL NULL NULL NULL ddäe NULL NULL NULL "dress CC Folc.wi-,g ard trmsadOn_id covewtjd uu_id NULL NULL NULL opeäcr NULL NULL NULL curency_id NULL NULL NULL 123456 covenM_vaLe NULL NULL NULL review _freqæncy Every Ca-tjrur,; net _date mechariyne use 16 16 16 dae use 330 330 330 NULL NULL NULL 16 2018-12-1S02EUS5C IS 2018-12-1SUU02.560 NULL uue 2018-12 2018-12 NULL con•gnent4 comrrert7 relatn±ip for pr*cted Pefcqnarce.

Satellite tables (Mitigant tables). [image].

Satellite tables (Mitigant tables). Table Name ACP Tab Name Definition and Usage Mitigant Main Data Stores the general information about the collateral Mitigant_transaction Collateral Allocation defines the many to many relation between the Facilities and collaterals with the appropriate link type Mitigant_valuation Collateral Valuation Contains the different valuations made on the collateral Mitigant_participant Collateral Participants Defines the owner and the participants of the collateral Mitigant_address Address Contains the address(es) of the collateral.

Satellite tables (User tables).

Satellite tables (User tables). Table Name ACP Tab Name Definition and Usage Software_user User Management Defines all users in ACL. User_group Group Stores the related group(s) for each user. User_profile Profile Stores the related profile(s) for each user. Profile_access_right Defines the different access rights for each profile..

Satellite tables (Parameterzation tables). Table Name ACP Menu Name Definition and Usage Constant_matrix Matrices Contains all the matrices of the client (DOA Matrice, CBG User Mapping, Policy Rules, etc.) Static_data_table N/A All dropdown lists. (DoA levels, Pricing Types, etc.) Transaction_type Product Catalogue Defines the facility products of the bank. Counterparty_type Legal Structure Contains different customer types. Country Countries Stores all the countries. Currency Currency All currencies used by the bank. group Groups Defines all groups used in ACP. profile Profiles Defined profiles in ACP. Internal_entity Branches & Regions Contains different branches / Region & Business Teams of the bank. Internal_segment Segments Contains the different business segment (CBG, Large Corp, Mid-Corp, FI, SME, etc.).

SQL Concepts. 3.

SQL Concepts. Selecting data Filtering data Ordering data.

SQL Concepts (Selecting data). The select statement is used to query the database and retrieve selected data. Here is the format of a simple select statement: SELECT "column1" [,"column2",etc] FROM "schema.tablename" The column names that follows the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns. The schema name and table name that follows the keyword from specifies the table that will be queried to retrieve the desired results. SELECT counterparty_id FROM dbo.counterparty SELECT counterparty_id, counterparty_name, phone FROM dbo.counterparty SELECT * FROM [dbo].[counterparty].

SQL Concepts (Filtering data). The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where. SELECT * FROM "schema.tablename" WHERE “condition1" [AND|OR “Condition2",etc] Conditional selections used in the where clause:.

SQL Concepts (Filtering data). The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where. SELECT * FROM "schema.tablename" WHERE “condition1" [AND|OR “Condition2",etc] Conditional selections used in the where clause:.

SQL Concepts (Filtering data). The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where. SELECT * FROM "schema.tablename" WHERE “condition1" [AND|OR “Condition2",etc] Conditional selections used in the where clause:.

SQL Concepts (Ordering data). ORDER BY is an optional clause that allows display the results of a query in a sorted order (either ascending order or descending order) based on the columns specified to order with. ORDER BY clause syntax: SELECT * FROM "tablename" ORDER BY "column1" [ASC|DESC], "column2" [ASC|DESC] [ ] = optional ASC = Ascending Order – default DESC = Descending Order SELECT * FROM dbo.static_data_table ORDER BY table_name SELECT * FROM dbo.counterparty_address ORDER BY default_address DESC, counterparty_id ASC.

SQL Concepts (Eliminating duplicates). The distinct keyword is used with Select statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving from database. SELECT DISTINCT ColumnName FROM "tablename" SELECT DISTINCT counterparty_screen FROM dbo.counterparty SELECT DISTINCT table_name, name FROM dbo.static_data_table SELECT DISTINCT * FROM dbo.constant_matrix.

SQL Concepts (Selecting number of records). The SELECT TOP clause is used to specify the number of records to return. When you use TOP in the query, the result set is limited to the first N number of rows. SELECT TOP(N)|TOP N PERCENT ColumnName FROM "tablename" SELECT TOP(3) user_name FROM dbo.software_user SELECT TOP 20 PERCENT * FROM dbo.internal_segment SELECT TOP(5) * FROM dbo.transaction_condition WHERE amortization_frequency = 'Semi Annual' ORDER BY notional_amount DESC.

SQL Concepts (Selecting number of records). The OFFSET and FETCH clauses are the options of the ORDER BY clause. They allow you to limit the number of rows to be returned by a query. The following illustrates the syntax of the OFFSET and FETCH clauses: SELECT * FROM "ColumnName" ORDER BY "ColumnName" OFFSET N ROWS [FETCH NEXT M ROWS ONLY] SELECT internal_entity_id, internal_entity_name FROM dbo.internal_entity ORDER BY internal_entity_id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY SELECT internal_entity_id, internal_entity_name FROM dbo.internal_entity ORDER BY internal_entity_id OFFSET 20 ROWS FETCH NEXT 30 ROWS ONLY.

SQL Concepts (Joining tables). A JOIN clause is used to combine rows from two or more tables, based on a related column between them. The following are the types of JOIN that we can use in SQL: Cross Inner Outer Left Outer Join Right Outer Join Full Outer Join.

SQL Concepts (Joining tables). This type of JOIN returns the Cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. SELECT * FROM "tableName1" CROSS JOIN "tableName2" SELECT * FROM dbo.counterparty CROSS JOIN dbo.group.

SQL Concepts (Joining tables). This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query. SELECT * FROM "T1" INNER JOIN "T2" ON T1.column = T2.Column SELECT transaction_id, transaction_type_name FROM dbo.[transaction] INNER JOIN dbo.transaction_type ON transaction_type.transaction_type_id = [transaction].transaction_type_id WHERE transaction_id IN (29850, 28618, 29484).

SQL Concepts (Joining tables). The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table's column. SELECT * FROM "T1" LEFT OUTER JOIN "T2" ON T1.column = T2.Column.

SQL Concepts (Joining tables).

SQL Concepts (Aggregate functions). Aggregate functions are used to compute against a "returned column of numeric data" from the SELECT statement. They basically summarize the results of a particular column of selected data. Although they are required for the "GROUP BY" clause, these functions can be used without the "GROUP BY" clause. The SQL syntax to use aggregation function is: SELECT AGGREGATION_FUNCTION("ColumnName") * FROM "T1" [ORDER BY ColumnName] The aggregation functions are: MIN: returns the smallest value in a given column MAX: returns the largest value in a given column SUM: returns the sum of the numeric values in a given column AVG: returns the average value of a given column COUNT: returns the total number of values in a given column.

SQL Concepts (Aggregate functions). SELECT COUNT(counterparty_id) FROM dbo.counterparty SELECT AVG(notional_amount) FROM dbo.transaction_condition SELECT COUNT(transaction_id), transaction_type_id FROM dbo.[transaction] GROUP BY transaction_type_id SELECT AVG(notional_amount) FROM dbo.transaction_condition SELECT SUM(mitigation_amount) FROM dbo.mitigant_transaction WHERE transaction_id = 114.

SQL Concepts (SQL order of execution). ORDER 1 2 3 4 5 6 7 CLAUSE from where group by having select order by limit FUNCTION Choose and join tables to get base data. Filters the base data. Aggregates the base data. Filters the aggregated data. Returns the final data. Sorts the final data. Limits the returned data to a row count..

SQL Concepts (IUD Queries). The data modification clauses in SQLite are INSERT, UPDATE, and DELETE statements. It is used respectively for inserting new rows, updating existing values, or deleting rows from the database. The syntaxes for these 3 statements are as follow: INSERT INTO Schema.TableName (ColumnName1[, ColumnName2]) VALUES (Value1[, Value2]) UPDATE "Schema.TableName" SET "ColumnName" = "NewValue" [WHERE …] DELETE FROM "Schema.TableName" [WHERE …].

SQL Concepts (IUD Queries). INSERT INTO dbo.status (status_shortname, status_name) VALUES (‘UTI’,’Utilized’) UPDATE dbo.country SET country_name = ‘Algeria’ WHERE country_shortname = ‘DZA’ AND ddate IS NULL and duser IS NULL DELETE FROM dbo.user_profile WHERE profile_id = 4 For ACP, the deletion of data is done in a logical way, meaning that we modify the idate and iuser columns instead of doing a DELETE query: DELETE FROM user_profile WHERE profile_id = 4 UPDATE user_profile SET idate = GETDATE() AND iuser = 1 WHERE profile_id = 4.

Practice. 4.

Practice. Exercise 1 : Retrieve all customers of the bank Exercise 2 : Retrieve all third parties from table counterparty Exercise 3 : Retrieve possible values of counterparty_screen Exercise 4 : Get all countries ending with letter ‘N’ Exercise 5 : Select status which begins with the term ‘CAMAC’ Exercise 6 : Select Name of customers belonging to Large Corp Exercise 7 : Get all contact information for ACP Customer Ref “6496” Exercise 8 : Select name and main address of each CBG customer Exercise 9 : Update Collateral Ref “62” with valuation amount equal to 200000 Exercise 10 : Insert the group ID “9” for ACP User ID “20” Exercise 11 : Delete all the write ups related to the CA Ref “30777”.

Thank You. Focus VCIIIJC.