Data for Decision Making Quiz 1 Part 1 (copy)

Database

Stores, organizes, and manages large amounts of information in a single software application.

SQL

A programming language used by nearly all relational databases to query, manipulate, and define data, and to provide access control.

Store data; provide an organizational structure for data; and to provide a mechanism for querying, creating, modifying, and deleting data.

What is the purpose of a database?

Specific details about each type of object.

What can be stored in a regular database?

Relational databases

Store information in related two-dimensional tables.

Through data models

How is the design of a database communicated?

Data Model

Graphical, logical structures that detail the relationships among data fields.

Tables, Data Fields/Attributes, Primary and Foreign Keys, and Relationships

What’s included in a data model?

Always start talking about one thing.

How to read/understand data models?

Entity

An object. Examples: Customer, Product, and Building.

Data Model and Data Table View

What are the two views of a database table?

Data Field/Attribute

The smallest or most basic unit of information that is stored about an object. Characteristic of an entity.

Contains data about a specific entity. Goes across.

What is a record?

Primary Key

A data field that uniquely identifies each row in a table. The values in this column can never repeat or be duplicated.

Foreign Key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables. Ex: customer_id in the order table is a foreign key.

Parent table

The table that houses the primary key. It has two perpendicular lines coming from it. The perpendicular lines represent One in the One-to-Many relationship.

Child table

The other table that is not the parent table is this table. It has the crow’s foot connected to it and represents the Many in the One-to-Many relationship.

Crow’s foot- Perpendicular lines connect to the primary key in one table, “one”. The circle represents the many side and connects to the foreign key in another table.

What is represented by this?

Referential Integrity

A foreign key must reference a primary key in the parent table.

Update problems (with lists)

When a single piece of data requires changes in multiple rows.

Deletion problems (with lists)

Deleting a record unintentionally removes other important data

Insertion problems (with lists)

It is impossible to add new data to a table

Increased flexibility, increased scalability and performance, reduced information redundance, increased information integrity (quality), increase information security.

Advantages of using a database

DBMS

Serves an interface between the database and its end users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized. Ex: MySQL, Microsoft Access, Microsoft SQL, Oracle.

Insertion, Deletion, Update Problems + getting information out of spreadsheets is difficult.

Cons of spreadsheets