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

All lessons in HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates