MySQL - Lesson 06: Assistance With Data Entry

	Assistance With Data Entry

The Uniqueness of Records

Introduction

In a good database, each record on a table should be uniquely identified so that two records cannot interfere with each other. To make this possible, you can designate one column that will have unique values. To create a column whose values will be distinguishable, add the UNIQUE keyword to its definition. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Employees Records</title>
</head>
<body>

<div align="center">
<h2>Employees Records</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("CREATE TABLE Employees
              (
                  EmployeeNumber INT UNIQUE,
                  FirstName      VARCHAR(24),
                  LastName       VARCHAR(24),
                  HourlySalary   DECIMAL(5, 2)
               );");

$link->query("INSERT Employees
              VALUES(208584, 'Catherine', 'Watts', 36.45),
                    (422581, 'Frank', 'Bishop', 14.65),
                    (648265, 'Paula', 'Swanson', 22.08),
                    (974288, 'James', 'Edwards', 8.16),
                    (293847, 'Kimberly', 'Peters', 15.25), 
                    (774974, 'Henry', 'Dunn', 12.84);");

$employees = $link->query("SELECT * FROM Employees;");

echo "<table border=3><tr>";
echo "<td><b>Employee #</b></td>";
echo "<td><b>First Name</b></td>";
echo "<td><b>Last Name</b></td>";
echo "<td><b>Hourly Salary</b></td></tr>";

while($staff = $employees->fetch_array(MYSQLI_ASSOC))
{
    echo "<tr><td>" . $staff["EmployeeNumber"] . "</td>";
    echo "<td>" . $staff["FirstName"] . "</td>";
    echo "<td>" . $staff["LastName"] . "</td>";
    echo "<td>" . $staff["HourlySalary"] . "</td></tr>";
}

echo "</table>";

$employees->close();
$link->close();
?>

</div>
</body>
</html>

When a column has been marked as unique, during data entry, a unique value must be provided for each new record created. If an existing value is assigned to the column, this would produce an error.

Automatically generating a Uniqueness Number

Instead of creating the values yourself, the database engine can generate a unique number and assign it to the column every time a new record is created. This is done using the AUTO_INCREMENT keyword.

The Nullity of a Column

Introduction

Sometimes during data entry, a value is not available for a certain column. A column is said to be null when there no value for its record. As a suggestion and sometimes a rule, you should avoid having null records in your table. A column is said to be required if its value must be provided in order to create a record.

To specify that a column can allow null values, type NULL (remember that the SQL is not case-sensitive) on the right side of the column definition. To specify that the values of the column are required, on the right side, type NOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL by default. Here are examples:

CREATE TABLE Persons
(
    FirstName VARCHAR(20) NULL,
    LastName VARCHAR(20) NOT NULL,
    Gender smallint
);
GO

After specifying that a column would NOT allow NULL, if the user tries creating a record but omits to create a value for the column, an error would display.

Data Entry With a NULL Column

If you specify that a column will allow null, during data entry, if you don't have a value for that column, you can omit that column:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

// The MinRange, MaxRange, MinPercent, and MaxPercent columns are left out.
$link->query("INSERT GradesScales28(LetterGrade, Descriptor) VALUES('I', 'Insufficient')");

$grades = $link->query("SELECT LetterGrade,
                               MinRange,
                               MaxRange, 
                               MinPercent,
                               MaxPercent,
                               Descriptor
                        FROM GradesScales28");

echo "<table border=3><tr>";
echo "	  <td>&nbsp;</td><td colspan='2' align='center'><b>Range</b></td>";
echo "	  <td colspan='2' align='center'><b>Grade Percentage</b></td>";
echo "	  <td>&nbsp;</td>";
echo "	</tr><tr><td><b>Letter Grade</b></td>";
echo "    <td><b>Minimum</b></td><td><b>Maximum</b></td>";
echo "    <td><b>Minimum</b></td><td><b>Maximum</b></td>";
echo "	  <td><b>Descriptor</b></td></tr>";

while($grade = $grades->fetch_array(MYSQLI_ASSOC))
{
    echo "<tr><td>" . $grade["LetterGrade"] . "</td>";
    echo "<td>" . $grade["MinRange"] . "</td>";
    echo "<td>" . $grade["MaxRange"] . "</td>";
    echo "<td>" . $grade["MinPercent"] . "</td>";
    echo "<td>" . $grade["MaxPercent"] . "</td>";
    echo "<td>" . $grade["Descriptor"] . "</td></tr>";
}

echo "</table>";

$grades->close();
$link->close();
?>

</body>
</html>

When performing data entry, for the columns that are not included in the list, the database engine would automatically set their values to NULL. As an alternative, in the placeholder of the column, specify its value as NULL. Here are examples:

I<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("INSERT GradesScales(LetterGrade, MinRange, MaxRange,
                                    Descriptor, MinPercent, MaxPercent)
              VALUES('W', NULL, NULL, 'Withdrawn', NULL, NULL)");

$grades = $link->query("SELECT LetterGrade,
                               MinRange,
                               MaxRange, 
                               MinPercent,
                               MaxPercent,
                               Descriptor
                        FROM GradesScales28");

echo "<table border=3><tr>";
echo "	  <td>&nbsp;</td><td colspan='2' align='center'><b>Range</b></td>";
echo "	  <td colspan='2' align='center'><b>Grade Percentage</b></td>";
echo "	  <td>&nbsp;</td>";
echo "	</tr><tr><td><b>Letter Grade</b></td>";
echo "    <td><b>Minimum</b></td><td><b>Maximum</b></td>";
echo "    <td><b>Minimum</b></td><td><b>Maximum</b></td>";
echo "	  <td><b>Descriptor</b></td></tr>";

while($grade = $grades->fetch_array(MYSQLI_ASSOC))
{
    echo "<tr><td>" . $grade["LetterGrade"] . "</td>";
    echo "<td>" . $grade["MinRange"] . "</td>";
    echo "<td>" . $grade["MaxRange"] . "</td>";
    echo "<td>" . $grade["MinPercent"] . "</td>";
    echo "<td>" . $grade["MaxPercent"] . "</td>";
    echo "<td>" . $grade["Descriptor"] . "</td></tr>";
}

echo "</table>";

$grades->close();
$link->close();
?>

</body>
</html>

The Default Value of a Column

Introduction

Sometimes most records under a certain column may hold the same value although just a few would be different. For such a column, you can provide a default value. To specify the default value when creating a column, after specifying the other pieces of information of the column, type DEFAULT followed by an empty space and followed by the desired value. When performing data entry on the table, if a value is not provided for the column, the default value would be used. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Employee Time Sheet</title>
</head>
<body>
<h2>Employee Time Sheet</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("CREATE TABLE TimeSheets10
              (
                  TimeSheetNumber INT NOT NULL,
                  EmployeeNumber VARCHAR(10) NOT NULL,
                  Monday float(3, 2) DEFAULT 0.00,
                  Tuesday float(3, 2) DEFAULT 0.00,
                  Wednesday float(3, 2) DEFAULT 0.00,
                  Thursday float(3, 2) DEFAULT 0.00,
                  Friday float(3, 2) DEFAULT 0.00,
                  Saturday float(3, 2) DEFAULT 0.00,
                  Sunday float(3, 2) DEFAULT 0.00,
                  Notes VARCHAR(255) DEFAULT 'None'
              );");

$link->query("INSERT TimeSheets10(TimeSheetNumber, EmployeeNumber, Monday,
                                 Tuesday, Friday)
              VALUES(1001, '422581', 6.50, 8, 7);");

$link->query("INSERT TimeSheets10(Wednesday, Monday, Friday,
                                EmployeeNumber,
                                Thursday, TimeSheetNumber, Notes)
              VALUES(9.50, 8.5, 7, '974288', 10, 1002,
              'The employee received approval to work overtime');");

$link->query("INSERT TimeSheets10(TimeSheetNumber, EmployeeNumber, Monday,
                                 Tuesday, Wednesday, Thursday, Friday)
              VALUES(1003, '293847', 8, 8, 8, 8, 8);");

$link->query("INSERT TimeSheets10(TimeSheetNumber, EmployeeNumber,
                                 Friday, Saturday, Sunday)
              VALUES(1004, '774974', 8, 9.50, 9);");

$link->query("INSERT TimeSheets10(EmployeeNumber, Monday, Tuesday, 
                                 Wednesday, TimeSheetNumber, Notes)
              VALUES('648265', 4, 8, 8, 1005,
'There was a mistake on time recorded for Monday. The employee worked 6 hours.');");


$WorkRecorded = $link->query("SELECT * FROM TimeSheets10");

echo "<table border=3><tr>";
echo "<td><b>Time Sheet #</b></td>";
echo "<td><b>Employee #</b></td>";
echo "<td><b>Monday</b></td>";
echo "<td><b>Tuesday</b></td>";
echo "<td><b>Wednesday</b></td>";
echo "<td><b>Thursday</b></td>";
echo "<td><b>Friday</b></td>";
echo "<td><b>Saturday</b></td>";
echo "<td><b>Sunday</b></td>";
echo "<td><b>Notes</b></td></tr>";


while($work = $WorkRecorded->fetch_array(MYSQLI_ASSOC))
{
    echo "<tr><td>" . $work["TimeSheetNumber"] . "</td>";
    echo "<td>" . $work["EmployeeNumber"] . "</td>";
    echo "<td>" . $work["Monday"] . "</td>";
    echo "<td>" . $work["Tuesday"] . "</td>";
    echo "<td>" . $work["Wednesday"] . "</td>";
    echo "<td>" . $work["Thursday"] . "</td>";
    echo "<td>" . $work["Friday"] . "</td>";
    echo "<td>" . $work["Saturday"] . "</td>";
    echo "<td>" . $work["Sunday"] . "</td>";
    echo "<td>" . $work["Notes"] . "</td></tr>";
}

echo "</table>";

$WorkRecorded->close();
$link->close();
?>

</body>
</html>

Forcing a Default Value During Data Entry

During data entry, if you add a column that has a DEFAULT option in an INSERT statement, you must provide a value. An alternative is to force the default value for the column. To do this, in the placeholder of the value of the column, use the DEFAULT keyword. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Employee Time Sheet</title>
</head>
<body>
<h2>Employee Time Sheet</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("INSERT TimeSheets10
  VALUES(1006, '774974', DEFAULT, DEFAULT, DEFAULT, DEFAULT, 9, 10.50, 8, DEFAULT) ,
                    (1007, '422581', 7.50, 8, 8, DEFAULT, 8, 8, 6.50, DEFAULT),
                    (1008, '293847', 8, 8.50, 9.50, 8, 8, DEFAULT, 6,
                     'Overtime work not authorized');");

$WorkRecorded = $link->query("SELECT * FROM TimeSheets10");

echo "<table border=3><tr>";
echo "<td><b>Time Sheet #</b></td>";
echo "<td><b>Employee #</b></td>";
echo "<td><b>Monday</b></td>";
echo "<td><b>Tuesday</b></td>";
echo "<td><b>Wednesday</b></td>";
echo "<td><b>Thursday</b></td>";
echo "<td><b>Friday</b></td>";
echo "<td><b>Saturday</b></td>";
echo "<td><b>Sunday</b></td>";
echo "<td><b>Notes</b></td></tr>";


while($work = $WorkRecorded->fetch_array(MYSQLI_ASSOC))
{
    echo "<tr><td>" . $work["TimeSheetNumber"] . "</td>";
    echo "<td>" . $work["EmployeeNumber"] . "</td>";
    echo "<td>" . $work["Monday"] . "</td>";
    echo "<td>" . $work["Tuesday"] . "</td>";
    echo "<td>" . $work["Wednesday"] . "</td>";
    echo "<td>" . $work["Thursday"] . "</td>";
    echo "<td>" . $work["Friday"] . "</td>";
    echo "<td>" . $work["Saturday"] . "</td>";
    echo "<td>" . $work["Sunday"] . "</td>";
    echo "<td>" . $work["Notes"] . "</td></tr>";
}

echo "</table>";

$WorkRecorded->close();
$link->close();
?>

</body>
</html>

Computed Columns

Introduction

You can create an expression and apply it to a column so that the value of the column would come from other values. Such a column is referred to as a computed column. To create a computed column, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

CREATE TABLE Circle
(
    CircleID int identity(1,1) NOT NULL,
    Radius decimal(8, 3) NOT NULL,
    Area AS Radius * Radius * PI()
);
GO

Using an Expression During Data Entry

When performing data entry, you must not provide a value for a column that has an expression. The SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
GO
INSERT INTO Circle(Radius) VALUES(8.15);
GO
INSERT INTO Circle(Radius) VALUES(122.57);
GO

Practical Learning: Creating an Expression on a Column

In the top section of the Table window, right-click EmailAddress and click Insert Column
Type the name as EmployeeName and press Tab (ignore the data type)
In the bottom section, click the + button of Computed Column Specification
Click (Formula) and type LastName + N', ' + FirstName
Close the table
When asked whether you want to save, click Yes
In the Object Explorer, under University3, right-click Tables and click Refresh.
Right-click Academics.Employees and click Edit Top 200 rows

Create the records as follows:

EmployeeNumber	FirstName	MiddleName	LastName
279227	Donald	Henry	Leighton
582007	Alexander		Nolan
502494	Anthony	Robert	Parrish
702048	Laurentine	Felicit�	Avrilien
927486	Robert	John	Preston
204850	Simon		Lew
930248	Jeannette	Veronica	Holms

Close the table
In the Object Explorer, right-click Academics.Employees and click Edit Top 200 rows to view the records
Close the table

Persisted Columns

When you create a column that has an expression, the column doesn't have actual values. It is only a representative of values from other columns or constants. The column is referred to as a virtual column. Consider the following table:

CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    HourlySalary money,
    TimeWorkedInAWeek decimal(6, 2),
    FullName AS FirstName + N' ' + LastName,
    WeeklySalary AS HourlySalary * TimeWorkedInAWeek
);
GO

The FullName only shows values that are from the FirstName and the LastName columns. This means that, unlike the FirstName and the LastName columns that have actual values, there is no real value in the FullName column. Transact-SQL allows you to actually store the value of the expression in the column. Storing the value in the column is referred to as persisting the value.

To ask the database engine to store the value of the expression (as an actual value), if you are visually creating the column, in the bottom section, under (Formula), set the Is Persisted field to Yes:

Persisted Columns

If you are creating the column using code, add a flag named PERSISTED at the end of the column definition. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber nchar(60),
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    HourlySalary money,
    TimeWorkedInAWeek decimal(6, 2),
    FullName AS LastName + N', ' + FirstName PERSISTED,
    WeeklySalary AS HourlySalary * TimeWorkedInAWeek
);
GO

Now, when you create a record, the result of the expression is saved in its own memory area. If you change the record, that is, if you change the value of (one of) the column(s) involved in the expression, the database engine would update the saved value of the expression.

Column

//------------------------------------------

We continue with our university database. To run its many services, the school has a managing team that consists of a president, deans, and other employees.

In this new version of the database, University3, we will create a table for employees. The table will be stored in the Academics schema.

Practical Learning: Introducing Nullity

Start the computer and log in
Launch Microsoft SQL Server and click Connect
In the Object Explorer, right-click the Database node and click New Database...
Set the Database Name to University3
Click OK
In the Object Explorer, expand the University3 node and expand its Security node
Right-click Schemas and click New Schema...
Set the name to Academics
Click OK
Under University3, right-click Tables and click New Table...
While the table is displaying, in the Properties window, click Schema, then click the arrow of its combo box and select Academics

Create the following columns for the table:

Column Name	Data Type
EmployeeNumber	VARCHAR(20)
FirstName	VARCHAR(25)
MiddleName	VARCHAR(25)
LastName	VARCHAR(25)
EmailAddress	VARCHAR(50)
Username	VARCHAR(30)
Password	VARCHAR(24)

To save the table, on the Standard toolbar, click the Save button
Set the name to Employees
Click OK

----------------------------------------------------------------------------

Practical Learning: Assigning a Default Value to a Column

In the top section, click Password
In the bottom section, click Default Value or Binding
Type N'Password1'
Save the table

If the user provides a value for a column that has a default value and then deletes the value, the default value rule would not apply anymore: The field would simply become empty.

After doing this, click Close.

Practical Learning: Setting the Uniqueness on a Column

In the top section of the table, right-click EmployeeNumber and click Indexes/Keys...
In the Indexes/Keys dialog box, click Add
On the right side, double-click Is Unique to change its value from No to Yes
Click Close
Save the table