CST 363 Week 8

Week 8

This week is our final week. We had a final exam and have been working on a group project, which is to design a database. I've been really happy with my group for this assignment. I felt like everyone came together and really put in a lot of effort. Our database is quite large (11 tables!), but I'm happy with the thought that was put into it. As for the final exam, it was a bit more challenging than I was anticipating and I was pretty discouraged that I didn't do as well as I thought I had.

This course had decent content and it was nice to get to know a new database system (Oracle), but I think this content could have been presented with other databases that are more accessible to everyone, like MySQL or PostgreSQL, since we didn't go into anything that was too Oracle specific and I think that the students could learn to write sequence tables if they need to later on when they need to.

CST 363 Week 7

Week 7

The material this week was pretty easy, at least easy to follow. The string manipulation we did was very similar to string manipulation in other regular programming languages. Most of the links were to regular Oracle database documentation and it's all pretty straight forward there. Overall, it's pretty difficult to read documentation when you aren't looking to do something specific so I had a difficult time staying focused there.

This week we covered the following ideas:

• Creating a sequence table to be used for automatically incrementing values in a table we're already using. The most basic command to get up and running is CREATE TABLE sequenceName. The user utilizes this by calling for the next value, like INSERT INTO tableName VALUES (sequenceName.NextVal, …); (professor's example).
• Creating aliases for columns. The user has the power to change column names, but they can also temporarily display alternate names just for the temporary use of a particular display. This can be done by adding the alias directly after the column name in a select statement or by specifying "as", like SELECT EName as "Student's Name" FROM STUDENT or SELECT ENAME Stu_Name FROM STUDENT. This can also be done for tables, like SELECT * FROM STUDENT s, CLASS c WHERE s.CLASS_ID = c.ID;
• Adding padding to the left or the right of a particular string with "lpad" and "rpad".
• Removing extra whitespace characters to the left and right hand side of a string with "ltrim" and "rtrim".
• Using "substr" to get a subsection of a string.
• Utilizing "sysdate" to show the current date. The sysdate variable can be incremented up and down by the day by just adding/removing an integer, like "sysdate+1" to get tomorrow's date. The date can also be formatted for the column to provide more details as well as compared to other existing dates in the database.

CST 363 Week 6

Week 6

The first section of this week covered SQL queries using group by, having, and order by. Last week I didn't know that the reading for this wasn't supposed to be read yet, so I had already done the reading for these by the time this week rolled around. They're pretty basic and extremely useful, but I honestly haven't used having in my regular work and I can see it being very useful in some cases.

We also covered prompting the user for inputs and saving their inputs to variables for use later in the script. These are done by using the keywords "accept" and "prompt". To get entry from the user, you could do something like:

ACCEPT vUserInput PROMPT 'Enter your input: ';

The variables are accessed by prefixing them with an "&":

PROMPT Your input: &vUserInput 

Finally, we covered sub-queries. They're basically all just a query added into a SQL query in parenthesis instead of using variables, like you might in a regular programming language.

I've enjoyed that this week has been more hands-on. All of our assignments this week have allowed me to utilize the new computer I purchased and also use this Oracle database that many of us worked very hard to get installed.

CST 363 Week 5

Week 5

This week was heavily focused on entity relationships and modeling the relationships using ER diagrams. The two diagrams we learned are the Chen Model and the Crow's Foot Model. I've played around with modeling database systems using the Crow's Foot Model in the past. It was nice to learn all of the additional bits that I didn't know (like a dashed line indicates that a relationship is weak). Also, the Chen Model is interesting to know, too. It provides a very quick overview of a database.

I'm hoping that we can refine these ER diagram skills and get some hands-on practice in the next couple of weeks. I'd like to make use of the computer that I purchased for this course.

Overall, the material this week was dense and I'm still uncertain about how to interpret some of the "business rules" that were provided in class. I think that there are some odds n' ends that could be left up to interpretation, so I'm hoping that it's recognized that way when the homework from this week is graded.

CST 363 Week 4

Week 4

This week was all about normal forms regarding relations in a database. This week was pretty informative for me. I understand most of these aspects and was actually practicing them a lot in my daily life, but I never knew the names for any of these or the defined normal form steps that would help logically break these up. I was just reasoning about it on my own and eventually getting to a databases that are very close to Boyce-Codd normal form (if not, actually in BCNF). We didn't get to the higher normal forms, but hopefully I'll get to dive into that in the future.

For the exercises and assignments this week I utilized the draw functionality in Google Docs and found it extremely useful and will hopefully find more cases to use it in the future. Although, I probably should have started familiarizing myself with Dia. But, I think Dia will be useful for schema diagrams (assuming we're getting there).

The content from this week is exactly the kind of content I was hoping to get out of completing my bachelor's degree. There are so many concepts and theories that I don't feel I was able to fully know from the bootcamp, but this is making me start to feel like I made the right choice in returning to school to get my degree.

Below are the normal forms that we covered and what they indicate for each table.

• First normal form (1NF): A table is in first normal form when there aren't any repeating groups, the keys are defined, and all of the attributes are dependent on the primary key.
• Second normal form (2NF): A table is in second normal form when it meets the requirements of the first normal form and there are no partial dependencies (or no attributes are dependent on a portion of the primary key).
• Third normal form (3NF): A table is in third normal form when it meets the requirements of the second normal form and there are no transitive dependencies (or no non prime attribute depend on another non prime attribute).
• Boyce-Codd normal form (BCNF): A table is in Boyce-Codd normal form when no primary key (or portion of the primary key) depends on any non prime attributes. The book describes this as normal form as "when every determinant in the table is a candidate key."

CST 363 Week 3

Week 3

I'm already pretty familiar with using primary and foreign keys in tables and writing queries on tables with those constraints, so the material this week has been pretty quick for me to get through.

Overall, I've enjoyed this section more than the others up until this point since we've had more hands-on assignments. It's difficult to stay engaged when you're not really trying to accomplish anything. I also enjoyed that we had some examples to work on that didn't have to do with students and classes (they're in every class and they're always the most boring to look at).

We're sending a spool file for credit on our homework and I think that these commands are very useful, so I'll post those here for safe keeping:

-- Display the SQL statement along with the result of
-- the execution within the command line.
set echo on

-- Store the output of running the SQL script to a
-- plain text file in a specified location. The txt
-- file is useful for debugging purposes.
spool C:\Users\{user}\{location}\{file name}.txt

-- INSERT SQL COMMANDS HERE

-- Disable spool (stop printing to specified file).
spool off

CST 363 Week 2

Week 2

I got my new computer running Windows this week (just in time to install Oracle)! I upgraded it to Windows 10 and have played with it a bit to get more familiar with Windows. There were a couple of hiccups in the installation of Oracle, but overall it wasn't too bad once the two installation directories were properly merged after unzipping them (heh).

As for this week's content, it's been pretty basic. We covered relational algebra and most of the basic syntax for SQL statements. There were a couple of pieces that were new to me, such as the "flashback" command that was mentioned in the professor's slides (textbook doesn't cover it). Also, the idea of division of tables required some extra research to understand. I ended up stumbling upon this slideshow that had some good examples that helped me understand it.

The structure of this course has been very easy to follow. I like that each week is broken up into smaller sections. However, I'm finding that I don't like the textbook. The book felt extremely dumbed down in the relational algebra section. I would have appreciated some examples of how to format written relational algebra statements and some explanations of the symbols commonly used would have helped as well.

CST 363 Week 1

Week 1

Our first week of this course has been pretty mild. The assignments opened a week early, so I was able to get a head start. The majority of the material we covered had to do with the pitfalls when data is repeated and the anomalies that occur from that. We also learned the various types of keys in a table (primary, foreign, composite, etc.).

I'm very glad everything opened up early this week. It gives me more time to get familiar with this new course structure. It's also helpful because I also started another course (called Operating Systems) and the junior college near me and I'm very nervous about that one.

This week we were told that we will need a PC to install oracle (PC as in not mac from what I could tell), but I only have apple computers at home. I had considered trying to do something like run a virtual machine on my mac, but I decided that I should just by a machine that I can play with for development purposes. I found a refurbished dell on overstock and ordered that. I'm hoping it will come in time for me to get set up on it and install Oracle.