break normalization rules , create view from muliple tables that have 1 to M realtionship

  • Hello Everyone,

    I have a question and am not sure if this is the corecct forum to post it .

    I have two table studenTtable and courseTable which is each student take more than one course . 1:M

    for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).

    I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.

    for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score

    and since Student one just have two courses , I should enter NULL in 'Course 3 fields'

    My Struggle is in the insert statement I tried the following but it show an error

    Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)

    Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );

    I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.

    I tried also the PIVOT BY functionality but no luck with it .

    I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.

    FYI , I'm not expert in SQL Syntax , I just know the basic.

    I will be great full for any helpfull suggestions to try , thank you very much.

    🙁

    Note: English is not my first language But I tried my best to explain my problem.

  • It would probably help if we had table definitions. I can't quite figure out what comes from what table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • First question, is there a maximum number of courses a student may take at one time?

    Two, if you could post the DDL (CREATE TABLE statement) for the tables involved, some sample data, just 3 or 4 students and related data, as INSERT INTO statements, and the expected results based on the sample data that would really help.

    Since you may not know what it is I am asking for here, please read the first article I reference below in my signature block. It will walk you through what you should post and how to do it.

  • The following my table structure :

    First Table is Member table which Represent Students Information .The fields in this table are

    member_sk (PrimaryKey), full_or_part_time, gender, age_at_entry, age_band_at_entry, disability, ethnicity,

    widening_participation_level, nationality

    Second Table is Modules table which include the Courses' scores that Student took .

    The fields in this table are

    Module_result_k(Primary Key), member_sk(Foreign key to connect to Member table), member_stage_sk ,module_k(Foreign key to connect to Module table), module_confirmed_grade_src, credit_or_result

    Third Table is AllModuleInfo which is include general information for each course .The fields in this table are

    Module_k (Primary key), module_name ,module_code, Module_credit, Module stage.

    The New table that I will create has the following fields

    member_sk (PrimaryKey), full_or_part_time, gender, age_at_entry, age_band_at_entry, disability, ethnicity,

    widening_participation_level, nationality " This will be retrieved from Member table"

    Also will include

    Module 1_name ,module1_code, Module1_credit, Module1_ stage, member1_stage_sk , module1_confirmed_grade_src, credit1_or_result

    Module 2_name ,module2_code, Module2_credit, Module2_ stage, member2_stage_sk , module2_confirmed_grade_src, credit2_or_result

    -

    -

    -

    I will repeat this fields 14 times which is equal to Maximum courses number that any of the students took.

    //// I hope now my questions become more clear

  • yes there is a maximum number of 14 courses that student can take . and I included above my Tables Structure .

    Many thanks for the Reply

  • Zee1 (7/31/2014)


    yes there is a maximum number of 14 courses that student can take . and I included above my Tables Structure .

    Many thanks for the Reply

    Unfortunately, what you posted isn't what I asked for. Please read the first article I reference below in my signature block.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply