Chapter 4 - Databases

What is DBMS? define a relational database.

Database Management System. A relational database model means that data are stored in separate tables but they are structured so they can be linked together.

Define Attribute, Record, and Field.

Attribute - Column

Record - Row

Field - Individual cell/entry

Define the three attribute types and give an example: Primary, Foreign, and Non-key

Primary - attribute (OR combo of attributes) that can be used to uniquely identify a specific row/record in a table (ex. Item #)

Foreign - an attribute in one table that is a primary key in another table (ex. Vendor #)

Non-key - an attribute that is used to describe the primary key (Ex. Color)

Basic rules of tables and attributes.

Every table needs at least one key (Primary or foreign) but it is NOT necessary for a table to have a Primary Key. The keys are used to connect to other tables.

List the 4 design requirements for Relational Databases.

1. Every attribute (column) must be single valued

2. Primary keys must (1) contain data and (2) cannot have duplicates

3. Foreign Keys must be the primary key in another table

4. All other non-key attributes must identify a characteristic of the table identified by the primary key.

List and define the 3 database design errors discussed in the notes.

Update Anomaly - changes to existing data on one table may not update another table that uses that data

Insert Anomaly - Unable to add new record to the database tables if primary key is not defined

Delete Anomaly - Removing a record also removes other unintended data from the database

List 5 advantages of a relational database.

1. Data Integration

2. Data Sharing

3. Minimizing Data Redundancy and Inconsistency

4. Data Independence

5. Cross-Functional Analysis

List and explain the 6 SQL commands we learned in the notes.

1. SELECT - defines attributes you want in the results

2. FROM - What tables you use to pull the data

3. WHERE/HAVING - applies filters to the data

4. GROUP BY - organizes similar information together(sometimes calculations)

5. ORDER/SORT BY - sorts data in a desired way’

6. JOIN - Used to when the query requires two or more tables

What is an REA Diagram?

• Stands for Resources Events Agents Diagram

• It is used as a planning tool and similar to a document flowchart

• Has chicken feet and circles and lines in diagram connecting tables together