Auto Time Table Part 27 Database Changes

 Auto Time Table Part 27 Database Changes

Some Important Changes in Database:

1) Table : ProgramSemesterTable

         Open Database and Add SessionID Column in ProgramSemesterTable or Run below code

         Code:
    ALTER TABLE ProgramSemesterTable 
    ADD [SessionID] [int] NOT NULL CONSTRAINT                            [DF_ProgramSemesterTable_SessionID] DEFAULT ((1))

2) Table : SectionTable
         Open Database and Create Section Table or Run below code  

        Code :
              CREATE TABLE [dbo].[SectionTable]
       ([SectionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
        [SectionTitle] [nvarchar](150) NOT NULL,
        [ProgramSemesterID] [int] NOT NULL,
        [IsActive] [bit] NOT NULL CONSTRAINT                                [DF_SectionTable_IsActive] DEFAULT  ((1)), 
        [SectionCapacity] [int] NOT NULL CONSTRAINT                        [DF_SectionTable_SectionCapacity] DEFAULT ((40)))

3) Table : TimeTableDetailsTable
          Open Database and Add LectureID, DayID,IsActive,SessionID, SessionTitle in                                      TimeTableDetailsTable or Run below code

          Code : 
               ALTER TABLE TimeTableDetailsTable
      ADD [LectureID] [int] NOT NULL,
      [DayID] [int] NOT NULL,
      [IsActive] [bit] NOT NULL CONSTRAINT                                  [DF_TimeTableDetailsTable_IsActive] DEFAULT ((1)), 
      [SessionID] [int] NULL,
      [SessionTitle] [nvarchar](250) NULL

4) Table : TimeTblTable
         Open Database and Add TimeTableTitle, SemesterTitle, SessionTitle in TitleTblTable or Run                  below code  

        Code :
               ALTER TABLE TimeTableDetailsTable
      ADD [TimeTableTitle] [nvarchar](150) NOT NULL,
      [SemesterTitle] [nvarchar](200) NOT NULL,
      [SessionTitle] [nvarchar](250) NULL

5) View : Re-Create View > v_ProgramSemesterActiveList
       Open Database and Delete v_ProgramSemesterActiveList and then copy below code and press execute button in sql server IDE..

        Code :
        CREATE VIEW [dbo].[v_ProgramSemesterActiveList]
     AS
         SELECT dbo.ProgramSemesterTable.ProgramSemesterID,
         dbo.SessionTable.Title + ' ' + dbo.ProgramTable.Name + ' ' +          dbo.SemesterTable.SemesterName AS Title,
         dbo.ProgramSemesterTable.IsActive AS                                     ProgramSemesterIsActive,                                         dbo.ProgramSemesterTable.ProgramID,
         dbo.ProgramTable.Name AS Program,
         dbo.ProgramTable.IsActive AS ProgramIsActive,
         dbo.ProgramSemesterTable.SemesterID,
         dbo.SemesterTable.SemesterName AS Semester,
         dbo.SemesterTable.IsActive AS SemesterIsActive,
         dbo.ProgramSemesterTable.Capacity,
         dbo.ProgramSemesterTable.SessionID,
         dbo.SessionTable.Title AS Session
    FROM dbo.ProgramSemesterTable INNER JOIN
         dbo.ProgramTable ON dbo.ProgramSemesterTable.ProgramID =             dbo.ProgramTable.ProgramID INNER JOIN dbo.SemesterTable ON          dbo.ProgramSemesterTable.SemesterID =                                 dbo.SemesterTable.SemesterID INNER JOIN dbo.SessionTable ON          dbo.ProgramSemesterTable.SessionID =                                 dbo.SessionTable.SessionID
GO 

6) Create View : v_AllSemesterSections 
        Open Database and Create View v_AllSemesterSections, copy below code and execute the code in sql IDE

        Code : 
        CREATE VIEW [dbo].[v_AllSemesterSections]
        AS
            SELECT dbo.SectionTable.SectionID,
            dbo.SectionTable.SectionTitle,
            dbo.SectionTable.ProgramSemesterID,
            dbo.v_ProgramSemesterActiveList.Title,
            dbo.SectionTable.IsActive
         FROM dbo.SectionTable
            INNER JOIN dbo.v_ProgramSemesterActiveList ON                        dbo.SectionTable.ProgramSemesterID =                                dbo.v_ProgramSemesterActiveList.ProgramSemesterID
       GO

7) Create View : v_AllSemesterSectionSubjects
        Open Database and Create View v_AllSemesterSectionSubjects, copy below code and execute the code in sql IDE

        Code : 
    CREATE VIEW [dbo].[v_AllSemesterSectionSubjects] 
    AS
    SELECT AllSemesters.ProgramSemesterID, 
    AllSemesters.Title,
    AllSemesters.SectionID,
    AllSemesters.SectionTitle,
    AllSemesters.IsActive,
    AllSemesters.Capacity,
    AllSubjects.ProgramSemesterSubjectID,
    AllSubjects.SSTitle,
    AllSubjects.Title AS ProgramSemester,
    AllSubjects.ProgramSemesterIsActive,
    AllSubjects.ProgramID,
    AllSubjects.Program,
    AllSubjects.ProgramIsActive,
    AllSubjects.SemesterID,
    AllSubjects.Semester,
    AllSubjects.SemesterIsActive,
    AllSubjects.LectureSubjectID,
    AllSubjects.SubjectTitle,
    AllSubjects.LectureID,
    AllSubjects.FullName,
    AllSubjects.CourseID,
    AllSubjects.SubjectIsActive,
    AllSubjects.IsSubjectActive,
    AllSubjects.CrHrs,
    AllSubjects.RoomTypeID,
    dbo.RoomTypeTable.TypeName,
    AllSemesters.SessionID,
    AllSemesters.SessionTitle
FROM (SELECT PS.ProgramSemesterID, PS.Title +
(CASE ISNULL(ST.SectionTitle, '') WHEN '' THEN '' ELSE ' (' + ST.SectionTitle + ') ' END) AS Title, ST.SectionID, ST.SectionTitle, ST.IsActive, (CASE ISNULL(ST.SectionCapacity, 0) WHEN 0 THEN PS.Capacity ELSE ST.SectionCapacity END) AS Capacity, PS.SessionID, PS.SessionTitle FROM dbo.SectionTable AS ST RIGHT OUTER JOIN (SELECT ProgramSemesterID, Title, ProgramSemesterIsActive, ProgramID, Program, ProgramIsActive, SemesterID, Semester, SemesterIsActive, Capacity, SessionID, Session AS SessionTitle FROM dbo.v_ProgramSemesterActiveList WHERE (ProgramSemesterIsActive = 1) AND (ProgramIsActive = 1) AND(SemesterIsActive = 1)) AS PS ON ST.ProgramSemesterID = PS.ProgramSemesterID) AS AllSemesters INNER JOIN dbo.v_AllSemestersSubjects AS AllSubjects ON AllSemesters.ProgramSemesterID = AllSubjects.ProgramSemesterID INNER JOIN dbo.RoomTypeTable ON AllSubjects.RoomTypeID = dbo.RoomTypeTable.RoomTypeID
GO

8) Create View : v_AllActiveTimeSlots
        Open Database and Create View v_AllActiveTimeSlots, copy below code and execute the code in sql IDE

        Code : 
      CREATE VIEW [dbo].[v_AllActiveTimeSlots]
        AS
        SELECT DTS.DayTimeSlotID,
        DTS.SlotTitle,
        DTS.StartTime,
        DTS.DayID,
        DT.Name,
        DTS.EndTime,
        DT.IsActive AS DayStatus,
        DTS.IsActive AS SlotStatus
    FROM dbo.DayTable AS DT
       INNER JOIN dbo.DayTimeSlotTable AS DTS ON DT.DayID = DTS.DayID
       WHERE (DT.IsActive = 1) AND (DTS.IsActive = 1)
    GO

9) Create Relationships between Table's
        Open Database and copy below code and execute the code in sql IDE

        Code : 
ALTER TABLE [dbo].[ProgramSemesterTable] WITH CHECK ADD CONSTRAINT [FK_ProgramSemesterTable_SessionTable] FOREIGN KEY([SessionID]) REFERENCES [dbo].[SessionTable] ([SessionID])
GO
ALTER TABLE [dbo].[ProgramSemesterTable] CHECK CONSTRAINT [FK_ProgramSemesterTable_SessionTable]
GO

ALTER TABLE [dbo].[SectionTable] WITH CHECK ADD CONSTRAINT [FK_SectionTable_ProgramSemesterTable] FOREIGN KEY([ProgramSemesterID]) REFERENCES [dbo].[ProgramSemesterTable] ([ProgramSemesterID])
GO
ALTER TABLE [dbo].[SectionTable] CHECK CONSTRAINT [FK_SectionTable_ProgramSemesterTable]
GO

ALTER TABLE [dbo].[TimeTableDetailsTable] WITH CHECK ADD CONSTRAINT [FK_TimeTableDetailsTable_DayTable] FOREIGN KEY([DayID]) REFERENCES [dbo].[DayTable] ([DayID])
GO
ALTER TABLE [dbo].[TimeTableDetailsTable] CHECK CONSTRAINT [FK_TimeTableDetailsTable_DayTable]
GO

ALTER TABLE [dbo].[TimeTableDetailsTable] WITH NOCHECK ADD CONSTRAINT [FK_TimeTableDetailsTable_LectureTable] FOREIGN KEY([LectureID]) REFERENCES [dbo].[LectureTable] ([LectureID]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[TimeTableDetailsTable] NOCHECK CONSTRAINT [FK_TimeTableDetailsTable_LectureTable]
GO