Week Twenty-Four - CST 363 - Database Systems

In your own words what is a data warehouse and why is it important?

A data warehouse is a system that stores data from a company’s operational databases as well as external sources. Data warehousing is an increasingly important business intelligence tool, allowing organizations to ensure consistency, make better business decisions, and improve their bottom line.

Briefly name and describe 3 things that you learned in this course that you think will be important in your future career.

1. A basic understanding of how computer memory.

2. A basic understanding of how to build efficient databases.

3. How to communicate with databases using SQL.

Week Twenty-Two - CST 363 - Database Systems

In what situations would an index be helpful?  Not helpful?

Indexing makes columns faster to query by creating pointers to where data is stored within a database. With an index we can locate a few rows in a large table without doing a linear scan of the table file. However, in smaller tables, using and index is usually not necessary. Also, if many/most rows in a table will be returned, using an index can actually slow you down. In this case, a linear case may be faster.

What does consistency (the C in ACID) mean to you?

Consistency means that integrity constraints must be maintained so that the database is consistent before and after the transaction.

What are the 4 isolation levels and what do they mean?

Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus, it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.

Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update, or delete these rows, consequently it avoids non-repeatable read.

Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

Week Twenty-One - CST 363 - Database Systems

Someone described normalization of a DB design as "a column depends on the key, the whole key, and nothing but the key, so help me Codd."  Explain in your words what 3rd normal form is and why it is important.

Each normal form builds upon the one before. Third normal form introduces non-transitive dependency. In other words, all non-primary fields should be dependent on the primary key. This plays an important role in maintaining data integrity. When duplicated data changes, there is a risk of updating only some of the data, especially if it is spread out in many different places in the database. We avoid this error by storing data only once in the proper table, and then referencing it as needed.

If an index will speed up data retrieval, why is it not a good idea to index every column? 

If indexes are not properly created, the server may have to go through more records to retrieve the data requested by a query. This uses more hardware resources (processor, memory, disk, and network) and takes more time. A wrong index can be an index created on a column that doesn’t provide easier data manipulation or an index created on multiple columns which instead of speeding up queries, slows them down.

What is an SQL view and why is it useful?

A view is a virtual table, defined by a query, that does not exist until it is invoked by name in an SQL statement. One important use of views is security. Views can be made accessible to users while the underlying tables are not. This allows database administrators to give users only the data they need, while protecting other data in the same table.

Week Twenty - CST 363 - Database Systems

When coding a Java program that will perform a SELECT statement that return multiple rows,  what are the steps needed?  The first is to make a connection to the database and the last is close the connection.  What are the other steps?

1. Make a connection to the database
2. Create a PrepatedStatement object with the SQL SELECT statement
3. Set the parameters for the PrepatedStatement object with your Java variables
4. Execute the statement using a ResultSet object and the executeQuery() method - executeUpdate(), or execute() can be used for other types of SQL statements.
5. Process the ResulSet.
6. Close the connection.

What is a parameterized SQL statement? 

A parameterized query (also known as a prepared statement) is a means of pre-compiling a SQL statement so that all you need to supply are the "parameters" (think "variables") that need to be inserted into the statement for it to be executed. It's commonly used as a means of preventing SQL injection attacks.

Do a google search for "SQL injection attack".   What is an "injection attack" and how do parameterized statements help to prevent such security attacks?

SQL injection, also known as SQLI, is a common attack vector that uses malicious SQL code for backend database manipulation to access information that was not intended to be displayed. 

The root of the SQL injection problem is in the mixing of the code and the data. With prepared statements the data coming in from the user is treated as data - and there is no way it can be intermixed with your application SQL and/or be interpreted as SQL. The prepared statements "prepare" the SQL query first to find an efficient query plan and user data is guaranteed to never be executed.

Week Nineteen - CST 363 - Database Systems

1.  What do you think is the most useful feature in SQL and why?  Grouping, Joins, Sub-selects, something else? 

I’ve only been introduced to this software within the past few weeks so its hard to say. Joins are undoubtedly a fundamental aspect of working with databases and it would be hard to imagine a functional and efficient database software without some variant of the join operation.

2.  What was the most difficult aspect of SQL language to learn? What makes it difficult?

Great question. So far, I have found that each individual function in SQL is simple enough to learn. The tricky part seems to be pulling it all together to form a complex select statement that solves the problem.

3.  Can you think of a question you might want to ask of a database that would be difficult or impossible to do in SQL?  

The views in SQL have limited functionality. It is generally not possible to use the ‘order by’ clause in a view.

Week Eighteen - CST 363 - Database Systems

Prompt

SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).  As long the column(s) are type compatible.   98% of the time the join uses primary key of one table, foreign key of the other table and equal predicate.  Think of example where joining on something other than keys would be needed.  Write the query both as English sentence and SQL.  If you can't think of your own example, search the internet for an example.

Response

One example of such a query could be utilized in online retail to determine which customers made an order within the first day of creating their online account. The customer table would have an account_created_date field and the orders table would have an order_date field. Comparing these non-primary keys would give us this bit of information.

SELECT customer_name

FROM customers c JOIN orders o

              ON c.account_created_date = o.order_date;

Week Seventeen - CST 363 - Database Systems

Relational tables and spreadsheets look similar with both having rows and columns.  What are some important differences between the two?

Spreadsheets are a simplified version of a relational database. One important difference between database tables and spreadsheets is that, unlike spreadsheets, databases are relational, meaning that data between tables can be linked and cross-referenced. Additionally, databases have more efficient built-in mechanisms for additions, deletions, and sorting.

What are some important reasons for using a database rather than just using files?  (from orientation slides)

As mentioned above, databases are relational which gives the user more flexibility when for links and cross-references. Also, databases allow for quick and easy additions, deletions, and sorting.

An SQL select statements has SELECT, FROM, WHERE, ORDER BY, LIMIT parts.  Which are optional and which are required?

Only the SELECT clause is truly required. Generally, the FROM clause is an important piece as well, but there are cases where it is not required.

Week Sixteen - CST 338 - Software Design

They say hindsight is 20/20, what advice would you give to the next cohort regarding this course?

I’d give over the same advice I received. Expect to work hard and learn a lot!

How much do you envision using the skills you gained in this course in your career?

I think software design is the basis of every area in computer science so I’m sure these skills will be utilized daily in the course of my career.

Good job this session!  You are well on your way to finishing your degree!
Update your learning journal from what you experienced this week with class. 

What a class this has been. This was a challenging class and there were a lot of other things going on at the time which made focusing on college an extra challenge. Still, I put so many hours into learning this material and I know the skills I’ve gained in the past few weeks will serve as the foundation of my education and career.

Week Fifteen - CST 338 - Software Design

Have you ever created an app before, either for Android or iPhone?  How did it go?

Nope – never created one before but really enjoyed my first stint in app creation and development. It was cool to see how java code is used in android studio and android app creation.

Do you have any ideas for apps that you could create for recreation?  What about business?

I was just thinking of how to go about creating an app to log your diet or calculate BMI or other basic calculations relating to nutrition. I know there plenty of similar software exists though, so I don’t think there is need for such an app. I would be interested in developing something like that just for educational purposes.

Update your learning journal from what you experienced this week with class. 

After a few weeks of intense work, this material was a fun and cool change! I really enjoyed exploring the creation and development of apps and am interested in learning more about the multitude of available features in Android Studio and other IDEs.

Week Fourteen - CST 338 - Software Design

What experience do you have with UML diagrams?

I’ve only worked with UML in the assignments from the last 2 weeks. They are a cool representation of your program and good to use if you are inheriting methods from other classes. They are helpful but I find they are a pain to create.

Are they used at your workplace?

No – I am actually working in education right now so we wouldn’t use UMLs there. I do use other types of diagrams to facilitate learning in the classroom.

Have you done multithreading in any language before?

Nope – this was my introduction to multithreading. I did not create that part of this assignment so I plan to review other examples of multithreading someday when I have an extra minute.

Did you find Patterns to be helpful? 

Yes, course. It helps organize your code in a very meaningful way. The biggest obstacle to deal with is to access variable of other classes in Model, View, or Controller. That is something I am still working through.

Update your learning journal from what you experienced this week with class. 

It seems that each week just gets harder. This week’s reading was clear but when it came to building the program I was really lost. This is also a hectic time for the world and for my family so at the moment I’m not sure things will work out. I may need to get an extension on this week’s assignment but I’m not sure how that will affect the rest of my group.

Week Thirteen - CST 338 - Software Design

GUI is learned late in the game with Java.  Do you think this is helpful or harmful and why?

I think it’s helpful. It’s good to have a comprehensive education of classes and methods, before moving on to GUI programs.

Why do think that coding the Swing elements in Java would be more difficult compared to some other languages?

I don’t really know other languages. Thus far, I have only worked with Java. It was pretty difficult to build this GUI program in Java, though, so I’m glad to hear that other languages may have smoother solutions.

Update your learning journal from what you experienced this week with class. 

This week was intense. The learning was interesting to read about but very very difficult to put into practice. I believe it will take some time before I am proficient at building such programs. Also, with the current pandemic and other personal things going on at the moment, I do not have as much time as I would like to devote to college. I feel like I’m working super hard though!

Week Twelve - CST 338 - Software Design

Give an example of polymorphism and what would be inherited between classes.

Polymorphism allows the user to make changes in the method definition for derived classes and to have those changes override the software written in the base class. For example a display method in a derived class can be defined specific to its needs and variables regardless of the display definition in the base class.

After the learning this week, can you identify the difference between Abstract classes and Interfaces?  Be specific.

An interface is a set of method headings that must be defined in any class that implements it. It has no method definitions, only headings. On the other hand, an abstract class is a group of defined and undefined methods to be inherited by descendant classes.

Reflections on Week Twelve

With the current COVID-19 crisis, this has been a hectic week. The kids are home and off-schedule which makes it difficult to work until later in the night. Thankfully, I am a great night worker, so while inconvenient, this can be managed. Also, my job has become very part-time which gives me more time to help at home and spend on school. The Optical Barcode Reader assignment was intense but I didn’t have the hardest part. I want to go over the section of the assignment that I did not write so that I still have an idea of how that class works. I’m curious to see how I will do on the mid-term.

Week Eleven - CST 338 - Software Design

What is the largest multi-dimensional array that you have used?

At this point, I have only dealt with basic level programming. I haven’t worked multi-dimensional arrays in the past.

What is the largest that you think would be unwieldy and why?

With my limited understanding of arrays, they seem to be systematic, efficient, and predictable storages of data. With that in mind, I don’t see why the size of an array should affect its efficiency. If all the data types are the same, I don’t see a reason to limit input. Of course, this attitude may change once I gain more experience working with arrays.

What does inheritance do for your classes?  Have you used it in the past? How?

Inheritance makes classes reusable. Instead of repeating code in multiple classes we can create one super class with all the generic data and then create smaller subclasses with the added code specific to that object subtype. I have never used inheritance before but I’m looking forward to learning more about it in the coming weeks.

Reflections on Week Eleven

This week I experienced a lot of confusion and intense work. The Deck of Cards assignment was detailed and I found I had to do a lot of outside research. I’m glad it was a group assignment, so I only had to build one part of the program. It’s hard to believe that programs like these will one day be easy and quick to build. I sure hope that reality comes soon!