Skip to main content

Kari Larson

MLIS ePortfolio

Mental Health Resources for the Homeless or Those With Critical Housing (SQL Database)

LIS 7510 Database Management

The final paper (PDF) is a combination of the papers from previous phases of the project and our final reflections after finishing the project.

The blocks of code below are the SQL from our project. Unfortunately, there is not a great visual element to the database we made.

The SQL code we wrote can also be viewed in PDF format:

Here's the SQL that creates our database.

/*
        ELIZABETH BASTYR
        KARI LARSON
        LIS7510 DATABASE MANAGEMENT
        MAY 1, 2016
        CREATING DATABASE 
        */

        -- Run as ROOT

        DROP DATABASE IF EXISTS mhresources;

        CREATE DATABASE mhresources;

        GRANT ALL ON mhresources.* TO eebastyr;
        GRANT ALL ON mhresources.* TO kjlarson;

        FLUSH PRIVILEGES;

This is the SQL code that set up the tables for our database.

    /*
    ELIZABETH BASTYR
    KARI LARSON
    LIS7510 DATABASE MANAGEMENT
    MAY 1, 2016
    creating tables
    */


    CREATE TABLE zip_code
    (
        id SMALLINT NOT NULL AUTO_INCREMENT,
        zip_code VARCHAR(10) NOT NULL,
        CONSTRAINT PRIMARY KEY (id)
    );

    CREATE TABLE city
    (
        city_id INT(11) NOT NULL AUTO_INCREMENT,
        city_name VARCHAR(45),
        CONSTRAINT PRIMARY KEY (city_id)
    );

    CREATE TABLE contact
    (
        contact_id INT(11) NOT NULL AUTO_INCREMENT,
        full_name VARCHAR (60),
        fname VARCHAR(40),
        lname VARCHAR(40),
        phone VARCHAR(20),
        Email VARCHAR(30),
        CONSTRAINT pk_contact_id PRIMARY KEY (contact_id)
    );

    CREATE TABLE location
    (
        location_id INT(11) NOT NULL AUTO_INCREMENT,
        address VARCHAR(40),
        city_name VARCHAR(40),
        zip_code CHAR(10),
        state ENUM('MN', 'WI'),
        phone CHAR(12),
        website VARCHAR(100),
        fax CHAR(12),
        notes VARCHAR (200),
        CONSTRAINT pk_location_id PRIMARY KEY (location_id)
    );

    CREATE TABLE service
    (
        service_id INT(11) NOT NULL AUTO_INCREMENT,
        service_type ENUM('Government Agency', 'Inpatient', 'Library', 'Psychiatry', 'Residential Treatment', 'Shelter', 'Support Group', 'Clinic', 'Hospital'),
        CONSTRAINT pk_service_id PRIMARY KEY (service_id)
    );



    CREATE TABLE resource
    (
        resource_id INT(11) NOT NULL AUTO_INCREMENT,
        resource_name VARCHAR(45),
        service_id INT(11),
        location_id INT(11),
        contact_id INT(11),
        CONSTRAINT pk_resource_id PRIMARY KEY (resource_id),
        CONSTRAINT fk_service_id FOREIGN KEY (service_id) REFERENCES service (service_id),
        CONSTRAINT fk_location_id FOREIGN KEY (location_id) REFERENCES location (location_id),
        CONSTRAINT fk_contact_id FOREIGN KEY (contact_id) REFERENCES contact (contact_id)
    ) ;

Here's the SQL that adds the data to our database.

/*
        ELIZABETH BASTYR
        KARI LARSON
        LIS7510 DATABASE MANAGEMENT
        MAY 1, 2016
        INSERTING DATA INTO TABLES
        */

        INSERT INTO zip_code (zip_code)
        	VALUES	('55104'), ('55401'), ('55402'), ('55403'), ('55404'), ('55405'), ('55406'),
        			('55407'), ('55408'), ('55409'), ('55410'), ('55411'), ('55412'),
        			('55413'), ('55414'), ('55415'), ('55416'), ('55417'), ('55418'),
        			('55419'), ('55420'), ('55421'), ('55422'), ('55423'), ('55424'),
        			('55425'), ('55426'), ('55427'), ('55428'), ('55429'), ('55430'),
        			('55431'), ('55432'), ('55433'), ('55434'), ('55435'), ('55436'),
        			('55437'), ('55438'), ('55439'), ('55440'), ('55441'), ('55442'),
        			('55443'), ('55444'), ('55445'), ('55446'), ('55447'), ('55448'),
        			('55449'), ('55450'), ('55451'), ('55452'), ('55453'), ('55454'),
        			('55455'), ('55456'), ('55457'), ('55458'), ('55459'), ('55460'),
        			('55461'), ('55462'), ('55463'), ('55464'), ('55465'), ('55466')
        ;

        INSERT INTO city (city_id, city_name)
        	VALUES 	(NULL, 'Minneapolis'), (NULL, 'St Paul'), (NULL, 'Eagan'),
        			(NULL, 'Eden Prairie'), (NULL, 'Brooklyn Park'), (NULL, 'Richfield'),
        			(NULL, 'Bloomington'), (NULL, 'Edina')
        ;

        /* 1 minneapolis 
        2 st paul 
        3 eagan 
        4 eden prairie 
        5 brooklyn park 
        6 richfield 
        7 bloomington
        8 Edina
        */

        INSERT INTO contact (contact_id, full_name, fname, lname, phone, email)
            VALUES	(NULL, 'Jerrod_Brown', 'Jerrod', 'Brown', '651-641-1555 ext 115', NULL),
        			(NULL, 'Elizabeth_Bastyr', 'Elizabeth', 'Bastyr', '952-454-5835', 'eebastyr@stkate.edu'),
        			(NULL, 'Lois Langer Thompson (Director)', '612-543-8500', NULL)
        ;

        INSERT INTO location (location_id, address, city_name, state, zip_code, phone, website, fax)
            VALUES
        -- 1 Pathways Counseling Center, Inc
        			(NULL, '1919 University Avenue West, Suite 6', 'St Paul', 'MN', '55104', '651-641-1555', 'http://www.pathwayscounselingcenter.org/', NULL),
        -- 2 Fairview Southdale
        			(NULL, '6401 France Ave. S', 'Edina', 'MN', '55435', '952-924-5000', 'http://www.fairview.org/Hospitals/Southdale/index.htm', NULL),
        -- 3 Minneapolis Central Library
        			(NULL, '300 Nicollet Mall', 'Minneapolis', 'MN', '55401', '612-543-8000', 'http://www.hclib.org', NULL),
        -- 4 VA Hospital
                    (NULL, 'One Veterans Dr', 'Minneapolis', 'MN', '55417', '612-725-2000', 'http://www.minneapolis.va.gov/locations/', NULL),
        -- 5 St Stephen's Shelter
        			(NULL, '2211 Clinton Ave S', 'Minneapolis', 'MN', '55404', NULL, 'http://ststephensmpls.org/', NULL),
        -- 6 MN Dept of human services
        			(NULL, NULL, NULL, 'MN', NULL, '651-431-2000', 'http://mn.gov/dhs', NULL)
        ;

        INSERT INTO service (service_id, service_type)
            VALUES	(NULL, 'Government Agency'), (NULL, 'Inpatient'),
        			(NULL, 'Library'), (NULL, 'Psychiatry'),
        			(NULL, 'Residential Treatment'), (NULL, 'Shelter'),
        			(NULL, 'Support Group'), (NULL, 'Clinic'), (NULL, 'Hospital');
        /*
        1 Government Agency		2 Inpatient		3 LIBRARY	4 Psychiatry	5 Residential Treatment
        6 Shelter				7 Support GROUP	8 Clinic	9 Hospital
        */

        INSERT INTO resource (resource_id, resource_name, service_id, location_id, contact_id)
            VALUES	(NULL, 'Pathways Counseling Center, Inc', 8, 1, 1),
        			(NULL, 'Fairview Southdale', 2, 2, NULL),
        			(NULL, 'Minneapolis Central Library', 3, 3, 3),
        			(NULL, 'VA Hospital', 9, 4, NULL),
        			(NULL, 'St Stephens Shelter', 6, 5, NULL),
        			(NULL, 'Minnesota Department of Human Services', 1, 6, NULL)
        ;

        INSERT INTO location (location_id, notes)
        	VALUES	(3, 'Hennepin County Library System')
        	;

This is the SQL for the queries we did during our presentation to the class.

/*
        ELIZABETH BASTYR
        KARI LARSON
        LIS7510 DATABASE MANAGEMENT
        MAY 1, 2016
        Queries of database
        */



        SELECT r.resource_name, l.phone  FROM resource r
        	INNER JOIN location l
        	ON r.location_id = l.location_id
        WHERE zip_code = '55401'
        ;

        SELECT r.resource_name, l.phone  FROM resource r
        	INNER JOIN location l
        	ON r.location_id = l.location_id
        	INNER JOIN service s
        	ON s.service_id = r.service_id
        WHERE s.service_type = 'Hospital'
        ;

        SELECT r.resource_name, l.phone  FROM resource r
        	INNER JOIN location l
        	ON r.location_id = l.location_id
        	INNER JOIN service s
        	ON s.service_id = r.service_id
        WHERE s.service_type = 'Hospital' OR
        		zip_code = '55401'
        ;