When you create a database table, for each column you are allowed to indicate whether or not it can contain the NULL value. It represents something which is simply not known. NULL can be thought of as being of all data types or of no data type at all. How does the database represent missing data?Īll relational database systems have the concept of a NULL value. If a table needs to be related to several other tables, then there will be a foreign key (column) for each of those tables. This allows a relationship to be created between the two tables. A foreign key in one table will relate to the primary key in another table. A foreign key does not have to be unique or identified as a foreign key when the table is created. In addition to the primary key, a table may have one or more Foreign keys. A primary key can also be made up of more than one column, although this is less usual. For example if you had a table of car information, then the “Reg_No” column could be made the primary key as it can be used to uniquely identify a particular row in the table.Ī table doesn’t have to have a primary key although they are recommended for larger tables. The primary key column for a table is usually of type Integer although you could have Text. This can be an advantage which adding rows to the table as you will not be allowed to add the same row (or a row with the same primary key) twice. That is you cannot have duplicate primary keys. The main property of the primary key column is that the values contained in it must uniquely identify that particular row. Whenever you create a table, you will have the option of designating one of the columns as the primary key column. We will look at manipulating dates in Lesson 5. You can use TEXT, REAL, or INTEGER valuesįor these and use the built-in Date And Time Functions to manipulate them. There are some common datatypes which are missing from the SQLite list.īOOL or BOOLEAN : This type typicaly accepts values of ‘True’ and ‘False’ In SQLite we would use the Integer type and assign vlaues of 1 to represent ‘True’ andĭATE, DATETIME, TIMESTAMP : SQLite does not have a datatype for storing dates and/or times. We won’t be using any BLOB data and it is debatable whether or not NULL should be considered a type at all. The data is stored exactly as it was input, Used for binary The value is a floating point value, stored in 8-bytes Or 8 bytes depending on the magnitude of the value The value is a signed integer, stored in 1, 2, 3, 4, 6, Stores year, month, day, hour, minute, and second valuesĬomposed of a number of integer fields, representing a period of time, depending on the type of intervalĪ set-length and ordered collection of elementsĪ variable-length and unordered collection of elementsīut in practice you can usually restrict your usage to a few Data type A floating number in base 10 exponential notation. (Same as DECIMAL)Īpproximate numerical, mantissa precision p. Maximum length nĮxact numerical, precision p, scale s. Typical examples will be Integer or Text. Each database system recognises its own set of datatypes, although some are common to many. What is a data type?Ī data type is a description of the kind of data in a table column. Datatypes are discussed in the next section. Because of this the data in each column will always be of the same type, such as an Integer or Text, of values for all of the rows in the table. A column always represents the same variable for each row contained in the table. The columns typically have a name associated with them indicating the variable name. Often one or more columns in a row will be designated as a ‘primary key’ This column or combination of columns can be used to uniquely identify a specific row in the table. A row represents a single observation and the columns represents the various variables contained within that observation. What is a table?Īs were have noted above, a single table is very much like a spreadsheet. You could have a relational database with only one table, but then you would’t have any relationships and it would be more like a spreadsheet.ĭatabases are designed to allow efficient querying against very large tables, more than the 1M rows allowed in an Excel spreadsheet. SQL allows a great many queries to be constructed from the use of only a few keywords. This querying is usually done using SQL (Structured Query Language). The relational database system will provide mechanisms by which you can query the data in the tables, re-assemble the data in various ways without altering the data in the actual tables. Relationships can be defined between the data in one table and the data in another or many other tables. A relational database is a collection of data items organised as a set of tables.

Idatabase relational