Bus 237 Ch 17 Lec 7 Data and Competitive Advantage

BUS 237 – Lecture 7: Database Design & Relationships

1. What Is a Database?

What is data?

  • different from information in mis, its raw fact and figures

  • information: data presented in a context that has been processed. like average mark of a class

  • knowledge: insight derived from experience and expertise

Database: An organized collection of data, often stored in multiple related tables.

  • Helps businesses store, organize, retrieve, and update information efficiently.

  • Reduces unnecessary duplication of data (redundancy).

  • Allows different business operations to access related information.

Example – Amazon: Uses information systems and databases to coordinate orders, customers, inventory, and shipping.

Self-describing database: A database system that contains information describing its data, such as attributes, data types, and relationships between tables.

Think of a database like an Excel workbook with multiple connected sheets.

2. Basic Database Terminology

Very important — know these terms and how to identify them.

Database term

Meaning

Starbucks example

Entity (Table)

Stores information about one category

Members

Attribute (Column)

A characteristic of an entity

First Name

Record (Row)

One complete entry in a table

One customer's information

Primary Key (PK)

Uniquely identifies each record

Member ID

Foreign Key (FK)

References a PK in another table

Member ID in Sales

Relationship

Connection between tables

Members → Sales

Database

A collection of related tables (or a single table)

Starbucks database

Example: Members Table

Member ID (PK)

First Name

Loyalty Points

101

Amy

250

102

Ben

180

103

Chloe

400

  • Entity: Members (the entire table)

  • Attributes: Member ID, First Name, Loyalty Points

  • Record: 102 | Ben | 180 (one row)

  • Primary Key: Member ID (each member has a different ID)

Remember: Attributes describe a record, while a record contains values for those attributes.

3. Primary Keys vs. Foreign Keys

These are especially important because they explain how different tables connect.

Primary Key (PK)

Foreign Key (FK)

Uniquely identifies each record

Connects to a key in another table

Cannot contain NULL

Can contain NULL if allowed

Cannot have duplicate values

Can have duplicate values

Example: Member ID in Members

Example: Member ID in Sales

Important: The PK and the FK referencing it must have compatible data types.


ERD:

Starbucks Example

Members Table

Member ID (PK)

Name

101

Amy

102

Ben

Sales Table

Sales ID (PK)

Member ID (FK)

Total

5001

101

$6

5002

101

$8

5003

102

$5

Notice that Member ID 101 appears twice in the Sales table.

Why is that allowed?

Because Member ID is a foreign key in Sales, meaning the same customer can make multiple purchases.

However, Sales ID must always be unique because it is the primary key of Sales.

4. Entity Relationship Diagram (ERD)

ERD: A diagram that visually represents entities and the relationships between them in a database.

There are three main types of relationships:

A. One-to-One (1:1)

One record in Table A corresponds to one record in Table B.

Example: One citizen → One unique ID number.

B. One-to-Many (1:M)

One record in Table A can be connected to many records in Table B.

Example: One Starbucks member → Many sales transactions.

One member can make multiple purchases.

C. Many-to-Many (M:N)

Many records in Table A can relate to many records in Table B.

Example: Sales ↔ Products.

  • One sale can contain multiple products.

  • One product can appear in multiple sales.

ERD symbols: A short stroke represents one, while a crow's-foot symbol represents many.

5. Why Are Many-to-Many Relationships a Problem?

Consider two customers:

  • Customer A buys 10 products.

  • Customer B buys 3 products.

Two possible ways of storing this information create problems.

Problem 1: Too many columns

If each sale has 10 Product ID columns, Customer B only needs 3 of them.

Result: 7 unnecessary empty columns.

Problem 2: Repeating Sales IDs

If each purchased product gets its own row, Customer A's Sales ID would repeat 10 times.

Result: Sales ID could no longer uniquely identify each row if it remains the sole primary key.

Solution: Introduce a Third Table

Create a SalesProduct table that connects Sales and Products.

The SalesProduct table stores which products belong to each sale.

Sales ID (FK)

Product ID (FK)

5001

P01

5001

P02

5001

P03

5002

P01

Now:

  • One sale can contain many products.

  • One product can belong to many sales.

  • No need for 10 separate Product ID columns.

  • Sales ID remains unique in the Sales table, even though it repeats as an FK in SalesProduct.

Key takeaway: A junction table resolves a many-to-many relationship into two one-to-many relationships.

6. Database Normalization

Normalization: The process of organizing data into related tables to reduce redundancy and improve data consistency.

Why is it important?

  • Prevents unnecessary duplicate information.

  • Reduces data storage inefficiencies.

  • Makes information easier to update and maintain.

  • Helps prevent inconsistencies and errors.

Example: Instead of repeatedly storing a customer's name and email in every sales record, Starbucks stores them in the Members table and references the Member ID in Sales.

7. Other Important Database Terms

These are also covered in your BUS 237 Lecture 7 slides.

Term

Definition

DBMS (Database Management System)

Software used to create, maintain, and manipulate databases

SQL (Structured Query Language)

Language used to create and manipulate databases

DBA (Database Administrator)

Person responsible for overseeing databases

Relational Database

Database where tables are connected using common keys

Lecture 7, slides 10–12.

8. Three Most Important Things to Remember for the Exam

  1. Entity vs. Attribute vs. Record: Entity = table, Attribute = column, Record = row.

  2. PK vs. FK: A primary key uniquely identifies a record; a foreign key connects records between tables.

  3. Database relationships: Know the difference between 1:1, 1:M, and M:N, and understand why a junction table is used to resolve many-to-many relationships.

Quick comprehension check

1. In Starbucks' Sales table, Member ID appears multiple times. Why is this allowed?

Because Member ID is a primary key

Because Member ID is a foreign key

Because databases do not require unique IDs


2. One sale contains five products. What type of relationship exists between Sales and Products before adding a junction table?

One-to-one

One-to-many

Many-to-many


3. What is the main purpose of the SalesProduct table?

To eliminate all foreign keys

To connect Sales and Products without a direct many-to-many relationship

To store customer loyalty points


My study tip: Spend the most time on identifying primary and foreign keys and interpreting ERDs. Those are the concepts that require understanding rather than just memorizing definitions.


know this page

DBA: Database administrator


Blockchain


Transaction processing system: can be any erp you purchase, systems that record a transaction or some form of business-related exchange

Most common managerial problems:

  • data quality, inconsistent data, or the inability to integrate data sourses into a single dataset capable of input into machine learning systems can all stifle efforts