Courses / HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates
4.1 Database Basics, ER Model and Keys
Area 4 – Databases (10%) · 45 min read · 10 MCQs to check yourself
Primary and foreign keys (Eddie Woo)
Why a DBMS?
A DBMS (MySQL, PostgreSQL, Oracle, SQL Server) stores shared data with less redundancy, enforces integrity, controls concurrent access, provides security and backup/recovery.
Three-schema architecture
External (user views) → Conceptual (logical design: tables, relationships) → Internal (physical storage). Data independence: change one level without rewriting the others (logical and physical independence).
ER model
| ER symbol | Meaning |
|---|---|
| Rectangle | Entity (Student, Course) |
| Ellipse | Attribute (underlined = key; double ellipse = multivalued; dashed = derived) |
| Diamond | Relationship (Enrols) |
| Double rectangle | Weak entity (no key of its own, e.g. Dependent of Employee) |
| Cardinality: 1:1, 1:N, M:N. An M:N relationship becomes a separate table (junction table) holding both foreign keys. |
Relational model vocabulary
Table = relation, row = tuple, column = attribute, number of columns = degree, number of rows = cardinality.
Keys
| Key | Meaning |
|---|---|
| Super key | Any set of columns that uniquely identifies a row |
| Candidate key | A minimal super key |
| Primary key | The candidate key chosen; unique and NOT NULL |
| Alternate key | Candidate keys not chosen |
| Composite key | Key made of 2+ columns |
| Foreign key | Column that refers to the primary key of another table – enforces referential integrity |
| Surrogate key | Artificial key, e.g. auto-increment id |
This lesson ends with a quiz of 10 MCQs. Open the lesson on PrepOasis to take it and save your progress.
Previous: Area 3 Review, Checklist and Area Test · Next: 4.2 SQL – Queries, Joins and Aggregation