Topics on Data Entry

NULL or NOT NULL Fields During data entry, users of your database will face columns that expect data. Sometimes, for one reason or another, data will not be available for a particular field. An example would be an MI (middle initial) field: some people have a middle initial, some others either don't have it or would not provide it to the user. This aspect can occur for any field of your table. Therefore, you should think of a way to deal with it. A field is referred to as null when no data is available for it: Saying that a field is null doesn't mean that it contains 0 because 0 is a value. Saying that a field is null doesn't mean that it is empty. A field being empty could mean that the user had deleted its content or that the field itself would not accept what the user was trying to enter into that field, but an empty field can have a value. A field is referred to as null if there is no way of determining the value of its content or its value is simply unknown. As you can see, it is not a good idea to have a null field in your table. As a database developer, it is your responsibility to always know with certainty the value held by each field of your table. Remember that even if a field is empty, you should know what value it is holding because being empty could certainly mean that the field has a value. To show that a column would allow null values, it can be marked with the NULL keyword. If a column must always require a value, it must be marked as NOT NULL. If you are creating a new table, to indicate that a column would allow null values, type NULL before the comma that separates it from the next column. If it is the last column, enter NULL before the closing parenthesis of the CREATE TABLE statement. This is the same way you would use NOT NULL.  If a table has already been created and you want to mark one of its columns as NULL or NOT NULL, if you are using MSDE, you can modify the column using the ALTER COLUMN expression. Here is an example: ALTER TABLE Contractors ALTER COLUMN LastName varchar(20) NOT NULL GO   This would apply the NOT NULL property to the LastName column of the Contractors table.   Practical Learning: Creating NULL or NOT NULL Columns To create a new table and specify NULL or NOT NULL columns, execute the following statement:   CREATE TABLE StudentAssignments ( ClassOrMajor char(6) NOT NULL, TeacherName varchar(50) NULL, AssignmentName varchar(100) NOT NULL, DateGiven date, DateExpected date); To enter data into the new table, execute the following statement:   INSERT INTO StudentAssignments VALUES(ENG201,'Alfred Locktar','First Impressions With a New Boss','2005-06-12','2005-06-28'); The Default Value When performing data entry, with some fields, the records under a certain column usually have the same value. For example, for a local database with a table that includes an address, most employees live in the same state and the same country. When creating a column with a value that occurs regularly, you can specify that value as default. To specify the default value in a SQL statement, when creating the column, before the semi-colon of a column or before the closing parenthesis of the last column, type the DEFAULT keyword followed by the desired value. If the value is text-based, a date or a time value, then make sure you include it in single-quotes. If the value is a number, then simply provide it. Here are examples: CREATE TABLE Employees( EmployeeName varchar(50), State char(3) DEFAULT 'NSW', PostalCode int default 2000, Country varchar(32) default 'Australia');   After creating the table, the user doesn't have to provide a value for a column that has a default. If the user doesn't provide the value, the default would be used when the record is saved.

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

 

A Column to Keep Track of Records

When updating a record and changing a value, the user can make a mistake and change the wrong value. Consider the following table: Video Title Director © Year Length Rating A Few Good Men Rob Reiner 1992 138 Minutes R The Silence of the Lambs Jonathan Demme 1991 118 Minutes   The Distinguished Gentleman James Groeling   112 Minutes R The Lady Killers Joel Coen & Ethan Coen   104 Minutes R Ghosts of Mississippi Rob Reiner   130 Minutes   Imagine you ask the user to open this table and, for the video that is rated R, to change the name of the director to Jonathan Lynn. The user would be confused because there is more than one video that is rated R. This means that you should use the most restrictive criterion to locate the record. In future lessons, when we study data analysis, we will review other operators you can use, such as asking the user to locate the video whose title is "The Distinguished Gentleman" AND whose director is Jonathan Lynn. To be able to uniquely identify each record, you can create a special column and make sure that each value under that column is unique. You have two main options. You can put the responsibility on the user to always provide a unique value. For example, if the table includes records of students of a school, since each student must have a student number and that number must be unique from one student to another, you can ask the data entry person to make sure of this. What if the user forgets? What if the user cannot get that number at the time of data entry? What if that number can only be generated by the administration but only after the student has been registered? Based on this, an alternative is to ask the SQL interpreter to automatically generate a new and unique number for each record. A column whose values are automatically generated by the database engine is referred to as an identity column. An identity column can have only a numeric-based data type: bigint, decimal, int, numeric, smallint, or tinyint. To create an identity column, when creating the table, after the name of the column and before the semi-colon or the closing parenthesis of the last column, enter AUTO_INCREMENT. Although you can name an auto-incrementing column any way you like, by tradition, its name is usually made of the singular name of the table appended with Id or ID.

The Primary Key

We have seen that an identity column was used to make sure that a table has a certain column that holds a unique value for each record. In some cases, you can use more than one column to uniquely identify each record. For example, on a table that holds the list of employees of a company, you can use both the employee number and the social security number to uniquely identity each record. In our description of the identity column, we saw that it applied only to one column; but we also mentioned that more than one column could be used to uniquely identity each record. The column or the combination of columns used to uniquely identity each column is called a primary key. To specify that a column is used as the primary key, when creating the table, at the end of the list of columns, type PRIMARY KEY() and, in the parentheses, type the name of the column that would be used as the primary key.

 

Practical Learning: Creating an Auto-Incrementing Column

 

To create a table that has an auto-incrementing column as a primary key, execute the following statement:   mysql> CREATE TABLE Courses( -> CourseID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Course char(6) NOT NULL, -> CourseName varchar(100) NULL, -> Credits tinyint, -> FullDescription text); Query OK, 0 rows affected (0.23 sec) mysql> To enter data into the new table, execute the following three statements:   mysql> INSERT INTO Courses(Course, CourseName, Credits) -> VALUES('ENG101', 'English Composition', 3); Query OK, 1 row affected (0.11 sec) mysql> INSERT INTO Courses(Course, CourseName, Credits) -> VALUES('MATH150', 'Introduction to Statistical Methods', 4); Query OK, 1 row affected, 1 warning (0.08 sec) mysql> INSERT INTO Courses(Course, CourseName, Credits) -> VALUES('CIS232', 'Java Programming', 4); Query OK, 1 row affected (0.08 sec)

 

Data Import

Another technique used to perform data entry consists of importing already existing data from another database or from another recognizable data file. One way you can do this is to create a text file and you can use Notepad to do it. In the file, include all normal and valid SQL code. After creating the file, you should save it with a .sql (preferably) or a .txt extension. In MySQL, to import the contents of a file that contains all the necessary code already, at the SQL prompt, after specifying the target database, use the following formula: SOURCE FilePath;   The SOURCE keyword lets the interpreter know that you are going to import the code from an external file. On the right side of the SOURCE keyword, enter the complete path to the SQL file.

 

Practical Learning: Importing Data From an External Source

1. Download the Students text file and save it to your hard drive. I will assume that you saved it in a folder as C:\Programs
2. Display the MySQL prompt
3. To create a new database, execute the following statement
   
   CREATE DATABASE ROSH1;
4. Execute the following statement:
   
   USE ROSH1;
5. Assuming that you installed the Students.sql file at C:\Programs, execute a statement as follows (change the path to the file accordingly):
   
   SOURCE C:Programs\Students.sql;
6. Notice that a new table with values filled with values has been created.
   Type Exit and press Enter to close the SQL environment