Curriculum Database BY Albert Fiorillo Jagmohan Gandhi Tezir

Curriculum Database BY: Albert Fiorillo Jagmohan Gandhi Tezir Turgut Winston Casalinuevo

OUTLINE Purpose of Database Functions Use Case Diagrams 1. 2. Get Approval Changes for Classes. Entity Relationship Diagram Database Relationship Diagram Objects, Attributes, and Queries

Purpose of Database To support activities of the Curriculum Committee of the college and Curriculum Committees of departments

FUNCTIONS Departments can support their local databases and export data to the college database Build unique format of syllabi of courses in departments. Track changes in curricula. Produce required reports. Export/Import data to and from other applications.

Use Case Diagram 1 Get Approval Changes

Use Case Diagram 2 Changes for Classes Approved

Sequence Diagram 2 Approved Change

COURSE Course is the central object of the curriculum database. Different feature of the courses change over time. The Database must preserve the historical view of the course. In case course is changed, database stores : 1. Reason for the change. 2. Explanation of the change. 3. Property that is changed (ex. Name, Requisites, Hours, Credit, etc…). 4. Date of the proposed change. 5. Date of actual change. Each course might have multiple changes over lifetime of the course. Each version might have fallowing status: 1. Active (Must have one. ) 2. Inactive (Might have multiple. ) 3. Submit for changes (Might have multiple)

Entity Relationship Diagram

Database Relationship Diagram

Objects & Attributes COURSES Course Code Course Description Course Objective Course Department Faculty In Charge Category ID TEXTBOOK ISBN Number Title Edition Author Publisher Year DEPARTMENT Department Code Department Name COURSE TEXT BOOK ID Course Textbook Title Need Type

Objects & Attributes Course Major ID Major Description Course Type Major Category ID Major Category of Course Number of Credits Category ID Category Description

Objects & Attributes Course Version Course Code Course Version Course Name Course Passing Grade Attendance Policy Credits Hours Change Reason Purposed Change Date Actual Change Date Course Status Course Requisite ID Course Co Prerequisite Requisite Type Required Grade Major ID Major Description Department Name Degree

Create Table SQLStatements • Majors Table CREATE TABLE tbl. Major (Major. Id INTEGER PRIMARY KEY, Major. Description CHAR (50), Dept. Id CHAR (50), Degree CHAR (50), CONSTRAINT FKMajor. Dept. Id FOREIGN KEY (Dept. Id) REFERENCES tbl. Departments);

• Course Table CREATE TABLE tbl. Course 1 (Code INTEGER PRIMARY KEY, Description CHAR (50), Objective CHAR (50), Faculty. Incharge CHAR (50), Dept. Id CHAR (50), Category. ID CHAR (50), CONSTRAINT FKCourse. Dept. Id FOREIGN KEY (Dept. Id) REFERENCES tbl. Departments, CONSTRAINT FKCourse. Category. ID FOREIGN KEY (Category. ID) REFERENCES tbl. Categories);

• Version Table CREATE TABLE tbl. Version (Code CHAR (50) PRIMARY KEY, Version INTEGER PRIMARY KEY, Name CHAR (50), Passing. Grade CHAR (1), Attendence. Policy CHAR (50), Credits CHAR (50), Hours INTEGER, Changed. Reason MEMO, Purposed. Change. Date DATE, Actual. Change. Date DATE, Status CHAR (50), CONSTRAINT FKVersion. Course FOREIGN KEY (Code) REFERENCES tbl. Courses);

QUERIES Queries must be written to support the functionality of application: All information about courses which is currently active. All information about courses which is currently not active. Text book, requisites, category or department of a course. Maximum version of the courses.

Queries • Selecting Maximum Course Version Query SELECT tbl. Course. Versions. Code, Max(tbl. Course. Versions. Version) AS Max. Of. Version FROM tbl. Course. Versions GROUP BY tbl. Course. Versions. Code; • Selecting All Inactive Course Versions SELECT [tbl. Course. Versions]. [Code], [Version], [Passing. Grade], [Attendence. Policy], [Credits], [Hours], [Change. Reson], [Purposed. Change. Date], [Actual. Change. Date], [Status] FROM tbl. Course. Versions WHERE ([Status])<>"Active") • Selecting Text Book for Courses SELECT tbl. Course. Text. Books. Code, ISBN_No, Title, . Edition, Author, Publisher, Year FROM tbl. Text. Books INNER JOIN tbl. Course. Text. Books ON tbl. Text. Books. ISBN_No = tbl. Course. Text. Books. ISBN_No;