Homework SourseYour database design should meet the following minimum requi
Your database design should meet the following minimum requirements
Design a database - normalizing your database design.
Construct stored procedures to manage enrollments of a student into courses.
Demonstrate the use of triggers (ensure course are not over enrolled)
Develop one (or more) views for viewing student information and course enrollments (current and previous) by denormalizing your table data into an easily readable structure
Solution
Database:
A database is a persistent, logically coherent collection of inherently meaningful data, relevant to some aspects of the real world.
Consider the Given Student Enrollment System database:
Students(STUDENT_ID, STUDENT_NAME, ADDRESS)
Enrollment(STUDENT_ID, SUBJECT_ID, MARK)
Subject(STUDENT_ID, STUDENT_NAME, DEPARTMENT)
Create student table:
CREATE TABLE Students
(
STUDENT_ID VARCHAR2(5) PRIMARY KEY,
STUDENT_NAME VARCHAR2(15),
ADDRESS VARCHAR2(25));
Create enrollment table:
CREATE TABLE Enrollment
(
STUDENT_ID VARCHAR2(5) REFERENCES STUDENTS(STUDENT_ID),
SUBJECT_ID VARCHAR2(6) REFERENCES SUBJECTS(SUBJECT_ID),
MARK NUMBER(3),
CHECK (MARK BETWEEN 0 AND 100),
CHECK (SUBJECT_ID LIKE \'MCA___\'),
PRIMARY KEY(STUDENT_ID,SUBJECT_ID));
Create subject table:
CREATE TABLE Subjects(
SUBJECT_ID VARCHAR2(6) PRIMARY KEY,
SUBJECT_NAME VARCHAR2(20),
DEPARTMENT VARCHAR2(20),
CHECK (DEPARTMENT IN(\'MATHS\',\'COMPUTER SCIENCE\')),
CHECK (SUBJECT_ID LIKE \'MCA___\'));
Description of Students table:
DESC Students;
Name Null? Type
STUDENT_ID NOT NULL VARCHAR2(5)
STUDENT_NAME VARCHAR2(15)
ADDRESS VARCHAR2(25)
Description of Enrollment table:
DESC Enrollment;
Name Null? Type
STUDENT_ID NOT NULL VARCHAR2(5)
SUBJECT_ID NOT NULL VARCHAR2(6)
MARK NUMBER(3)
DESC Subjects;
Name Null? Type
SUBJECT_ID NOT NULL VARCHAR2(6)
SUBJECT_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
Inserting values into Students table:
INSERT INTO Students (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
(\'303\',\'KRUNAL\',\'MANINAGAR,AMD\');
INSERT INTO Students (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
(\'301\',\'BHAVESH\',\'VADAJ,AMD\');
INSERT INTO Students (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
(\'309\',\'KINA\',\'GANDHINAGAR\');
INSERT INTO Students(STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
(\'310\',\'KRISHNA\',\'GANDHINAGAR\');
INSERT INTO Students (STUDENT_ID,STUDENT_NAME,ADDRESS) VALUES
(\'311\',\'RITA\',\'GANDHINAGAR\');
Inserting values into Enrollment table:
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'303\',\'MCA302\',87);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'303\',\'MCA303\',75);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'303\',\'MCA304\',70);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'303\',\'MCA305\',72);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'301\',\'MCA302\',82);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'301\',\'MCA303\',93);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'301\',\'MCA305\',70);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'309\',\'MCA302\',60);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'309\',\'MCA303\',55);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'309\',\'MCA304\',61);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'309\',\'MCA305\',67);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'310\',\'MCA302\',78);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'310\',\'MCA303\',55);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'310\',\'MCA304\',72);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'310\',\'MCA305\',90);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'311\',\'MCA302\',40);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'311\',\'MCA303\',52);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'311\',\'MCA304\',53);
INSERT INTO ENROLMENT (STUDENT_ID,SUBJECT_ID,MARK) VALUES
(\'311\',\'MCA305\',60);
Inserting values into Subjects table:
INSERT INTO Subjects (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
(\'MCA302\',\'DISCREATE MATHS\',\'MATHS\');
INSERT INTO Subjects(SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
(\'MCA303\',\'Fundamental Of C\',\'COMPUTER SCIENCE\');
INSERT INTO Subjects (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
(\'MCA304\',\'C++\',\'COMPUTER SCIENCE\');
INSERT INTO Subjects (SUBJECT_ID,SUBJECT_NAME,DEPARTMENT) VALUES
(\'MCA305\',\'VB\',\'COMPUTER SCIENCE\');
Display records of student table:
SELECT * FROM STUDENTS;
Output :
STUDE STUDENT_NAME ADDRESS
303 KRUNAL MANINAGAR,AMD
301 BHAVESH VADAJ,AMD
309 KINA GANDHINAGAR
310 KRISHNA GANDHINAGAR
311 RITA GANDHINAGAR
Display enrollments which starts with MCA letters, ex. MCA302, MCA505 etc and marks can only be between 0-100.
SELECT * FROM ENROLMENT;
Output :
STUDE SUBJEC MARK
303 MCA302 87
303 MCA303 75
303 MCA304 70
303 MCA305 72
301 MCA302 82
301 MCA303 93
301 MCA305 70
309 MCA302 60
309 MCA303 55
309 MCA304 61
309 MCA305 67
310 MCA302 78
310 MCA303 55
310 MCA304 72
310 MCA305 90
311 MCA302 40
311 MCA303 52
311 MCA304 53
311 MCA305 60
Sample Stored Procedure:
SELECT STUDENT_ID, SUBJECT_NAME, DEPARTMENT
FROM Subjects
WHERE SUBJECT_NAME = @SUBJECT_NAME AND Department = @Department;
