Objectives

  • Create tables with a primary key.
  • Enter a small data set and run a select query.

Theory: Introduction to databases, data concepts, DBMS.

If Access is unavailable, use LibreOffice Base or a spreadsheet with two sheets named as tables, plus a FILTER or VLOOKUP demo, and note the substitute in your report.

Tasks (55 minutes)

Database file Surname_IT231_Lab7.accdb (or equivalent):

  1. Table Students: StudentID (PK, Short Text), FullName, Program (e.g. BBA).
  2. Table Enrolments: EnrolID (PK, AutoNumber), StudentID, CourseCode (IT231), Marks.
  3. Enter at least five students and six enrolment rows.
  4. Query: list FullName and Marks for CourseCode = "IT231", sorted by Marks descending.
  5. Optional: a simple form to add a student.

Do not store real personal data of classmates without permission; use fictional names.

Before You Start

  • Open Access and choose Blank database, then name and save the file before creating anything. Access saves to disk continuously.
  • Use fictional names only (see the sample data below).
  • Decide each field’s data type before you type data: IDs as Short Text or AutoNumber, Marks as Number.

Step-by-Step Guide

  1. Students table. Create → Table Design. Fields: StudentID (Short Text), FullName (Short Text) and Program (Short Text). Select StudentID and click Primary Key (a key icon appears). Save the table as Students.
  2. Enrolments table. Fields: EnrolID (AutoNumber, primary key), StudentID (Short Text), CourseCode (Short Text) and Marks (Number). Save it as Enrolments.
  3. Relationship. Database Tools → Relationships. Add both tables, drag Students.StudentID onto Enrolments.StudentID, tick Enforce Referential Integrity, and click Create. A one-to-many (1–∞) line appears.

  4. Enter the sample data (switch to Datasheet View):

    StudentID FullName Program
    S001 Aarati Gurung BBA
    S002 Bikash Thapa BBA
    S003 Dipesh Rai BBA
    S004 Kritika Shrestha BBA
    S005 Sujan Adhikari BBA

    Enrolments (EnrolID fills itself): S001 IT231 78; S002 IT231 64; S003 IT231 85; S004 IT231 71; S005 IT231 58; S001 ENG201 66.

  5. Query. Create → Query Design, and add both tables (the join line appears automatically). Double-click FullName, CourseCode and Marks. In the CourseCode Criteria row type "IT231". In the Marks Sort row choose Descending. Untick Show under CourseCode if you only want name and marks. Click Run.
  6. Expected result (5 rows): Dipesh Rai 85; Aarati Gurung 78; Kritika Shrestha 71; Bikash Thapa 64; Sujan Adhikari 58. The ENG201 row is excluded by the criteria. Save the query as qryIT231Marks.

Check Your Work

Checkpoint How to verify
Keys Both tables show a key icon on their primary key field
Relationship A 1-to-many line with referential integrity joins StudentID
Data types Marks is a Number field (it sorts 100 after 99, not after 10)
Query output Five IT231 rows, highest marks first

Common Errors and Fixes

Problem Fix
“You cannot add or change a record because a related record is required” Referential integrity is working: add the student to Students before enrolling them.
Marks sort as 100, 58, 64… Marks is stored as Short Text. Change the field to Number in Design View.
Query returns no rows Criteria has a typo or a space (e.g. "IT 231"). It must match the stored value exactly.
Duplicate key error on StudentID Each StudentID must be unique in Students; use S006 and so on for new students.

Extension Task

For those who finish early: Add a query showing each student’s average marks (Query Design → Totals, Group By FullName, Avg of Marks), and build a Report (Create → Report Wizard) that lists students grouped by CourseCode.

Deliverable

The database file plus a screenshot of the query datasheet.

Viva

  1. What is a primary key?
  2. Why is a database better than many Excel files for student records?
  3. What does a query do that a table does not?
  4. Name two functions of a DBMS besides storing data.
  5. What is a foreign key? Which field is the foreign key here?
  6. What does “Enforce Referential Integrity” prevent?
  7. Why is StudentID stored as Short Text and not Number?
  8. What is the difference between a table, a query, a form and a report?