CST334 - Week 1

 

CST334 – Operating Systems

In week 1, I am working on a comprehensive review of the C-language syntax and functions. The C programming language is similar to C++, which I am familiar with. The first lab is beneficial for learning about shell debugging and using the GDB project debugger. Tracing the memory allocation issue was a great hands-on experience and reminded me of debugging in assembly language. For the project, I am learning how to code in C and use unit tests to verify that functions are working accordingly. I also had an opportunity to review the presented information regarding Unix and Linux.  By using Windows PowerShell, I tried some of the Linux shell commands to familiarize myself with them.

Additionally, I am learning about operating systems and how physical resources are managed through virtualization. The virtualizing of the CPU allows many programs to run at once, controlled by the resource manager and the OS policy. The OS manages the physical memory as a shred resource, allocating virtual address space for every process. In reviewing the provided material regarding systems architecture, the two main tasks of operating systems are abstraction layer and resource manager. In the abstraction layers, the operating system runs in Kernel mode to protect and allow the software to access the hardware directly. In contrast, the user mode prohibits direct access to hardware and isolates the sharing of memory, allowing programs running on user mode exclusive access to address space. Each layer in the system architect provides Application Program Interfaces (APIs) to access abstractions. Moreover, system calls control the transition from user to kernel mode, allowing programs to run without compromising the concept of abstraction.

 

CST363 - Week 7

CST 363 Introduction to Database

In the previous weeks, the modules covered relational databases and the use of structured data. In Week 7, the course introduces the application and characteristics of non-relational databases. While MySQL is a database management system for relational databases, MongoDB is the leading NoSQL database platform for the non-relational databases. There are similarities between MongoDB and MySQL; both offer query languages to manipulate data to insert, retrieve, and update. Both offer scalability, although MongoDB supports sharding for horizontal scaling, while MySQL offers vertical scaling wit some chances of scaling horizontally.

The two systems have major differences in how data is stored. As a relational structured database, MySQL enforces a schema in support of transactional data. The data is stored in tables with columns and rows that strictly adhere to the relational data structure model. Logical constraints like primary key uniqueness, no duplicate rows, and unique column names are fundamental to MySQL's relational structure. Meanwhile, MongoDB doesn’t require a structured schema, providing more flexibility for data growth. Documents are stored in collections in a binary format called BSON.

The choice to use one over the other relies on several factors. For transactional data that requires controlled data consistency, MySQL and relational architecture provide ACID properties that ensure reliability. On the other hand, MongoDB meets the requirements for high-volume and high-rate-of-change databases like big data. Overall, each system offers unique advantages geared towards different needs.

CST363 - Week 6

  CST 363 Introduction to Database

In week 6, the focus is on database programming. In this part of the course, I am learning about the difference between imperative and declarative languages and how to combine both to close the gaps in database programming. Syntax and paradigm gaps exist in database programming, and embedded SQL, procedural SQ, and API (application programming interface) are three techniques that offer solutions to syntax and paradigm gaps.

The embedded SQL technique is used in a host language and begins with the keyword EXEC SQL followed by the SQL statement. The compiler translates the SQL statements to the host language and then to an executable program. For the embedded SQL, establishing a connection between the host and the database is necessary to run queries. The connections within embedded SQL are managed by the three steps: 1- defining the connection name and login credentials, 2- set the connection to the database, and 3- terminate the connection and release any computing resources.

I also learned about the Java Database Connectivity or JDBC. The connection interface is created by calling the DriverManager.getConnection() method and passing the database information and login credentials as a parameter. The Statement interface is used for SQL query execution. A Statement object is created using createStatement() from the connection interface. The ResultSet interface retrieves the query results by returning a ResultSet object. PreparedStatement interface uses the prepareStatement() method in the connection interface by assigning a query. The PreparedStatement.executeQuery() prevents SQL injection attacks.

The most common technique is the API, which is a library of classes or procedures that connects the application programming language to a service host. One example of these libraries is JDBC, which contains the Java classes required to access the database. Most APIs can manage connections, prepare queries, execute queries for single and multiple rows, and call the stored procedure.

CST363 - Week 5

  CST 363 Introduction to Database

If indexes are supposed to speed up performance of query,  what does the author mean by a slow index? 

In the article Slow Indexes, Part I, author Markus Winand points out the effectiveness of rebuilding the index to increase performance. He explains that the use of a self-balanced B-tree prevents the deep growth of a large index. Most of what a rebuild could do is reduce the leaf nodes. Reducing 20% to 30% of leaf nodes does not reduce the depth of an index, which in return helps with only a 0%—2% reduction in a case like INDEX UNIQUE SCAN.

The index depth of millions of records in a B-tree is only four or five. Hence, a  B-tree traversal is very efficient in finding a leaf node. According to the author, tree traverses are believed to be the culprit of slow indexes, but in reality, the leaf node chain leads to slow queries.  

The speed of an index is dependent on the scan operation. To illustrate this, Winand explains the following three different combinations of the tree traversal only and tree traversal with leaf node chain:

•      INDEX UNIQUE SCAN: the lookup operation is limited to finding the unique value. The lookup stops when the unique value is found, making this index the fastest.
•      INDEX RANGE SCAN: the lookup is somewhat ambiguous as performance depends on the count of the matching entries. The smaller the index to read, the faster the performance.
•      TABLE ACCESS BY INDEX ROWID: The additional steps to access the table rows to find a match come at a high cost and are potentially slow if the number of matched entries is high.

The point is that the index's performance depends on how the database uses the index.

 

CST363 - Week 4

  CST 363 Introduction to Database

1-      Five things I have learned in the course so far.
I have been learning about database systems for the past four weeks, focusing on relational model design and rules. The course highlights the advantages of using database systems compared to saving and retrieving data from file systems. Moreover, I have been practicing commands for Structured Query Language, or SQL, to create statements for inserting, deleting, retrieving, and updating data from the database. I also learned about the data structure and how relational data is organized in tables, columns, and rows. This week, I am studying the three phases of database design: analysis, logical design, and physical design. The Java labs are also helpful in understanding the Schema creation and how to represent data and improve performance by creating data indexes.

2-      Three questions I still have about databases.
How is data integrity thoroughly tested in large databases?
How can data retrieval performance be improved? This seems to be a very common challenge with large databases in virtual environments.
In what ways is the data modeled in the NoSQL database, and how does it work with NoSQL management systems?

CST363 - Week 3

 CST 363 Introduction to Database

The third normal form of data ensures data consistency across all tables. First, the data must be in a second normal form. The columns in this format must not have a transitive dependency among non-key attributes. For example, if we have three columns in one table called A, B, and C. If column A is a primary key and column B depends on column A , but column C depends on column B and not column A, this form is a violation of the third normal form. The solution is to create another table for the direct dependent attributes. In other words, "a non-key column depends on the key, the whole key, and nothing but the key, so help me Codd."

An SQL view is the restructure of table columns without changing the design. The view query is created by a statement called ‘Create View’ followed by a user-defined name of the view. The outcome of the query is another table that is different from the base table. SQL view is used in combination with SQL queries for different purposes. The advantages of SQL view are producing a table that masks sensitive data from views, generating faster results with optimal queries, and simplifying the writing of complex queries.

Unlike data tables, SQL view is usually not stored like a base table. Instead, the view query is executed with the statement.

1

CST363 - Week 2

CST 363 Introduction to Database

1-     SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ). Most of the time the join will use equality between a primary and foreign key.   

One example of SQL-JOIN other than keys are the non-equijoin, cross-join, and self-join. In many cases these types of joins are helpful in tracking objects quantities, benchmarking, threshold control, or obtaining statistical data.

Take a look at this scenario. Imagine a retail store management wants to understand the effectiveness of a marketing campaign that offers discounts during specific months. The SQL join returns any month where the sales profit is larger than regular full price profit (Finance.Profit)

SELECT Month, Discount, Finance.Profit, Sales.SalesProfit

FROM Finance

Left Join Sales

ON SalesProfit > Finance.Profit;

2-In my opinion, SQL is easy to learn and use. The simplicity of its syntax and language flexibility provide a powerful avenue for retrieving meaningful data. One aspect that I sometimes find challenging to translate from English to SQL is the “most of” or “least of” in a multi-JOIN query.