Revision notes

Database Basics: Foundation

The key points, rules to remember, tables and diagrams from all 9 lessons, on one page for quick revision. To keep a copy, use your browser's Print and choose "Save as PDF".

Lesson 1

Data, database and DBMS

Database = organised data. DBMS = software that manages the database.

— Remember
  • A database stores organised data; a DBMS manages it.
  • A DBMS reduces redundancy and inconsistency and adds security.
Read the full lesson

Lesson 2

Tables, records, fields and keys

Table: Students
RollNameCityMarks
1AshaPune72
2RaviJaipur55
3MeenaPatna90
4ImranPune63

Row = record, column = field. Primary key = unique identifier; foreign key = link to another table.

— Remember
  • Tables have fields (columns) and records (rows).
  • Primary keys are unique; foreign keys connect tables.
Read the full lesson

Lesson 3

Relational databases and relationships

Relationships
TypeMeaningExample
One-to-one (1:1)One record matches exactly one recordOne person and one Aadhaar-style ID record
One-to-many (1:N)One record matches many recordsOne class has many students
Many-to-many (M:N)Many records match many recordsStudents and subjects: a student takes many subjects and a subject has many students

1:1, 1:N, M:N. Many-to-many needs a third (junction) table.

— Remember
  • Relational databases connect tables with keys.
  • Three relationship types: 1:1, 1:N and M:N.
Read the full lesson

Lesson 4

SQL: asking questions of a database

Students
RollNameCityMarks
1AshaPune72
2RaviJaipur55
3MeenaPatna90
4ImranPune63
Core commands
CommandPurposeExample
SELECTRead dataSELECT Name FROM Students WHERE Marks > 60;
INSERTAdd a recordINSERT INTO Students VALUES (5, 'Kiran', 'Surat', 80);
UPDATEChange a recordUPDATE Students SET Marks = 58 WHERE Roll = 2;
DELETERemove recordsDELETE FROM Students WHERE Roll = 4;

SELECT reads, INSERT adds, UPDATE changes, DELETE removes; WHERE picks the rows.

— Remember
  • Know SELECT, INSERT, UPDATE and DELETE.
  • WHERE limits the rows; leaving it out affects all rows.
  • COUNT, SUM, AVG, MAX, MIN summarise data.
Read the full lesson

Lesson 5

Integrity, security and database users

Roles
RoleJob
Database administrator (DBA)Controls, secures and maintains the database
Application programmerWrites programs that use the database
End userUses the data through forms and reports

Integrity keeps data correct, authorisation limits actions, backups allow recovery.

— Remember
  • Integrity rules keep data valid.
  • Normalisation reduces repetition.
  • Authentication, authorisation and backups protect the data.
Read the full lesson

Lesson 6

Database models: hierarchical, network, relational and NoSQL

Data models compared
ModelShapeParents per childExample picture
HierarchicalTreeOneFolders inside folders; school to class to student
NetworkWebManyA student in several clubs
RelationalTablesNot applicable; linked by keysStudent table linked to Class table
NoSQLDocuments, key-value or graphDepends on the typeA profile stored as one flexible document

Tree = hierarchical, web = network, tables = relational, flexible non-table stores = NoSQL.

— Remember
  • A data model decides how data is arranged and linked.
  • Hierarchical records have one parent; network records can have many.
  • Relational databases use tables and SQL.
  • NoSQL covers flexible, non-table databases.
Read the full lesson

Lesson 7

Fields and data types: choosing the right type

Data types
Data typeHoldsExample field
Text (string, CHAR/VARCHAR)Letters, digits and symbols treated as textName, address, phone number
IntegerWhole numbersNumber of copies, age
Decimal / FloatNumbers with a fractional partPrice, percentage
Date / TimeCalendar dates and timesDate of birth, date of issue
Boolean (Yes/No)Only two values: true or falseFees paid?
CurrencyMoney amounts with fixed decimalsFee amount
AutoNumberA number the system increases by itselfRecord number

Number type only for values you calculate with. Codes and phone numbers are text.

— Remember
  • Match the data type to the kind of value and the operations you need.
  • NULL means unknown, not zero.
  • Validation rules and defaults keep data accurate and save effort.
  • A primary key must be unique and not NULL.
Read the full lesson

Lesson 8

More SQL: conditions, patterns and changing data

WHERE picks rows. AND needs both, OR needs one. % means many characters, _ means one. Never run UPDATE or DELETE without WHERE unless you mean every row.

— Remember
  • Combine conditions with AND, OR and NOT.
  • BETWEEN includes both ends.
  • LIKE uses % and _ for patterns.
  • ORDER BY sorts; DISTINCT removes duplicates.
  • UPDATE and DELETE need a careful WHERE.
Read the full lesson

Lesson 9

Designing a simple database step by step

Entities become tables, identifiers become primary keys, and links become foreign keys.

— Remember
  • Design on paper first: entities, attributes, keys, links.
  • Store each fact once to avoid inconsistency.
  • A linking table turns many-to-many into two one-to-many relationships.
  • Forms enter data, queries ask questions, reports present answers.
Read the full lesson

Privacy · Terms · Refunds · Delivery