Scribd Logo
Upload

Welcome to Scribd!

  • 7 views

    Read Me

    Uploaded by

    Sridharan Thulasi

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Read Me

    Uploaded by

    Sridharan Thulasi
    7 views4 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    7 views4 pages

    Read Me

    Uploaded by

    Sridharan Thulasi

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 4

    1.

    Academic session details are stored in 'academicSettings' table

    CREATE TABLE dbo.academicSettings

    acadyear_id int identity,

    fin_id int not null,

    branch_code int not null,

    academic_yearname varchar(15) not null,

    CONSTRAINT pk_acddet PRIMARY KEY (acadyear_id),

    constraint fk_accadbranchdet foreign key (branch_code) references


    branchMaster(branch_code)

    GO

    'acadyear_id' is used as session_code

    2. Table for term details : term

    CREATE TABLE dbo.term

    term_code int identity,

    branch_code int not null,

    term_name varchar(15) not null,

    month_from smallint not null,

    month_to smallint not null,


    active char(1) default 'N',

    CONSTRAINT pk_termdet PRIMARY KEY (term_code),

    constraint fk_termbranchdet foreign key (branch_code) references


    branchMaster(branch_code)

    GO

    -- Class wise term details for the academic year.

    CREATE TABLE dbo.term_classAllocation

    term_class_id int identity,

    branch_code int not null,

    acadyear_id int not null,

    term_code int not null,

    class_code int not null,

    is_deleted int default 0 not null,

    CONSTRAINT pk_termclassdet PRIMARY KEY (term_class_id),

    CONSTRAINT fk_termclassdet1 FOREIGN KEY (branch_code)


    REFERENCES branchMaster(branch_code),

    CONSTRAINT fk_termclassdet2 FOREIGN KEY (acadyear_id)


    REFERENCES academicSettings(acadyear_id),

    CONSTRAINT fk_termclassdet3 FOREIGN KEY (term_code)


    REFERENCES term(term_code),

    CONSTRAINT fk_termclassdet4 FOREIGN KEY (class_code)


    REFERENCES class(class_code),

    GO
    Important

    1. Instead of using class and section codes separately, we can use ‘class_section_id’ in
    Assessment, exam_grade_master, exam_result tables.

    Table : TOGET SECTIONS ALLOCATED FOR EACH CLASS FOR THE ACADEMIC YEAR (SELECT RECORD
    WITH 'IS_DELETED' AS 0)

    CREATE TABLE class_sectionAllocation


    (
    class_section_id int identity,

    branch_code int not null,


    acadyear_id int not null,
    class_code int not null,
    sec_code int not null,
    class_sec_name varchar(20) not null,

    is_deleted int default 0 not null,

    CONSTRAINT pk_secclassdet PRIMARY KEY (class_section_id),

    --CONSTRAINT fk_secclassdet2 FOREIGN KEY (acadyear_id) REFERENCES


    academicSettings(acadyear_id),
    CONSTRAINT fk_secclassdet3 FOREIGN KEY (sec_code) REFERENCES section(sec_code),
    CONSTRAINT fk_secclassdet4 FOREIGN KEY (class_code) REFERENCES
    class(class_code),
    )
    GO

    2. Each transaction table should contain a audit table where to store the user log details
    like

    mode_action char(1) default 'A', to identify whether an insertion/updation

    ‘A’ for new record and ‘E’ for updation of a record

    modified_date datetime not null, updated time

    done_by int not null, updated by (store user code)

    reason_edit varchar(100) null, reason to edit in case of updation


    (mandatory)

    On insertion/updation, a new record is inserted into corresponding audit table using trigger

    -----------------------Reference------------------------------------
    'branchMaster' table in database.sql script

    ----------------------------------------------------------------------

    Regarding deletion of a record.

    No records should be deleted from the transaction tables.

    For that,

    is_deleted int default 0 not null, for deleted record it should be updated as '1'

    reason_delete varchar(100) null, reason to delete (mandatory)

    Only display records with is_deleted=0

    -----------------------------------------------------------

    3. In ASP,

    Session variables used

    branch code - Session("comp_branchCode")

    session code - Session("acadYearID")

    user code(for updated by) - Session("UserID")

    You might also like