No Big Deal

1. Database Design

The architecture of a database management system
Let’s go over two focal points of database design: the layout of  a database system and the proper way to design a database for programming.  Please note that every bit of information will not be completely correct at the granular level to facilitate the learning process.

A database system (we will be focusing on Microsoft SQL Server) can be described as a database computing server.  I’ll break down how a database is set up.

A record is a single set of information.  A record could be a single customer, an order line item, a calendar event, or a Facebook wall post.  A record represents the smallest logical grouping of information within a RDBMS (Relational Database Management System).

Similar records (customer records, for this example) are grouped into a table (a table looks similar to an Excel spreadsheet).  Records are rows and have data separated into columns.  Each column has the same type of data.  A customer record may contain a first name, last name, and e-mail address.  All of those columns are text columns.  A customer record may also have an identification number, which would be a number column.

This is a database table

Tables that relate to one another and serve to store data for a single application typically reside on the same database (not to be confused with the entire RDBMS).  A customer relationship management system may have an order, order line item, customer, employee, and inventory tables.  These tables would be grouped into a database, perhaps called CRM.

Multiple databases are thrown together on the same instance.  A SQL Server instance can be considered a virtualized server, enabling multiple instances to operate in parallel.   Why would you use multiple instances?  Instances are crammed onto a SQL Server RDBMS.  A single SQL Server RDBMS is genrally allocated a dedicated computer, meaning nothing else typically operates on that computer (no other applications should use the compter’s resources).

How to design your data in a database
Here are some rules for designing data in your database:
“Each column in a single record explains a fact about the key, the whole key, and nothing but the key, so help me Codd“.

1. A record should have no columns that depend on another column… in a customer record, you should not have a zip code, because a zip code depends on the city and address.  This is called a transitive dependency.  First name and last name directly depend on the customer record, so they are allowed.
2. Each record should be uniquely identified with an identity column.  This identity column will allow you to reference any record without any resultant duplicates.
3. Make a separate table for each set of related columns.  First name, last name, and e-mail address all would go into the customer table.  Product name, description, and price would all go into the inventory table.

2. CRUD

Create - add records to a table

Retrieve - get records from a table

Update - change data in records in a table

Delete - delete records from a table

Now go to W3Schools again, and go through all of the SQL Basic tutorials and complete the test with a 80% or better.

I am currently developing a plan for the Fall for my AITP@VCU student organization to better acquaint students with tacit knowledge of programming.  I’m currently writing the articles that will coincide with each of the topics to be covered.  These posts will be released starting in September.  The topics are listed:

This is an awesome post: http://www.dijksterhuis.org/10-c-coding-shorthands-that-improve-productivity/

If you’re a database expert, skip to part 2!

Part 1: the Basics

In order to be an effective programmer for multi-tiered applications, you’ve got to know the database language: SQL.  SQL is the Structured Query Language.  I recommend that you understand all items under SQL Basic at W3Schools: http://www.w3schools.com/sql/

Understanding databases can be daunting, and it’s probably better to learn SQL and database design separately!

Part 2: Built-in Functionality

Taking advantage of the built-in functionality of Microsoft SQL Server is essential to effective database programming.  Here are some topics you should take a vested interest into understanding:

• NOLOCK and READPAST - execute queries faster and prevent instances of deadlock
• Proper usage of SQL Joins… the best resource for JOINS can be found at: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html.  That’s the best reference for joins that I’ve found.
• Built-in Functions:
  • getdate - Get the current date
  • newid - generate a unique identifier
  • suser_sname - get the user name of who is executing a query
  • substring - get a part of a string
  • convert - convert one datatype to another
  • isnull - provide default values
  • upper/lower - convert text to upper/lower case
  • rtrim/ltrim - remove whitespace surrounding text

There’s more, but that’s more than enough to chew on for now.  I’ll also publish some  functions that I’ve created or found that have assisted me.

I’m currently working on a couple articles:

Pillar of Programming #2: SQL, the database Language and Joins
Pillar of Programming #3: REGEX - How to use Regular Expressions

They should be up soon!

The idea that nested Master pages use principles of inheritance is accurate. The concept of inheritance is simplistic; classes may be parents, or children. Child classes may or may not be able to access elements of the Parent, but in any case, they somehow extend the functionality of the Parent. The same is true of Master pages. Master pages may be parents, or children. Child (nested) Master pages can access some named server elements that belong to the Parent, using the “Parent” property and FindControl method. Child Master pages have direct access to any content placeholders defined by the Master, and in any case, child Master pages extend the functionality of the Parent.

Utilization of principles of inheritance is easy to show, however, Master pages do not employ a traditional relationship of class inheritance. This can be proven by analyzing the class structure of the nested Master page. The class is derived from the System.Web.UI.MasterPage class, not the “Parent” Master page.

The role of the WSDL (Web Services Description Language) and proxy classes are to provide information about how a web service operates, where it operates, and what kind data it communicates.  In any situation, when communication takes place, there are some precedents that must be met in order for communication to happen effectively.  Lingual syntax, definitions, and a proper time/place are all paramount for a conversation to occur.  There is no exception in the case of web services.  The WSDL provides valuable data and metadata that allows for web services to be consumed by applications that wish to tie in.

The WSDL communicates a base set of information (provided in XML format):

• A schema that represents each non-traditional (or custom-programmed) object that the web service may utilize in a result or a call; these are proxy classes
• Web URL(s) or IP addresses that identify where a web service stub resides
• Metadata about web-based programming APIs, functions, or methods that allow for remote programming (via the web service) to be initiated by a client (the web service tie)

Without any one piece of this information, the web service may not be consumed effectively or maybe even at all.

For more information on web services, here are some search keywords: WCF, Windows Communication Foundation, ASP.NET Web Service, WSDL, Web Services Description Language, XML Class Serialization.

More information can be found at: http://www.w3.org/TR/wsdl

In order to effectively program, you’ve got to know how to bend text to your will.   Knowing how to locate points in a string, find/replace, read/parse, reformat, and change string casing is paramount.

Read the rest of this entry »

Many moons ago when I started learning programming, it was a “walk around blindly” affair.  Every skill learned was forged from the fire and the reasons for certain programming methods weren’t well understood.   Efficiency and effectiveness took a back seat to “IT WORKS.”

I’m starting this blog to share some of my skills with you all, so that you can better tackle the titan called programming.  I intend to highlight skills using PHP/MySQL, ASP.NET/C#, and Microsoft SQL Server.  Hold on to your hats, ladies and gentlemen.  In the great words of Rafiki the baboon, “it is time!”