university of alaska anchorage · university of alaska anchorage university of alaska anchorage...

UNIVERSITY OF ALASKA ANCHORAGE

University of Alaska Anchorage

Department of Engineering

Database and Backend for Engineering on Display

by

Brian J. Kapala

Supervisor:

Prof. Adriano Cavalcanti, PhD

Anchorage AK, May 2015

© Copyright 2015

by Brian J. Kapala

[email protected]

Version 1.0

i

Abstract

The capstone project presented in this documentation is for the creation of an application for

Engineering on Display. This project is created for the University Of Alaska Anchorage Department

Of Engineering. The application is meant to be a tool for students, faculty, and most anyone else. This

application will make it possible for a person to look up the usage of electricity, water, and heating

sources. The application will also allow an administrator to push data to the user in the form of

campus alerts or news. This project is being designed so it can grow and possibly supply other forms

of support to the user. An important part of this project will be the android application being viewable

on a large touchscreen monitor in the new engineering building. The touchscreen monitors are being

donated to the college by Siemens.

The development of this project is divided into two (main) different teams. The front end of this

project will rely on the android development of the project and the user interface. The backend of the

project will run in a linux server. This part of the project is what I am working on. The server will be

in linus and run Apache, and MySQL. The creation of the database and the design of the database will

be done by myself. To integrate the database design into the application and the web interface, I chose

to use a php format design. This is an industry applicable format to run all the queries in and is simple

to implement.

Much of this project will undergo testing throughout the development phase. The testing will be

recorded and the results will be shared with the group. On the server the side the testing will be done

in short iterations while the creation of the database and the connection of the database to the

application.

“It’s 2015, you be you”

- Lamar Wilson

ACKNOWLEDGMENTS

This capstone project is being submitted by myself, but could not have been completed without

the help of others. I would like the people in my life who made it possible for me to complete this

project.

My first thanks would go out to my wife, Heather Kapala. She has been there with me as I

returned to college. She also put up with the late nights I had doing schoolwork and meeting deadlines.

For all she has done for me I thank her.

On this project, I would also like to thank Dr. Adriano Cavalcanti. Without his guidance I

wouldn’t have been working on this project.

I would also like to thank the members of the Engineering on Display student team. The team

members, Yoon Sukjin, Brent Gonzales, and Nailya Galimzyanova have been a great help and an

inspiration.

I would also like to thank the Siemens team for their generous donations to this project. This

project could not happen without them.

ii

iii

Contents

1 Introduction 1

1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.2 Application. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1.3 Motivation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

1.4 Recent Developments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

2 Continuation 8

2.1 Going Further. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

2.2 Technology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

2.3 Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

2.4 Project Developments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

2.5 Agile Methodology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

3 Design Testing and User Interface 14

3.1 Continuation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

3.2 User Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

3.3 Testing methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

3.4 Test Cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

3.5 More Agile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

4 The How To and Discussion 20

4.1 Refresh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

4.2 Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4.2.1 Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4.2.2 Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

4.3 Recommended Software. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

4.4 Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

4.4.1 Product Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

4.4.2 Discussion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

iii

4.4 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

5 The Finale 27

5.1 Summation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

5.2 Implications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

5.3 Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

5.4 Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Bibliography 30

Publication List from Brian Kapala TBD

iv

List of Tables

1.1 No Tables So Far . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

v

List of Figures

1.1 The new UAA Engineering Building being constructed. (McCoy, 2014) . . . . . . . . . . . . 2

1.2 A rendering, from the Siemens website, depicting one of the various projects that the

company is working on in the South Pole. ("About Siemens", 2015) . . . .

3

1.3 An image of the monitor in the current engineering building maintenance room . . . . . . . 4

1.4 A visualization of the multiplatform capabilities that Android development has to offer

to developers and customers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5

1.5 Just some of the features that MySQL has to offer is its high availability and ability to

shard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

7

2.1 The setup of a Joint Network Node used by the US Army. . . . . . . . . . . . . . . . . . . . . . . . . 9

2.2 A relationship table of the first iteration of the SQL database for the engineering on

display project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10

2.3 A side by side comparison of the color shift within monitors . . . . . . . . . . . . . . . . . . . . . . 11

2.4 Just one of the options we have for our application displays . . . . . . . . . . . . . . . . . . . . . . 12

2.5 A visual example of how the agile process works with this project . . . . . . . . . . . . . . . . . 13

3.1 A close up shot of part of a server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

3.2 A screen capture of PuTTY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

3.3 The Engineering on Display Database tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

3.4 A sample php info page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

3.5 An example of a daily burn down chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

4.1 Login screen for putty. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

4.2 MySQL workbench setup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

4.3 Win SCP login screen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

4.4 Win SCP connecting to server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

4.5 Ftp server file directory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

5.1 The application screen capture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

CHAPTER 1. INTRODUCTION

Chapter 1

Introduction

1.1 Introduction

What is it that cars, phones, restaurants and schools have in common? If you guessed that someone had

to design and build them, you are correct. At the UAA campus a (McCoy, 2014) new engineering building

is being built (Figure 1.1). “The added space will allow for new computer-aided-design (CAD) labs. The

Prototyping Lab, including 3-D printers, will move from its temporary home in the University Lake

Complex.” (McCoy, 2014). These facilities will benefit the students greatly as they complete their

degrees. The current building has become too small for the campus population. “The building is needed

to maintain academic accreditation for the engineering program and accommodate an enrollment that has

quadrupled to 1,200 since 2000.” (McCoy, 2014). Engineering students will be taking their classes in this

building starting next semester, fall 2015.

Siemens is a worldwide business with a powerful background and strong history in business. “Siemens

is a global powerhouse positioned along the electrification value chain – from power generation,

transmission and distribution to smart grid solutions and the efficient application of electrical energy –

as well as in the areas of medical imaging and in-vitro diagnostics.” ("About Siemens", 2015). They are

a very technological based business working towards advancements in technologies and power sources.

This is an ideal business to be working with the engineering department at UAA.

The new engineering building is a huge investment for UAA. According to the reports (Buchholdt , 2013)

the building will cost nearly 80 million dollars to complete. This new facility is going to create more

space for students to learn. The UAA website also states “A new 81,500 sf facility will house engineering

laboratory and teaching areas not currently available on campus.” This is a large expansion for the


engineering department. The adddition of the new laboratories will impact the students for many years

to come.

To be the engineering building, it needs to be a building for engineers. For Engineering on Displaywe

are to implement an application that will make the engineering building more interactive for the students,

instructors and anyone else who visits the building. This means an application that can monitor statistics

of the building in a new way. Students will be able to see in real time information about how much heat,

energy, and water the bulding is using. In addition of energy consumption, the students will be able to

monitor seismic activity and its affect on the new building. This application will also stand as an

information board to the students about what is going on in the department. Through instant alerts, anyone

using the application will get news messages about the building or anything else that is going on in the

department.

For a capstone project, this application will be a testament of my skills. It will be on display for student,

faculty and anyone else to see every day. When this project is complete, it has the potential to be used

for other students assignments as a source for information that their instructors could require them to use

for their class. This is something that UAA currently doesn’t have available for their students in any of

its other buildings.

Figure 1.1: The new UAA Engineering Building being constructed. (McCoy, 2014)


1.2 Application

For this project, UAA will be installing large touch screen monitors and android devices for the display

of the application. These will be strategically placed at various locations within the new building. Each

of these devices will display, in real time, building stats on the consumption of water, electricity, heating

and seismic activities that affect the building. This will be available for students to look at and study. We

also plan on making it so that anyone can go to the google app store and download it to their android

device. Another application of this that we intend to implement is the ability to look up this data on the

internet. This will make it so you don’t need an android device in order to see the information on the

application.

Figure 1.2: A rendering, from the Siemens website, depicting one of the various projects that the

company is working on in the South Pole. ("About Siemens", 2015)

We plan on creating the initial application in an android environment. Android development has zero

cost. It would be easier to maintain this environment for development since the cost of the development

tools are zero and it is primary a java based environment. You can go to http://developer.android.com

http://developer.android.com/


and download the development tools from there for free (Android, "Distribute Your Apps | Android

Developers", 2015). These tools can also run in nearly every operating system environment. Since

android is google based, it is widely available for use. For us to develop an application in this

environment, is a wonderful thing. The benefits include compatibility, cost efficiency, maintainability,

and productivity.

This application will be on display in different sections of the new engineering building. The app will

also be available for download from the website. In addition to the application downloads, this will also

be viewable in a web browser. Since this “display” is going to be widely available for use and measure,

it is going to need to be professional and maintainable. Some of the original plans for this application are

to display water, heat and electrical usage as well as seismic activity on the building. There is a small

team of students working to create this application. We plan on making it available in many ways but the

main priority is to get the application to work with the display systems.

The University of Alaska Anchorage Engineering Department is going to display multiple monitors

around the new building to present this application for the students and faculty. A list of recommended

equipment for this project include a Samsung Galaxy Tab S 10.5 in tablet, Android compatible touch

screen monitor (ranging from 32 up to 84 inches), Galaxy Tab USB and SD connection kit, and also a

pluggable USB2-E100 Ethernet adapter. This is highly expensive equipment with a very high profile.

Figure 1.3 An image of the monitor in the current engineering building maintenance room.


The only thing that this equipment is going to be used for is the application for Engineering on Display.

The touchscreen monitors are going to be the main focus on the application. All the information will be

displayed through the monitors as well as all the interactions. The tablets will be hosting the application

for the display. All of this equipment will be supplied to the University by Siemens. It is through this

partnership that this will be able to happen.

This capstone project is the compilation of my skills and expertise in the field of computer science. I will

be working as a member of the team for Engineering on Display. For this project we will be creating an

application where anybody (students in particular) can interact with information about the new

engineering building. This will be an application that only displays the information about the project. My

part of this project will include database connectivity.

Figure 1.4 A visualization of the multiplatform capabilities that Android development has to offer to

developers and customers (Android, "Distribute Your Apps | Android Developers", 2015)

1.3 Motivation

University of Alaska Anchorage is a large campus. People from around the world go to this campus for

many reasons. The two most popular reasons that people go to this campus are to learn and teach. The

new engineering building is going to change how the students attending this campus will be taking their

classes. This will allow instructors to teach in ways they couldn’t before since there will be new

classrooms. The engineering department would like to have a display of engineering. That is where

Engineering on Display comes in.

For this application we chose to create it in Android. Android is owned by Google. Google is a popular

brand with a strong background in technology. Google Play is Google’s form of an application store.

This is widely distributed. The Google Play app store can be accessed on an android device. Phone


companies offer to their clients the option to have Android based mobile phones on their plan. Google

also offers Android based tablets and full computer systems. The large client base and easy access to

Android devices make it an easy choice for us to choose Android as a developing environment for this

project.

Many software developing environments are very costly to use to create applications. Android

Development Tools/Studio are/is free to download. The tools are supported and updated on a regular

basis. Developing in Android for this project will make it cost effective for UAA to continue support on

this application. New developers will not have to purchase any tools or software in order to maintain,

upgrade or create patches for this application. The primary programming languages for Android are java

and xml. The Engineering on Display Application team will be using a GitHub as a main source of

software updates. GitHub is free to register and easy to use.

For this project our team will be using an agile methodology. In an agile methodology, the programmer

has an initial idea of what the program is supposed to work like. On a regular basis, the programmers

will have enough code to show the client, what has been completed. It’s very flexible for a project like

this. The client already has an idea about what they want for their application but are unsure of which

features they want in it. They know the main features (messages, seismic data, and building statistics)

but there are also some other features (class info, instructor info, maps, ect…) that can be added in later

or if they seem feasible or necessary. An agile method allows us to break the project up in parts and

estimate the total cost of creating each part of the project. From this, we can set actual timelines of when

work should be done.

1.4 Recent Developments

For this project, I will be working on connectivity with the values for the power, water, and heat usage.

This will be connecting the android application to the server and updating the app output in real-time

whenever we get new. One option we considered using for this project was MySQL ("The world's most

popular open source database", 2015) since it is an open source database management system that has a

high usability.

We were first considering using MySQL. This has changed. After recent meetings, we discovered that

for this project we will be getting all of our information for this in Comma Separated Values (CSV).

From these data files we will be populating our on screen displays for the Android application. I will be

working on this backbone of the project. This doesn’t mean we won’t be needing a database to store the

information. It just adds another extra step in the information storage.

We will be creating test servers with testing information in it to start with system building and testing

functionality of our code and application. Recently, we acquired approval to build our prototype

application to model the ANSEP building. It is a smaller building, but will create a great backbone

structure for our application. We will be doing most of our code in Android, which is a java based

application.

This application will likely be made with a BSD license. This is a free license that makes our software

available to whomever wants to use it. It also makes it easier for us to distribute our software to any

clients. Using this license will make it easier to hand the software to a new development team when the

original development team has moved on to newer projects. My recommendation will be the Apache 2.0

license. It offers the option for the works created with it to be sold or distributed for free ("Apache


License, Version 2.0", 2012). With the redistribution you are still giving credit to where credit is due.

Developing this project will better prepare me for what I may be working on in future jobs. I do enjoy

the fact that I get to work in a small team. Teamwork gives me the chance to produce better work. It also

give me more experience working with a GITHub. Dr. Cavalcanti will be providing us a link to that.

From there we will be making patches to the software and he will be approving them,

Figure 1.5 Just some of the features that MySQL has to offer is its high availability and ability to shard.

("The world's most popular open source database", 2015)

http://dev.mysql.com/downloads/fabric/

CHAPTER 2. System Integration and Modeling / Methodology

Chapter 2

System Integration and Modeling /

Methodology

2.1 Introduction

The engineering on display project is moving along quite well. As a group we have a long and

professional journey ahead of us. We split up the task into three main components, the front end

application, the front end web site, and the backend server side. I will be working on the backend server

side. It’s been a while since I’ve worked on the back end of a server but I feel as if this may be a defining

moment for me in my college career.

The first professional server I had worked in was part of a Joint Network Node (JNN). It was a

communications system that I knew nothing about at the time. I only touched upon the basics when I was

in my training just weeks before. When I arrived at Fort Drum, New York, I was immediately thrown

into their JNN team. “Similar to a home Internet connection, WIN-T Increment 1, formerly known as the

"Joint Network Node, or JNN, Network," began fielding in 2004 to support operations in Iraq and

Afghanistan.” (Walker, "ARMY.MIL, The Official Homepage of the United States Army", 2012) While

in this team I learned a lot about remote access into servers and setting up and programming equipment

like Cisco routers and switches, a Promina, and Redcom. I also worked on programming the Cisco Call

Manager. This was what I did for the next two and a half years. I also worked in getting the satellite dish

setup and configured for communications. As a platoon, we made sure that everyone knew how to do

everyone else’s job. That’s precisely what I did.

As I relate my past experience with this project, I know that being the person to setup the server and

prepare it to interact with the front end is a large responsibility. I’m prepared to install Apache, MySQL,


and any other application we may need for this project. I’ve already created username and passwords for

each person that is working on this project. I also sent them each instructions on how to log-in remotely

to the server.

Figure 2.1: The setup of a Joint Network Node used by the US Army. (Image property of US Army)

2.2 Technology

Everything in this project has to come together and work as a cohesive unit. For this I chose to have our

server run in Ubuntu. When my supervisor asked what I would like to work in I thought about it for a

little bit. I remembered the days in my military training when I trained to work in information systems. I

remember when we spent a whole day just learning how to install windows server 2003. During this, we

also used virtual machine and setup user accounts. I still feel like I have a good handle on this. I also

remembered how I trained in Linux. Both have command line interface. I did remember my supervisor

for this project recommending Ubuntu so that is what I went for. I thought, “Heck, this will be fun. I’m

here to learn. I might as well familiarize myself with Linux more.” In my experience with working in a

Microsoft environment, I’ve learned that some things can get proprietary when I am working in

Windows.


Figure 2.2: A relationship table of the first iteration of the SQL database for the engineering on display

project. This allows for multiple buildings to have multiple readings of each type of data and also leaves

the option for expansion within the database. Eventually a room directory for each building could be put

in the database.

Another technology we will be using in our system is an Apache server. This is something we will be

using for our database component of the project. I have already set up the Apache server at the time of

this writing. This server is a part of how we are going to implement our PHP files for use opening up

SQL queries. “The ASF is made up of more than 150 top level projects which cover a wide range of

technologies. Chances are if you are looking for a rewarding experience in Open Source, you are going

to find it here.” ("The Apache Software Foundation", 2012). This quote states it well just how widely

used Apache is.

We will also be using MySQL alongside the Apache server. MySQL is an industry standard. Many

professional companies use this for programming and maintaining their databases. I will be using it to

write and run the database structure for storing our readings for the heat, electricity, and water

consumption of the building. “Ubuntu 14.04 LTS continues to provide official support for MySQL 5.5.”

("ReleaseNotes", 2015). And this is also really good since we are using an Ubuntu server and MySQL.


Figure 2.3: A side by side comparison of the color shift within monitors. (Image property of ASUS)

2.3 Components

This project will be worth over $100,000. This is quite a large sum of money. The components for this

includes touch screen monitors, server systems, and other various pieces to the system. This project will

also include an Android application and a web interface. We will be using Java and Android for the

application development of this project. The other component we will be using for this is a web interface

that involves the likely use of HTML 5 and PHP.

The touch screen monitors that have been requested for this project are the ASUS ET2702 All-in-One

computer systems. “The ET2702 Series AIO provides a technological leap in multi-touch interactivity,

surpassing traditional two-finger multi-touch with full 10-finger multi-touch capability. This offers

smooth control of the new touch-based Windows 8 UI, optimizing your touch experience. Pinch-to-zoom,

rotate images and scroll through webpages, all with ease.” ("ET2702IGTH - Overview", 2015) The main

idea is to get a system that had a large touchscreen. This system has a large viewing area and a great

amount of touchscreen ability. “The ET2702 adopts 178˚ of horizontal and vertical viewing angle that

eliminates color shift on your screen no matter from where and how you look at it.” ("ET2702IGTH -

Overview", 2015). This is great for when people are walking near the display.

We’ve contacted the UAA IT department and have been granted use of a server for this engineering on

display. The server is going to be where all our data for the application gets to come together. We are

using an Ubuntu server with Apache and MySQL installed on it.


Figure 2.4: Just one of the options we have for our application displays. (Image property of ASUS)

2.4 Project Developments

We’ve been working diligently on this project. We have a GitHub setup as to where our project

information will be stored. The address location of the GitHub is https://github.com/Avanznow/EoD-

key-branch. The project has a clearer separation of tasks as we have gone to our weekly meetings. We

have been tasked into two person teams. I will be working mostly on the server setup and database

creation. We have Brent and Jim working on the application interface through the Android application

and the simulated server program for the running demonstration. They are putting together a program

that will run in conjunction with our main Ubuntu server. This program will generate simulated data for

my SQL program to read and interpret. Nailya and I will be working on the web interface for the program.

I will be helping with the queries and html interaction. For the most part though, I will be working on the

database and server side of the project.

We’ve had meetings with UAA staff from their IT department. Within this meeting, we were able to tell

them our needs and concerns for this project. We were given the option of working with a Windows or

Ubuntu based server. I made the decision to go with an Ubuntu server. The Ubuntu server version we are

https://github.com/Avanznow/EoD-key-branch

https://github.com/Avanznow/EoD-key-branch


working in is the Ubuntu Server 14.04. I’ve worked in both windows and Ubuntu many times in a

previous career and I feel that Ubuntu would be more relevant to work in.

2.5 Agile Methodology

For this project we are using an agile methodology. I have been informally and formally told what was

needed from me on my part of this project. I’ve been writing down what I’ve been assigned. I also have

been keeping track of when I am working on what I need to get done for this project.

As I am working on this project I am updating my files on the GitHub. This really only applies to the

coding side of my task. I have been setting up the server and have been reporting it to my supervisor. I

do my best to show that some work has been completed at least once a week. This is a chapter one

material. “When you commit to delivering something of value every week and showing your customer

how you’ve spent their money, you become accountable.” (Rasmusson, The agile samurai: how agile

masters deliver great software, 2010, p. 5). This part is very true.

Another look into the agile methodology will also explain how this project is evolving. At first the group

was given an idea of what needed to be done. We’ve had an idea of what we wanted in the beginning. At

first the plan was to get updated information directly from Siemens for the building. This changed to us

getting information on a smaller building to start with. Then eventually it became us creating our own

simulated data for this project so we can create the project. In addition to this part of the plan, we were

originally going to work with android devices connected to large touch screen monitors. This is also

another change that happened. We are now working with all in one touch screen PCs that run windows.

These are not going to be the last unplanned changes we may see on this project.

Figure 2.5: A visual example of how the agile process works with this project.

(Image taken from The Agile Samurai)

CHAPTER 3. Design Testing and User Interface

Chapter 3

Design Testing and User Interface

3.1 Introduction

Each process has steps and procedures. During these steps and procedures, we come across some that

are a defining moment for the project as a whole. As the process for the Engineering on Display project

continues, some of the more defining pieces come together. What people will remember when they

think about this project is the interface. This is the first defining moment in this project. Included with

the interface is the backend of the interface, which is the part that I am most responsible for. This part

requires a lot of testing and programming. Most of this in on the spot.

The back end of this project requires a lot of work. The more that needs to be done means the more

testing that needs to be done. The interface is the most recognizable part of this project, but that doesn’t

mean it is the most important. In this respect, it is very similar to my experience as a network

technician in the US Army. In that job, I trained for the year prior to my overseas mission. A large part

of this was not just the setting up of the equipment or learning how to repair any defective parts, there

was a huge need for me to learn how to get into the equipment and program it. I had to learn how to

program telephone and internet switches as well as the routers. Since this was a specialized setup, I had

to learn how to do this on nearly double the standard equipment. Most of this was using a terminal to

connect to a server or login to a router or switch. A good portion of the work that I did was backend

server or systems. From my prior experience I learned that working on the backend was possibly the

most important part of the job, since nothing else could work without it. This is a very similar situation

with the Engineering on Display project. If I don’t get the server up and running, none of the interface

could work without it. That is what makes the backend so important to this project.


Figure 3.1: A close up shot of part of a server. (Image taken from dwuser.com)

3.2 User Interface

The interface is an important part of this project. That is mostly left up to the android and webpage

teams. I have been assigned to assist with the web interface portion of this project. While doing some

research, I discovered that both the needs of the web and android interface could be met with the use of

php. “MYSQL is used as a database at the webserver and PHP is used to fetch data from the database.”

("Tutorials Point - Simply Easy Learning") This is quite a task and a very important part of this project.

For that I will be working mostly on the backend with this.

The android interface is going to model a touch screen based interface. This will be done using easily

identifiable tabs and swiping motions that are familiar and easy to understand for the user. For this the

team has decided to model it after the one used on the UAF website. This is what the customer has

requested and what we plan on doing. This also makes very much sense. We’ve also added a page for

news/headlines/alerts.

The web interface is going to be very similar to the android application. We’ve decided to do it this

way so it wouldn’t be confusing to anyone who is used to using one interface over the other. We are

using charts with usage vs. time to display our data. For the colors, we chose to keep it to a theme

aligned with the UAA school colors. This makes it easy to identify the project as a UAA application.

3.3 Testing Methodology

With this project we are to have two different servers. One of them is to be the main server used for

storing our database and any webhosting we may need for the Engineering on Display application.

Since we don’t have everything we need from Siemens for our database data, we are in the process of

using another server with the combination of information on a smaller scale building. Just because we

don’t have everything we need to make this work doesn’t mean we can’t exactly work on the project.


This is why we are using as much simulated information as we need so we can build a prototype

product.

Our testing is being done in parts. Since we are using the agile methodology, we need to work on parts

of this project and test before we can move on. One thing we do need for this project is a server to test

one. “Unless the only language in which you ever build web sites is 100%-static HTML then, yes, you

need a test server.” (Winslow, "Why You Need a Local Testing Server (and How To Do It) -

DWUser.com Education Center"). I’ve already worked on getting parts of the server set up. Most of

this methodology has been on the spot testing while working on the installation and creation of the

database and the server setup.

Figure 3.2: A screen capture of PuTTY (An application used for ssh into a server.)


3.3 Test Cases

Much of the backend is done with testing on the spot. A lot of it is server side and some of it is interface

side. I have been working with ssh to get into the server and have already installed the apache hosting

and the MySQL database software. I’ve also created a database structure and have been testing it. I’ve

also been writing queries so I know exactly if the database structure makes sense. I’ve discovered that

creating the backend of this server is tough and that the database integration into android is going to

work very similar to the web-interface integration.

The first form of testing that I have done was after the initial installation of the Apache software. I

created a file that told me information I needed to know to make sure that the server was up and

running. “We can install Apache easily using Ubuntu's package manager, apt. A package manager

allows us to install most software pain-free from a repository maintained by Ubuntu.” (Ellingwood,

2014). This allowed me to make sure that apache was installed and running correctly. I was able to poll

a page and see that it could display on my computer on a network outside of UAA’s network. This was

a good test. It passed and I was able to go onto the next part of the project.

Figure 3.3: The Engineering on Display Database tables.

The next part of the project I installed a SQL server on the system. I installed MySQL, created a root

user password and worked hard to come up with a database structure that made sense and could be

expanded if this project was to take in more buildings or other forms of information. I created a

database on the MySQL workbench. I was able to take the sql file and upload it to the server. After this

upload, I continued to work on the database with some minor testing.


I created some queries that I thought would make some sense. Since the main form of identification for

the buildings was their building number, I chose to make this the primary key for the buildings table.

From here I made tables for each type if readings that the buildings could have. I assumed this would

make it simple to assign only the readings needed for each building. In the future if the application was

to add, let’s say, a directory of instructors or anything else, then the creation of a table for this

information would be easy to make without ruining the integrity of the system. I continued to create

queries to test this information and created some test data to use the queries on. "Now the actual server

side coding starts.” (Tamada, "How to connect Android with PHP, MySQL"). I tried creating a

building at first and it succeeded. Then I tested by creating another building with the same BuildingID

to see if it would take it and that query failed just like it was supposed to. I also tried retrieving the

information from the database with select queries and those passed. Since these queries have worked

the way they are supposed to, the creation of php files for the android and web interface should work

without a hitch.

Figure 3.4: A sample php info page. (Taken from digitalocean.com)


3.5 Agile Methodology

As we continue to work on this project we come across new requests for this project. To fulfil each

request, we are continuing to use the agile methodology. Each week, the process continues. The

database has been submitted for patching and the server has been setup.

At this point, we get new tasks for this project on a weekly basis. This usually happens when we get

together for our weekly meetings. When we are at these meetings, there have been many new changes

to the project. One big change was the type of monitors we were getting. Another big change we have

had has been where we were getting our data from.

It has been from these changes that the project is evolving from. The agile methodology is flexible

enough that we can use it to make sure these changes can fit into the project as a whole. “One reason why this is a common problem in traditional software development projects is because the testing is one big lump all at the end.” (Waters, 2007) This is why we do our weekly meetings and

our testing as often as we can.

Figure 3.5: An example of a daily burn down chart. (Taken from allaboutagile.com)

CHAPTER 4. The How To and Discussion

Chapter 4

The How To and Discussion

4.1 Refresh

Engineering on Display is a project for the students of the engineering department created by students

from the engineering department. This is an application that will display statistical information about

the building power, heat, and water consumption for each building. This will also provide information

to the students about events that may be happening within the department or campus.

For this project, most of the work I have done has been on the server side. By doing all of the server

support for the project, I have mostly done troubleshooting with connectivity. This has not been all that

I have done. I also have worked on creating PHP files for database connectivity on the website and

application support. These files were created to give connectivity to the SQL database that I also

created. The database is meant to store any data related to the application for the purpose of retrieval. I

created this so it would be easy to update, maintain or even expand.

This chapter will act as a work log and manual of the back-end support of this project. Due to the

nature of this type of equipment, the logins, usernames, or any other secure part of this project will not

be discussed in this chapter. Initially I will discuss the database of this project. After that I will discuss

the supporting PHP files and their purpose. What will be discussed afterwards are the account types and

recommended software to use when working on the server.


4.2 Database

4.2.1 Structure

The database structure will be kept in a sql file. This file contains all the table requirements, restraints,

layouts and key structure. This sql file can be updated using various software but the recommended

software for updating or improving this file is the Oracle MySQL Workbench as it has a graphical user

interface that makes it easy to understand the table relations. This is only a recommendation and any

maintenance personnel can use any software they deem fit for the further production of the database

structure.

The database has initially been created to store the data for one building and is theoretically capable of

expanding this amount infinitely. There are tables for the different types of data. These have been

created for the initial needs of the Engineering on Display application. The tables can store data linked

to each building by building number. From there, each type of sensor for the building is linked to a

table. Each table has the capability to store the amount of unit value, the unit type and the date and time

of the reading.

One recommended expansion for the database would be to create a table for the rooms in each building.

For each room, there could be an instructor, or class linked to a timeslot in that room. From here the

programmer could add in function for instructors to have office hours, rooms, numbers, or anything

else. The possibilities for expansions are a lot.

Figure 4.1: The login screen for the Putty ssh connection to the server.


Another recommendation would be to start a backup of the sql database. This could be done as often as

deemed fit. The only major recommendation for this I would have would be to store the backups in a

remote server or in a fire proof container on a removable media. This would be good in case there is a

natural disaster of some sort that wipes out all of the data.

4.2.2 Connection

This database is used to support two parts of the system. The priority is the android application. The

other support is for a web application. Each of these applications can be supported with the use of PHP

files.

The PHP files created for this project have been created in three parts. The first part is the connectivity

to the database itself. Initially there is a database connection file that contains the login information for

each user that needs to use PHP. The second part is a, yet another, separate file for connecting to the

database. Then within each file that either sends or receives data, it has to call the database connection

file for the specified user.

Figure 4.2: A screen capture of how the MySQL workbench is setup.

Each of the users have been given only the amount of rights that are needed for the system. The

appuser user only has the capability to do queries on the system. This is good for the android

application and the web application. The other user that has been created for this project is the


dataretriever user. This user only has the ability to insert data into the database. This has been created

for the retrieval of the data from the CSV file (the CSV file will be discussed later). The purpose of

limiting the amount of rights that these users have is to prevent any of them stepping outside of their

bounds.

The appuser profile will be called to make a database connection with the PHP files that do SELECT

queries. This prevents anyone from searching in any of the source code for any of the applications and

trying to figure out any of the MySQL admin user profiles and compromising the integrity of the

database.

The dataretriever user has the required rights to insert data into the database. This is to so that when the

system gets a new CSV file, it can insert it into the database. The CSV file will be the file sent to the

server by Siemens. This file contains all the information on the building sensors for the application.

This is recommended to be checked every quarter hour (fifteen minutes).

Figure 4.3: The login page for the Win SCP program.


4.3 Recommended Software

This section will discuss the types of development tools that have been used for the making of the

database and the connection of the server. These are primarily the tools that I have used but can be seen

as a recommendation. The reason for discussing them here is to make is easier for the next person that

is developing this database and back-end. These are tools that have been used on a windows machine

and I have primarily did all my work on a windows machine.

The first tool to talk about is one for connecting remotely to the Ubuntu server. In windows, you can

download the client called Putty. This is a client for connecting to the server in a command line through

SSH or secure shell. This is a simple to use and easy to understand program. The website

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html explains all about how to install it

and also contains a link to a downloads sections so that you can download it as needed. What is needed

for this is a VPN tunnel for connection to the server and the IP address to the server. Afterwards you

will be able to log into the server with your provided username and password.

The next tool for connectivity to the server that I am recommending is the WinSCP software. Just like

the last client discussed, you can go to http://winscp.net/eng/index.php and download the installation

file from there and follow the instructions on how to download it. This one is a little trickier to set up.

What this software does, is it allows you to upload files or download files to and from the server. You

can upload files directly to user directory of the user account that is logged in. To place them in the

folder of the directory that is needed you may need to log into secure shell and move them over in the

command line.

The development tool that I recommend for making changes to the SQL database is called MySQL

workbench. This can be downloaded from http://www.mysql.com/ . Like every other development tool

mentioned earlier this one is also free to download. This one gives the user a graphical user interface

for creating a relational database. You can export all the work done in this into a sql database file. This

makes for easy database reconstruction if anything happens to wipe the database from the server. You

could just open up a putty connection to the database and login to the sql database and then just copy

all of the text from the sql file and paste it into the command line and construct the tables this way.

http://winscp.net/eng/index.php

http://www.mysql.com/


Figure 4.4: The sftp software as it is connecting to the server.

4.4 Results

4.4.1 Product Results

For this project, drawing some results on the end product of the server and database is difficult to do.

These are usually the most difficult to do with a back-end support. The most conclusive thing I can

show is that the front end products actually works. For this part of the chapter, I will discuss the

outcome of the project on the backend server.

The first server has been setup and is running smoothly. Apache has been installed and is currently

hosting a web space on the internet for the engineering on display project. The MySQL database has

been created and is currently able to store the required data for the engineering on display project. At

the moment, there are some parts of the project where the applications is having difficulty connecting

to the server. This is a permissions issue at the moment and will be cleared up soon enough. The web

server connects to the MySQL database no problem.


Figure 4.5: The sftp server file directory and a directory on the remote computer getting ready to

upload some files.

4.4.2 Discussion

From the (near) end product, we are able to see how well this fits in the trend of today’s technology. We

are able to use our computing devices to illustrate to a client the amount of resources their buildings are

using. This will be able to save the user money by providing real time information on the consumption

of resources and materials. This is a great tool for more than just a college campus to use. This is

something that any business could use to help cut costs. The money they save from using our kind of

technology could be used to create more jobs or even expansion.

4.5 Conclusion

Overall, the project is coming along very well. What has been done was a creation of a new application

that displays the supported data in an exciting way for the client. Nearly all of the backend support has

been completed. At this point it is just working out the bugs. Hopefully, this project will inspire future

students to improve it and learn from it.

CHAPTER 5. The Finale

Chapter 5

The Finale

5.1 Summation

Engineering on display is an ongoing project for the new engineering building. The engineering

department is partnering with Siemens to bring this project to light. The project as a whole is a series of

applications that allow the students or anyone else with the access to use the application to view the

building’s usage of water, electricity, gas and outside temperature.

The important part of this project is the application on display in the new engineering building. The

large touch screen monitors are the most important part of this project. The engineering building will be

hosting the new application on every floor.

The application itself has many interesting features. There are charts and graphs that give the user a

pleasant experience. The charts are color coded and the transition between screens is a smooth

experience.

For the application to work, it needs a place to draw its data from. For this is where my main part of the

project comes into play. The application is designed to read from a MySQL database that stores all the

readings of the building. This is a large part of the backend of the product.

The backend is not strictly reliant on MySQL to communicate with the application. The database

communicates with the application through the use of php. This is the backbone of the application.


5.2 Implications

The application has money saving usage built in. The idea of tracking the amount of gas, water, or

electricity usage is to see how much the building is using at a time. This can be used to monitor the

trends of how much the building is costing the university.

A theoretical but possible situation that could arise from this is a gas usage decrease. Let’s say the

building starts all of it’s heating at around 5 AM. This is a relatively early time in the morning and

chances are nobody will be there then. If this is the case, then you wouldn’t need to keep the building

heated above 70 when there is nobody inside the building. Now you notice the trend in the application,

you will be able to map it visually with let’s say a schedule of classes that are being hosted in the

building. The application would be able to make this possible.

Another good idea of what this project will be able to do has to do with the display part of the

application. We have a feature designed into the app where the maintainer of the application can push

out messages to the user. If there is something important that the user should know, it can be pushed out

to the application and display on the big screens for everyone to see. This is handy for things like bear

sightings.

Figure 5.1: A screen capture of the application.


5.3 Recommendations

The engineering on display project has a lot of potential expandability built in. At the moment the

application is built to keep track of the information that the client needs. Further down the road, the

addition of a directory, a map, or anything else related to the buildings could be put in.

My first major recommendation or this project is the directory. The addition of a directory would be

great for new students or even existing students who need to find an instructor or a class. The idea

would be to store a class schedule with a list of the room and instructor for that class. What could also

be stored in the application is the instructor’s office number and hours.

Initially this project is being built to support only one building. The database was designed and setup so

that more than one building could be stored. This is good for any future expansion of the application.

Since the data that is stored is almost a generic format, you don’t need to make new tables to store the

information. Creating indexes would help retrieve the data from the database for the application.

5.4 Summary

The engineering on display project is a compilation of the hard work of many students. I have been

honored to be the creator of the heart and soul of the project, the server and the database. The design of

the database has been designed in a clever way that allows the next group of people who work on it to

be able to create a new and expandable experience for the user.

With this project, a developer will have the ability to make the system grow. The connectivity of the

database has been implemented through php. This allows the developer to make the data available

across multiple platforms. There is so much that can be done with this. The possibilities are endless.

BIBLIOGRAPHY

vii

Bibliography

[1] Buchholdt, R. (2013, July 24). UAA Engineering & Industry Building. Retrieved

February 6, 2015, from http://www.uaa.alaska.edu/fpc/projects/engineering.cfm

[2] Ellingwood, J. (2014, April 18). How To Install Linux, Apache, MySQL, PHP (LAMP)

stack on Ubuntu 14.04 | DigitalOcean. Retrieved March 14, 2015, from

https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-

php-lamp-stack-on-ubuntu-14-04

[3] McCoy, K. (2104, January 18). Hometown U: New engineering facility is a hands-on

learning lab. Retrieved February 6, 2015, from

http://www.adn.com/article/20140118/hometown-u-new-engineering-facility-hands-

learning-lab

[4] Rasmusson, J. (2010). Agile in a Nutshell. In The agile samurai: How agile

masters deliver great software. Raleigh, North Carolina: The Pragmatic

Bookshelf.

[5] Tamada, R. (2015, January 1). How to connect Android with PHP, MySQL. Retrieved

March 15, 2015, from http://www.androidhive.info/2012/05/how-to-connect-android-

with-php-mysql/

[6] Walker, A. (2012, October 10). ARMY.MIL, The Official Homepage of the United States

Army. Retrieved February 22, 2015, from http://www.army.mil/article/88818/

[7] Waters, K. (2007, October 3). Step 3: Sprint Planning (Requirements). Retrieved March

14, 2015, from http://www.allaboutagile.com/how-to-implement-scrum-in-10-easy-steps-

step-3-sprint-planning-requirements/

[8] Winslow, D. (n.d.). Why You Need a Local Testing Server (and How To Do It) -

DWUser.com Education Center. Retrieved March 14, 2015, from

http://www.dwuser.com/education/content/why-you-need-a-testing-server-and-how-to-

do-it/

[9] About Siemens. (2015, January 1). Retrieved February 6, 2015, from

http://www.siemens.com/about/en/

[10] Apache License, Version 2.0. (2012, January 1). Retrieved February 8, 2015, from

http://www.apache.org/licenses/LICENSE-2.0

[11] Distribute Your Apps | Android Developers. (2015, January 1). Retrieved February 6,

2015, from http://developer.android.com/distribute/index.html

[12] ET2702IGTH - Overview. (2015, January 1). Retrieved February 22, 2015, from

http://www.asus.com/us/AllinOne_PCs/ET2702IGTH/

[13] ReleaseNotes. (2015, February 20). Retrieved February 23, 2015, from

https://wiki.ubuntu.com/TrustyTahr/ReleaseNotes#Ubuntu_Server

[14] The Apache Software Foundation. (2012, January 1). Retrieved February 23,

2015, from http://www.apache.org

https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-14-04

https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-14-04

http://www.androidhive.info/2012/05/how-to-connect-android-with-php-mysql/

http://www.androidhive.info/2012/05/how-to-connect-android-with-php-mysql/

http://www.army.mil/article/88818/

http://www.allaboutagile.com/how-to-implement-scrum-in-10-easy-steps-step-3-sprint-planning-requirements/

http://www.allaboutagile.com/how-to-implement-scrum-in-10-easy-steps-step-3-sprint-planning-requirements/

http://www.apache.org/licenses/LICENSE-2.0

http://www.asus.com/us/AllinOne_PCs/ET2702IGTH/

http://www.army.mil/article/88818/



BIBLIOGRAPHY

vii

[15] The world's most popular open source database. (2015, January 1). Retrieved February 6,

2015, from http://www.mysql.com/

[16] Tutorials Point - Simply Easy Learning. (n.d.). Retrieved March 13, 2015, from

http://www.tutorialspoint.com/android/android_php_mysql.htm

http://www.mysql.com/

http://www.tutorialspoint.com/android/android_php_mysql.htm


Appendix A.


Appendix B. package com.example.brian.myapplication;

import android.support.v7.app.ActionBarActivity;

import android.os.Bundle;

import android.view.Menu;

import android.view.MenuItem;

public class MainActivity extends ActionBarActivity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

}

@Override

public boolean onCreateOptionsMenu(Menu menu) {

// Inflate the menu; this adds items to the action bar if it is present.

getMenuInflater().inflate(R.menu.menu_main, menu);

return true;

}

@Override

public boolean onOptionsItemSelected(MenuItem item) {

// Handle action bar item clicks here. The action bar will

// automatically handle clicks on the Home/Up button, so long

// as you specify a parent activity in AndroidManifest.xml.

int id = item.getItemId();

//noinspection SimplifiableIfStatement

if (id == R.id.action_settings) {

return true;

}

return super.onOptionsItemSelected(item);

}

}

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

xmlns:tools="http://schemas.android.com/tools"

android:layout_width="match_parent"

android:layout_height="match_parent"

android:paddingLeft="@dimen/activity_horizontal_margin"

android:paddingRight="@dimen/activity_horizontal_margin"

android:paddingTop="@dimen/activity_vertical_margin"

android:paddingBottom="@dimen/activity_vertical_margin"

tools:context=".MainActivity">

<TextView android:text="@string/hello_world"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:id="@+id/textView" />

</RelativeLayout>


Appendix C.

-- MySQL Script generated by MySQL Workbench

-- 03/15/15 20:28:04

-- Model: New Model Version: 1.0

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------

-- Schema EoDdb

-- -----------------------------------------------------

-- -----------------------------------------------------

-- Schema EoDdb

-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS ÈoDdb` DEFAULT CHARACTER SET utf8 COLLATE

utf8_general_ci ;

USE ÈoDdb` ;

-- -----------------------------------------------------

-- Table ÈoDdb`.`Buildings`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.`Buildings` (

ìdBuildings` INT NOT NULL,

`BuildingName` VARCHAR(45) NULL,

`BuildingAddress` VARCHAR(45) NULL,

PRIMARY KEY (ìdBuildings`))

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table ÈoDdb`.ÒutsideTemperature`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.ÒutsideTemperature` (

`Buildings_idBuildings` INT NOT NULL,

`Degrees` INT NULL,

Ùnits` TEXT(6) NULL,

`Date/Time` DATETIME NULL,

PRIMARY KEY (`Buildings_idBuildings`),

CONSTRAINT `fk_OutsideTemperature_Buildings1`

FOREIGN KEY (`Buildings_idBuildings`)

REFERENCES ÈoDdb`.`Buildings` (ìdBuildings`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table ÈoDdb`.ÈlectricityDemand`


-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.ÈlectricityDemand` (


`Demand` INT NULL,




CONSTRAINT `fk_ElectricityDemand_Buildings1`



ON DELETE NO ACTION


ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table ÈoDdb`.ÈlectricityUsage`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.ÈlectricityUsage` (


Ùsage` INT NULL,




CONSTRAINT `fk_ElectricityUsage_Buildings1`



ON DELETE NO ACTION


ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table ÈoDdb`.`GasConsumption`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.`GasConsumption` (


`Consumed` INT NULL,




CONSTRAINT `fk_GasConsumption_Buildings1`



ON DELETE NO ACTION


ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table ÈoDdb`.`WaterUsage`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS ÈoDdb`.`WaterUsage` (



`Used` INT NULL,




CONSTRAINT `fk_WaterUsage_Buildings1`



ON DELETE NO ACTION


ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


Appendix D.


Appendix E.

university of alaska anchorage · university of alaska anchorage university of alaska anchorage...

Documents