A database is a system for storing information.
When starting a database, decide what to store and how to structure it. Make a list of ways you might use the data. This might be reports that could be created.
Database. A database includes all the database components. In other words, everything associated with the data is part of the database. The components of a database include: tables, queries, reports, and forms.
Field. A piece of information for multiple records. Also known as a variable. In a spreadsheet, the field name (or “header”) is typically in first row.
Record. A record is a row of information.
Table. A table stores data as a logical grouping of fields. A table should have a unique identifier for each record. Examples of tables are — product table, customer table, sales table, supplier table, and an employee table.
- Product table: Product ID, product name, supplier ID, supplier name, retail price, invoice price
- Inventory table: Product ID, product name, number of items in stock
- Customer table: Customer ID, name, address, phone number, email address, etc.
- Sales (order) table: Transaction ID, date, time, product ID, customer ID, quantity
- Supplier table: Supplier ID, supplier name, product ID, (contact information)
- Employee table: Employee ID, employee name, job title, date hired
- Expenses table: Expense ID, expense name, date, time, dollar amount
Flat file. A flat file is a single table.
Relational database. A relational database has more than one table and they are related. The database defines the relationships between the tables. Access is a relational database. You can sketch out the database by drawing a box for each table and listing the fields in each one. Draw arrows to show how they might be related.
Data sources. Databases typically have multiple data sources.
Form. A form is a customized interface for data entry, editing, and for viewing a database one record at a time. Using “enumeration” means a drop-down window of options rather than an open text box. Online shopping involves forms for filling out customer information.
Data quality. Some data exists but in analog form, like paper files. There are many forms of data errors, such as duplicate records, records with misspellings, and records with missing information.
Big data. Excel can only store so many rows.
Descriptive analytics. Search (look up), sort, filter, and query your data to understand the data.
Report. A report shows what the data is. It is a compilations of data. Reports are one of the most important ways that data is used and shared. A report highlights something about specific fields in the data. Data software programs can help the user create customized reports quickly and easily. A “canned” report is a form template built into the software. Another phrase for this is a “cookie cutter” template. Spreadsheets like Excel are not designed primarily for reporting capabilities. A quick report in a database application uses preset layouts and is often one click away. An updated report can be generated quickly when new data is available.
Template. A template is a design term for a prepackaged format. For instance, “we followed the template.” Software programs include template libraries. Examples of templates include charts (bar, pie, line, …) and reports.
Pivot Table. A pivot table is a subtotal report. A subtotal is a total for a subset (group) of the data.
Query. Queries are questions asked of a database. They help search and understand the data. An example is “How many customers do we have in Illinois?”
Application (App). A user interface controls what people see, which tables they can edit, and how they work with the data. Access is a database application.