Friday, June 17, 2016

SQL Final Project

Our team decided to design and build a music streaming database for our final project in SQL, something similar to Spotify and consisting of artist, album, subscriber, playlist, etc. entities and their corresponding relationships and attributes.  Both the processes of working with the team and reviewing the many lessons and concepts we've learned over the course, thereby, proved to be educational and, also, enjoyable.  One of the biggest challenges I've faced working with a team in the classroom setting has been the lack of both managerial direction and subsequent communication to fill that void.  When time constraints exist, duplicating work and comparing output tends to be a luxury for which there is insufficient resource.  Organization becomes critical in planning and delineating responsibilities, as does a sense of urgency and timeliness in executing on the project, a challenge given diverse schedules and commitments outside the program.  I think it must be somewhat similar to the workplace environment, obviously, absent the manager, since we are all colleagues here of equal footing, and these are typical challenges any group and group project must face.  But I just think there was significant improvement, this time, in all these respects, and I feel very good about our working situation, which is very pleasant.  Working with a team benefitted me in that I gained greater clarity over, understanding of, SQL rules, best practices, code and concepts, such as those in database modeling and writing the SQL commands, sometimes via some rigorous, constructive discussion/debate.  Each team member contributed a great deal and brought their strengths/knowledge for the benefit of the project.  We wish we had more time to finish the final and add additional code to showcase what we've learned, given the deadline was moved forward, but all aspects of the spec. have been addressed, and it's satisfactory to submit on time.  I've read that, in the professional workplace, this is also an issue, where one doesn't want to complete a project because one feels there is still more to be done; yet, it's best to just deliver it, to some extent.

Tuesday, June 14, 2016

Developing and Refining my Understanding of some more Functions and Operations

Some of the lessons this week involved processing and formatting text using various functions, such as concatenation, which has a rather strange | | syntax, upper/lower, substring, to_char, year, month and other date/time related functions.  Additionally, we've explored creating sequences for automatically generated, consecutive/numerical (datatype is object) identity attributes, which comes in handy with table entities that lack good, natural candidate keys.  The create view operation is also handy for generating a storable, virtual tabular report based on a more lengthy select query that can be re-run based, with the select operator, on its given variable name alone.

I've also learned that SQL does not support conditional or looping operations like other programming languages; however, persistent stored modules (PSMs), blocks of code containing standard statements, can be saved and run.  MS SQL uses Procedural Language SQL (PL/SQL) to store and run or anonymously run such procedural code traditionally used in programming within the database.  The blocks can use DECLARE or BEGIN/END at each end.  The former can be saved with a variable name, which is convenient for business operations.  Similarly, stored procedures and triggers can automatically invoke such procedural code blocks.  With respect to triggers, the text gives one example of the updating of a product inventory number upon a sale and the flagging of inventory levels when quantity falls below a certain number, so items can be re-ordered.  (These are not things one should have to remember to periodically do.)  The syntax involves that mentioned just above plus timing and event indications.  Otherwise, as throughout, I've tried to focus, when in situations where multiple solutions exist, to use the most efficient or precise command or function to achieve the desired result.

Tuesday, June 7, 2016

Penultimate SQL Lesson before Finals Week

This week's assignments involving SQL Plus have been enjoyable, as it gives me more pleasure to get into the tool and interact with it than design diagrams, although that's not to say that the latter hasn't been important in learning database modeling and design fundamentals.  In SQL Plus, we're continuing to spool our file runs, and now applying SQL aggregate commands, as well as Oracle SQL commands to prompt input requests and accept them.  Further, we're compounding queries to make subqueries based on the one directly prior.