CST363 - Final Week

In this database course, I have learned important ideas that help in designing, managing, and improving databases. Looking back at Weeks 1 to 7, the three most important things I learned are how to design a relational database, write SQL queries, and use normalization.

One key thing I learned is relational database design. It is important to set up a database in a way that keeps data organized and avoids mistakes. I learned about entities, attributes, and relationships, and how to use primary and foreign keys. This helped me understand how data is stored in tables and how tables connect to each other. Good database design makes sure data is correct and easy to use.

Another important skill I gained is writing SQL queries. I practiced using SQL commands to get, change, and manage data. Learning to use SELECT, INSERT, UPDATE, and DELETE commands made me more comfortable working with databases. I also learned more advanced SQL features like JOINs, subqueries, and aggregate functions, which help find useful information from large amounts of data. Being able to write good SQL queries is very useful for working with databases.

Normalization is another important idea I learned. Normalization helps keep data clean and avoids repeating the same information. I learned about different normal forms and how to break big tables into smaller ones while keeping connections between them. Using normalization makes databases run better and prevents problems when adding, deleting, or updating data.

This course has given me a strong base in database management. Database is my favorite topic, and this course reinforces my understanding and passion for it. These three main areas are very useful skills for my future projects and career.

 

CST363 - Week 7

We covered two databases: MongoDB and MySQL. Both are used to store and manage data, and they help developers add, update, and remove information. MongoDB is a NoSQL database, which means it does not require a fixed structure. This makes it very flexible and allows you to store data in a free-form way. MySQL, on the other hand, is a relational database that uses tables with a clear structure, making it easier to run complex queries and manage data that is organized in a specific format.

In addition to their differences, both databases share some similar features. They both allow you to work with data in a way that supports modern applications and websites. MongoDB is often chosen when projects need to handle varied or quickly changing data, while MySQL is preferred when the data is predictable and requires strong relationships between different pieces of information. Therefore, the choice depends on the project needs. If we need flexibility and fast development, MongoDB might be a better fit. If we need strict organization and detailed queries, MySQL could be the right option.

CST 363 - Week 6

I learned about using the JDBC API to connect a Spring Boot application with a MySQL database. I used JdbcTemplate to manage database connections and wrote SQL queries using PreparedStatements to prevent errors and avoid security risks. I learned how to insert, update, and retrieve data using JDBC, making sure that all values were set correctly before running the queries. One important thing I worked on was handling auto-generated primary keys after inserting data, which helped me store and manage new records properly.

Debugging SQL errors was a big part of my learning. I fixed issues like "Column index out of range" and "No value specified for parameter" by carefully checking the number of columns returned in queries and making sure all parameters were set. I also learned how to use ResultSet to get data from the database and store it in Java objects. Working with JDBC helped me understand how databases and web applications work together, and I now feel more confident in handling database operations in Java.

CST363 - Week 5

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

Article: "Use the Index Luke" - https://use-the-index-luke.com/sql/anatomy/slow-indexes

 
Based on what I learned from the article above, even though indexes are designed to speed up queries, the author explains that certain situations can make index lookups slower than expected. One reason is that after finding a matching value in the index, the database may need to follow the leaf node chain to check for additional matches. This extra step adds to the lookup time. Another reason is that even after finding matches in the index, the database must still retrieve the actual table data, which is often spread across multiple blocks. This extra work can slow down queries, especially if the index range scan has to read a large portion of the index.

The author also discusses common myths about slow indexes. Some people believe that an index becomes broken or unbalanced, making queries slow, but the real issue is usually how the index is used rather than its structure. The article describes different types of index scans, such as INDEX UNIQUE SCAN, which is fast because it only searches for one result, and INDEX RANGE SCAN, which can be slower because it needs to find multiple matching entries. If a query retrieves many rows and needs to access the table for each one, it can become slow despite using an index. This shows that while indexes are useful, they do not always guarantee fast performance.

CST 363 - Week 4

5 Things I Have Learned So Far:

1. I learned the basic concepts of database, including how database systems work, the purpose of query langues like MySQL, and how database are designed and programmed. These fundamental concepts helped me understand how data is stored and managed efficiently.
   
   
2. I learned about the relational model, SQL, and key principles like tables, data types, primary and foreign keys, and constraints. Understanding how to manage databases through inserting, updating, and deleting rows gave me hands-on experience with relational databases.
   
   
3. I studied different types of SQL queries, including special operators, aggregate functions, and subqueries. I also learned about various types of joins, including left/right joins, self-joins, and cross-joins. which are essential for retrieving data from multiple tables effectively. 
   
   
4. I explored database design, such as entities, relationships, attributes, and how to design a well-structured database. We covered cardinality, strong and weak entities, and normalization techniques, including first, second, third normal forms, and Boyce-Codd normal form (BCNF) to minimize redundancy and improve data consistency. 
   
   
5. I gained knowledge of different storage mechanisms, including table structures and indexing techniques. I learned how single-level and multi-level indexes improve query performance, as well as how partitions help manage large datasets in a database. 

3 Questions I Still Have About Database:

1. Normalization:
   While I understand the basic idea of normalization, I still find it challenging to fully understand the differences between third normal form and Boyce-Codd normal form. I would like to gain more insights about when and why BCNF is necessary in real-world database design.
   
   
2. Indexing in Real-World Application:
   I understand that indexes improve query performance, but I am unsure how many indexes should be created in a real-world database project. I would like to know how to determine the right balance between performance improvement and storage overhead.
   
   
3. Table Design and Query Efficiency:
   I have learned that breaking data into smaller tables and using indexes improves efficiency, but I also heard that excessive joins can negatively impact performance. I want to understand how to find the best balance between normalization, indexing and query performance, especially in large-scale applications.