Requirement
I searched the internet for possible information/properties for countries and their currencies and Languages and their Culture info for developers/programmers, and got multiple sources and tables, then I began linking them to make a much more informative table, so it came out with the following fields, and created a simple application to show them, using .Net 4.5 + SQL server localDB 2012:
This article can be seen in CodeProject
http://www.codeproject.com/Articles/1030981/Countries-currencies-Languages-Culture-Info-for-De
Download
The database script and the .Net application with the database is in this link:
http://1drv.ms/1FNPqyP
The Excel sheet for the collected Languages and Culture Info:
Languages b 2015-09-22
The Excel sheet for the collected Countries and currencies Info:
Nationality Country & Dialing Codes b – 2015-09-22
The Excel sheet for the collected Nationality Country & Dialing Codes:
Nationality Country & Dialing Codes – 2015-09-16
Requirments
Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express:
http://www.microsoft.com/en-sa/download/details.aspx?id=43351
Language Columns:
| # | Field | Description | عربي | Size | Current maximum data size |
|---|---|---|---|---|---|
| 0 | # | Language number | رقم اللغة | Integer | Integer |
| 1 | orderNo | Ordering number | ترتيب | Integer | Integer |
| 2 | eName | Language English name | الاسم عربي | nvarchar(100) | 51 |
| 3 | aName | Language Arabic name | الاسم انجليزي | varchar(100) | 58 |
| 4 | NativeName | Language native name | الاسم بنفس كتابة اللغة | nvarchar(150) | 57 |
| 5 | LanguageFamily | Language family name | عائلة اللغة | varchar(100) | 19 |
| 6 | code_639_1 | ISO 639 1 | رقم ثقافة اللغة بالايسو | varchar(2) | 2 |
| 7 | code_639_2_T | ISO 639 2 T | varchar(3) | 3 | |
| 8 | code_639_2_B | ISO 639 2 B | varchar(3) | 3 | |
| 9 | code_639_3 | ISO 639 3 | varchar(16) | 8 | |
| 10 | code_639_6 | ISO 639 6 | varchar(6) | 4 | |
| 11 | Notes | ملاحظة | nvarchar(150) | 75 |
.
.
Language Culture Columns:
| # | Field | Description | عربي | Size | Current maximum data size |
|---|---|---|---|---|---|
| 0 | languageCultureNo | language Culture No | رقم ثقافة اللغة | Integer | |
| 1 | code_639_1 | ISO 639 1 | رقم ثقافة اللغة بالايسو | Integer | Integer |
| 2 | LCID_CultureIdentifier | LCID Culture Identifier | رقم ثقافة اللغة الحرفي | varchar(6) | 6 |
| 3 | cultureName | Language Culture Name | ثقافة اللغة | varchar(16) | 11 |
| 4 | locale_Language_Country_Region | locale Language Country Region | اسم اللغة مع المنطقة او الدولة | nvarchar(150) | 50 |
| 5 | eLanguageName | english Language Name | اسم اللغة انجليزي | varchar(150) | 33 |
| 6 | aLanguageName | arabic Language Name | اسم اللغة عربي | nvarchar(150) | 48 |
| 7 | nativeName | Language Name in its native form | اسم اللغة بكتابتها | nvarchar(150) | 50 |
| 8 | ANSI_codepage | ANSI code page | كود انسي | Integer | Integer |
| 9 | OEM_codepage | OEM code page | كود او اي ام | Integer | Integer |
| 10 | countryRegion_abbreviation | country Region abbreviation | مختصر الدولة او المنطقة | varchar(3) | 3 |
| 11 | languageName_abbreviation | language Name abbreviation | مختصر الاللغة | varchar(3) | 3 |
.
.
This will help in giving nationality / country / currency for applications.
Country & Currency Table fields:
| # | Field | Description | عربي | Size | Current Data max size |
|---|---|---|---|---|---|
| 0 | ISO3166_1_numeric | Numeric codes from ISO 3166-1 (synonymous with UN Statistics M49 Codes) | رقم أيزو الخاص بالدولة | Integer | Integer |
| 1 | eName | Country’s official English short name | الاسم انجليزي | varchar(150) | 44 |
| 2 | aName | Country’s official Arabic short name | الاسم عربي | nvarchar(150) | 36 |
| 3 | frName | Country’s offical French short name | الاسم فرنسي | nvarchar(150) | 46 |
| 4 | ISO3166-1-Alpha-2 | Alpha-2 codes from ISO 3166-1 | ايزو حرفين | varchar(2) | 2 |
| 5 | ISO3166-1-Alpha-3 | Alpha-3 codes from ISO 3166-1 (synonymous with World Bank Codes) | ايزو ثلاث حروف | varchar(3) | 3 |
| 6 | ITU | Codes assigned by the International Telecommunications Union | رمز الاتحاد الدولي للاتصالات | varchar(3) | 3 |
| 7 | MARC | MAchine-Readable Cataloging codes from the Library of Congress | رمز مكتبة الكونغرس | varchar(15) | 14 |
| 8 | WMO | Country abbreviations by the World Meteorological Organization | رمز البلاد من قبل المنظمة العالمية للأرصاد الجوية | varchar(2) | 2 |
| 9 | DS | Distinguishing signs of vehicles in international traffic | العلامات المميزة للمركبات في حركة المرور الدولي | varchar(3) | 3 |
| 10 | dialingCode | Country code from ITU-T recommendation E.164, sometimes followed by area code | رمز البلد من ITU-T توصية E.164، وجاء في بعض الأحيان رمز المنطقة | varchar(30) | 17 |
| 11 | FIFA | Codes assigned by the Fédération Internationale de Football Association | رموز فيفا | varchar(30) | 15 |
| 12 | FIPS | Codes from the U.S. standard FIPS PUB 10-4 | رموز من PUB FIPS القياسية الولايات المتحدة | varchar(50) | 26 |
| 13 | GAUL | Global Administrative Unit Layers from the Food and Agriculture Organization | طبقات الوحدة الإدارية العالمية من منظمة الأغذية والزراعة | Integer | Integer |
| 14 | IOC | Codes assigned by the International Olympics Committee | رمز اللجنة الأولمبية الدولية | varchar(3) | 3 |
| 15 | currency_numeric_code | ISO 4217 currency numeric code | ISO 4217 العملة رمز رقمي | Integer | 3 |
| 16 | currency_alphabetic_code | ISO 4217 currency alphabetic code | ISO 4217 العملة رمز أبجدي | varchar(3) | 3 |
| 17 | currency_minor_unit | ISO 4217 currency number of minor units | ISO 4217 عدد العملات وحدات صغيرة | Integer | Integer |
| 18 | currency_EName | ISO 4217 currency english name | ISO 4217 اسم العملة | varchar(150) | 29 |
| 19 | currency_AName | currency Arabic name | اسم العملة بالعربي | nvarchar(150) | 29 |
| 20 | currency_ESign | currency english sign | رمز العملة بالانجليزي | varchar(6) | 3 |
| 21 | currency_ASign | currency arabic sign | رمز العملة بالعربي | nvarchar(6) | 3 |
| 22 | capital_CityNo | Capital City number | رقم العاصمة | Integer | Integer |
| 23 | GPSPoint_Latitude | GPS Latitude | خط العرض | varchar(16) | 7 |
| 24 | GPSPoint_Longitude | GPS Longitude | خط الطول | varchar(16) | 7 |
| 25 | exchangeRateToBaseCurrency | exchange Rate To Base Currency | قيمة التحويل من العملة الحالية إلى عملة البرنامج | Decimal(18,6) | Decimal |
| 26 | decimalPlaces | number of decimals after the point | عدد الخانات العشرية | smallint | 1 |
| 27 | decimalSeparator | the character used for specifing the decimal | الرمز المستخدم للتفرقة بين الرقم العشري والرقم الصحيح | nvarchar(5) | 1 |
| 28 | thousandsSeparator | the character used for specifing the thousand seperator | الرمز المستخدم للتفرقة بين الألاف | nvarchar(5) | 1 |
| 29 | CR_CurrencyPositionType | Later | smallint | 1 | |
| 30 | CR_NegativeType | Later | smallint | 1 |
.
Fields for the Country Capital Table:
| # | Field | Description | عربي | Size |
|---|---|---|---|---|
| 0 | CountryCityNo | city number | رقم المدينة | Integer |
| 1 | CountryNo | Numeric codes from ISO 3166-1 (synonymous with UN Statistics M49 Codes) | رقم أيزو الخاص بالدولة | Integer |
| 2 | eName | City official English short name | الاسم انجليزي | varchar(150) |
| 3 | aName | City official Arabic short name | الاسم عربي | nvarchar(150) |
| 4 | GPSPoint_Latitude | GPS Latitude | خط العرض | varchar(16) |
| 5 | GPSPoint_Longitude | GPS Longitude | خط الطول | varchar(16) |
.
The Sql Code for creating the tables:
/****** Object: Table [dbo].[Country] Script Date: 9/17/2015 1:06:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Country](
[countryNo] [dbo].[AddressNationality_Country_Currency_Dom] NOT NULL,
[eName] [dbo].[eName_150_sDom] NOT NULL,
[aName] [dbo].[aName_150_sDom] NOT NULL,
[frName] [dbo].[aName_150_sDom] NULL,
[ISO3166_1_Alpha_2] [dbo].[TypeChar02_Dom] NULL,
[ISO3166_1_Alpha_3] [dbo].[TypeChar03_Dom] NULL,
[ITU] [dbo].[TypeChar03_Dom] NULL,
[MARC] [dbo].[TypeVarChar15_Dom] NULL,
[WMO] [dbo].[TypeChar02_Dom] NULL,
[DS] [dbo].[TypeChar03_Dom] NULL,
[dialingCode] [dbo].[TypeVarChar30_Dom] NOT NULL,
[FIFA] [dbo].[TypeVarChar30_Dom] NULL,
[FIPS] [dbo].[TypeVarChar50_Dom] NULL,
[GAUL] [dbo].[TypeIntegerShort_Dom] NULL,
[IOC] [dbo].[TypeChar03_Dom] NULL,
[currency_numeric_code] [dbo].[AddressNationality_Country_Currency_Dom] NULL,
[currency_alphabetic_code] [dbo].[TypeChar03_Dom] NULL,
[currency_minor_unit] [dbo].[TypeIntegerShort_Dom] NULL,
[currency_EName] [dbo].[eName_150_sDom] NULL,
[currency_AName] [dbo].[aName_150_sDom] NULL,
[currency_ESign] [dbo].[TypeVarChar06_Dom] NULL,
[currency_ASign] [dbo].[TypeVarChar06_Dom] NULL,
[capital_CityNo] [dbo].[AddressCityNo_Dom] NULL,
[GPSPoint_Latitude] [dbo].[TypeVarChar16_Dom] NULL,
[GPSPoint_Longitude] [dbo].[TypeVarChar16_Dom] NULL,
[exchangeRateToBaseCurrency] [dbo].[ExchangeRate_Dom] NULL,
[decimalPlaces] [dbo].[TypeIntegerShort_Dom] NULL,
[decimalSeparator] [dbo].[TypeVarChar06_Dom] NULL,
[thousandsSeparator] [dbo].[TypeVarChar06_Dom] NULL,
[CR_CurrencyPositionType] [dbo].[TypeIntegerShort_Dom] NULL,
[CR_NegativeType] [dbo].[TypeIntegerShort_Dom] NULL,
[accountNo] [dbo].[TreeAccountNo_aDom] NULL,
[row_timestamp] [timestamp] NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[countryNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’
عملتها هي في حقل:
currenyCode1′ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’Country’
GO
/****** Object: Table [dbo].[CountryCity] Script Date: 9/17/2015 1:08:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CountryCity](
[countryCityNo] [dbo].[AddressCityNo_Dom] NOT NULL,
[countryNo] [dbo].[AddressNationality_Country_Currency_Dom] NOT NULL,
[eName] [dbo].[eName_150_sDom] NOT NULL,
[aName] [dbo].[aName_150_sDom] NOT NULL,
[GPSPoint_Latitude] [dbo].[TypeVarChar16_Dom] NULL,
[GPSPoint_Longitude] [dbo].[TypeVarChar16_Dom] NULL,
[row_timestamp] [timestamp] NULL,
CONSTRAINT [PK_CountryCity] PRIMARY KEY CLUSTERED
(
[countryCityNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CountryCity] WITH CHECK ADD CONSTRAINT [FK_CountryCity_R_484_Country] FOREIGN KEY([countryNo])
REFERENCES [dbo].[Country] ([countryNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CountryCity] CHECK CONSTRAINT [FK_CountryCity_R_484_Country]
GO
References:
http://www.loc.gov/standards/iso639-2/php/English_list.php
https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes
https://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
http://timtrott.co.uk/culture-codes
http://data.okfn.org/data/core/country-codes
https://www.unc.edu/~rowlett/units/codes/country.htm
http://www.nationsonline.org/oneworld/country_code_list.htm
https://en.wikipedia.org/wiki/ISO_3166-1_numeric
http://www.worldatlas.com/aatlas/ctycodes.htm
https://www.countries-ofthe-world.com/world-currencies.html
http://www.nationsonline.org/oneworld/currencies.htm
http://www.tripmondo.com/magazine/facts-and-statistics/list-of-capitals-and-countries
http://www.yallaforex.net/school-yallaforex-pages/5.htm
https://www.microsoft.com/resources/msdn/goglobal/default.mspx
Versions:
1.2 Database normalization on table “Language”, and extracted: “LanguageCulture”, and made new form for it
1.0 Started the article
Reader Man Blog 