4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.1 Database Terms in Simple Words
Start with a problem
Zoya hears primary key, foreign key and table in a SQL meeting and feels lost.
Explain simply
A table stores rows and columns. A primary key uniquely identifies each row. A foreign key in another table points to that key, which is how tables join.
Show the data
| DarkStores (Store = primary key) | City |
|---|---|
| Kothrud | Pune |
| Baner | Pune |
| Gangapur | Nashik |
| College Road | Nashik |
| Rajarampuri | Kolhapur |
About this data: This table uses columns: DarkStores (Store = primary key), City. This table uses columns: Table, Key, Unique?. This table uses columns: Term, Meaning, Our example. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: dark_stores_maharashtra.csv, visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Open
dark_stores_maharashtra.csv: one row per Store. - Open the orders file: Store repeats (Kothrud in 301 and 306).
- Mark Store as primary key in DarkStores.
- Mark Orders[Store] as foreign key.
- Match 306, then Kothrud, then Pune.
Show the result
Your result should look like this (fictional sample):
| Table | Key | Unique? |
|---|---|---|
| DarkStores | Store | Yes (5 rows, 5 stores) |
| Orders | Order ID | Yes (8) |
| Orders | Store (foreign key) | No (repeats) |
Explain what happened
Unique keys on one side and repeating keys on the other side create one-to-many joins.
Explore further
- Relationships in Power BI (Module 11) use the same idea.
Detailed reference for this lesson:
| Term | Meaning | Our example |
|---|---|---|
| Server | The computer/service that runs the database engine | localhost, QCSERVER01, qc-sql.database.windows.net |
| Database | A container of related tables | QuickCommerceDB |
| Schema | A folder inside a database that groups tables | dbo, sales, ops |
| Table | Rows and columns of stored data | dbo.Orders, dbo.DarkStore |
| View | A saved SQL query that looks like a table | dbo.vw_MaharashtraOrders |
| Port | The network "door" number of the server | SQL Server 1433, MySQL 3306 (defaults) |
| Credentials | How you prove who you are | Windows login, database user/password, Microsoft Entra ID |
QuickCommerceDB (fictional) has these tables: dbo.Orders, dbo.Product, dbo.Customer, dbo.DarkStore, dbo.DeliveryPartner and a view dbo.vw_MaharashtraOrders. The columns match Module 1.7, with compact names (OrderID, OrderDateTime, StoreID …).
Ravindra Bagale's Tip
Friends, many students confuse server and database. The server is the machine or instance (QCSERVER01\SQL2022). The database is one container on it (BlinkitDW). Get both names exactly right from your DBA before you start, because typos give confusing "cannot connect" errors.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students server आणि database मध्ये गोंधळतात. Server म्हणजे machine किंवा instance (QCSERVER01\SQL2022). Database म्हणजे त्यावरचा एक container (BlinkitDW). सुरू करण्याआधी दोन्ही नावं DBA कडून अगदी अचूक घ्या, कारण typos मुळे गोंधळात टाकणारे "cannot connect" errors येतात.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students server और database में उलझ जाते हैं. Server मतलब machine या instance (QCSERVER01\SQL2022). Database उस पर एक container है (BlinkitDW). शुरू करने से पहले दोनों नाम DBA से बिल्कुल सही लो, क्योंकि typos से उलझाने वाले "cannot connect" errors आते हैं.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Join doubles rows | Duplicate key in DarkStores | Remove duplicates |
| Order without store match | Spelling differs (College Rd) | Standardise names |
| Used Store name as key across cities | Names may repeat in future | Use a Store ID |
Practise independently
Practice task
How many orders use store Rajarampuri?
Hints
- 305, 308.
Solution
2.
Think like a professional
Knowing keys lets you talk to database teams confidently.
Revision card