Independent Component 2

LITERAL
(a) Statement saying:
“I, Jason Wu, affirm that I completed my independent component which represents 37.5 hours of work.”

(b) Cite your source regarding who or what article or book helped you complete the independent component.
Rockoff. Larry. The Language of SQL. San Bernadino, CA: Course Technology Cengage Learning,

     Feb. 27, 2015. Print. Mar. 27, 2015.

(c) Provide a digital spreadsheet (aka log of the 30 hours).

The digital spreadsheet has been completed. This is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1pk06m750zrh1IsLXwaHWU21neVqb5LkzlIeC5KYTGr0/edit#gid=0

(d) Explanation of what you completed.

For Independent Component 2, I expanded Jason Internet Information System (JIIS) to include the use of the Oracle database as well as additional functions, as specified by my mentor's service requests, spanning over three phases. The first phase involved setting up a database table, named JIIS_User_Profiles, using the PL/SQL Developer so that the JIIS Web Application can have access to the database table to store user ID's, user passwords, first names, last names, phone numbers, security levels, and password status. I also reviewed several sample ColdFusion programs that my mentor gave me to study. Based on what I learned, I used the sample programs as a template to create JIIS programs to connect to the JIIS_User_Profiles database table. I tested the programs and debugged four of them so that they could connect to the database properly. The second phase, I converted my existing JIIS Web Application programs from HTML to ColdFusion so that they could interact with the database. Basically, the task was converting the static web application to become a dynamic web application. Here, I also made the header bar, footer bar, and Cascading Style Sheets (CSS) code, which alters the look and format of the page, into their own, separate programs so that they could be easily changed in the future without having to alter every page that contains them. The third phase involved including addition functionality to the JIIS Web Application. I created entry form programs to allow "Administrator" and "Manager" users to add new user records into the JIIS_User_Profiles table. Users who do not have the "Administrator" or "Manager" security levels will be unable to have access to this form and will get a message stating that the add function is not available to them. In addition, when a record had been successfully added to the JIIS_User_Profiles table, I generated a display message to indicate that it had been added successfully. Next, I created programs that also allows "Administrator" and "Manager" users to view all of the existing user data records that had been saved into the JIIS_User_Profiles database table. Again, users who do not have the "Administrator" or "Manager" security levels will not have access to the view function. Furthermore, I created several programs that would allow "Administrator" and "Manager" users to have the ability to modify, update, and delete user records, as well as have the ability to reset a user's password. First, I modified the view program to add a hyperlink in the User ID column to allow "Administrator" and "Manager" users to select and click on the link to have access to the update entry form and transfer the selected record onto the update form. Second, I created programs that allowed "Administrator" and "Manager" users to modify existing user data records and update it in the database table. The programs also allows them to delete the user's data record from the database table as well as reset the user's password. While developing these programs, I performed an extensive amount of testing to ensure that everything was working, including validation of the user records in the database table.

After development and testing of the web application was completed, I modified the header button images to better reflect the contents of the web application. I also added in comments into the programs to log changes and to describe what the programs do, as I had learned from my mentor before hand. Once this was completed I preformed a final test to ensure all of the the programs were working properly and turned it over to my mentor for acceptance testing. Throughout the development of the JIIS Web Application, I researched web design and read an SQL book.

 
INTERPRETIVE
Defend your work and explain how the significant parts of your component and how it demonstrates 30 hours of work. Provide evidence (photos, transcript, art work, videos, etc) of the 30 hours of work.
Developing JIIS allowed me to understand what it really took to develop a web application for a customer. Each phase of my Independent Component had requirement tasks that I needed to complete by a deadline. I had to exactly meet these needs and had to follow them exactly as they were stated in the service request specification from my customer. The experience I had developing a web application is similar to what people do in the work place. I also dealt with setting up a database, creating a table, and using it.

Phase #1 Work Assignment

Phase #2 Work Assignment

Phase #3 Work Assignment

The JIIS_User_Profiles table and it's contents during developement

The Update form without the Reset Password function

Notification that the user was updated successfully

The View Page and the links to the Update form

The Add Form

Code for the add form that adds the inserted information into the database

Code that makes the actual, visual part of the Add Form

Code that checks to see if the current user has enough administrative rights to access the page

APPLIED
How did the component help you answer your EQ? Please include specific examples to illustrate how it helped.
This component helped me answer my EQ as I was able to see how all three of my answers apply to meeting a customer's needs. My first answer was supported in that I needed extensive knowledge of how to program before actually creating the application. I spent a large amount of time learning how to use SQL code and planning the logical steps of how to achieve my objective. My second answer was supported in that I needed to understand the business side of the application, how it will be used, and the customer's expectations. This included designing the layout of the webpage. Also, if I didn't meet a requirement exactly, my mentor had me correct it. I also had to learn to communicate with my mentor to ensure that I understand all the requirements completely. This, as well as other factors, enforce the need of understanding the business side of the application. My third answer was also supported in that I needed to know how to use tools such as the PL/SQL Developer to create database tables and Adobe Dreamweaver to design and write programs. I also had to learn how to program using the ColdFusion programming language to create a dynamic web application. I learned some of these aspects as I was developing JIIS, but they were all something new to me that I needed to know to be successful in completing this component. This enforced the idea that continuous learning of new skills in technology is essential in creating a web application for a customer.

Extra Blog; HTML to CFM

On April 12th (starting on April 10th), I completed the first and second phase of the Independent Component work assignment. This involved creating a database table for user profiles and converting my previously made HTML programs of JIIS into ColdFusion (CFM) programs.

For the first phase of the independent component, I created a new database table in the PL/SQL Developer and named it "JIIS_User_Profiles". Here, I added in user ID's, user passwords, first names, last names, phone numbers, security levels, and password status. Then, using SQL code, I retrieved information, added new records, updated existing information, and deleted records from the table. I then turned my work assignment over to my mentor to perform acceptance testing for phase 1. Once she completed, I moved on to the next phase.

After using existing FRS ColdFusion programs that were given to me by my mentor that allowed the application to connect to the database tables, I was able to complete this phase 2 swiftly. Some versions of the code weren't working so I had to debug the programs. Some programs worked after renaming the specified table to the one I made in the database. Others were more complicated. One major problem I ran into was updating a user's password from the default password, "12345". At first, I thought that the problem was due to a few lines of code that converted the user ID from lower case characters to upper case characters to check against the user ID's in the database. Since the user ID's were lower case  in the database (an 'e' followed by a number), I decided to remove that part of code. Unfortunately, it was late at the time, and I could not fix the problem.

The next day, I started working on it again. I decided to make the user ID's upper case in the database table and re-entered the lines of code that I deleted the day before. After testing it, the program worked fine and I confirmed that the user's password was updated in the the database table correctly.

Once I fixed this problem, I needed to convert my previously made HTML programs to ColdFusion programs. This part was pretty easy as I had a clear idea of what to do. First, I copied and pasted my HTML programs into the ColdFusion "CFM_Code" folder and changed their file extensions to .cfm. Then, I decided to update the directories of the images and hyperlinks in the programs to match the new locations of the program files. Once I started doing this, I realized I should make two new programs for the header and footer bars so that, instead of altering every hyperlink and directory path of the images repetitively in each program, I could instead just modify them once.

After making separate programs for the header and footer, I altered the "JIIS_Functions" program to match the directory paths in the folders as well. I then entered the <cfinclude> tag into each of the programs where the header and footer were used. After performing unit testing, the result displayed the header and footer correctly.

After converting all the programs from HTML to ColdFusion, I performed the final acceptance test to ensure that all the buttons and hyperlinks worked. After changing some minor bugs, I was finally able to get the program to work completely. I turned over my work assignment to my mentor and she checked all of my programs and performed validation testing to ensure that all the requirements for the second phase of the work assignment were met.

Now that I have completed phase 2, I will be starting on phase 3, the final part of my independent component. It will consist of creating a new entry form that would allow users to add, delete, update, and view user information. 

Extra Blog; Structured Query Language (SQL)

On April 5th, me and my mentor met to learn about creating tables in the Oracle database and using SQL to retrieve, update, add, and delete records from the database. We also briefly went over how to convert my JIIS HTML pages to ColdFusion Pages and how the Financial Reporting System for my mentor's work uses the database. The following is the work assignment that lists out what we did:

First, we went over creating a table in the PL/SQL Developer. Creating a table was pretty straight forward. All we needed to do was right click on the "Tables" Object from the PL/SQL Developer and select "New..." Doing this displays a popup window that lets us define the name of the table, the columns/fields of the table, the datatypes for each column/field, and the primary key for the table. After doing so, the table that we created was listed in the Tables Object in the PL/SQL Developer.

Since there was no data within the table we had just created, we used an existing table to see how to retrieve, add, update, and delete records. We right clicked on a table named "Departments" and selected "Query data." Doing so displayed all the data within the Departments table, as well as showed us the SQL code that retrieved the data. My mentor pointed out that there was a little blue arrow that pointed downwards at the top of the table display. She said that this meant that there was more records than what was currently being displayed on the screen. Upon clicking it, the rest of the data was displayed (after scrolling  a long way down).

The SQL code that displayed was:

select * from Departments

This code retrieved data from the table "Departments" and displayed it on the screen. The select command retrieves and displays the data from the specified column(s). In this case, the columns specified was an asterisk (*). This mean that the indicated column to select was "all" the columns in the table. To indicate which table to select 'from,' the command from, followed by a table name allows the SQL code to know which table to retrieve all the columns from. In this case, the table is "Departments". 

My mentor showed me how, instead of selecting all columns, it is possible to just indicate specific columns from the Departments table to select from. This was done by typing each columns' name in place of the asterisk in the order that we wished to display them, each separated by a comma. Doing so would only display the specified column(s), once the code is executed by clicking the "Execute" button at the upper left corner of the PL/SQL Developer.

Following  the from command, there was more code that could be written. These were the where and the order by commands. First, the where command filtered what was being retrieved. It created a statement 'where' if the condition specified was true for a record, the record would be selected. If a record did not meet the condition, that record would not be retrieved and, therefore, would not be displayed. For example:

select * from Departments

where ID = '1234'

Doing this will only display the record(s) with a value of '1234' in the ID column/field. To specify multiple conditions, the word and can be placed directly after a condition. For example:

select * from Departments

where CITY = 'Pomona' and STATE = 'California' 

The second additional piece of code was the order by command. This ordered the records by a certain column in ascending order. The first records to be displayed were the records that had no value (null) in the field. Then, the records with a numbers and letters in the specified field would be displayed (ex: 1, 13, 1G, 2, 24, A, A3, AB, B45, BC, BW, C, ect.). For example, the SQL code

order by CITY

displays the records ordered by the CITY field.

After showing me the different pieces of SQL code for retrieving data from a table, my mentor then showed me two different ways to add and delete data from a table. The first way was right clicking on a table, selecting "Edit data," and editing the records from there.

The second way was selecting "Query data" and typing in for update after the initial select command. After running it, the displayed table had a plus and minus button in the menu bar, as well as a "Post changes" button. Clicking the plus button would create a new record row in the table, while selecting a record row and clicking minus would result in deleting the selected record row. Once changes have been made, it is required to click the "Post changes" button to save the changes. To confirm the changes were made successfully, my mentor executes the SQL code again. To delete all the records in a table, we can type the SQL code:

delete from Departments

This delete command can also contain a where command.

In addition to showing me SQL code in the PL/SQL Developer, my mentor also showed me that, for my current HTML programs, I can open them in Adobe Dreamweaver where I would be able to convert them to ColdFusion programs. This is done by changing the file extension from .html to .cfm.

Afterward, my mentor taught me how the FRS Web Application uses a database. She showed me FRS tables, views, and stored procedures. The tables were organized similar to the Department table; they just had different values pertaining to different things. She then showed me existing FRS View Objects, which contained SQL code that contain combinations of tables to be used for specific inquiries. She then showed me Stored Procedures and explained that they were like sub-programs to save multiple SQL statements in a single procedure and to use parameters in conjunction with the SQL statements.

Extra Blog; Databases

On Friday, April 3rd, my mentor and I sat down to install a new Oracle Database and ColdFusion Web Application Development Platform on her computer so that I could work on my Independent Component 2, an extension of JIIS. She created a work assignment for what we were going to do and we followed each step, using past documentation as an example.

The first thing we did was to install an Oracle Database 11g Release 2 64-Bit Server on a server for my mentor's computer to connect to. In doing this, we setup the database name, set up the Administrative password for the database, and the directory in where the database would be saved (D: Drive). Once we finished setting everything up, the database started installing. Due to my mentor's fast computer, the set up took only 30 minutes. She stated that at here work, it took around three hours. After it finished, we also has to set up the passwords for the SYS and SYSTEM Database accounts. The SYS and SYSTEM accounts are the initial accounts that are used to access the database since no users had been created yet.

Next, we setup the Oracle TNSName Network Connection in an Oracle folder under the D: directory. We created backup copies of the existing TNSName Network Connection file and altered the file to connect to the server's IP address. Due to the file being an ORA file type that is not originally designated to be opened by a preset program, we opened the file using notepad. In the file, there was a line that had the text "HOST =" and "PORT =" in separate parenthesis. For "HOST =", the IP Address of the server we were connecting to was required. My mentor stated that typing the IP address directly did not work since the IP address was given a name. However, typing in the name associated with the IP address worked instead. Therefore, we we entered in the name associated with the IP address of the server we were connecting to, rather than the actual numbers of the IP address. As for "PORT = ", the preset port associated with the IP address was typed.

After this was set up, we installed the PL/SQL developer, a tool that would allow us to create, add, update, and delete tables in the database once we connect to the server. This was easy to install; it was just like any other software and was not nearly as difficult as installing the database.

In order to connect to a server, a computer must have a client that can connect to it. Therefore, what we did next was to install the Oracle 11g Release 2 32-Bit Client onto the computer. At first, I was confused with this. I didn't get why we were installing a 32-bit client when associating with a 64-bit software. I wasn't  even sure if that would work. My mentor assured me that it would work with either a  32-bit client- or a 64-bit client.The only reason why we installed the 32-bit version was because of the PL/SQL Developer, a 32-bit piece of software. In order for the PL/SQL Developer tool to work with the data base, it reqires the client to be 32-bit as well.

Next, we tested the SYS account for the PL/SQL developer and created new users that could be used. Upon opening the PL/SQL Developer, we entered in SYS for the Username field, the designated password for the account, the database name, and what to connect to the database as. We connected as SYSDBA. Before connecting, we turned off windows firewall since leave it on can cause some errors in the connection process. After doing all of this, were were able to connect successfully to the database.

We then created new users by right clicking on the User file and clicking "New..." In the popup window, we were required to fill in the username, the password for the account, and the permissions for the account. First, we created an account with the name "MISADMIN" and tested to make sure it was working properly by logging in to the account. Once we confirmed that it was working, we then made 4 other accounts in the same way, but with different permissions.

To fill the database with tables and data records to test and work on, we then transferred test data records from the FRS Test Database by exporting and importing those files. First, we created a folder to designate with as to where the exported data should go to. We then opened cmd.exe while connected to the server the test database was in. We used the command "exp" and filled in necessary information such as the username and password. We also created a .log file to record what is exported. After running the program and waiting for a significant amount of time, we finally ended up with both a .log and a .dmp file. The dump file contained all the data records and tables from the test database.

In order to transfer this file from the test server to the server with which the new database was installed, a tool named Total Commander was used. The servers were already connected to each other so, in Total Commander, all we needed to do was to move the .log and the .dmp files over. In the server with the database that we just installed, we opened cmd.exe again and, instead of typing in "exp" for export, we instead did the "imp" command to import it into the corresponding location.

Next, we needed to install the software that would allow us to actually create web applications. In this case, we used the ColdFusion 9.0 64-Bit Enterprise Server as it could be used with the Oracle Database. In making this work, we set up the Internet Information Services (IIS) and the Web Server IIS. We enabled this through windows by going to the Control Panel and running the server manager. Here, we enabled IIS and allowed for Application Development, IIS 6 management Compatibility, and the FTP Server. After installing the IIS, we tested it to make sure it was working by opening Internet Explorer and typing in "http://localhost." What was display was an image of IIS7.

After this, we installed the ColdFusion 9.0 64-Bit Enterprise Server on the same server as the database. This installation was straight forward. It was like installing any other piece of software with a product key. After filling the key in, marking the corresponding settings, and creating an administrative password, the installation of the ColdFusion 9.0 64-Bit Enterprise Server was complete.

We then setup the final settings for the ColdFusion 9.0 Administrator Configuration Site. We connected to the servers IP address and opened the file under the directory CFIDE/Administrator/Index.cfm. There, we entered in the password and changed the settings for each major component: Server Settings, Data & Services, Debugging & Logging, Server Monitoring, Extensions, Event Gateways, Security, and Packaging & Deployment. Each one of these component consisted of many different sub settings. Once all of these were set properly (which took a long time due to the immense number of settings), the installation of the Oracle Database and ColdFusion Web Application Development Platform was complete.

Overall, this entire process of completing this work assignment took 9.5 hours in total and spanned over 2 days.