Database Design for Student Registration System

This assignment focuses on designing a database for a student registration system. It requires creating context, level-0, and level-1 Data Flow Diagrams (DFDs) to illustrate the system's information flow. Additionally, you need to develop a data model with tables like 'student', 'class', 'semester', 'transcript', 'prerequisites', and 'rooms'. Finally, construct a CRUD matrix outlining the read, create, update, and delete operations for processes related to transcripts, rooms, enrollment, and course management.

8/17/2017
SYSTEM ANALYSIS
STUDENT NAME
STUDENT ID

Table of Contents
PART A- Case Study: Student Registration System................................................................................................................3
TASK 1: IDENTIFY ENTITIES – NOUN PHRASE ANALYSIS....................................................................................................3
TASK 2: IDENTIFY RELATIONSHIP – VERB ANALYSIS..........................................................................................................3
TASK 3: ENTITY RELATIONSHIP DIAGRAM.........................................................................................................................4
TASK 4: PREPARE DATA DICTIONARY FOR ENTITIES..........................................................................................................4
PART B Case: Student Registration System...........................................................................................................................7
TASK 1: CONTEXT DATA FLOW DIAGRAM.........................................................................................................................7
TASK 2: LEVEL-0 DFD.........................................................................................................................................................8
TASK 3: LEVEL-1 DFD HERE................................................................................................................................................9
TASK 4: DATA MODEL and PROCESS MODEL - CRUD MATRIX...........................................................................................9

TABLE OF FIGURES
Figure 1: Chens database notation ERD................................................................................................................................4
Figure 2: context diagram.....................................................................................................................................................7
Figure 3: Level-0 DFD............................................................................................................................................................8
Figure 4: Level-1 DFD............................................................................................................................................................9

PART A- Case Study: Student Registration System
TASK 1: IDENTIFY ENTITIES – NOUN PHRASE ANALYSIS
The following are the entities derived;
 Student
 Section
 Semester
 Prerequisite
 Transcript
 Classes
 Grades
 Rooms
 Notifications
TASK 2: IDENTIFY RELATIONSHIP – VERB ANALYSIS
Entity
relation
matrix
Studen
t
Sectio
n
Semeste
r
Prerequisite
s
Transcrip
t
Classe
s
Grades Rooms Notification
s
student Enrolls
in
has attend
s
achieves uses gets
section
Semester has belong Achieve
d in
Prerequisite
s
Transcript gets Gotten
from
results make
Classes uses Assigne
d to
Grades gets Gotten
in
make make Gotten
in
Rooms uses uses
Notification
s
gets Sent in
a

TASK 3: ENTITY RELATIONSHIP DIAGRAM
Figure 1: Chens database notation ERD
TASK 4: PREPARE DATA DICTIONARY FOR ENTITIES
 Table student
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
studentID INT NO Constraint primary key
Auto increment
Name VARCHAR(100) NO Composite key
F_name VARCHAR(50) NO
L_name VARCHAR (50) NO
Email VARCHAR(250) NO
Address VARCHAR(100) NO
gender VARCHAR(10) NO

 Table semester
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
semesterID INT NO Constraint primary key
Auto increment
period VARCHAR(100) NO Composite key
Start_date DATE NO
End_date DATE NO
 Table sections
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
sectionID INT NO Constraint primary key
Auto increment
period VARCHAR(100) NO Composite key
Start_date DATE NO
End_date DATE NO
semesterID INT NO Constraint foreign key (semesterID) References
semester(SemesterID)
 Table class
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
classID INT NO Constraint primary key
Auto increment
name VARCHAR(100) NO
roomNO INT NO Constraint foreign key (roomNO) references room(roomNO)
limit INT NO
 Table student_classes
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
studentID INT NO Constraint foreign key (studentID) references student (studentId)
classID INT NO Constraint foreign key (classID) references class (classID)
Date_enrolled
TIMESTAMP NO Timesamp
0=current
timestamp
 Table notifications
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
notificationID INT NO Constraint primary key
Auto increment
message VARCHAR(500) NO
studentID INT NO Constraint foreign key (student)references student (studentID)

 Table grades
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
gradeID INT NO Constraint primary key
Auto increment
grade VARCHAR(5) NO
classID INT NO Constraint foreign key (classID) references class (classID)
studentID INT NO Constraint foreign key (student)references student (studentID)
semesterID INT NO Constraint foreign key (semesterID) References
semester(SemesterID)
 Table transcript
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
transcriptID INT NO Constraint primary key
Auto increment
details TEXT NO
studentID INT NO Constraint foreign key (student)references student (studentID)
semesterID INT NO Constraint foreign key (semesterID) References
semester(SemesterID)
 Table prerequisites
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
prerequisiteI
D
INT NO Constraint primary key
Auto increment
Details TEXT NO
classID INT NO Constraint foreign key (classID) references class (classID)
 Table rooms
Attribute Data Type Nul
l
Default Constraint/criteria/options/comments
roomNO INT NO Constraint primary key
Auto increment
name VARCHAR(50) YES

PART B Case: Student Registration System
TASK 1: CONTEXT DATA FLOW DIAGRAM
Figure 2: context diagram

TASK 2: LEVEL-0 DFD
Figure 3: Level-0 DFD

TASK 3: LEVEL-1 DFD HERE
Figure 4: Level-1 DFD
TASK 4: DATA MODEL and PROCESS MODEL - CRUD MATRIX
Processes transcripts rooms Waiting list enrollment section course
Verify
prerequisites
READ READ
Check room
availability
READ CREATE READ,CREATE
Add to waiting
list
READ CREATE
Notify
enrollment
READ READ
Enroll CREATE UPDATE