What Can a Database Do?
If the database is relational, which most databases are, cross-references records in different tables. This means that you can create relationships between tables. For example, if you linked a Customers table with an Orders table, you could find all purchase orders from the Orders table that a single customer from the Customers table ever processed, or further refine it to return only those orders processed in a particular time period – or almost any type of combination you could imagine. Because of these table relationships, a database supports complex querying, with various combinations of columns across tables and filters to fine-tune which rows return after the query executes. A database performs complex aggregate calculations across several tables. For example, you could list expenses across a dozen retail outlets, including all possible sub-totals, and then a final total. A database enforces consistency and data integrity, avoiding duplication and ensuring data accuracy through its design and a series of constraints.
What Is the Structure of a Database?
At its simplest, a database is made up of tables that contain columns and rows. Data separates by categories into tables to avoid duplication. For example, a business might have a table for Employees, one for Customers, and another for Products. Each row in a table is called a record, and each cell is a field. Each field (or column) holds a specific type of data, such as a number, text or a date. This specification is enforced by a series of rules called constraints to ensure that your data is accurate and dependable. The tables in a relational database are linked through a key. This is an ID in each table that uniquely identifies a row. Each table uses a primary key column, and any table that needs to link to that table offers a foreign key column whose value will match the first table’s primary key.
Queries and Reports
All database engines support querying, which is a process of defining a specific set of rules to obtain an extract a subset of information from the database. However, different engines offer different levels of support. A server-based solution, for example, returns tabular output that must be rendered more aesthetically pleasing through a different report-writing tool. A desktop-based database, like Microsoft Access, includes a visual report designer integrated with its query tool, leading to one-stop shopping for direct-to-print reports.
Common Database Products
Microsoft Access is one of the most popular database platforms on the market today. It ships with Microsoft Office and is compatible with all Office products. It features wizards and an easy-to-use interface that guides you through the development of your database. Other desktop databases are also available, including FileMaker Pro, LibreOffice Base (which is free) and Brilliant Database. These solutions are optimized for small-scale, single-user desktop applications. For businesses, a large-scale, multi-user database server makes more sense. Server databases like MySQL, Microsoft SQL Server, and Oracle are enormously powerful—but also expensive and can come with a steep learning curve.
Essential Skills
All but the simplest databases rely on Structured Query Language to develop new database assets (like tables and columns) or to extract information through queries. Although SQL is an easy scripting language, different database vendors use slightly different implementations of it relative to their own proprietary database engines.
