Curriculum Database

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

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

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

4. 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.

5. Use Case Diagram 1Get Approval Changes

6. Use Case Diagram 2Changes for Classes Approved

7. Sequence Diagram 2Approved Change

8. 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 : • Reason for the change. • Explanation of the change. • Property that is changed (ex. Name, Requisites, Hours, Credit, etc…). • Date of the proposed change. • Date of actual change. • Each course might have multiple changes over lifetime of the course. • Each version might have fallowing status: • Active (Must have one.) • Inactive (Might have multiple.) • Submit for changes (Might have multiple)

9. Entity Relationship Diagram

10. Database Relationship Diagram

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

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

13. 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 Course Co Prerequisite Requisite Type Required Grade Major Major ID Major Description Department Name Degree Objects & Attributes

14. Create Table SQLStatements • Majors Table CREATE TABLE tblMajor (MajorId INTEGER PRIMARY KEY, MajorDescription CHAR (50), DeptId CHAR (50),Degree CHAR (50), CONSTRAINT FKMajorDeptId FOREIGN KEY (DeptId) REFERENCES tblDepartments);

15. Course Table CREATE TABLE tblCourse1 (Code INTEGER PRIMARY KEY, Description CHAR (50), Objective CHAR (50), FacultyIncharge CHAR (50), DeptId CHAR (50),CategoryID CHAR (50), CONSTRAINT FKCourseDeptId FOREIGN KEY (DeptId) REFERENCES tblDepartments, CONSTRAINT FKCourseCategoryID FOREIGN KEY (CategoryID) REFERENCES tblCategories);

16. Version Table CREATE TABLE tblVersion (Code CHAR (50) PRIMARY KEY, Version INTEGER PRIMARY KEY, Name CHAR (50), PassingGrade CHAR (1), AttendencePolicy CHAR (50),Credits CHAR (50), Hours INTEGER, ChangedReason MEMO, PurposedChangeDate DATE, ActualChangeDate DATE, Status CHAR (50),CONSTRAINT FKVersionCourse FOREIGN KEY (Code) REFERENCES tblCourses);

17. 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.

18. Queries • Selecting Maximum Course Version Query SELECT tblCourseVersions.Code, Max(tblCourseVersions.Version) AS MaxOfVersion FROM tblCourseVersions GROUP BY tblCourseVersions.Code; • Selecting All Inactive Course Versions SELECT [tblCourseVersions].[Code],[Version], [PassingGrade], [AttendencePolicy], [Credits],[Hours], [ChangeReson],[PurposedChangeDate], [ActualChangeDate], [Status] FROM tblCourseVersions WHERE ([Status])<>"Active") • Selecting Text Book for Courses SELECT tblCourseTextBooks.Code,ISBN_No, Title,.Edition,Author,Publisher, Year FROM tblTextBooks INNER JOIN tblCourseTextBooks ON tblTextBooks.ISBN_No = tblCourseTextBooks.ISBN_No;