Relational Database Design
A relational database is a set of formally described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The standard user and application programming interface (API) of a relational database is the Structured Query Language (SQL). SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports.
What’s in a relational database model?
The relational database was invented in 1970 by E. F. Codd, then a young programmer at IBM. In his paper, “A Relational Model of Data for Large Shared Data Banks,” Codd proposed shifting from storing data in hierarchical or navigational structures to organizing data in tables containing rows and columns.
Each table, which is sometimes called a relation, in a relational database contains one or more data categories in columns, also called attributes. Each row, also called a record or tuple, contains a unique instance of data, or key, for the categories defined by the columns. Each table has a unique primary key, which identifies the information in a table. The relationship between tables can then be set via the use of foreign keys — a field in a table that links to the primary key of another table.
For example, a typical business order entry database would include a table that described a customer with columns for name, address, phone number and so forth. Another table would describe an order: product, customer, date, sales price and so forth. A user of a relational database can then obtain a view of the database to fit their needs. For example, a branch office manager might like a view or report on all customers that bought products after a certain date. A financial services manager in the same company could, from the same tables, obtain a report on accounts that need to be paid.
A relational database includes tables containing rows and columns.
When creating a relational database, you can define the domain of possible values in a data column and further constraints that may apply to that data value. For example, a domain of possible customers could allow up to 10 possible customer names but be constrained in one table to allowing only three of these customer names to be specifiable. Two constraints relate to data integrity and the primary and foreign keys:
- Entity integrity ensures that the primary key in a table is unique and that the value is not set to null.
- Referential integrity requires that every value in a foreign key column will be found in the primary key of the table from which it originated.
Types of Databases
There are a number of database categories, from basic flat files that aren’t relational to NoSQL to newer graph databases that are considered even more relational than standard relational databases.
A flat file database consists of a single table of data that has no interrelation — typically text files. This type of file enables users to specify data attributes, such as columns and data types.
Flat file vs. relational database
Standard relational databases enable users to manage predefined data relationships across multiple databases. Popular relational databases include Microsoft SQL Server, Oracle Database, MySQL and IBM DB2. Cloud-based relational databases, or database as a service (DBaaS), are also widely used because they enable companies to outsource database maintenance, patching and infrastructure support requirements. Cloud relational databases include Amazon Relational Database Service (RDS), Google Cloud SQL, IBM DB2 on Cloud, Microsoft Azure SQL Database and Oracle Database Cloud Service.
A NoSQL database is an alternative to relational databases that’s especially useful for working with large sets of distributed data. These databases can support a variety of data models, including key-value, document, columnar and graph formats.
A graph database expands beyond traditional column- and row-based relational data models; this NoSQL database uses nodes and edges that represent connections between data relationships and can discover new relationships between the data. Graph databases are more sophisticated than relational databases, and thus, their uses include fraud detection or web recommendation engines.
Advantages of relational databases
The main advantages of relational databases are that they enable users to easily categorize and store data that can later be queried and filtered to extract specific information for reports. Relational databases are also easy to extend and aren’t reliant on physical organization. After the original database creation, a new data category can be added without all existing applications being modified.
Other relational database advantages include:
- Accurate: Data is stored just once, which eliminates data deduplication.
- Flexible: Complex queries are easy for users to carry out.
- Collaborative: Multiple users can access the same database.
- Trusted: Relational database models are mature and well-understood.
- Secure: Data in tables within relational database management systems (RDBMSes) can be limited to allow access by only particular users.