DATABASE SYSTEM ASSIGNMENT
A) Plan the Database Environment (Logical Framework)
Planning the database environment involves identifying what information is required, how it will be
stored, and how different data items relate.
For example, when creating a School Student Management System, the following plan applies:
1. Purpose of the Database
To store, manage, and process student information, class details, subjects, and exam scores.
2. Identify Key Entities (Main data groups)
Student
Class
Subject
Marks
3. Identify Attributes (Data fields for each entity)
Entity Attributes (Fields)
StudentStudentID, Name, Gender, DateOfBirth, ClassID
Class ClassID, ClassName, TeacherID
Subject SubjectID, SubjectName
Marks MarkID, StudentID, SubjectID, Score
4. Identify Relationships
One class has many students (1–M)
One student has many marks (1–M)
One subject has many marks (1–M)
5. Logical Framework – ERD (Entity Relationship Diagram
+-------------+ 1 M +-------------+
| Class |---------------------| Student |
|-------------| |-------------|
| ClassID (PK)| | StudentID(PK)|
| ClassName | | Name |
+-------------+ | Gender |
| ClassID (FK) |
+--------------+
1 M
+-------------+------------+ +-------------+
| Subject | |------| Marks |
|-------------| | |-------------|
| SubjectID(PK)| | MarkID (PK) |
| SubjectName | | StudentID(FK)|
| SubjectID(FK)|
| Score |
+-------------
B) Design Database System
This step involves converting the plan into a structure that can be implemented.
1. Table Design Structure
Table Name Fields PK/FK Description
StudentStudentID, Name, Gender, DOB, ClassID PK = StudentID; FK = ClassID Stores student personal
info
Class ClassID, ClassName, TeacherID PK = ClassID Stores class details
Subject SubjectID, SubjectNamePK = SubjectID Stores subjects
Marks MarkID, StudentID, SubjectID, Score PK = MarkID; FK = StudentID, SubjectID Stores student
exam scores
2. Constraints
Primary Keys must be unique
Score must range from 0–100
Name cannot be blank
ClassID must match an existing class
3. Design of Forms and Reports
Forms: Student Entry Form, Marks Entry Form
Reports: Student List Report, Class Performance Report
C) Create Database (Tables, Queries, Forms, Reports)
This is the practical implementation in MS Access.
1. Create the Tables
Example table (Student):
Field Name Data Type Description
StudentID AutoNumber Primary Key
Name Short Text Student full name
Gender Short Text M/F
DateOfBirth Date/Time DOB
ClassID Number Foreign Key
2. Create Queries
Examples:
Query 1: List all students in Class 1
Query 2: Show marks greater than 75
Query 3: Calculate average score per subject
3. Create Forms
Student Entry Form – used to input student details
Marks Entry Form – used to add exam scores
4. Create Reports
Student List Report – shows all students
Performance Report – displays student results and averages
D) Demonstrate Procedures for Data Collection
This describes how data is gathered, entered, and prepared for storage.
1. Data Collection Tools
Registration forms
Exam scoring sheets
Attendance lists
2. Data Entry Procedure
1. Open the Student Entry Form.
2. Type in: Name, Gender, Date of Birth, Class.
3. Save the record.
4. Open Marks Entry Form and enter: StudentID, SubjectID, Score.
5. Validate data:
Ensure score is 0–100
Ensure StudentID exists in Student table
3. Verification of Data
Cross-check entries with paper records
Correct errors immediately
4. Storage
Data is automatically saved inside the database tables.
5. Backup
Save the database to external drive or cloud storage.
E) Demonstrate DBMS (Log in, User Interface, Enter Data, Retrieve, Analyze, Generate Reports)
1. Log In
Open MS Access
Open the database file
Enter password (if protected)
2. Use the User Interface
Use navigation pane to open tables, queries, forms, reports
Use ribbon (Home, Create, Database Tools, etc.)
3. Enter Data
Example (using Student Entry Form):
Enter “Mary John, Female, Class 2, Born 2010”
Save record
4. Retrieve Data
Using queries:
Show students in Class 3
List students who scored above 80
Display subjects taught in the school
5. Analyze Data
Using query design tools:
Calculation of average scores
Sorting marks from highest to lowest
Summaries (Count, Sum, Max, Min)
6. Generate Reports
Examples:
Class Performance Report (average per subject)
Student List Report (all students with class)
Top Performers Report
Reports can be printed or saved as PDF.