quote:

---

Originally posted by nasim.sona

Hi All,

I am designing a new database for a small application for online-course portal. I designed a table called courses like.

create table courses
( course nvarchar(25) not null,
semister int not null,
subject1 int,
subject2 int,
subject3 int,
subject4 int,
subject5 int,
..
subject10 int,
primary key(course,semister))

and
create table subjects
(
subjectid int primary key,
sname nvarchar(20),
duration int,
path nvarchar(30));

I found it may result in complex query while retrieving data.
Is this normalized ? If not ,what design should I go for?
I appreciate your time and suggestion!!

-Nasim

---

CREATE TABLE Course (
    [CourseCode] CHAR(6) NOT NULL
    , [CourseTitle] VARCHAR(255) NOT NULL
    , [CoursePoints] TINYINT NOT NULL

    , CONSTRAINT PK_Course_CourseCode
		PRIMARY KEY CLUSTERED ([CourseCode])
    )

CREATE TABLE Student (
    [StudentMatriculationNumber] INT NOT NULL
    , [StudentName] VARCHAR(255) NOT NULL

    , CONSTRAINT PK_Student_StudentMatriculationNumber
		PRIMARY KEY CLUSTERED ([StudentMatriculationNumber])
    )

CREATE TABLE StudentCourse (
    [StudentMatriculationNumber] INT NOT NULL
	, [CourseCode] CHAR(6) NOT NULL
	
	, CONSTRAINT PK_StudentCourse
		PRIMARY KEY CLUSTERED ( [StudentMatriculationNumber], [CourseCode] )
	
	, CONSTRAINT FK_StudentCourse_StudentMatriculationNumber
		FOREIGN KEY ([StudentMatriculationNumber])
		REFERENCES Student ( [StudentMatriculationNumber])
	
	, CONSTRAINT FK_StudentCourse_CourseCode
		FOREIGN KEY ([CourseCode])
		REFERENCES Course ( [CourseCode] )	
	)