CSIS 4720 Database Systems: Normalization, ERD, and MS Access Project

This assignment focuses on database design and implementation, starting with a STUDENT table containing a repeating group. The student normalizes the table to 1NF, 2NF, and 3NF, identifying primary and foreign keys at each normal form level. An Entity Relationship Diagram (ERD) is created using Chen's notation. Subsequently, the assignment involves creating the table structures in SQL. Finally, the student implements the system using MS Access, including creating forms for data entry, writing SQL queries to retrieve specific data based on GPA, advisor, classification, and major, and generating reports to display the query results. A data dictionary is also included, detailing the attributes of each table, along with a bibliography of references used.

Running head: Database
DATABASE
Name of the Student
Name of the University
Author Note

1DATABASE
Table of Contents
1. Given the following STUDENT table which contains a repeating group:.........................2
2. For the STUDENT table perform the following:................................................................2
a. Normalize the table into 1NF, 2NF, and 3NF.................................................................2
b. List of the primary keys, foreign keys, and all fields for each table you identify at each
normal form level...................................................................................................................3
c. Creating ERD using Chen’s Notation:............................................................................4
d. Create the structure for each table identified in the ERD...............................................4
3. Using MS Access to implement the system:......................................................................6
Creating forms to populate/enter data:...................................................................................6
Queries:..................................................................................................................................7
Reports:..................................................................................................................................9
4. Data Dictionary:................................................................................................................11
Bibliography:............................................................................................................................14

2DATABASE
1. Given the following STUDENT table which contains a repeating
group:
STUDENT (STUDENT-NUMBER, STUDENT-NAME, MAJOR, CLASSIFICATION,
TOTCRDITS, GPA, COURSE-PREFIX, COURSE-DESC, CREDIT_HOURS, GRADE,
ADVISORNUMBER, ADVISOR-NAME)
2. For the STUDENT table perform the following:
a. Normalize the table into 1NF, 2NF, and 3NF
UNF 1NF 2NF 3NF
Un normalized data Identify repeating
groups
Identify partial
dependencies
Non key dependencies
STUDENT-NUMBER,
STUDENT-NAME,
MAJOR,
CLASSIFICATION,
TOTCRDITS, GPA,
COURSE-PREFIX,
COURSE-DESC,
CREDIT_HOURS,
GRADE,
ADVISORNUMBER,
ADVISOR-NAME
STUDENT-NUMBER,
STUDENT-NAME,
MAJOR,
CLASSIFICATION,
TOTCRDITS, GPA
STUDENT-NUMBER,
COURSE-NUMBER,
COURSE-PREFIX,
COURSE-DESC,
CREDIT_HOURS,
GRADE,
ADVISORNUMBER,
ADVISOR-NAME
STUDENT-NUMBER,
STUDENT-NAME,
MAJOR,
CLASSIFICATION,
TOTCRDITS, GPA
STUDENT-NUMBER,
COURSE-NUMBER,
ADVISORNUMBER,
COURSE-PREFIX,
COURSE-DESC,
CREDIT_HOURS,
GRADE.
STUDENT-NUMBER,
STUDENT-NAME,
MAJOR,
CLASSIFICATION,
STUDENT-NUMBER,
TOTCRDITS, GPA,
CREDIT_HOURS, GRADE.
COURSE-NUMBER,
COURSE-PREFIX,
COURSE-DESC,
ADVISORNUMBER,

3DATABASE
ADVISORNUMBER,
ADVISOR-NAME
ADVISOR-NAME
STUDENT-NUMBER,
COURSE-NUMBER.
ADVISORNUMBER,
Date.
b. List of the primary keys, foreign keys, and all fields for each table you identify at
each normal form level
The primary keys are shown in bold and foreign keys in Italic and underlined.
STUDENT (STUDENT_NUMBER, STUDENT_NAME, MAJOR, CLASSIFICATION)
MARKS (STUDENT_NUMBER, TOTCRDITS, GPA, CREDIT_HOURS, GRADE.)
COURSE (COURSE_NUMBER, COURSE_PREFIX, COURSE_DESC)
ADVISOR (ADVISOR_NUMBER, ADVISOR_NAME)
STUDENT_ENROLMENT_COURSE (STUDENT_NUMBER, COURSE_NUMBER,
ADVISOR_NUMBER)

4DATABASE
c. Creating ERD using Chen’s Notation:
Figure 1: Entity Relationship Diagram using Chen’s Notation
(Source: Created by Author)
d. Create the structure for each table identified in the ERD
Student Table:
create table student (STUDENT_NUMBER INT PRIMARY KEY, STUDENT_NAME
VARCHAR (20) NOT NULL, MAJOR VARCHAR (20), CLASSIFICATION VARCHAR
(20));
Marks Table:
create table Marks (STUDENT_NUMBER INT PRIMARY KEY NOT NULL,TOTCRDITS
int not null, GPA int not null, CREDIT_HOURS int not null, GRADE VARCHAR(5) NOT
NULL,

5DATABASE
CONSTRAINT STUDENT_FK FOREIGN KEY (STUDENT_NUMBER) REFERENCES
STUDENT (STUDENT_NUMBER));
Course Table:
Create table Course (COURSE_NUMBER varchar(10) primary key, COURSE_PREFIX
varchar(20) not null, COURSE_DESC varchar(50) not null);
Advisor Table:
Create table Advisor (ADVISOR_NUMBER VARCHAR(10) PRIMARY KEY,
ADVISIOR_NAME VARCHAR(50) NOT NULL);
STUDENT_ENROLMENT_COURSE Table:
CREATE TABLE STUDENT_ENROLMENT_COURSE (STUDENT_NUMBER INT
PRIMARY KEY,
COURSE_NUMBER VARCHAR(20) NOT NULL,
ADVISOR_NUMBER varchar(20) NOT NULL,
CONSTRAINT student_number_FK FOREIGN KEY (student_NUMBER) REFERENCES
STUDENT (STUDENT_NUMBER),
CONSTRAINT COURSE_FK FOREIGN KEY (COURSE_NUMBER) REFERENCES
COURSE (COURSE_NUMBER),
CONSTRAINT ADVISOR_FK FOREIGN KEY (ADVISOR_NUMBER) REFERENCES
ADVISOR (ADVISOR_NUMBER));

6DATABASE
3. Using MS Access to implement the system:
Creating forms to populate/enter data:
Student Table form:
Marks Table form:
Course Table form:

7DATABASE
Advisor Table form:
Student_enrolment_course Table form:
Queries:
a.
SELECT * FROM STUDENT

8DATABASE
INNER JOIN MARKS ON
STUDENT.STUDENT_NUMBER=MARKS.STUDENT_NUMBER
WHERE MARKS.GPA =8;
b.
SELECT *
FROM STUDENT INNER JOIN STUDENT_ENROLMENT_COURSE ON
STUDENT.STUDENT_NUMBER=STUDENT_ENROLMENT_COURSE.STUDENT_NU
MBER
WHERE STUDENT_ENROLMENT_COURSE.ADVISOR_NUMBER="A01";
c.
SELECT *
FROM STUDENT
WHERE STUDENT.CLASSIFICATION="freshmen" OR
STUDENT.CLASSIFICATION="sophomores" OR
STUDENT.CLASSIFICATION="juniors" OR STUDENT.CLASSIFICATION="seniors";

9DATABASE
d.
select * from student
where
student.major="Math";
e.
select * from student
where
student.classification="juniors";
Reports:
 Display all students with a specific GPA

10DATABASE
 Display all students advised by a specific advisor
 Display all students who are freshmen, sophomores, juniors, or seniors
 Display students with a specific major

11DATABASE
 Display students with a specific classification
4. Data Dictionary:
Advisor Table:
COLUMN NAME DATA TYPE SIZE KEYS
ADVISOR_NUMBER VARCHAR 10 PRIMARY
KEY
ADVISIOR_NAME VARCHAR 50
Course Table:
COLUMN NAME DATA TYPE SIZE KEYS
COURSE_NUMBER VARCHAR 10 PRIMARY
KEY

12DATABASE
COURSE_PREFIX VARCHAR 20
COURSE_DESC VARCHAR 50
Marks Table:
COLUMN NAME DATA TYPE SIZE KEYS
STUDENT_NUMBER NUMBER PRIMARY
KEY,
FOREIGN
KEY
TOTCRDITS NUMBER
GPA NUMBER
CREDIT_HOURS NUMBER
GRADE VARCHAR 5
Student Table:
COLUMN NAME DATA TYPE SIZE KEYS
STUDENT_NUMBER NUMBER PRIMARY
KEY
STUDENT_NAME VARCHAR 20
MAJOR VARCHAR 20
CLASSIFICATION VARCHAR 20
Student_Enrolment_Course Table:

13DATABASE
COLUMN NAME DATA TYPE SIZE KEYS
STUDENT_NUMBER NUMBER PRIMARY
KEY,
FOREIGN
KEY.
COURSE_NUMBER VARCHAR 20 FOREIGN
KEY
ADVISOR_NUMBER VARCHAR 20 FOREIGN
KEY

14DATABASE
Bibliography:
Amin, M. Miftakul, et al. "Active Database System Approach and Rule Based in the
Development of Academic Information System." International Journal of Engineering &
Technology 7.2.26 (2018): 95-101.
Coronel, Carlos, and Steven Morris. Database systems: design, implementation, &
management. Cengage Learning, 2016.
Elmasri, Ramez, and Sham Navathe. Fundamentals of database systems. Vol. 7. Pearson,
2017.
Groom, Colin R., et al. "The Cambridge structural database." Acta Crystallographica Section
B: Structural Science, Crystal Engineering and Materials 72.2 (2016): 171-179.