SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 );


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •