Data modeling Projects list

 ========================================== 

1. Pharma client

========================================== 

• Modeling tool : Erwin 2021 R1 
  
  
•  Source & Target : 
• Source : Excel , flat files  
• Target : Snowflake
  
  
• Requirement : 

1. Create Data model for new entities and
2. create data mart for each of the departments

• Steps followed :  

1. Create STTM document 
2. Update confluence document about the new changes to be made like list of Tables and updated columns
3. Create Logical / Physical model 
4. Create PDF of the models and upload into sharepoint 

 ========================================== 

2. Food and restaurant business client

========================================== 

• Modeling tool : Erwin 2021 R1 
  
  
•  Source & Target : 
• Source : Excel , flat files  , CSV, TSV ,xml 
• Target : Snowflake
  
  
• Requirement : 
1. This restaurant has bought several new outlets in each country but the database of each new outlet is not similar the the database of JFC , 
2. so the client wants to take the database of NA as the standard and convert each country of South america, japan , malasia , singapore, indonesia to be similar to that of NA 
   
   
• Steps followed :  
1. Create STTM document for NA 
2. get sample data files from each country and do a due deligence investigation and study  the differences between DB of NA and each country and discuss with the POC of each country DB architect and propose solution 
3. Try to find out the new tables and columns in each country and identify similar tables in NA and propose solution 

========================================== 

3. Banking client

========================================== 

• Modeling tool : IBM IDA 
  
  
•  Source & Target : 
• Source : Excel , flat files  , CSV, TSV ,xml 
• Target : DB2
  
  
• Requirement : 
1. During the COVID season there were a number of Medical claims from the Bank customers
2. So we worked on a data model of the DB of all COVID pateients tables and thier total claims
3. 
   
• Steps followed :  
1. Create STTM document for  all the COVID claims related tables 
2. Design the grain of the reports 
3. identify the facts and dimensions from each of the tables
4. Design a data model 

SQL DATA TYPES AND SIZES

  

 

DATATYPES AND SIZES	Meaning
VARCHAR2(65535)	Means you can save 200 characters in the column
	The maximum length for a VARCHAR2 column is 32,767 bytes or 8,168 characters. However, the maximum length can be increased to 4,000 bytes if
varchar2	VARCHAR2 field is 65,535 characters
The NVARCHAR(MAX) type, which can store more than 2 GB of data in traditional SQL Server
Decimal(5,2)	means you can save an amount of 7 digits ( 5 digits before dot and 2 after dot Decimal(64,2) is max
INT	-2,147,483,648 to 2,147,483,647
	whether you give int or int(4) are sameQ
STRING    (8000)	Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB)

DATATYPE Example Meaning Min max values Size in bytes bigint bigint   -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8 bytes int int stores values > -2,147,483,648 to 2,147,483,647 4 bytes smallint smallint   -32,768 to 32,767 2 bytes tinyint tinyint   0 to 255 1 byte