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
Entity vs. Attribute vs. Record: Entity = table, Attribute = column, Record = row.
PK vs. FK: A primary key uniquely identifies a record; a foreign key connects records between tables.
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.



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