relational database defined
A relational database is a collection of data items organised as a set of tables.
Relationships can be defined between the data in one table and the data in another or many other tables.
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.
This querying is usually done using SQL (Structured Query Language). This is a relatively straight forward language to learn, certainly for simple queries.
You could have a relational database with only one table, but then you wouldn’t have any relationships and it would be more like a spreadsheet.
Databases are designed to allow efficient querying against very large tables, more than the 1M rows allowed in an Excel spreadsheet.
What is a table?
As were have noted above, a single table is very much like a spreadsheet. It has rows and it has columns. A row represents a single observation and the columns represents the various variables contained within that observation. The rows may or may not have an index or key column as a means of uniquely identifying a row. The columns typically have a name associated with them indicating the variable name, or in one of the examples we are going to use a question number from a survey.
A column always represents the same variable for each row contained in the table. Because of this the data in each column will always be of the same type, such as an Integer, or Text value for all rows in the table.
Datatypes are discussed in the next section.
What is a data type?
- A data type is a description of the kind of data in a table column.
- Typical examples will be Integer or Text. In SQLite there is quite a number of possible data type.
Show most used data type table from notes
Why do tables have key columns?
Whenever you create a table, you will have the option of designating one of the columns as the primary key column.
The main property of the primary key column is that the values contained in it must uniquely identify that particular row. That is you cannot have duplicate primary keys.
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.
- The primary key column for a table is usually of type Integer although you could have Text.
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.
A table doesn’t have to have a primary key although they are recommended for larger tables.
What different types of keys are there?
In addition to the primary key, a table may have one or more Foreign keys. A foreign key does not have to be unique or identified as a foreign key when the table is created.
A foreign key in one table will relate to the primary key in another table. This allows a relationship to be created between the two tables. If a table needs to be related to several other tables, then there will be a foreign key (column) for each of those tables.
How does the database represent missing data?
All relational database systems have the concept of a NULL value. NULL can be thought of as being of all data types or of no data type at all. It represents something which is simply not known.
When you create a database table, for each column you are allowed to indicate whether or not it can contain the NULL value. Like primary keys, this can be used as a form of data validation.
In many real life situations you will have to accept that the data isn’t perfect and will have to allow NULL or missing values in your table.
*In SQL queries you can specifically test for NULL values.
- We will look at missing data in more detail in a later episode.
LS0tCnRpdGxlOiAiV2hhdCBpcyBhIFJlbGF0aW9uYWwgRGF0YWJhc2U/IgpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sKLS0tCgojIHJlbGF0aW9uYWwgZGF0YWJhc2UgZGVmaW5lZAoqIEEgYHJlbGF0aW9uYWwgZGF0YWJhc2VgIGlzIGEgY29sbGVjdGlvbiBvZiBkYXRhIGl0ZW1zIG9yZ2FuaXNlZCBhcyBhIHNldCBvZiB0YWJsZXMuIAoKKiBSZWxhdGlvbnNoaXBzIGNhbiBiZSBkZWZpbmVkIGJldHdlZW4gdGhlIGRhdGEgaW4gb25lIHRhYmxlIGFuZCB0aGUgZGF0YSBpbiBhbm90aGVyIG9yIG1hbnkgb3RoZXIgdGFibGVzLiAKCiogVGhlIHJlbGF0aW9uYWwgZGF0YWJhc2Ugc3lzdGVtIHdpbGwgcHJvdmlkZSBtZWNoYW5pc21zIGJ5IHdoaWNoIHlvdSBjYW4gcXVlcnkgdGhlIGRhdGEgaW4gdGhlIHRhYmxlcywgcmUtYXNzZW1ibGUgdGhlIGRhdGEgaW4gdmFyaW91cyB3YXlzIHdpdGhvdXQgYWx0ZXJpbmcgdGhlIGRhdGEgaW4gdGhlIGFjdHVhbCB0YWJsZXMuIAoKKiBUaGlzIHF1ZXJ5aW5nIGlzIHVzdWFsbHkgZG9uZSB1c2luZyBTUUwgKFN0cnVjdHVyZWQgUXVlcnkgTGFuZ3VhZ2UpLiBUaGlzIGlzIGEgcmVsYXRpdmVseSBzdHJhaWdodCBmb3J3YXJkIGxhbmd1YWdlIHRvIGxlYXJuLCBjZXJ0YWlubHkgZm9yIHNpbXBsZSBxdWVyaWVzLiAKCiogWW91IGNvdWxkIGhhdmUgYSByZWxhdGlvbmFsIGRhdGFiYXNlIHdpdGggb25seSBvbmUgdGFibGUsIGJ1dCB0aGVuIHlvdSB3b3VsZG7igJl0IGhhdmUgYW55IHJlbGF0aW9uc2hpcHMgYW5kIGl0IHdvdWxkIGJlIG1vcmUgbGlrZSBhIHNwcmVhZHNoZWV0LgoKKiBEYXRhYmFzZXMgYXJlIGRlc2lnbmVkIHRvIGFsbG93IGVmZmljaWVudCBxdWVyeWluZyBhZ2FpbnN0IHZlcnkgbGFyZ2UgdGFibGVzLCBtb3JlIHRoYW4gdGhlIDFNIHJvd3MgYWxsb3dlZCBpbiBhbiBFeGNlbCBzcHJlYWRzaGVldC4KCiMgV2hhdCBpcyBhIHRhYmxlPwoqIEFzIHdlcmUgaGF2ZSBub3RlZCBhYm92ZSwgYSBzaW5nbGUgdGFibGUgaXMgdmVyeSBtdWNoIGxpa2UgYSBzcHJlYWRzaGVldC4gCkl0IGhhcyByb3dzIGFuZCBpdCBoYXMgY29sdW1ucy4gQSByb3cgcmVwcmVzZW50cyBhIHNpbmdsZSBvYnNlcnZhdGlvbiBhbmQgdGhlIGNvbHVtbnMgcmVwcmVzZW50cyB0aGUgdmFyaW91cyB2YXJpYWJsZXMgY29udGFpbmVkIHdpdGhpbiB0aGF0IG9ic2VydmF0aW9uLiBUaGUgcm93cyBtYXkgb3IgbWF5IG5vdCBoYXZlIGFuIGluZGV4IG9yIGtleSBjb2x1bW4gYXMgYSBtZWFucyBvZiB1bmlxdWVseSBpZGVudGlmeWluZyBhIHJvdy4gVGhlIGNvbHVtbnMgdHlwaWNhbGx5IGhhdmUgYSBuYW1lIGFzc29jaWF0ZWQgd2l0aCB0aGVtIGluZGljYXRpbmcgdGhlIHZhcmlhYmxlIG5hbWUsIG9yIGluIG9uZSBvZiB0aGUgZXhhbXBsZXMgd2UgYXJlIGdvaW5nIHRvIHVzZSBhIHF1ZXN0aW9uIG51bWJlciBmcm9tIGEgc3VydmV5LiAKCiogQSBjb2x1bW4gYWx3YXlzIHJlcHJlc2VudHMgdGhlIHNhbWUgdmFyaWFibGUgZm9yIGVhY2ggcm93IGNvbnRhaW5lZCBpbiB0aGUgdGFibGUuIEJlY2F1c2Ugb2YgdGhpcyB0aGUgZGF0YSBpbiBlYWNoIGNvbHVtbiB3aWxsIGFsd2F5cyBiZSBvZiB0aGUgc2FtZSB0eXBlLCBzdWNoIGFzIGFuIEludGVnZXIsIG9yIFRleHQgdmFsdWUgZm9yIGFsbCByb3dzIGluIHRoZSB0YWJsZS4gCgoqIERhdGF0eXBlcyBhcmUgZGlzY3Vzc2VkIGluIHRoZSBuZXh0IHNlY3Rpb24uCgojIFdoYXQgaXMgYSBkYXRhIHR5cGU/CiogQSBkYXRhIHR5cGUgaXMgYSBkZXNjcmlwdGlvbiBvZiB0aGUga2luZCBvZiBkYXRhIGluIGEgdGFibGUgY29sdW1uLiAKKiBUeXBpY2FsIGV4YW1wbGVzIHdpbGwgYmUgSW50ZWdlciBvciBUZXh0LiBJbiBTUUxpdGUgdGhlcmUgaXMgcXVpdGUgYSBudW1iZXIgb2YgcG9zc2libGUgZGF0YSB0eXBlLgoKWyoqU2hvdyBtb3N0IHVzZWQgZGF0YSB0eXBlIHRhYmxlIGZyb20gbm90ZXMqKl0oaHR0cHM6Ly9kYXRhLWxlc3NvbnMuZ2l0aHViLmlvL3NxbC1zb2NpYWxzY2kvMDEtcmVsYXRpb25hbC1kYXRhYmFzZS8pCgojIFdoeSBkbyB0YWJsZXMgaGF2ZSBrZXkgY29sdW1ucz8KCiogV2hlbmV2ZXIgeW91IGNyZWF0ZSBhIHRhYmxlLCB5b3Ugd2lsbCBoYXZlIHRoZSBvcHRpb24gb2YgZGVzaWduYXRpbmcgb25lIG9mIHRoZSBjb2x1bW5zIGFzIHRoZSBgcHJpbWFyeSBrZXkgY29sdW1uYC4gCgoqIFRoZSBtYWluIHByb3BlcnR5IG9mIHRoZSBwcmltYXJ5IGtleSBjb2x1bW4gaXMgdGhhdCB0aGUgdmFsdWVzIGNvbnRhaW5lZCBpbiBpdCBtdXN0IHVuaXF1ZWx5IGlkZW50aWZ5IHRoYXQgcGFydGljdWxhciByb3cuIFRoYXQgaXMgeW91IGNhbm5vdCBoYXZlIGR1cGxpY2F0ZSBwcmltYXJ5IGtleXMuIAoKKiBUaGlzIGNhbiBiZSBhbiBhZHZhbnRhZ2Ugd2hpY2ggYWRkaW5nIHJvd3MgdG8gdGhlIHRhYmxlIGFzIHlvdSB3aWxsIG5vdCBiZSBhbGxvd2VkIHRvIGFkZCB0aGUgc2FtZSByb3cgKG9yIGEgcm93IHdpdGggdGhlIHNhbWUgcHJpbWFyeSBrZXkpIHR3aWNlLgoKKiBUaGUgcHJpbWFyeSBrZXkgY29sdW1uIGZvciBhIHRhYmxlIGlzIHVzdWFsbHkgb2YgdHlwZSBJbnRlZ2VyIGFsdGhvdWdoIHlvdSBjb3VsZCBoYXZlIFRleHQuIAoqIEZvciBleGFtcGxlIGlmIHlvdSBoYWQgYSB0YWJsZSBvZiBjYXIgaW5mb3JtYXRpb24sIHRoZW4gdGhlIGDigJxSZWdfTm/igJ1gIGNvbHVtbiBjb3VsZCBiZSBtYWRlIHRoZSBwcmltYXJ5IGtleSBhcyBpdCBjYW4gYmUgdXNlZCB0byB1bmlxdWVseSBpZGVudGlmeSBhIHBhcnRpY3VsYXIgcm93IGluIHRoZSB0YWJsZS4KCiogQSB0YWJsZSBkb2VzbuKAmXQgaGF2ZSB0byBoYXZlIGEgcHJpbWFyeSBrZXkgYWx0aG91Z2ggdGhleSBhcmUgcmVjb21tZW5kZWQgZm9yIGxhcmdlciB0YWJsZXMuCgojIFdoYXQgZGlmZmVyZW50IHR5cGVzIG9mIGtleXMgYXJlIHRoZXJlPwoKKiBJbiBhZGRpdGlvbiB0byB0aGUgYHByaW1hcnkga2V5YCwgYSB0YWJsZSBtYXkgaGF2ZSBvbmUgb3IgbW9yZSBgRm9yZWlnbiBrZXlzYC4gQSBmb3JlaWduIGtleSBkb2VzIG5vdCBoYXZlIHRvIGJlIHVuaXF1ZSBvciBpZGVudGlmaWVkIGFzIGEgZm9yZWlnbiBrZXkgd2hlbiB0aGUgdGFibGUgaXMgY3JlYXRlZC4gCgoqIEEgZm9yZWlnbiBrZXkgaW4gb25lIHRhYmxlIHdpbGwgcmVsYXRlIHRvIHRoZSBwcmltYXJ5IGtleSBpbiBhbm90aGVyIHRhYmxlLiBUaGlzIGFsbG93cyBhIHJlbGF0aW9uc2hpcCB0byBiZSBjcmVhdGVkIGJldHdlZW4gdGhlIHR3byB0YWJsZXMuIElmIGEgdGFibGUgbmVlZHMgdG8gYmUgcmVsYXRlZCB0byBzZXZlcmFsIG90aGVyIHRhYmxlcywgdGhlbiB0aGVyZSB3aWxsIGJlIGEgZm9yZWlnbiBrZXkgKGNvbHVtbikgZm9yIGVhY2ggb2YgdGhvc2UgdGFibGVzLgoKIyBIb3cgZG9lcyB0aGUgZGF0YWJhc2UgcmVwcmVzZW50IG1pc3NpbmcgZGF0YT8KCiogQWxsIHJlbGF0aW9uYWwgZGF0YWJhc2Ugc3lzdGVtcyBoYXZlIHRoZSBjb25jZXB0IG9mIGEgYE5VTExgIHZhbHVlLiBgTlVMTGAgY2FuIGJlIHRob3VnaHQgb2YgYXMgYmVpbmcgb2YgYWxsIGRhdGEgdHlwZXMgb3Igb2Ygbm8gZGF0YSB0eXBlIGF0IGFsbC4gSXQgcmVwcmVzZW50cyBzb21ldGhpbmcgd2hpY2ggaXMgc2ltcGx5IG5vdCBrbm93bi4KCiogV2hlbiB5b3UgY3JlYXRlIGEgZGF0YWJhc2UgdGFibGUsIGZvciBlYWNoIGNvbHVtbiB5b3UgYXJlIGFsbG93ZWQgdG8gaW5kaWNhdGUgd2hldGhlciBvciBub3QgaXQgY2FuIGNvbnRhaW4gdGhlIE5VTEwgdmFsdWUuIExpa2UgcHJpbWFyeSBrZXlzLCB0aGlzIGNhbiBiZSB1c2VkIGFzIGEgZm9ybSBvZiBkYXRhIHZhbGlkYXRpb24uCgoqIEluIG1hbnkgcmVhbCBsaWZlIHNpdHVhdGlvbnMgeW91IHdpbGwgaGF2ZSB0byBhY2NlcHQgdGhhdCB0aGUgZGF0YSBpc27igJl0IHBlcmZlY3QgYW5kIHdpbGwgaGF2ZSB0byBhbGxvdyBOVUxMIG9yIG1pc3NpbmcgdmFsdWVzIGluIHlvdXIgdGFibGUuCgoqSW4gU1FMIHF1ZXJpZXMgeW91IGNhbiBzcGVjaWZpY2FsbHkgdGVzdCBmb3IgYE5VTExgIHZhbHVlcy4KCiogV2Ugd2lsbCBsb29rIGF0IG1pc3NpbmcgZGF0YSBpbiBtb3JlIGRldGFpbCBpbiBhIGxhdGVyIGVwaXNvZGUuCgoKCgoK