IC3 Lesson 11: Database Concepts

In a database, what does "null" mean?

"Null" can mean (1) without value, (2) nonexistent, or (3) equal to zero. However, in a database, null means "unknown".

What is a "composite key"?

A COMPOSITE KEY or COMPOSITE PRIMARY KEY is a primary key that is a combination of fields that together are unique.

What is a "database"?

A DATABASE is an organized collection of information (data). The basic function of a database is to store data so it can be retrieved as needed.

What is a "dynamic" web page?

A DYNAMIC WEB PAGE changes every time it is loaded into the browser window. It changes its content based on the query (the form) that is sent to it by a user. Dynamic web pages have .asp, .cgi, .jsp, or .pl file extensions.

What is a "foreign key"? What is the purpose of a foreign key?

A FOREIGN KEY (fk) is a column, or combination of columns, in a table that references (matches) the primary key in another table. A foreign key is used to create relationships among tables.

What do databases use to make data entry easier?

A FORM is user-friendly interface that is used to make data entry easier and help users understand what they need to do.

What is a "primary key"? What are the most important characteristics of a primary key?

A PRIMARY KEY (PK) is a field, or a combination of fields, that uniquely identifies each record (row) in a table. The primary key must be: (1) unique (unlike any other record) and (2) must not contain a "null" value.

What is a "query"? What does it do?

A QUERY asks a question of a database and selects records from the tables which answer the question. (A "query" is a noun meaning a question or request for information. It can also be used as a verb, as a synonym for "ask".)

What is a "static web page"?

A STATIC WEB PAGE displays content that does not change (unless it is edited.) The page is stored on a server and appears the same way every time a request is received, until it is changed by the web developer. Static web pages have .html or .xhtml file extensions.

What is a "table schema"?

A TABLE SCHEMA is a visual representation of the names of the tables and the fields (columns) in the tables. A table schema would be created for each table (spreadsheet) in the database. It is a plan for the layout of a table.

Before you can add data to a table in an RDBMS, what do you have to do?

Before you can add data to a database table, you must define the structure of the table.

What is "data"?

DATA are pieces of information. Data is separate from the container (e.g., hard drive, a flash drive, in the cloud) in which it is stored.

What is "SQL"?

SQL is STRUCTURED QUERY LANGUAGE (pronounced "SEE-kwell").

What do you use SQL for?

SQL is the universal language used to create and work with databases. Even though many RDBMSs provide a user-friendly interface, SQL is used behind the scenes to create and use the application.

What are the names of some RDBMS applications?

Some RDMS applications are: Oracle, SQL Server, MySQL, PostgreSQL, Sybase, IBM DB2, and Microsoft Access.

What are some examples of metadata that must be added before you can start entering data in a database table?

Some examples of metadata that define the structure of a table are: the name of the table, the name of each field (column), the type of data to be entered in each field, and the size of each field.

What is "table metadata"?

TABLE METADATA is the data that defines the table (see next question), which then creates the structure of the table. METADATA is data that describes other data.

In the example on p.355, what connects the data in the different tables together?

The "Orders" and "OrderItems" tables (spreadsheets) are connected (related) because they both have the "Order#" data. The "OrderItems" and "InventoryItems" tables are connected because they both have the "ItemID" data. The "Orders" and "Customers" tables are connected because they both have the "Cust#" data.

What does the data type determine? What does the size of each field determine?

The DATA TYPE determines what type of data the field (column) can contain. Each field can only contain ONE type of data. The size of the field determines the range of numeric or date values that can be entered or the maximum number of characters that can be entered.

What is the purpose of "database metadata" or "database schema"?

The DATABASE SCHEMA (or DATABASE METADATA) is a visual representation of the tables in the database. It defines how the data is organized and how the tables are related.

"Dynamic" means "always active or changing". Why is the answer to a query called a "dynaset"?

The answer to a query is called a DYNASET (from DYNAmic subSET) because the result set will change as records are added to the database. (A SUBSET is a group within a group.)

Can the data in an AutoNumber field be deleted or changed?

The data in an AutoNumber field cannot be deleted or changed.

What must be the same about a primary key and a foreign key to create a relationship?

The parent key and the foreign key must have the same data type.

What is a "parent key"?

The primary key to which a foreign key connects is also known as a PARENT KEY.

What are the two kinds of Web pages?

The two kinds of web pages are STATIC WEB PAGES and DYNAMIC WEB PAGES.

How do Web pages use databases?

The web page is connected to a database through programming, and it pulls information from the database and inserts that information onto the web page each time the page is loaded. As the data in the database changes, the web page connected to the database also changes automatically. Most online shopping sites present their catalogs in database-powered pages.

What are the types of relationships between tables? RDBMSs cannot handle one of the relationships, so what must be done instead?

There are three types of relationships between tables: One-to-One, One-to-Many, and Many-to-Many. One-to-One is the most common type of relationship. RDBMSs cannot handle Many-To-Many relationships and these must be replaced by multiple One-to-Many relationships.

How are relationships represented in database schema?

You represent relationships by drawing lines between the key fields in the related tables. The primary key is marked by an underlined field name or a key icon. In a One-to-Many relationship, the "one" side is marked by a "1" and the "many" side is marked by an infinity symbol (the number eight sideways).

How can you connect a Web page to an RDBMS?

You can connect a Web page to an RDBMS using an API.

What is an "API"? What do you use an API for?

An API is an APPLICATION PROGRAMMING INTERFACE. An API is used as an interface between different database applications so they can interact. It specifies how software components should interact. There are APIs for operating systems, applications and web sites.

What is an RDBMS designed to do?

An RDBMS contains multiple spreadsheets which are linked together so that all of the spreadsheets can be searched at the same time. This allows the user to find relationships between data items in more than one spreadsheet that would be too difficult to find by searching spreadsheets individually. An RDBMS can include millions of records.

What is an "RDBMS"?

An RDBMS is a RELATIONAL DATABASE MANAGEMENT SYSTEM application.

In a database, data is stored in tables. What are the rows in a table called? What are the columns in a table called?

A row in a database table can also be called a RECORD. A column in a database table can also be called a FIELD or an ATTRIBUTE.

What is an "entity"?

Each table in a database stores data about a specific type of thing, e.g. customers, orders, or inventory. An ENTITY is the specific type of thing for which data is stored. Usually there is a table for each entity in a database.

What are examples of web sites that use database driven pages?

Examples are: shopping sites, banking sites, and online accounts.

Can two records in a database be the same?

No two records can be exact duplicates; however, two records may have identical data, other than the primary key. Most tables include an ID field to guarantee that each record is unique.

What are "criteria"?

Queries use CRITERIA to define what data should be retrieved. A CRITERION (criteria, pl.) is a specification for what can be included, e.g. a date or number, a range of numbers or dates, a name or a string of text, etc. A query examines each record, and if the value in a record matches the value(s) specified in the criteria, then it is displayed in the RESULT SET (the group of answers).

What is "AutoNumber"?

To create unique primary keys, you can select AUTONUMBER as the data type property of the ID field. A field with an AutoNumber data type is automatically updated with a unique sequential number whenever a record is added to the table.

What are the names of two APIs that allow access to different RDBMSs?

Two APIs are: ODBC = OPEN DATABASE CONNECTIVITY and JDBC = JAVA DATABASE CONNECTIVITY.

Can a database in one RDBMS application be used in another RDBMS?

Usually, a database is not portable from one RDBMS application to another.

When you are selecting a primary key, what should you think about?

When selecting a primary key, you should consider that: (1) it must be a value that will never change and (2) it should have a value that is extremely unlikely to be null.