DBMS introduction and SQL Server basics

Access, SQL Server, Oracle, and MySQL; editions, system databases, security, backup, maintenance, and the path from ER to database.

Beginner 38 min read SQLDBMSSQL Server

Lesson 4 / 7 0%

View path

DBMS introduction and getting started with SQL Server

Level: beginner · Reading time: about 30 to 40 minutes

Topics: DBMS, Access, SQL Server, Oracle, MySQL, editions, system databases, security, backup, maintenance, case study

What will we learn?

We already know entities, attributes, keys, and ER modeling. Now we see which software runs those designs in the real world.

By the end you should be able to:

name several famous DBMS products
explain Access, SQL Server, Oracle, and MySQL at a high level
pick a better DBMS for project size
recognize SQL Server editions and system databases
understand login, sa, and sysadmin at a beginner level
understand backup and maintenance plans
see the path from ER diagram to a real database

1. What is a DBMS?

A DBMS is a Database Management System.

It sits between us and the data and handles creating databases and tables, storing and searching data, updates and deletes, users and permissions, backup and recovery, concurrency, security, and integrity.

Database = the data and its structure
DBMS = the software that manages the database

2. Well-known DBMS products

Many DBMS products exist. Famous ones include:

Microsoft Access
Microsoft SQL Server
Oracle Database
MySQL
PostgreSQL
SQLite

This course briefly introduces the first four, then focuses on SQL Server.

3. Is every DBMS right for every project?

No. Choice depends on project size, user count, data volume, concurrency, budget, security, operating system, application type, backup needs, and future growth.

A small office and a bank rarely need the same DBMS.

4. Microsoft Access

Microsoft Access targets small, simple projects.

Easy to install and use
GUI for tables, queries, forms, reports
Limited concurrent users
Usually on Windows

A small office tracking staff or inventory may find Access enough.

5. Access limitations

A large e-commerce site with thousands of concurrent users is usually a poor fit for Access; it is not built for very large scale or heavy load.

Access fits small scale best.

6. Microsoft SQL Server

SQL Server is Microsoft’s relational DBMS, from small apps to enterprise systems.

Large data volumes
Many concurrent users
Security, backup, transactions
Stored procedures, triggers, views, indexes
Management and reporting tools

It supports a client/server architecture.

7. What is client/server?

The database runs on a server; clients connect from applications:

text

Client 1 ─┐
Client 2 ─┼────> SQL Server ────> Database
Client 3 ─┤
Client 4 ─┘

Clients do not edit database files directly; they send requests to SQL Server and get results back.

8. Does SQL Server run only on Windows?

SQL Server was long tied to Windows, but modern versions also run on Linux. This course assumes Windows as the main environment.

9. Where is SQL Server used?

Examples: finance, HR, ERP, CRM, e-commerce, inventory, university, hospital, desktop, web, and enterprise apps.

SQL Server is not desktop-only; it backs Windows, web, and enterprise systems.

10. Oracle Database

Oracle Database is a major enterprise DBMS used in banks, large organizations, finance, and high-volume systems.

High scalability
Strong security
Rich management features

11. Is Oracle only for Java?

No. Oracle Database is independent of Java. Java, C#, Python, PHP, and others can connect. It is common in Java enterprise stacks, but not Java-only.

12. Is Oracle free?

Oracle has several editions and licenses. Commercial use is usually paid; limited free editions exist for learning and development. Check edition and license for production.

13. MySQL

MySQL is widely used, now supported by Oracle, and very popular on the web. PHP, Python, Java, and Node.js can all use it.

14. MySQL and PHP

PHP + MySQL has been a common web stack for years. WordPress is a famous example. MySQL is not PHP-only; any language with a driver can connect.

15. A simple DBMS comparison

DBMS	Typical use
Access	Small projects
SQL Server	Small to enterprise
Oracle	Large enterprise
MySQL	Web

This is a first glance only; real choice is more nuanced.

16. Choosing a DBMS — examples

Project 1: tiny office, two users → Access may suffice.

Project 2: company with finance, HR, inventory, many users → SQL Server.

Project 3: huge enterprise, millions of rows → Oracle.

Project 4: PHP website → MySQL.

17. Course focus from here

From here the course focuses on Microsoft SQL Server. We will not go deep into Access, Oracle, or MySQL yet; the goal is SQL and database design with SQL Server.

18. SQL Server vs SSMS

SQL Server = the database engine.

SSMS = the GUI tool for connecting, creating databases and tables, running queries, logins, backup, restore, and jobs.

SQL Server = engine · SSMS = management tool

19. SQL Server editions

Important editions:

Express
Standard
Enterprise
Developer

20. SQL Server Express

Express is the free, limited edition — good for learning, small apps, and prototypes, with caps versus larger editions.

21. SQL Server Standard

Standard fits many mid-size organizations; professional features without everything in Enterprise.

Standard = common organizational needs

22. SQL Server Enterprise

Enterprise is the full commercial edition for very large, high-load, mission-critical systems with advanced performance, availability, scalability, and security.

23. SQL Server Developer

Developer is for development, testing, and learning — nearly Enterprise features but not licensed for commercial production. Great for this course.

24. Edition summary

Edition	Use
Express	Small, free
Standard	Mid-size org
Enterprise	Large systems
Developer	Dev and learning

25. After installation in SSMS

In Object Explorer you usually see Databases, Security, Server Objects, Replication, Management, and SQL Server Agent. For now, focus on Databases.

26. System databases

After install, built-in system databases exist. Four names to know:

text

master
model
msdb
tempdb

27. master

master holds instance-level metadata; SQL Server knows which databases exist from it. Do not change it casually.

master = core server information

28. model

model is the template for new databases: model → new database.

29. msdb

msdb stores SQL Server Agent jobs, backup history, and maintenance plan data.

msdb = jobs, backup, management

30. tempdb

tempdb holds temporary data; it is recreated on restart. Never store permanent app data there.

31. System database cheat sheet

Summary:

text

master → core server info
model → template for new databases
msdb → jobs, backup, management
tempdb → temporary data

32. Security in SQL Server

Databases hold sensitive data. SQL Server security and permissions stop everyone from creating databases, dropping tables, reading finance data, or managing the server.

33. What is a login?

A login connects to SQL Server — e.g. ali, reza, app_user. Each login has different rights; not everyone should be fully privileged.

34. Login vs database user

A login is server-level. A database user is inside one database. Login → enter SQL Server → user → permissions inside a database.

35. What is sa?

sa means System Administrator — a famous login with very high access when enabled. Treat it as highly sensitive.

36. What is sysadmin?

sysadmin is a server role with near-full administrative power. Do not make every login a sysadmin.

37. Least privilege

Least privilege: give each user only the access they truly need. A read-only product app should not drop databases or create logins.

38. What is backup?

Disk failure, bad deletes, or corruption without backup can mean lost data. A core DBA duty is regular backup.

39. Backup needs a schedule

Backup should not rely on someone remembering manually — e.g. every night at 02:00 using SQL Server scheduling tools.

40. What is a maintenance plan?

A maintenance plan schedules database care — nightly backup, weekly maintenance, and related checks.

Maintenance plan = scheduled database maintenance

41. SQL Server Agent

Scheduled work runs through SQL Server Agent:

text

Every day at 02:00
        ↓
SQL Server Agent
        ↓
Backup Database

42. Backup alone is not enough

Verify backups are created, healthy, restorable, and stored safely. A DBA must be able to restore — not just say “we have a backup.”

43. What is a case study?

A case study is a realistic problem — e.g. a university system with students, teachers, courses, enrollment, and grades. We analyze before building tables.

44. Step 1 — find entities

For a university system:

text

Student
Teacher
Course
Department
Enrollment

45. Step 2 — attributes

Example Student: student_id, first_name, last_name, birth_date

Course: course_id, title, units

46. Step 3 — relationships

Department 1 ---- N Student

Student N ---- N Course → bridge entity Enrollment

47. Step 4 — ER diagram

Draw entities and relationships in an ER diagram:

text

Student 1 ---- N Enrollment N ---- 1 Course

48. Step 5 — ER to relational design

Example:

Students (student_id PK, name)

Courses (course_id PK, title)

Enrollments (student_id FK, course_id FK, grade)

49. Step 6 — build in SQL Server

After design:

sql

CREATE DATABASE UniversityDB;

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

We will learn the full syntax in later sessions.

50. The right design order

A common mistake is jumping straight to tables. The proper path:

text

Requirements
      ↓
Entities
      ↓
Attributes
      ↓
Relationships
      ↓
ER diagram
      ↓
Primary / foreign keys
      ↓
Table design
      ↓
Create database
      ↓
Implement in SQL Server

Design first, implement second.

Session summary

Four DBMS products: Access (small), SQL Server (small to enterprise), Oracle (enterprise), MySQL (web).

Course focus: SQL Server.

Editions: Express, Standard, Enterprise, Developer.

System databases: master, model, msdb, tempdb.

Security: login, sa, sysadmin, least privilege.

Backup and maintenance plans matter.

Project path: case study → analysis → ER → database design → SQL Server.

Session 4 exercises

Work these after the reading. A sample answer appears after you submit.