Chapter 4 Relational Databases

Advantages of Databases

• Data is integrated into large pools of data which minimizes data redundancy and inconsistencies

• Data is shared between different functions/ units this allowed for cross-functional analysis to become efficient

• Data is independent of the programs that use the data, each can be changed without changing the other

Database Management system (DBMS)

software that manages and controls the data and the interfaces between the fata and the applications.

Types of databases:

Relational, hierarchical, network and object-oriented

How are most DBMS accessed

SQL or its variations to access the database

Update anomaly

changing a customer’s address involves searching the entire table and changing every occurrence of that customer’s address

Insert Anomaly

there is no good way to store information about prospective customers or new products until a sales transaction is completed

Delete Anomaly

deleting a sales record could delete information about that customer or that product, resulting unintended consequences

what is a database

a set of interrelated, centrally coordinated data files

what is a file

AKA a (table) is a related group of records

what is a record

(row) is a related group of fields

what is a field

(column) is a specific attribute for the record

Primary Key

An attribute, or combination of attributes, that uniquely identifies a specific record/row in a table.

Foreign Key

an attribute in a table that is also a primary key in another table and is used to link the two tables.

Fact Table

• contains facts about a business process (e.g., sales of cars)

• Contains foreign keys to link to dimension tables

• Often long and contains more numerical fields

• Located at the center of a star schema

Dimension table

• Contains attributes of the business process (e.g., who made the sales) and those attributes can be used as filters or grouping variables in data analysis

• Contains primary key to link to fact tables

• Often short and contains more descriptive fields

• Surrounding fact table in a star schema

One to Many or Many to One (1:* or *:1):

the column in one table (usually fact table) can have many instances of a value, and the other related table (usually dimension table) has only one instance of a value.

One to One (1:1)

the column in both tables has only one instance of a value, most probably because that variable is the primary key of both tables.

Many-to-Many (*:*):

This type of relationship could create errors; must be used with caution.

Relational Database General Design Rules

• Every column in a row must be single valued (e.g., not “television, freezer and refrigerator”).

• Primary keys cannot be null/empty.

• Foreign keys, if not null, must have values that correspond to the value of a primary key in another table.

• All non-key attributes in a table must describe characteristics identified by the primary key.

Manage Relationships & Queries

• Users may want specific information found in a relational database; they “query” (ask a question) the database.

• Tools such as Microsoft Access, Structured Query Language (SQL) and Power Query Editor (PQE) can be used to query databases.