Question

Assignment 2 – Data Types

Research and find the best corresponding Data Types for the following Microsoft Access Data Types from the Micosoft SQL server as well as MySQL DBMS packages.

MS Access                                MS SQL Server                                      MySQL

Shorttext                                                

Longgtext                                            

Number (Byte)                                     

Number (Integer)                                 

Number (Long Integer)                         

Number (Decimal)   

Number (Double)   

Date/Time   

Currency

Yes/No

Answer 1

MS Access                                MS SQL Server                                      MySQL
Shorttext	A char(size) can be used here	Tinytext- Can hold 255 characters
Longgtext	varchar(max)Can store 2^31-1 bytes of data	Longtext Can hold 2^32-1 bytes of data
Number (Byte)	tinyint(-128 to 127)	tinyint(-128 to 127)
Number (Integer)	Int-----Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295	Int -same
Number (Long Integer)	BigInt (-2^63 to 2^63-1)	BigInt
Number (Decimal)	decimal or numeric can be used l	decimal or numeric can be used
Number (Double)	real can be used	Double can be used
Date/Time	It has date,datetime,datetime2,smalldatetime,time,datetimeoffset,timestamp are there. The difference is in the data it can hold .	It has date,timestamp,time,datetime
Currency	It has smallmoney and money. The difference is in the data it can hold	Here we can use numeric(15,2)
Yes/No	Bit- Stores 1 and 0. True can be converted to 1 and false to 0	There are a multiple ways but tinyint(1) is the most suitable to store 1 and 0, true or false respectively