CST 363 Week 8

CST 363 Week 8

The course at times was challenging. This was mostly due to the fact that I am working full time at a bank, have a 2 year old daughter, and am concurrently taking discrete math and computer architecture courses. It feels good to be done with the course. I went from having a basic understanding of what a database is to being able to write in SQL to create and manipulate databases. My team worked well together and we were able to finalize our project of creating a database a day before the due date.

Here is a sample of our database design.

This was a great introduction to databases and I feel like I have just scratched the surface of this field.

CST 363 Week 7

CST 363 Week 7

This week I learned about big data and the new forms of databases that are being implemented for them. I was able to learn about how data marts are used in data warehouses to help process large amounts of data for big corporations.

I also learned how OLAP is implemented to dynamically access data through the use of dimensions. A three dimensional OLAP report was examined.

With this knowledge I was able to create an OLAP report through Excel PivotTables. 

The different types of distributed databases was then explored. These include: 

I learned the difference between scale up (larger processor) vs. scale out(more servers). The No SQL movement and Hadoop were also explored.

Our group was able to complete the database design and add the data to the tables this week.

CST 363 Week 6

CST 363 Week 6

This week I learned about the basic concepts with regards to database security. I learned that permissions are managed using SQL Data Control Language statements GRANT and REVOKE. User accounts were explored and their relation to groups and objects. One very important aspect of database security is that permissions generally aren't assigned to individual users but are instead assigned to groups.

Next I was able to retain the basics behind DBMS security guidelines. They are as follows:

• Run the DMBS behind a firewall
• Apply the latest operating system and DBMS service packs and fixes
• Limit DBMS functionality to needed features
• Protect the computer that runs the DBMS
• Manage accounts and passwords
• Encryption of sensitive data transmitted across the network
• Encryption of sensitive data stored in databases

In general DBMS security features should be used before application level security.

I learned about database recovery via rollback/ rollforward and how it is the preferred method of recovery. We were able to apply this concept through Assignment 11 in MySQL.

I also learned about how to apply user defined functions and triggers in a DBMS.

It would have been nice to spend  a bit more time exploring these concepts as I felt retention of this would come easier with practice but that is why I bought my own books so I may learn on my own.

CST 363 Week 5

CST 363 Week 5

This week I learned about how transactions and ACID properties are used in a DBMS. The acronym ACID stands for Atomic, Consistent, Isolation, and Durable. I learned about a logical unit of work within a transaction.

I then explored concurrency problems that can occur with multiple users updating a database concurrently. These problems include dirty reads, nonrepeatable reads, and phantom reads. It was then learned that locking is a way to avoid concurrency issues. I learned about the various isolation levels for locking and how to avoid a deadlock. These isolation levels in order of the weakest to strongest locking are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. I also learned about the differences between optimistic and pessimistic locking.

SQL views were also explored this week. It was learned that this is a good way to create different views that may be used later within a DBMS. I learned about the properties of SQL views and how to implement the syntax for an SQL view in MySQL. Views may be used to hide data, hide complicated syntax, and ensuring consistent results from multiple developers working on a single database.

CST 363 Week 4

CST 363 Week 4

This week we delved deeper into the design process of database building. I learned about the process of looking for entities is very similar to the process of looking for objects in standard programming. You must look for the nouns that could be entities in the data model. I learned about the difference in weak vs strong entities, how the representation of a weak entity differs from that of a strong entity. The various types of keys where explored and when it is necessary to use them. The concept of referential integrity was learned about when implementing foreign keys in a design.

We then learned about how to create an E-R diagram in MySQL Workbench and how to forward engineer it into a functional .sql script. Conversely, I learned how to reverse engineer a . sql script into an E-R diagram. Through the building of E-R diagrams, I also learned about the implementation of subtypes in database design.

The next step in the design process is the normalize the tables. Without normalization, the tables can experience modification problems. It was also discussed the after normalization comes denormalization as sometimes the process of normalizing can go to far and negatively affect the design of the database.

CST 363 Week 3

CST 363 Week 3

This week I learned more SQL statements and how they relate to other coding functions. I learned about SQL grouping, outer join and sub-selects operations.  I was able to comprehend how a SQL select statement is similar to a loop through an array. I also learned about the various aggregate functions SQL uses such as COUNT(), MIN(), MAX(), SUM(), and AVG(). I am now able to differentiate on when to use a cartesian product join or inner join, a left outer join, and a right outer join. I learned that a subquery is like the inner loop of a nested loop control structure in programming. It is important to know that a DBMS is better at optimizing join operations when compared to subquery operations.

I took my first exam for the course which I felt adequately tested my knowledge on what has been learned so far. Again, practice, practice, practice is how I will get better.

On a side note, I was accepted to volunteer at the Game Developer's Conference and start my path in gaining contacts in the game development industry. I am excited for the opportunity that this will afford me in gaining knowledge on how to better myself to enter this industry.

CST 363 Week 2

CST 363 Week 2

This week I learned about implement SQL and php statements into HTML forms to be able to implement them in web pages. This was a bit of a crash course in HTML as I have only briefly touched on the subject. I learned about the basics of creating an HTML webpage and how to implement php files inside of them. I like how this showed a real world application of using SQL and php.

I also got a refresher on the various data structures that are used in databases such as linked lists and indexes. It was good to refresh my memory on the various indexes such as B-Tree index and Hash index and when it is good to implement each.

It is evident that a lot more practice will be needed in order to be proficient at writing php statements to be implemented in HTML as I have only scratched the surface. This week has laid a good foundation on which to build upon.

I found it uplifting that I was able to help a classmate out on getting the ball rolling with assignment 3.