locked
Student Information System Database Design RRS feed

  • Question

  • Hi Experts,

    I am developing Student Information System (SIS). I need to know which factors should I have to consider while Database Design.

    Please help. I am new to Database Design.

    Note: I am using SQL-Server 2000.

    Thanks.

     

    Thursday, January 8, 2009 1:29 PM

Answers

  • The following script is a starter example for a student info database. Let us know if helpful.

    USE tempdb;  
    GO  
    CREATE TABLE Student (  
              StudentID int identity(1,1) PRIMARY KEY,  
              FirstName varchar(30),  
              LastName varchar(30),  
              Birthdate char(8),   -- example 19901023  
              SSNo varchar(20),  
              Email varchar(70),  
              CellPhone varchar(15),  
              ModifiedDate datetime default(getdate())  
    );  
     
    CREATE TABLE [Profile] (  
              StudentID int NOT NULL PRIMARY KEY 
                     REFERENCES Student(StudentID),  
              Gender char(1),  
              HighSchoolGPA smallmoney,  
              HeightFt tinyint,  
              HeightIn tinyint,  
              BirthPlace varchar(40),  
              MotherName varchar(50),  
              FatherName varchar(50),  
              -- SubmittedApplication XML,  -- SQL 2005 and SQL 2008 feature  
              ModifiedDate datetime default(getdate())  
    );  
     
     
    CREATE TABLE Course (  
              CourseID int identity(1,1) PRIMARY KEY,  
              Code char(8) NOT NULL UNIQUE-- UNIQUE KEY constraint  
              Title varchar(90) NOT NULL,  
              Credits int  CHECK (Credits between 1 and 5), -- CHECK constraint  
              ModifiedDate datetime default(getdate())  
    );  
     
    CREATE TABLE Semester (  
              SemesterID int identity(1,1) PRIMARY KEY,  
              Code char(7) NOT NULL UNIQUE-- YYYYMON like 2010SEP  
              [Description] varchar(90) NOT NULL,  
              ModifiedDate datetime default(getdate())  
    );  
     
    -- The following is a junction (or xref) table  
    -- It represent many to many relationship between students and courses  
    CREATE TABLE Enrollment (  
              StudentID int   
                     REFERENCES Student(StudentID),  
              CourseID int    
                     REFERENCES Course(CourseID),  
              SemesterID int NOT NULL 
                    REFERENCES Semester(SemesterID),  
              Grade char(2) default (''),  
              ModifiedDate datetime default(getdate()),  
              PRIMARY KEY (StudentID, CourseID),  
     
    );  
    GO  
     
    -- Population example  
     
    INSERT Student (FirstName, LastName, BirthDate, SSNo, Email, Cellphone)  
    VALUES('Kevin''Rogers''19900315','111-22-4444',  
           'kevin.rogers@abcd.edu','1-947-323-4444')  
    GO  
    SELECT * FROM Student  
    GO  
    /* Results  
     
    StudentID   FirstName   LastName    Birthdate   SSNo    Email   CellPhone   ModifiedDate  
    1   Kevin   Rogers  19900315    111-22-4444 kevin.rogers@abcd.edu   1-947-323-4444  2009-01-17 08:20:54.017  
    */  
     
    INSERT Course (Code, Title, Credits)  
    VALUES ('CSSQLBI1','Introduction to Business Intelligence', 4)  
    GO  
    SELECT * FROM Course  
    GO  
    /* Results   
     
    CourseID    Code    Title   Credits ModifiedDate  
    1   CSSQLBI1    Introduction to Business Intelligence   4   2009-01-17 08:25:23.840  
    */  
     
    INSERT Semester (Code, Description)  
    VALUES ('2009JAN''2009 Spring semester - JAN to JUN')  
    GO  
     
    SELECT * FROM Semester  
    GO  
    /* Resutls  
     
    SemesterID  Code    Description ModifiedDate  
    1   2009JAN 2009 Spring semester - JAN to JUN   2009-01-17 08:28:10.550  
    */  
     
    INSERT Enrollment (StudentID, CourseID, SemesterID)  
    VALUES (1,1,1)  
    GO  
    SELECT * FROM Enrollment  
    GO  
    /* Results  
    StudentID   CourseID    SemesterID  Grade   ModifiedDate  
    1   1   1       2009-01-17 08:31:05.350  
    */  
     
    -- Upon course completion, grading UPDATE  
    UPDATE Enrollment SET Grade = 'A+' 
    WHERE   StudentID = 1  
        AND CourseID = 1  
        AND SemesterID = 1  
    GO  
    SELECT * FROM Enrollment  
    GO  
    /* Results  
    StudentID   CourseID    SemesterID  Grade   ModifiedDate  
    1   1   1   A+  2009-01-17 08:31:05.350  
    */  
     
    /* Cleanup  
    drop table Enrollment  
    drop table Profile  
    drop table Semester  
    drop table Course  
    drop table Student  
    */ 

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    • Marked as answer by Vijay Jadhav Friday, July 31, 2009 12:36 PM
    Saturday, January 17, 2009 1:40 PM

All replies

  • There really is no simple answer but here are some basic guidelines you can follow.

    1. Use some type of naming convention. For example I always use singular names for my tables and for stored procedures I prefix them with usp_. Everyone has a different style but it is important to be consistent.
    2. Normalize but do not overnormalize. When designing your database try to get to at least the third normal form. However do not get too carried away with normalization because it can make reporting a nightmare if over done.
    3. Use the proper data types. Do not use a decimal data type if you are storing integers and do not store numeric data in varchar fields. Also, does your application require you to support multiple languages? This may require you to pick nvarchar instead of varchar.
    4. Don't forget to index your tables and try to test your design with as much data as possible to ensure that it can scale.
    • Proposed as answer by Cookin Mama Saturday, January 17, 2009 4:21 AM
    Friday, January 16, 2009 6:51 PM
  • The following script is a starter example for a student info database. Let us know if helpful.

    USE tempdb;  
    GO  
    CREATE TABLE Student (  
              StudentID int identity(1,1) PRIMARY KEY,  
              FirstName varchar(30),  
              LastName varchar(30),  
              Birthdate char(8),   -- example 19901023  
              SSNo varchar(20),  
              Email varchar(70),  
              CellPhone varchar(15),  
              ModifiedDate datetime default(getdate())  
    );  
     
    CREATE TABLE [Profile] (  
              StudentID int NOT NULL PRIMARY KEY 
                     REFERENCES Student(StudentID),  
              Gender char(1),  
              HighSchoolGPA smallmoney,  
              HeightFt tinyint,  
              HeightIn tinyint,  
              BirthPlace varchar(40),  
              MotherName varchar(50),  
              FatherName varchar(50),  
              -- SubmittedApplication XML,  -- SQL 2005 and SQL 2008 feature  
              ModifiedDate datetime default(getdate())  
    );  
     
     
    CREATE TABLE Course (  
              CourseID int identity(1,1) PRIMARY KEY,  
              Code char(8) NOT NULL UNIQUE-- UNIQUE KEY constraint  
              Title varchar(90) NOT NULL,  
              Credits int  CHECK (Credits between 1 and 5), -- CHECK constraint  
              ModifiedDate datetime default(getdate())  
    );  
     
    CREATE TABLE Semester (  
              SemesterID int identity(1,1) PRIMARY KEY,  
              Code char(7) NOT NULL UNIQUE-- YYYYMON like 2010SEP  
              [Description] varchar(90) NOT NULL,  
              ModifiedDate datetime default(getdate())  
    );  
     
    -- The following is a junction (or xref) table  
    -- It represent many to many relationship between students and courses  
    CREATE TABLE Enrollment (  
              StudentID int   
                     REFERENCES Student(StudentID),  
              CourseID int    
                     REFERENCES Course(CourseID),  
              SemesterID int NOT NULL 
                    REFERENCES Semester(SemesterID),  
              Grade char(2) default (''),  
              ModifiedDate datetime default(getdate()),  
              PRIMARY KEY (StudentID, CourseID),  
     
    );  
    GO  
     
    -- Population example  
     
    INSERT Student (FirstName, LastName, BirthDate, SSNo, Email, Cellphone)  
    VALUES('Kevin''Rogers''19900315','111-22-4444',  
           'kevin.rogers@abcd.edu','1-947-323-4444')  
    GO  
    SELECT * FROM Student  
    GO  
    /* Results  
     
    StudentID   FirstName   LastName    Birthdate   SSNo    Email   CellPhone   ModifiedDate  
    1   Kevin   Rogers  19900315    111-22-4444 kevin.rogers@abcd.edu   1-947-323-4444  2009-01-17 08:20:54.017  
    */  
     
    INSERT Course (Code, Title, Credits)  
    VALUES ('CSSQLBI1','Introduction to Business Intelligence', 4)  
    GO  
    SELECT * FROM Course  
    GO  
    /* Results   
     
    CourseID    Code    Title   Credits ModifiedDate  
    1   CSSQLBI1    Introduction to Business Intelligence   4   2009-01-17 08:25:23.840  
    */  
     
    INSERT Semester (Code, Description)  
    VALUES ('2009JAN''2009 Spring semester - JAN to JUN')  
    GO  
     
    SELECT * FROM Semester  
    GO  
    /* Resutls  
     
    SemesterID  Code    Description ModifiedDate  
    1   2009JAN 2009 Spring semester - JAN to JUN   2009-01-17 08:28:10.550  
    */  
     
    INSERT Enrollment (StudentID, CourseID, SemesterID)  
    VALUES (1,1,1)  
    GO  
    SELECT * FROM Enrollment  
    GO  
    /* Results  
    StudentID   CourseID    SemesterID  Grade   ModifiedDate  
    1   1   1       2009-01-17 08:31:05.350  
    */  
     
    -- Upon course completion, grading UPDATE  
    UPDATE Enrollment SET Grade = 'A+' 
    WHERE   StudentID = 1  
        AND CourseID = 1  
        AND SemesterID = 1  
    GO  
    SELECT * FROM Enrollment  
    GO  
    /* Results  
    StudentID   CourseID    SemesterID  Grade   ModifiedDate  
    1   1   1   A+  2009-01-17 08:31:05.350  
    */  
     
    /* Cleanup  
    drop table Enrollment  
    drop table Profile  
    drop table Semester  
    drop table Course  
    drop table Student  
    */ 

    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    • Marked as answer by Vijay Jadhav Friday, July 31, 2009 12:36 PM
    Saturday, January 17, 2009 1:40 PM
  • Hi Mike,

    Sorry for late reply.

    Thanks for such nice guidelines.
    Wednesday, May 13, 2009 1:47 PM
  • Hi SQLUSA,

    Sorry for late reply.

    Thanks for such nice DB script.
    Wednesday, May 13, 2009 1:48 PM
  • Hi Sir. I'm an IT student! I'm searchin' for some database examples. And I saw this example. How can I see the output of this program? Thank you. It's a big help for me as a student.
    Tuesday, January 21, 2014 3:56 AM