Week 4

 Week 4 (5/21-5/27)

Halfway done already? Time sure flies.

Five things I have learned these four weeks of class:

1. Create an ER (Entity Relationship) diagram in MySQL. I have created them in Lucid before, but never in MySQL, and this is a great feature.

2. How to use simple and complex queries

3. How to use the Binary Search Algorithm in Java for OrderedIndex

4. Storage media: Speed, cost, capacity, and volatility, and the types of media: main and flash memory, and magnetic disk

5. Entities, relationships, and attributes (PK and FK) and database design: conceptual, logical, and physical design

Questions I have about databases:

1. When do we implement dotted lines when creating relationships between entities?

2. How efficient is the Boyce-Codd normal form in large datasets and memory?

3. What are some disadvantages of views?

Week 3

 Week 3 (5/14-5/20)

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) ?

In a database, a view is a virtual table based on the result set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.  You can join multiple tables in a view and use the View to present the data as if the data were coming from a single table.

Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

However, views are different from tables in several ways. A view does not store data itself; it only shows data from the underlying tables. Views do not have primary keys or indexes like regular tables do. Also, you cannot insert, update, or delete data through a view unless it is a simple view based on a single table without aggregates or joins. Even when updates are allowed, the changes affect the base table(s), not the view itself.

MySQL views/tables:

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 such as Java.  What features are similar , and which are present in one language but not in the other?  For example,  Java has conditional if statements which are similar to SQL WHERE predicates,  the SELECT clause is similar to a RETURN statement in that it specifies what data or expression values are to be returned in the query result (although it is strange that a statement should specify the RETURN as the first part of a SELECT. 

In my opinion, Java and SQL are very different; one is a programming language, and the other is a query language, both are different types of language. In other words, SQL is a language where you declare what you want, for example, to retrieve data; meanwhile, in Java, we have to specify how to do something step by step. However, they share a few features, like the use of arithmetic and logical operations, the WHERE in SQL, and the IF in Java support conditional logic, and the return statement in Java mimics the SELECT statement in SQL to define the output. Other differences include the loops (for, while), which only Java implements, Java uses classes and OOP, while SQL uses tables, rows, and columns. Also, error handling and syntax are handled differently, in SQL with sets and relations, and Java handles individual objects.

Week 2

 Week 2 (5/7-5/13)

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

An example that was very similar to a few questions in the lab and the homework is:
- List all students who registered for a course before it officially started

So we are comparing the registration_date from the takes table with the start_date from the course tables. These are not keys, we are just comparing dates using <. We are using keys to join (ID, course_id) but the logic is on a non-key comparison, checking if registration_date < start_date

SQL Query:

SELECT s.ID, s.name, c.course_id, c.title, t.reg_date, c.start_date

FROM student s

JOIN takes t ON s.ID = t.ID

JOIN course c ON t.course_id = c.course_id

WHERE t.reg_date < c.start_date;

In student and takes:

student.ID is the primary key in the student table

takes.ID is a foreign key that references student.ID

In course and takes:

course.course_id is the primary key in the course table

takes.course_id is a foreign key that references course.course_id

What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging

I think that SQL is a resourceful language for interacting with relational databases. In most companies with big datasets, using queries is the easiest way to keep data clear and concise, as well as making data analysis much more dynamic and easier to digest. When translating from English questions to SQL, what I find more challenging is ambiguous requirements that don't explain exactly what they are looking for. For instance, recently, instead of in the last 5 days or in the last week... Also, when reading the prompt, sometimes I tend to mix GROUP BY and HAVING in aggregations like average enrolled courses per student, but only for students with > 3 credits. I think practicing more and getting more familiar with different schemas, as well as breaking problems into smaller subqueries, will help me overcome these challenges.

Week 1

 Week 1 (4/28-5/6)

First week in Intro to Database Systems

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

One of the main differences between relational databases and spreadsheets is the rules, such as the data types and relationships between tables, that spreadsheets don't have. Also, databases manage data more securely, allowing many users to access data simultaneously. DB can query SQL to search, join, and analyze data better.

For instance, using Excel to track customer transactions, anyone can type anything without making sure not to leave blanks, misspell, or enter text instead of numbers. Meanwhile, databases preserve rules like ensuring transaction data has a valid date, transaction ID exists in the transaction tables, and the amount is a number instead of text. Relational DB prevents bad data from filtering when big data sets are implemented and keeps everything linked.

2. Installing and configuration a database and learning how to use it is more complicated that just reading and writing data to a file.  What are some important reasons that makes a database a useful investment of time? 

The most important reason a database is a useful time investment is that it prevents mistakes, keeps data concise, and follows rules and constraints to keep all users on the same page in a safe manner. Also, DBs protect data with backups and recovery features, which are extremely helpful when mistakes happen. 

3. What do you want to learn in this course that you think will be useful in your future career? 

This is one of the core classes in CS, at least, for the Data Science field that interests me quite a lot. I want to gain as much knowledge as possible to help me understand how a company's warehouse stores all the data and how it is handled across departments when interning at a company or in the workforce.