Database Concepts and Keys
Data is raw facts; information is processed, meaningful data. A database is an organised collection of related data. A DBMS (Database Management System) is the software that creates, stores, retrieves and protects it. Examples: MySQL, Oracle, MS SQL Server, PostgreSQL, MS Access. MongoDB is a NoSQL database.
Why a DBMS beats separate files
Less redundancy (repeated data), better consistency, sharing among users, security and access control, backup and recovery, and data independence (changing storage does not change programs).
Database models
| Model | Structure |
|---|---|
| Hierarchical | Tree, parent and child (one parent). |
| Network | Graph, a child may have many parents. |
| Relational | Tables joined by keys. Proposed by E. F. Codd (1970). The most used model. |
| Object-oriented | Data stored as objects. |
Relational terms
| Formal term | Common term |
|---|---|
| Relation | Table |
| Tuple | Row or record |
| Attribute | Column or field |
| Degree | Number of columns |
| Cardinality | Number of rows |
| Domain | Set of allowed values for a column |
Keys
| Key | Meaning |
|---|---|
| Super key | Any set of columns that identifies a row uniquely. |
| Candidate key | A minimal super key. A table can have several. |
| Primary key | The candidate key chosen to identify rows. Unique and never NULL. Only one per table. |
| Alternate key | Candidate keys not chosen as primary. |
| Composite key | A key made of two or more columns. |
| Foreign key | A column that refers to the primary key of another table. Links tables. |
ER model
An Entity-Relationship diagram shows the design: entity (rectangle), attribute (ellipse), relationship (diamond). Relationship types: one-to-one (1:1), one-to-many (1:N), many-to-many (M:N). Example: one teacher teaches many students (1:N).
Other ideas
- Schema is the structure of the database. Three-level architecture: internal (physical), conceptual (logical), external (user views).
- DBA: Database Administrator.
- Transaction and ACID properties: Atomicity (all or nothing), Consistency, Isolation, Durability. COMMIT saves a transaction; ROLLBACK undoes it.
- Data warehouse: large store of historical data for analysis. Data mining: finding patterns in large data.
MS Access
MS Access is a desktop RDBMS (file extension .accdb). Its objects: Tables (store data), Queries (retrieve and compute), Forms (data entry), Reports (printed output), Macros and Modules. Data types: Short Text, Long Text, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, Attachment. Views: Datasheet and Design.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.