AS OF 12/11/2013 SCEDULR++ WILL BE LOCATED AT THE LINK BELOW

CLICK HERE FOR SCHEDULR++ Kofi Atuobi
Joe Weber
9/29/2013
Principles of Databases
Term Project: Phase 1

The database we intend to create is like Schedulr, however better. We intend to create a database that allows students to pick their major and see which courses they need to take to graduate during a specific target semester. At the moment it is for Computer Science majors only, specifically it will allow CSC and Non- CSC students who would like to transfer into CSC, to know what they need to take to graduate within a target semester. The program will list the classes depending on the studentsbill map out their schedule over their desired college career.

Business Rules
1. A student can join by registering with their email, graduating class and password.
2. A student will provide basic information about completed classes,
3. A student will have a drop list showing all majors (For this project just Computer Science)
4. A student will have the option via dropdown list to pick any minor or concentration
5. A student will be given the option if and when they would be willing to take summer courses over there college career.
6. A student will be given the option if they want to overload any given semester.
7. A student will then be shown all the semesters they have left and the courses they need to take as well as when they need to take them.
8. Electives will be provided via drop down list for the students own choosing.
9. Students will be given core classes according to their yr.
10. Students will only be scheduled a course if they have the prerequizites for it.
11. The application will save a studentbs requirements and set which graduating classes these requirements apply for.


Exceptions
1.For this project students will only be able to pick a computer science major.
2. Students will see what classes they have but not pick their professors. They will pick their professors and time them selves.
3. Students will only see a general outline, none of the classes shown will they be registered for. That is up to the student as well.
4. Students can change the times of their core and elective classes.


Questions
1. Will their AP credits count?
2. If they have to take a year off, will the program be able to include that?
3. Will the program be able to process a triple major?
4. Can the program include classes taken from semester abroad courses?
5. Will I be able to plan summmer classes?
6. Will I be able to plan for study abroad?



ER Diagram
:(
Schema Diagram

During the conversion process we made a few changes to our orriginal design. We changed the namme of the "Semester" entity to "Term" to make it more applicable to different school schedules. We also added a "slots" attribute to the Term entity in order to allow students to take different number of classes each semester. We also added a "Abroad" attribute to signal if the student is studying abroad during the semester.

Updated Changes 11/20/13


We updated the diagram and schema on Schedulr++ to include a more specified explanation. This update for the diagram and schema occurred by making a new entity, business rule, and specifying various entities with more information, like dates, and terms. The new entity is called Schedule and it interelates the Student, Classes and Terms. A date was added in the Degree entity. The class entity has also updated its term idea. The Requirement entity now has a Completed (Class ID, Term ID) part.

Functional Dependencies and 3NF

Our schema is of 2nd normal because our non keys are only dependent on other keys.
The functional dependencies are evaluated seperatly for each table.

- Student table
Degree -> Degree/Certificate-ID
- Term table
No columns depend on anything other than the primary key.
- Degree/Certificate table
No columns depend on anything other than the primary key.
- Requirement table
Name -> Term-slots
- Class table
Name -> Requirment-Name
Semester - > Term-TermID#

Schema DDL Script

ALTER TABLE Completed
DROP FOREIGN KEY completed_fk_3;
ALTER TABLE Completed
DROP FOREIGN KEY completed_fk_1;
ALTER TABLE Completed
DROP FOREIGN KEY completed_fk_2;

ALTER TABLE Fulfills
DROP FOREIGN KEY fulfills_fk_2;
ALTER TABLE Fulfills
DROP FOREIGN KEY fulfills_fk_1;

ALTER TABLE Requires
DROP FOREIGN KEY requires_fk_2;
ALTER TABLE Requires
DROP FOREIGN KEY requires_fk_1;

ALTER TABLE SDegree
DROP FOREIGN KEY sdegree_fk_2;
ALTER TABLE SDegree
DROP FOREIGN KEY sdegree_fk_1;

ALTER TABLE Slot
DROP FOREIGN KEY slot_fk_2;
ALTER TABLE Slot
DROP FOREIGN KEY slot_fk_1;

/*-------------------------------------------------------
---DELETE Section----------------------------------------
------------------------------------------------------*/

Drop table if exists Student;
Drop table if exists Degree;
Drop table if exists SDegree;
Drop table if exists Requires;
Drop table if exists Requirement;
Drop table if exists Completed;
Drop table if exists Fulfills;
Drop table if exists Class;
Drop table if exists Slot;
Drop table if exists Term;
/*------------------------------------------------------
--Create Section
-------------------------------------------------------*/
CREATE TABLE Student(
StudentID Integer not null,
Name Varchar(100),
Enroll Date not null,
Graduate Date,

Constraint uk_student UNIQUE (StudentID),
Constraint pk_student PRIMARY KEY (StudentID)
);

CREATE TABLE Degree(
DegreeID integer not null,
Subject varchar(100),
Lvl varchar(100),
Completion date,

Constraint uk_degree UNIQUE (DegreeID),
Constraint pk_degree PRIMARY KEY (DegreeID)
);

CREATE TABLE SDegree(
DegreeID integer,
StudentID integer
);

CREATE TABLE Requires(
DegreeID integer,
Requirement varchar(100) not null
);

CREATE TABLE Requirement(
Name varchar(100) not null,

Constraint uk_requirement UNIQUE (Name),
Constraint pk_requirement PRIMARY KEY (Name)
);

CREATE TABLE Completed(
StudentID int not null,
Requirement varchar(100) not null,
Slot integer
);
CREATE TABLE Fulfills(
Requirement varchar(100) not null,
ClassID integer not null
);

CREATE TABLE Class(
ClassID integer not null,
Name varchar(100),
Semesteroffered varchar(8),

Constraint uk_class UNIQUE (ClassID),
Constraint pk_class PRIMARY KEY (ClassID)
);

CREATE TABLE Slot(
SlotID integer not null,
TermID integer,
ClassID integer,

Constraint uk_slot UNIQUE (SlotID),
Constraint pk_slot PRIMARY KEY (SlotID)
);

CREATE TABLE Term(
TermID integer not null,
Time varchar(8)
);

/*--------------------------------------------------
Setting foriegn key constraints
--------------------------------------------------*/

ALTER TABLE `Completed`
ADD CONSTRAINT `completed_fk_3` FOREIGN KEY (`Slot`) REFERENCES `Slot` (`SlotID`),
ADD CONSTRAINT `completed_fk_1` FOREIGN KEY (`StudentID`) REFERENCES `Student` (`StudentID`),
ADD CONSTRAINT `completed_fk_2` FOREIGN KEY (`Requirement`) REFERENCES `Requirement` (`Name`);

ALTER TABLE `Fulfills`
ADD CONSTRAINT `fulfills_fk_2` FOREIGN KEY (`ClassID`) REFERENCES `Class` (`ClassID`),
ADD CONSTRAINT `fulfills_fk_1` FOREIGN KEY (`Requirement`) REFERENCES `Requirement` (`Name`);

ALTER TABLE `Requires`
ADD CONSTRAINT `requires_fk_2` FOREIGN KEY (`Requirement`) REFERENCES `Requirement` (`Name`),
ADD CONSTRAINT `requires_fk_1` FOREIGN KEY (`DegreeID`) REFERENCES `Degree` (`DegreeID`);

ALTER TABLE `SDegree`
ADD CONSTRAINT `sdegree_fk_2` FOREIGN KEY (`StudentID`) REFERENCES `Student` (`StudentID`),
ADD CONSTRAINT `sdegree_fk_1` FOREIGN KEY (`DegreeID`) REFERENCES `Degree` (`DegreeID`);

ALTER TABLE `Slot`
ADD CONSTRAINT `slot_fk_2` FOREIGN KEY (`ClassID`) REFERENCES `Class` (`ClassID`),
ADD CONSTRAINT `slot_fk_1` FOREIGN KEY (`TermID`) REFERENCES `Term` (`TermID`);

/*------------------------------------------------------------
inserts!
------------------------------------------------------------*/


insert into Degree values (1, 'Computer Science', 'Ba', null);
insert into Degree values (2, 'Computer Science', 'PHD', null);

insert into Requirement values ('intro algorithm');
insert into Requirement values ('intermediate algorithm');
insert into Requirement values ('intro to computer systems');

insert into Student values (5,'Joe Weber','2011-09-09','2015-05-20');
insert into Student values (6,'Kofi Atoubi','2011-09-09','2015-05-20');

insert into Term values (1,'fal 2013');
insert into Term values (2,'spr 2014');

insert into Class values (1051, 'Algorithms and Data Structures I', 'spr 2014');
insert into Class values (1052, 'Algorithms and Data Structures II', 'fal 2014');
insert into Class values (2400, 'Computer Systems II', 'fal 2013');

insert into Fulfills values ('intro algorithm', 1051);
insert into Fulfills values ('intermediate algorithm', 1052);
insert into Fulfills values ('intro to computer systems', 2400);

insert into Requires values (1, 'intro algorithm');
insert into Requires values (1, 'intermediate algorithm');
insert into Requires values (2, 'intro algorithm');
insert into Requires values (2, 'intermediate algorithm');
insert into Requires values (2, 'intro to computer systems');

insert into SDegree values (1,5);
insert into SDegree values (2,6);

insert into Slot values (1, 1, 1051);

insert into Completed values (5, 'intro algorithm', 1);

Sample DML Population

ysql> select * from Class;
+---------+-----------------------------------+-----------------+
| ClassID | Name | Semesteroffered |
+---------+-----------------------------------+-----------------+
| 1051 | Algorithms and Data Structures I | spr 2014 |
| 1052 | Algorithms and Data Structures II | fal 2014 |
| 2400 | Computer Systems II | fal 2013 |
+---------+-----------------------------------+-----------------+
3 rows in set (0.00 sec)

mysql> select * from Completed;
+-----------+-----------------+------+
| StudentID | Requirement | Slot |
+-----------+-----------------+------+
| 5 | intro algorithm | 1 |
+-----------+-----------------+------+
1 row in set (0.01 sec)

mysql> select * from Degree;
+----------+------------------+------+------------+
| DegreeID | Subject | Lvl | Completion |
+----------+------------------+------+------------+
| 1 | Computer Science | Ba | NULL |
| 2 | Computer Science | PHD | NULL |
+----------+------------------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from Fulfills;
+---------------------------+---------+
| Requirement | ClassID |
+---------------------------+---------+
| intro algorithm | 1051 |
| intermediate algorithm | 1052 |
| intro to computer systems | 2400 |
+---------------------------+---------+
3 rows in set (0.00 sec)

mysql> select * from Requirement;
+---------------------------+
| Name |
+---------------------------+
| intermediate algorithm |
| intro algorithm |
| intro to computer systems |
+---------------------------+
3 rows in set (0.00 sec)

mysql> select * from Requires;
+----------+---------------------------+
| DegreeID | Requirement |
+----------+---------------------------+
| 1 | intro algorithm |
| 1 | intermediate algorithm |
| 2 | intro algorithm |
| 2 | intermediate algorithm |
| 2 | intro to computer systems |
+----------+---------------------------+
5 rows in set (0.00 sec)

mysql> select * from SDegree;
+----------+-----------+
| DegreeID | StudentID |
+----------+-----------+
| 1 | 5 |
| 2 | 6 |
+----------+-----------+
2 rows in set (0.00 sec)

mysql> select * from Slot;
+--------+--------+---------+
| SlotID | TermID | ClassID |
+--------+--------+---------+
| 1 | 1 | 1051 |
+--------+--------+---------+
1 row in set (0.00 sec)

mysql> select * from Slot;
+--------+--------+---------+
| SlotID | TermID | ClassID |
+--------+--------+---------+
| 1 | 1 | 1051 |
+--------+--------+---------+
1 row in set (0.00 sec)

mysql> select * from Term;
+--------+----------+
| TermID | Time |
+--------+----------+
| 1 | fal 2013 |
| 2 | spr 2014 |
+--------+----------+
2 rows in set (0.00 sec)

Project Phase 5

Questions and queries that answer them.

Question 3 from the original project proposal....
3.A student will have a drop down list of all subjects available to study.


mysql> select distinct Subject from Degree;
+------------------+
| Subject |
+------------------+
| Computer Science |
+------------------+
1 row in set (0.00 sec)

Question 4 from original project proposal....
4.A student wil be able to choose the level of study of what subjects they choose.
Example -> A student querying the different levels of Computer Science available...

mysql> select distinct Lvl from Degree where Subject = 'Computer Science';
+------+
| Lvl |
+------+
| Ba |
| PHD |
+------+
2 rows in set (0.00 sec)

Question 10 from original project proposal....

10.A student will only be scheduled a course if they have the prequisite for it.
Example -> A student seeing which classes they can take given that they have completed
1051
mysql> select c.Name from Class c, Prerequisite p where c.ClassID = p.ClassID
and p.PClassID IN ( select co.ClassID from Completed co where co.StudentID = 5);
+-----------------------------------+
| Name |
+-----------------------------------+
| Algorithms and Data Structures II |
| Discrete Structures |
+-----------------------------------+
2 rows in set (0.00 sec)

New Question 1
Students can check which requirements in their degree remain to be filled.
Example -> A csc students remaining requirements with Algorthims and Data Structures I done
which fulfills the intro to algorithms requirement for csc.
mysql> select distinct rs.Requirement from Requires rs, Fulfills f where
rs.Requirement =f.Requirement and f.ClassID NOT IN (select co.ClassID from Completed co where co.StudentID=5);
+---------------------------------+
| Requirement |
+---------------------------------+
| intermediate algorithms for csc |
| intro to csc theory |
+---------------------------------+
2 rows in set (0.00 sec)

New Question 2
2. A student can check which classes can fulfill the same requirments
Example -> A student checking which classes fulfill the introduction to
csc theory requirement.
mysql> select c.Name from Class c, Fulfills f where c.ClassID = f. ClassID
and f.Requirement='intro to csc theory';
+-----------------------------------+
| Name |
+-----------------------------------+
| Discrete Structures |
| Discrete Structures for Computing |
+-----------------------------------+
2 rows in set (0.00 sec)

New Question 3
3. A student can see which semesters a class is offered
Example -> A student chekcing which semesters (currently planned for)
the class Algorithms and Data Structures II is offered.

mysql> select t.Time from SemesterOffered s, Term t where s.TermID =t.TermID
and s.ClassID IN (select c.ClassID from Class c where Name='Algorithms and Data Structures II');
+----------+
| Time |
+----------+
| fal 2011 |
| spr 2012 |
| sum 2012 |
| fal 2012 |
| spr 2013 |
+----------+
5 rows in set (0.00 sec)