Lesson 11: Database Concepts

Structure of a Database Table

1. The name of the table - each table within a database must have a unique name. 2. The name of each field (column) in the table 3. The data type of each field (column) 4. The size of each field (column)

Foreign Keys

A column (or combination of columns) in a table that references the primary key in another table.

Database Forms

Are tools that aid data entry and help users understand what they need to do. They are user-friendly interfaces designed to help users enter specific data without becoming distracted or confused by other data that may be stored in a table. It displays just the data that is necessary for performing a given task.

Metadata

Data that describes other data.

ODBC

Open Database Connectivity

SQL

Structured Query Language

Spreadsheet Databases

Sufficient for storing relatively small amounts of simple data; but it cannot represent or handle complex data relationships. Stores all information in a single table.

Structured Query Language

The standard language used to create and work with databases. Use to retrieve and manipulate the data. Any interaction among a user, program and database takes place through this language.

Data Type of each Field (Column)

This determines what type of data the field can contain. Each field can contain only one type of data. If the field is designed to accept text data, only text can be added to the field. If you enter a number into a text field, that number is treated as text (which means that you cannot perform any calculations on such numbers.) If you define a field to accept numeric data, you cannot enter text into that field.

Database

An organized collection of information. Its basic function is to store data.

API

Application Programming Interface

Primary Key

A field (or a combination of fields) that uniquely identifies each record stored in a table. No two records in a table may be exact duplicates; however, two records may have identical data, other than the primary key.

Database Schema

A logical view of the entire database. It defines how the data is organized and how the tables within the database are related to one another.

Composite Key

A primary key that is comprised of a combination of fields.

One-to Many

A relationship in which a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. The relationship is established between the primary key in Table A and the foreign key in Table B.

One-to One

A relationship in which each record in Table A can have only one matching record in Table B, and vice versa. This type of relationship is not very common.

Many-to-Many

A relationship in which one record in Table A or Table B can be related to many matching records in the other table. For example, suppose a database contains data about students and classes. Each student can be enrolled in several classes, and each class can have many students. This is _____________________ relationship. Relational databases cannot directly handle these type of relationships, and they must be replaced by multiple one-to-many relationships.

Database Tables

A series of rows and columns which data is organized in.

Application Programming Interface

A set of programming building blocks that specify how software components should interact. There are APIs for operating systems, applications, and web sites. These interfaces make it possible to connect databases to web pages.

Java Database Connectivity

A standard programming interface for accessing Oracle databases, which run on the Java platform.

Open Database Connectivity

A standard programming interface for accessing a number of different databases, including Access, DB2, SQL Server, and MySQL.

Relational Database Management Systems

Designed to handle complex relationships between various items of data.

Size of each Field

Determines the range of numeric or date values that can be entered, or determine the maximum number of characters that can be entered in a text field.

Field

Each column in a table can be referred to as a column, a __________ or an attribute. Each column contains a different type of information, such as item number, description, unit price and so on for each product.

Record

Each row in a table can be referred to as a row or a ____________.

Null Value

Is equivalent to "unknown" It is not same thing as zero.

JDBC

Java Database Connectivity

Where are Databases Used?

Medical records, flight reservations, legal pleadings, online orders, student records, Twitter, Facebook and WordPress post are all databases. Databases are used almost everywhere you look.

Data

Pieces of information which can be measured, collected, analyzed and reported. Examples: A phone number scribed on a scrap of paper, as a course listing in a catalog, a list of test scores on a grade report.

Table Metadata

Refers to the structure of a database table. Each table must be explicitly defined before you can enter any data.

RDBMS

Relational Database Management Systems

Multiple, Related Tables

Unlike spreadsheet databases which store all the information in a single table, relational databases store data in multiple tables which are related to one another. Example: Gardening Supply Company 1. Table which contains customer information 2. Table which contains order information 3. Table which contains detailed information for each order 4. Table which contains product information Because the data stored in each table is related to data in the other tables of the database, a complete set of information can be retrieved. For example, you could retrieve the complete information for a particular order (customer name, order number, order date, order status, item #, quantity, product description, price, line total, etc.)

Database Queries

Used to view specific data in one or more database tables. It asks a question of a table , such as "Which customers live in Arizona?" and selects records from the table which answers the question. The selected records are displayed in a result set.