Question

SIS is a management information system for education departments used to manage student data. SIS provide capabilities for registering students in courses; documenting results of student tests and other assessment scores; building student schedules; tracking student attendance; and managing many other student-related data needs in a school or universities.

In this regard, you are asked to do some research about SIS, select one of the aspects that managed by SIS and then design two database tables about this aspect [14 marks] and another database table that represents a relation between the previous two tables [6 marks]. For each table you need to:

1. Define the objective(s) of the table.
2. Design at least five fields for the table; data type and size.

3.Determine the primary key of the table.

Answer 1

********************PLEASE UPVOTE****************************

Table Name : Student

Column/Field Name	DataType	Size
Student_Id	int
Fname	varchar(50)	50
Lname	varchar(50)	50
DateOfBirth	Date
ContactNo	int
Gender	varchar(8)	8
Address	nvarchar(255)	255

Objective : To store student related information like name, age, sex

Primary Key : Student_Id (This will be unique for each student)

Table Name: Subject/Course

Column/Field Name	DataType	Size
ID	int
Subject_Name	nvarchar(50)	50
Abbreviation	nvarchar(25)	20

Objective : To store information of different courses offered.

Primary Key : ID

Table Name : ScoreRecord

Column/Field Name	DataType	Size
ID	int
Subject_Id	int
Student_Id	int
Grade	varchar(10)	10
Year	Date

Objective : To store grade scored by student for each subject. And creates a relation between Student and Subject table.  

Primary Key : ID

Foreign Key : Subject_Id, Student_Id