Task

Create a table to store addresses.

You may assume that all the addresses to be stored will be located in the USA.   As such, you will need (in addition to a field holding a unique identifier) a field holding the street address, a field holding the city, a field holding the state code, and a field holding the zipcode.   Choose appropriate types for each field.

For non-database languages, show how you would open a connection to a database (your choice of which) and create an address table in it. You should follow the existing models here for how you would structure the table.




import db.*
import util.*
 
Class.forName( 'org.h2.Driver' )
conn = DriverManager.getConnection( 'jdbc:h2:mem:test', 'sa', '' )
statement = conn.createStatement()
 
statement.execute( '''
  CREATE TABLE `user_data` (
    `id` identity,
    `name` varchar(255) NOT NULL,
    `street` varchar(255) NOT NULL,
    `city` varchar(255) NOT NULL,
    `region` char(2) NOT NULL,
    `country` char(2) NOT NULL,
    `code` varchar(20) NOT NULL,
    `phone` varchar(20) NOT NULL,
    PRIMARY KEY (`id`)
  )''' )
 
statement.execute( '''
  INSERT INTO `user_data` (`name`, `street`, `city`, `region`, `code`, `country`, `phone`) VALUES
    ('Jacinthe Steinert', '8540 Fallen Pony Villas', 'Searights', 'IA', '51584-4315', 'US', '(641) 883-4342'),
    ('Keeley Pinkham', '1363 Easy Downs', 'Mileta', 'TX', '77667-7376', 'US', '(469) 527-4784'),
    ('Rimon Cleveland', '8052 Blue Pond Dale', 'The Willows', 'UT', '84630-2674', 'US', '(385) 305-7261'),
    ('Berenice Benda', '2688 Merry Pines', 'Dacono', 'HI', '96766-7398', 'US', '(808) 451-2732'),
    ('Mehetabel Marcano', '109 Sleepy Goose Crescent', 'Plains', 'UT', '84727-7254', 'US', '(385) 733-8404'),
    ('Ambria Schiller', '7100 Tawny Robin Highway', 'Barlowes', 'ID', '83792-2043', 'US', '(208) 227-8887'),
    ('Carne Cancino', '3842 Broad Pioneer Cape', 'Bardstown', 'IA', '51571-6473', 'US', '(563) 060-8352'),
    ('Ince Leite', '7876 Stony Fawn Boulevard', 'Easton', 'ID', '83651-9235', 'US', '(208) 951-3024'),
    ('Britney Odell', '3386 Lazy Shadow Thicket', 'Kimberly', 'OK', '73539-6632', 'US', '(539) 848-4448'),
    ('Suprabha Penton', '9311 Dusty Leaf Alley', 'Niumalu', 'GA', '39927-8332', 'US', '(404) 589-0183')''' )
 
result = statement.executeQuery( '''SELECT * FROM user_data WHERE region = 'ID' ORDER BY code''' )
print( TextTable.apply(result) )
 
conn.close()