RDBMS Concepts and SSMS – SQL Server | Lesson 5
Introduction
NexCoding Public School keeps its records in registers and spreadsheets today. In this module, you design the database that replaces them.
Before you design anything, you need the basic words of databases. This lesson gives you those words and a guided tour of SQL Server in SSMS.
By the end of this lesson, you will be able to:
- Explain what a relational database is and why schools use one instead of spreadsheets.
- Name the parts of a table: rows, columns, and keys.
- Explain how a server, a database, a schema, and a table fit together.
- Name the five families of SQL commands.
- Find your way around SSMS and run a read-only query.
Why this matters
The school's fee spreadsheet has a problem. Two office staff edit it at the same time, and one person's changes overwrite the other's. Last month, a payment disappeared.
The attendance register has another problem. To find every day Kabir was absent this term, someone turns 60 pages by hand.
A relational database solves both. Many people can work at once without losing data, and a question like "show Kabir's absences" takes one line of SQL.
The concept
A database is an organized collection of data that a program can store, search, and change safely. A relational database stores data in tables that are linked to each other.
RDBMS (Relational Database Management System) is the software that runs it. SQL Server is an RDBMS.
Think of a school office cupboard. The cupboard is the database. Each labelled file inside is a table: one for students, one for teachers, one for fees.
Example: the register vs a table
| Spreadsheet or register | Relational database |
|---|---|
| Anyone can type anything in any cell | Each column has a fixed type and rules |
| Two people editing can overwrite each other | Many users work at once, safely |
| Data is copied between sheets by hand | Tables link to each other by keys |
| Finding data means scrolling | A query finds it in milliseconds |
| One file, easy to lose | Backups, security, and an audit trail |
Example: table, row, and column
A table stores one kind of thing. A row is one item of that thing. A column is one detail that every row has.
| AdmissionNumber | FirstName | LastName | DateOfBirth |
|---|---|---|---|
| ADM-2017-0001 | Aarav | Sharma | 2011-04-12 |
| ADM-2017-0002 | Diya | Patel | 2011-08-23 |
This is part of the Students table. Each row is one student. FirstName is a column.
Every table has a primary key: a column whose value is different for every row, so each row can be found exactly.
A foreign key is a column that points to the primary key of another table. That link is the "relational" part. You build both in Module 3.
Example: server, database, schema, table
SQL Server organizes things in layers, like folders inside folders:
| Layer | SMS example | What it is |
|---|---|---|
| Server | localhost | The running SQL Server program |
| Database | SchoolDb | One application's data |
| Schema | dbo | A named group of tables inside a database |
| Table | Students | The data itself |
A schema groups tables, like a drawer in the cupboard. dbo (database owner) is
the default schema. That is why we always write dbo.Students: it names the exact
drawer and file.
Example: the five families of SQL commands
SQL (Structured Query Language) is the language you use to talk to a relational database. Its commands come in five families:
| Family | Full name | Commands | SMS example |
|---|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP | Create the Students table |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE | Add a new student |
| DQL | Data Query Language | SELECT | List the students of 10-A |
| DCL | Data Control Language | GRANT, REVOKE | Let the reports user read tables |
| TCL | Transaction Control Language | BEGIN TRANSACTION, COMMIT, ROLLBACK | Save a payment and its receipt together |
Example: SQL vs T-SQL
SQL is a standard used by many databases. T-SQL (Transact-SQL) is Microsoft's
version of SQL for SQL Server, with extras such as variables and TRY...CATCH error
handling. Everything you write in this course is T-SQL.
Example: when a relational database fits
Relational databases fit data with a clear structure and strict rules: students, enrollments, marks, money. Other kinds of databases, called NoSQL, store flexible documents instead of fixed tables.
For a school system, the relational model wins. Fees must add up exactly, and a mark must belong to a real student. Those rules are what an RDBMS enforces best.
Apply it to the SMS
Open SSMS and connect to your server, as in Lesson 4.
Example: a tour of SSMS
| SSMS part | Where | What you use it for |
|---|---|---|
| Object Explorer | Left panel | Browse databases, tables, and other objects |
| New Query | Toolbar button | Open an editor to write SQL |
| Database drop-down | Toolbar | Choose which database the query runs in |
| Execute (F5) | Toolbar | Run the query, or only the selected lines |
| Results tab | Below the editor | See returned rows in a grid |
| Messages tab | Below the editor | See row counts and errors |
Expand Databases in Object Explorer. You see SchoolDb and a folder called
System Databases.
Use this routine every time you open SSMS:
- Confirm Object Explorer shows the correct server.
- Open a new query and choose
SchoolDbin the database list. - Save the query in the course's
queriesfolder before running it. - Read both Results and Messages after it runs.
Results show the rows returned by SELECT. Messages show errors, warnings, and
row counts. A query can return no rows without being broken, so check both tabs.
Example: the system databases
SQL Server creates four system databases for its own use:
| Database | Its job |
|---|---|
master | Knows about every database, login, and server setting |
model | A template: every new database starts as a copy of it |
msdb | Stores scheduled jobs and backup history |
tempdb | Holds temporary work; it is emptied every time SQL Server restarts |
Never create your own tables in these. The school's data always goes in SchoolDb.
Example: explore the server
Click New Query and type this script. Save it as queries\005-explore-server.sql
in your project folder. It only reads; it changes nothing.
-- Lesson 5: look around your SQL Server. Read-only: it changes nothing.
-- 1. Which databases exist on this server?
SELECT
name,
database_id
FROM sys.databases
ORDER BY database_id;
GO
-- 2. Switch to the school database.
USE SchoolDb;
GO
-- 3. Which tables does SchoolDb have? None yet: we create them in Module 3.
SELECT name
FROM sys.tables;
GO
Press F5. The first result shows every database on the server:
| name | database_id |
|---|---|
| master | 1 |
| tempdb | 2 |
| model | 3 |
| msdb | 4 |
| SchoolDb | 5 |
The second result is empty, with (0 rows affected) in the Messages tab.
SchoolDb has no tables yet. That is correct: you design them in this module and
build them in the next.
sys.databases and sys.tables are system views: read-only tables that SQL
Server keeps about itself. You will use them often to check your own work.
Common mistakes
- Running a query in the wrong database. New queries often open in
master. Fix: check the database drop-down, or start scripts withUSE SchoolDb;. - Creating tables in
master. They then mix with system objects. Fix: always confirm you are inSchoolDbbefore runningCREATE. - Treating a database like a spreadsheet. Typing anything anywhere loses the benefits. Fix: design columns, types, and rules first (Lessons 6 to 8).
- Leaving out the schema name.
Studentsmight not meandbo.Studentsfor every user. Fix: always writedbo.Students.
In real projects
The first real database I inherited had tables in master, created by a
developer who never checked the database drop-down. Moving them without downtime took
a weekend. Since then, every script my teams write starts with USE and names
the schema on every table.
When I review a design, I also ask: "What does the spreadsheet do today that this database must never allow?" The answers, such as duplicate receipts or marks over the maximum, become the rules you will build in Module 3.
Best practices
- Keep application data in its own database, never in system databases.
- Start every script with
USE <database>;. - Always write the schema:
dbo.Students, notStudents. - Use system views like
sys.tablesto check what exists.
Recap
- An RDBMS stores data in linked tables and enforces rules.
- A table holds one kind of thing; rows are items, columns are details.
- Server, database, schema, table: like building, cupboard, drawer, file.
- SQL commands come in five families: DDL, DML, DQL, DCL, TCL.
- T-SQL is SQL Server's version of SQL.
master,model,msdb, andtempdbbelong to SQL Server, not to your app.
Homework
Library module task: The library keeps a register with these headings: Book title, Author, ISBN, Copy number, Issued to, Class, Issue date, Return date. Decide which tables this register will become. List each table with its columns.
A correct result looks like: two or three tables, each about one kind of thing, with no column repeated across tables except the links between them.
Show a sample solution
| Table | Columns |
|---|---|
| Books | Title, Author, ISBN |
| BookCopies | Book (link to Books), CopyNumber |
| BookIssues | Copy (link to BookCopies), Student (link to Students), IssuedOn, ReturnedOn |
"Class" does not belong in the library tables: the student's class is already known from the school's enrollment data. Lesson 7 explains why.
Quiz
What is a row in a table?
Show answer
Answer: B. One item of the thing the table stores Each row is one item, such as one student; columns are the details.
Which family does the SELECT command belong to?
Show answer
Answer: C. DQL SELECT reads data, so it is Data Query Language.
Which system database is emptied every time SQL Server restarts?
Show answer
Answer: D. tempdb tempdb holds temporary work and is recreated at every restart.
What is dbo in dbo.Students?
Show answer
Answer: B. A schema dbo is the default schema that groups tables inside a database.
Why did the query on sys.tables return no rows?
Show answer
Answer: B. SchoolDb has no tables yet SchoolDb was created empty in Lesson 4; its tables come in Module 3.
Interview questions
Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.
Fresher (0-2 yrs)
F What is a relational database (RDBMS)? concept
A relational database stores data in tables made of rows and columns, and links tables to each other with keys. An RDBMS (Relational Database Management System), such as SQL Server, is the software that stores the data, runs queries, and enforces rules so many users can work safely at the same time.
F What are a table, a row, and a column? concept
A table stores one kind of thing, such as students. A row is one item of that thing,
such as one student. A column is one detail that every row has, such as DateOfBirth.
F What is the difference between DDL and DML? concept
DDL (Data Definition Language) defines the structure: CREATE, ALTER, and DROP
for tables and other objects. DML (Data Manipulation Language) changes the data inside
them: INSERT, UPDATE, and DELETE. Creating the Students table is DDL; adding a
student is DML.
Mid (3-5 yrs)
M What are the system databases in SQL Server, and what does each one do? concept
master stores server-wide information such as databases and logins. model is the
template copied for every new database. msdb stores SQL Server Agent jobs and backup
history. tempdb holds temporary objects and work tables, and is recreated at every
restart. Application tables never belong in any of them.
M What is the difference between SQL and T-SQL? concept
SQL is the standard language for relational databases. T-SQL (Transact-SQL) is
Microsoft's extension of SQL for SQL Server. It adds variables, control flow such as
IF and WHILE, and error handling with TRY...CATCH.
M What is a schema such as dbo, and why write dbo.Students instead of Students? concept
A schema is a named group of objects inside a database, used for organization and
permissions; dbo is the default. Writing dbo.Students names the object exactly, so
SQL Server does not have to resolve it per user. That is clearer, avoids picking the
wrong object, and lets cached query plans be reused.
Senior (6-9 yrs)
S A school keeps everything in spreadsheets and asks why it should move to a database. What do you tell them? scenario
A database lets many staff work at once without overwriting each other, enforces rules such as unique admission numbers and valid marks, and links data without copying it. It answers questions like "all of Kabir's absences this term" instantly, and adds security, backups, and an audit trail of who changed what.
How to answer: Lead with the pain they already feel: lost changes, inconsistent copies, slow searches. Map each pain to a database feature with an SMS example. Be honest about the cost: setup, training, and someone to own backups, and propose moving one module first.
S What is tempdb used for, and why does it matter for performance? concept
tempdb holds temporary tables and table variables, and work areas for sorting, hashing,
and row versioning. Every database on the server shares it. A busy or badly sized
tempdb slows down every application on that server, so it is usually given several
data files on fast storage.
How to answer: Explain what goes into tempdb (explicit temp objects and internal work), then why sharing makes it a bottleneck. Give an SMS case: a large report that sorts 600,000 attendance rows spills to tempdb. Close with the trade-off: more files and fast disks help, but fixing the query so it sorts less helps more.
Architect / Lead (10+ yrs)
A Would you use a relational database or a NoSQL database for a School Management System? Why? design
A relational database. School data has a stable structure and strict rules: every mark belongs to a real enrollment, and fees must add up exactly across invoices and payments. Constraints and transactions enforce exactly that. NoSQL fits flexible documents or huge write-heavy workloads, which is not the core of a school system.
How to answer: Start from the data's shape and rules, not from fashion. Name the relational strengths the SMS needs: constraints, joins, and transactions for fees. Then show balance: a NoSQL store could still fit a side feature, such as an activity feed or a search index, next to the relational core.
Resources
- Code for this lesson: git tag
lesson-5-rdbms-concepts(code repository coming soon) - Files:
queries/005-explore-server.sql - Video: coming soon
What's next
You know the building blocks. Next, you draw the full plan of the SMS database as an ER diagram. Lesson 6: ER diagram
Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.