Writing Homework Help

Discussion 1: Moses

A NULL value indicates a lack of a value, or an unknown value which is not the same thing as a value of zero. It is a good idea to always assume there might be NULL data in your records in the database. NULL value can have different meaning depending on situation. For example, if we are recording the hours worked by an employee and there are no hours then the data would contain a NULL because there are no hours to record.

While dealing with integers it is a good practice to write some extra code to ensure that NULL values have a user defined value, so the audience is not confused when they encounter a NULL value in the data. Extra coding may be worthwhile as it will allow the user to perform different kinds of operations at once. Adoption of best practices of installing controls on the dataset that would prevent such trend in the dataset mostly on very key columns, and, enhancing constraints on the table level on key columns to ensure that NULLS are not accepted. The extra code leads to various actions taking place once the code is created, the use of IS NULL or IS NOT NULL operators to check for a NULL value encouraged.

We should not only do the extra work involved in this process of ascertaining if there is a NULL value in the dataset or when there is an issue because it ought to be total routine to ensure the completeness of data, and that the data integrity of the dataset is always retained. The NULL value can cause problems when selecting data. However, because when comparing an unknown value to any other value, the result is always unknown and not included in the results.

Reference:

https://www.tutorialspoint.com/sql/sql-null-values…

Discussion 2: Mentor

In relational database systems, a null represents missing or unknown information at the column level. A null is not the same as 0 (zero) or blank. Null means no entry has been made for the column and it implies that the value is either unknown or inapplicable. Hoffer et al., (2015) described null values as a field with no values. A field with a NULL value is one that has been left blank during record creation. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. It is not possible to test for NULL values with comparison operators such as =, <, or <>. However, we will have to use the IS NULL and IS NOT NULL operators instead. The IS NULL operator is used to test for empty values (NULL values). The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. We use aggregate functions as expressions only in the situations the select list of a SELECT statement and a HAVING clause. The most popular aggregate functions are COUNT, SUM, MAX, MIN and AVG. Extra coding may be worthwhile due to data accuracy and integrity. With extra coding we can understand what fields are null and not null in order to draw a business insight and reporting accurately. It is very importance for data analyst to understand the kind of data they working with on daily basis. This will give a competitive edge when troubleshooting, debugging, reporting, analyzing and also evaluation. Sometimes this involves doing extra work in process of writing alternative codes for evaluations.

Example using the Adventurework2014, Select Count(*) from sales.Customer; returns 19820 rows. And Select Count(PersonID) from sales.Customer: returns 19119 rows. The major different is Count(PersonID) does not count the null values in the field while Count(*) will count all records. Also, from a performance stand point, comparing the execution plans for Count(*) cost 0.062 whereas Count(PersonID) is 0.125. We can observe that it will take twice amount of time and cost for SQL server to perform. Nulls are clearly one of the most misunderstood features of SQL database systems development. Although nulls can be confusing, we cannot ignore nulls. Understanding what nulls are, and how best to use them, can help you to create usable databases and design useful and correct queries in your database applications.

Reference

Hoffer, J., Venkataraman, R., & Topi, H. (2015). Modern database management (12th ed.). Prentice Hall.

https://www.youtube.com/watch?v=_73WHm4lRF0

Discussion 3: Alma

One common misconception about NULL values is that they represent “nothing” or “no value.” NULLs are indeed a value. They take up space on your database hard drive as opposed to “nothing” that indicates there is no value. It’s common for new database developers to think that they are saving hard drive space by adding NULL values, but this isn’t necessarily the case.

NULL values are used to indicate that you could have a value, but you don’t know what that value should be yet. They are placeholders until you finally collect the data needed to fill the table field with a real value.

You should never confuse NULL values for zeros or blank strings. This is another misconception. When you see a NULL value in a table, it means that you don’t know the value yet. When you see a zero value, it means that the total or integer value is supposed to be zero. You know that the value should be zero. Think of NULL as “unknown.”

You can use NULL values for any data type including integers, decimals, strings, or blobs. Even though many database administrators use NULL, they usually demand that NULLs are not used for numeric values. The reason is that NULLs used for numeric values can become confusing when developing code to calculate data.

The Advantages of Using NULL Values

While it seems silly to use NULLs, they actually have a good purpose in relational databases. Every major database vendor on the market allows you to insert NULLs by default including Microsoft SQL Server and MySQL. The databases automatically exclude these values when using internal functions.

For instance, suppose you want to add a list of order totals. You have some orders that haven’t completed, and you choose to use NULL values as a placeholder. The database programmer uses the internal SUM function to add all the totals. The function automatically removes the NULL values and doesn’t count them in the calculation. This is especially important when you use the AVG (average) function with your calculations. The database removes them from the calculation and your average is only the records with values. If you use a zero value, the function would include them in your calculations and skew results.

Programming languages outside of database SQL also have functions that use NULL. For instance, if you program in the Microsoft NET framework or the Linux-based PHP language, you have functions readily available to you that let you evaluate values for NULL and use them to create logical loops and structures. This is common when you have string values that have NULL as a placeholder. When your program sees a NULL value, you know that there is no real value and you can skip the record or display specific text to your users that a value hasn’t been stored yet. The logic you use for NULLs is your decision, but you have readily available functions that make it much easier to identify a placeholder value versus a real value.

If you used zero values, your programs wouldn’t know the difference between a user entering a zero or the database storing zero as a placeholder. This is one reason advocates for NULL database values prefer it.

With programming, having NULLs in your database improves the logic. You don’t need to account for several different possibilities for placeholder values. You just test for NULL and then use your logic to display content to your users. If you had several different placeholders, you’d need to write code for each one.

Another advantage is that databases allow you to automatically enter NULL into a field when no value is available. This means that you don’t need to figure out a value for each placeholder. You just use NULL and every database programmer recognizes it as the default placeholder.

The Disadvantages of NULL

With all of its advantages, NULL also has disadvantages that database administrators use to argue against using NULLs in any table.

The first one is that NULL is considered a variable-length value. This means that it could be a few bytes or several bytes. The database leaves room for extra bytes should the value be larger than what is stored in the field. The result is that your database might take up more hard drive storage space than if you used regular values.

Database administrators also argue that if all values can’t be filled, then a record shouldn’t be created. This argument doesn’t always work for all environments, but the idea is that a record should only be created when all fields have actual values without any placeholders. For instance, you wouldn’t want to allow a bank transaction to happen if you don’t know the amount of the transaction. This standard works in the financial industry, but it doesn’t work well in other industries such as e-commerce or websites that collect user data.

Another disadvantage is in your database programming procedures. While you can use functions that automatically detect NULL values, custom functions must be created to eliminate NULLs. This means that your SQL procedures might be much longer than necessary, and they can be too complex to read. A database administrator will reject code changes if the procedures are too convoluted and messy.

Should You Use NULL Values?

Now that you’ve seen the advantages and disadvantages, you must decide if you’ll allow NULL values. There are several more advantages than disadvantages. Hard drive space is much cheaper than it was a few decades ago, so using more hard drive space isn’t a good argument. Having cleaner code is much better than having complex code, and you can streamline it when you have NULL values as a default.

NULLs are widely recognized by desktop, web, and database programmers. You don’t need to explain the reasons for them like you do with custom placeholders. They are standards in the industry even with the widespread debate.

If you decide to use NULLs, always use them uniformly across all of your database tables. Every table must allow them, and you should never use multiple placeholders in different tables. This can lead to bugs in your applications.

https://doorda.com/the-null-debate-should-you-use-…