Create SQL Database

 The following steps assume that you have administrative access to an Instance of MS SQL Server (or SQL Express).

 

  1. Open Microsoft SQL Server Management Studio


     
  2. You will then be prompted to Connect to Server


     
    • First set the server that you want to connect to. The first part is the server name, the second piece is the SQL Instance.
       
      • ECOINFO, 6004\ECODEV
         
    • Recall that we discussed two methods for Authenticating your permissions to access a particular server, either 'Windows Authentication' or 'SQL Server Authentication'. In this case, since we are all authenticated within the UWYO network/domain, we will use Windows Authentication
       
    • Click Connect

       
  3. You are now connected the the ECODEV instance on the ECOINFO server on port 6004. 
    • The two folders/objects that you are going to interact with the most will be Databases & Security
       
  4. The next step is to create your Database within the SQL Server: A database is simply a container for you to store tables of data, create relationships between tables based on common fields, and create Views/Queries of your data so you can mix and match information to answer specific question(s). Remember that each column of a table will have a specific data type.
     
    • Right-click on Databases and select New Database...
    • Under the General page (see the far left of window)
       
      • For the Database name, you must type a DB name that will be unique to YOU
         
        • I chose to name my DB: AlbekeDB (see image below). Do not put spaces in your name!
           
      • Leave the Owner as <default>
         
      • You can change the path to be your unique folder (by user id) if we had more control of the server....we do not, so IGNORE THIS STEP AND ACCEPT THE DEFAULTS (****within the D:\SQLData\TDM2015 folder for the the DB file and the Log file (see image below)***). This is the path where your SQL Database file would have existed on disk.
         
        • Example = D:\SQLData\TDM2013\salbeke
        • I have set the permission within this directory so that you should be able to create and modify files within your own directory. I used Active Directory structure to set the permissions. If you have questions, please come and talk with me about how to set permissions.


           
    • Under the Options page (see the far left of window):
       
      • For the Recovery model, this is an important choice. If you are curious about the consequences of this choice, visit this website - msdn.microsoft.com/en-us/library/ms189275.aspx
         
        • In this case, let's just choose - Simple
           
      • Change Auto Shrink to be TRUE. I like this setting to keep my Log file and temporary data as small as possible. You can read up on the consequences and make your own choice when creating your own database with your own data.


         
    • Now Click OK... You have just created your first SQL Database! Now its time to populate it with some data.
       
  5. Import tabular data into your database 
     
    • The data were collected by a Master’s student during 2007 and 2008 at the Savannah River Ecological Laboratory (SREL). The study was aimed at assessing small mammal abundance given a set of three treatments, and a control, to the pine forest (CON=control, REM=remove all woody items, SNAG=remove only snags, DWD=remove only downed woody debris). The data were collected within 12 separate plots (each plot having a treatment) contained within 3 separate blocks (each block having each type of treatment). Within each plot, small mammals (and other vertebrates) were trapped at 8 possible ‘array’ locations.This is your standard experimental block design that can be analyzed using ANOVA to test for treatment effects.
       
    • Import data tables from the provided Excel spreadsheet
      • Right-click on your database
      • Select 'Tasks'
      • Select 'Import Data...' (a new window will popup)


         
    • For the Data source, choose Microsoft Excel
       
    • Browse to the SQLExample folder within YOUR directory on \\klondike
       
      • \\klondike\shannon$\TDM\salbeke\SQLExample\SmallMammalData.xlsx
         
      • Make sure that the 'First row has column names' is checked!
         
      • Click Next
         
    • For the Destination, all of the defaults should be good to go. Just verify that it is your Database that is selected
       
      • Click Next
         
    • For the Specify Table Copy or Query, make sure the top option is selected (Copy Data)
       
      • Click Next
         
    • These next steps are very crucial in getting your data imported correctly, so PAY ATTENTION TO DETAIL!
       
      • Check the boxes next to 'FieldData$' and 'Species$'
         
      • Now change the SQL table names to: 
         
        • [dbo].[tblFieldData]
           
        • [dbo].[luSpecies]


           
      • Now highlight the row for the FieldData and click the 'Edit Mappings...' button. This is where you can specify the column Names and the column Types for the tblFieldData table
         
        • Recall that 'Date' is a function name. Change the Destination name to SampDate
        • Change 'Plot' to be an Integer (int)
           
        • Change the nvarchar length of SpecCode to be 5. We want this length to match the length of our SpecCode in the luSpecies table so that we can easily create a relationship between the two tables later
           
        • Notice that a lot of the column types are 'nvarchar'. This is because the NULL values in the spreadsheet were described with a "-". We will have to deal with this later because many of the data are actually a float
           
        • Also notice that many of the columns have odd characters and spaces in the column names. We need to fix/standardize this. Below is an example of how I changed column names. See the image below to see how I named all of the columns. Make your column names match my column names to assist with future data processing please!
           
          • Change 'Dir Of Mvmt' to 'DirOfMvmt'


             
          • Click OK
             
      • Now highlight the luSpecies row and click the 'Edit Mappings...' button. You will see that these are all correct because each column is a Text data type (i.e. nvarchar), but the lengths are too long for some of the columns. Make the changes that I have made to the lengths.


         
        •  Click OK
           
      • Now that you have adjusted the Field Mappings, Click Next
      • SQL Server is giving you a Warning that you are going to truncate some data. This is ok, we changed it from float to int, so no data will be lost as the values are truly Integers...Click Next
      • Click Next to Run Immediately
      • Click Finish
        • You should see a summary that everything imported successfully into your database...Hooray! Now Close the window


           
           
  6. Fantastic! Now check out the tables within the database by expanding your database, Right-click on Tables and choose Refresh.
     
    • Now Right-click on luSpecies and choose 'Select Top 1000 Rows'. You have just performed your first Query!
       
    • Perform the same for tblFieldData
       
    • The data needs to be cleaned up, but for now we can call it good. A Job Well-Done!!
       
  7. Import the Spatial data into your database. This is super Cool and very easy and very USEFUL (all data are spatial)
     
    • Open ArcGIS
       
    • Now add the two shapefiles to your map that were provided - TrapArrays and Plots
       
    • Now you need to create a new database connection to your SQL Database
       
      • Expand the Catalog tab
         
      • Expand the Database Connections
         
      • Double-click the 'Add Database Connection'
         
      • Make sure the Database Platform is SQL Server. Type in your Instance (ecoinfo, 6004). Then Select YOUR database


         
    • You should now see a new Database connection named: Connection to ecoinfo.sde
      • To Import your shapefiles into your database, Right-click on the database connection within Catalog
      • Choose Import then Select 'Feature Class (single)
      • For the Input Features, select TrapArrays
      • For the Output Feature Class, name your data fcTrapArrays

         
      • Click OK
         
      • Now do the same thing for the Plots, naming the new data fcPlots
  8. Check out your handy work in the SQL Database. 
     
    • Right-click on Tables and select Refresh. You should see two new tables.The neat thing is that these data can now be viewed within ArcGIS or SQL Server! We can also use them in Program R, but we have to do a little more work (later).
       
      • To view the spatial data in SQL Server, Right-click on one of the spatial tables and choose 'Select Top 1000 Rows'. You can now see the attribute table and by choosing the Spatial results tab you can see the spatial features.
         
    • One annoying thing that ArcGIS does is make your table names ALL CAPS. I don't like that, so please rename them to be the Mixed-Case that we typed earlier.
       
    • We are now done with this section!!