Represent database

Hi

Can anyone please suggest me solution for this problem of database normalization ?

My problem is :

I have created one table named as student and one more table of BCS which contain attributes subject_id,subject_name so relation between student and BCS is many to many so how should I normalized it ? rather I want to ask which is the correct way to represent the tables.

I have created the table named as student_BCS_xref

student_BCS_xref(course_id(fk from Course Table),stud_id(fk from Student table),sub1,sub2,sub3,sub4).

or

something else which will make database more efficient.

Thanks in Advanced.

Nikhil Kulkarni :slight_smile:

what does BCS stand for?

how are courses related to subjects? one-to-many? many-to-many? many-to-one?

what is the minimum number of courses a student can take? maximum?

BCS is one of the course in university actually I have created 5 tables

1.Student(stud_id(pk),f_name,m_name,l_name,mother_name,dob,email,contact)

2.Course(courseid(pk),course_name)

3.BCS(subject_id(pk),subject_name)

4.BCom(subject_id(pk),subject_name)

5.BCA(subject_id(pk),subject_name)

one student can select more than one subject so I want to establish the relation between Student and BCS table.please suggest me solution.

first, i think you should combine/extract your last three tables into the following three tables –

degrees (cd,name) e.g. ‘BSc’,‘Bachelor of Science’

subjects (id,name)

degreesubjects (degree_cd, subject_id)

once that’s done, you need to re-think how courses should be related

i imagine it’s possible for the same course to be used in subjects for different degrees? like, an introductory statistical analysis course might go towards both a BComm and a BSc

I have created different table for different course because if I want to select the subjects related with BCS(Bsc.(comp.sci)) I have to write the query for searching the subjects.
I think that when any query that related particular table that complete table comes into the cache memory of DB server.Unnecessary we load complete table into cache memory because of that I have created three different tables.(Please correct me if I am going wrong).
It will search in the whole table that resultant in the delayed response to client because it must be match the value given by the user(It might be wrong).

Please correct me if anything is wrong in my above written post.

Thanks

Nikhil Kulkarni

couple of things aren’t quite right

first of all, entire tables are not brought into cache for any query – it depends on what data is needed from the tables, and in some instances (do a search for covering index), the table is not accessed at all

secondly, the database engine does not search the whole table (this is called a table scan) if there is an easier way to find the desired rows, and this easier way will exist if there is an index on the appropriate column(s)

design the tables for the correct relationships first, then worry about performance later

Please check this database given below and suggest me corrections :

Courses(course_id int primary key ,course_name text,course_duration int,course_fees int);

Students(student_id int primary key,course_id int references Courses,First_name text,Middle_name text,Last_name text,Mother_name text,Address text,email text,contact int);

Subjects(sub_id int primary key,sub_name text,course_id int references courses);

BCS_Student_performance(student_id int references Students,sub_name1 text,sub_name2 text ,sub_name3 text );

BBA_Student_performance(student_id int references Students,sub_name1 text,sub_name2 text ,sub_name3 text );

BA_Student_performance(student_id int references Students,sub_name1 text,sub_name2 text ,sub_name3 text );

BCOM_Student_performance(student_id int references Students,sub_name1 text,sub_name2 text ,sub_name3 text );

BCA_Student_performance(student_id int references Students,sub_name1 text,sub_name2 text ,sub_name3 text );