Posts

Week 24

-
 Briefly summarize the what you consider to be the three (3) most important things you learned in this course. The first most important thing I learned is SQL & Relational Databases. Mastering core SQL operations like select, insert, update, delete, and relational concepts like primary/foreign keys. The second most important thing I learned in Transaction Management & NoSQL. Understanding ACID transactions, concurrency control, and NoSQL systems like MongoDB. The third most important thing is SQL proficiency. Mastering complex queries like joins, subqueries, aggregates, and managing database structures like tables, constraints, and keys.
Post a Comment
Read more

Week 23

-
Compare MongoDB with MySQL. What are some similarities? They are both open source, run on multiple OS, support indexing, and support many programming languages. What are some differences? MySQL uses tables with rows and columns, and MongoDB uses JSON-like documents. MySQL uses enforced schema, while MongoDB is schemaless. MySQL supports joins and prefers vertical scaling. While MongoDB doesn't and prefers horizontal scaling. When would you choose one over the other?  Choose MySQL when you need structured data with well-defined relationships, rely heavily on joins, foreign keys, and/or complex SQL queries. MongoDB is better when data is unstructured, nested, or constantly evolving. If you're dealing with large-scale data and need horizontal scalability. 
Post a Comment
Read more

Week 22

-
Summarize what you have learned this week.        This week I learned about database programming concepts like Java's Connector/J (JDBC) API for interacting with MySQL. Like establishing connections using DriverManager.getConnection(), executing queries with Statement and PreparedStatement objects. Also embedded SQL in older languages like C, which use precompilers and shared variables. I also covered stored procedure calls with CallableStatement. Mostly focused on learning syntax, error handling, and security considerations.
Post a Comment
Read more

Week 21

-
If indexes are supposed to speed up performance of query,  what does the author mean by a slow index?    https://use-the-index-luke.com/sql/anatomy/slow-indexes What the author, Markus Winand, means by a slow index is that indexes are designed to speed up queries by organizing data in a search-optimized structure. However, they don't always guarantee a fast performance. This can be due to scanning leaf node chains to find all matches and fetching table data for each marked row, often involving slow random I/O. Indexes don't always guarantee fast approaches, due to the cost of large/scattered data. As for myself, I've been trying to read every day after work. Some of the readings were a little harder to retain than others, but I've been trying to remain diligent. I've gotten a better grasp on databases and stuff like deadlocks. Just gotta keep at it. Links to an external site.
Post a Comment
Read more

Week 20

-
 Briefly summarize 5 things what you have learned in the course so far.   1. I've learned about database systems and basics, concepts and designs of a database and how it stores and manages data. 2. Relational model and how data is organized in tables, select, insert, update, and delete rows. 3. Keys and integrity constraints, primary keys, foreign keys, referential integrity, etc.  4. Complex queries including joins, subqueries, and aggregate functions. 5. Database design and structure, including database design principles, storage media, table structures, and indexing methods. List at least 3 questions you still have about databases. 1. We skipped chapter 3.9, so what is the full scope and application of relational algebra in database querying? 2. What are the best practices for designing complex queries to optimize performance while maintaining readability? 3. What is cardinality in database design, and how does understanding cardinality impact the way you model r...
Post a Comment
Read more

Week 19

-
 What is an SQL view.  How is it similar to a table? In what ways is it different (think about primary keys,  insert, update, delete operations) ?      An SQL view is a virtual table based on the results of a select query. It doesn't store the data but presents data from one or more tables. It is similar in that you can query a view using select like a regular table, it has columns/rows, and you can join, filter, or sort a view like a table. It is different in that it does not store data, just saves a query. It cannot define primary keys. Insert/Update/Delete not allowed. And cannot be indexed. We have completed our study of SQL for this course.  This is not to imply that we have studied everything in the language.  There are many specialized features such as calculating rolling averages, query of spatial data (data with latitude and longitude) coordinates, and more. But take a minute to think about how SQL compares to other programming languages ...
Post a Comment
Read more

Week 18

-
 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.   Think of example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL.  If you can't think of your own example, search the textbook or internet for an example. A scenario where keys wouldn't be necessary is finding pairs of instructors and students where the student's total credits are greater than the instructor's years of service. "List instructors and students where the student's total credits exceed the instructor's years of service." SELECT      i.name AS instructor_name,     s.name AS student_name,     i.years_of_service,     s.tot_cred FROM instructor i JOIN student s ON s.tot_cred > i.years_of_service;  -- Join on inequality What is your opinion of S...
Post a Comment
Read more