January 6, 2008 at 5:15 pm
hello guys am doing my final university project and i need your help
I have created a database for a university and i am facing some difficulties with many to many relationships in my database
i have insert many students and i have manage to assign them many courses and see them with an INNER JOIN but i don't know how am going to add exams or grades to many students
This works in access but in the sql server i don't know how to do it with the sql server management tool
Can you guide me?
Do i need an insert query and how it would look like?
i have attached the database schema
and i am sure that it needs to be changed..
Thanks for your help
January 6, 2008 at 11:19 pm
Nice DB. Beter than I've seen a lot of professionals do.
Few points.
Bigints everywhere. Are you expecting more than 2.1 billion students?
The StudentCourseInstructor table looks misnamed. There's no reference to instructor in there. Perhaps StudentCourse rather?
Personal preference, I would remove the StudentCourseInstructor column and just have StudentID and CourseID, with the 2 been the primary key
Exams should (I would think) be linked to the StudentCourse table, not the course table. At the moment, there's no way to connect a student with an exam.
If that's changed, then inserting exam marks for students should be very easy.
So exams would have
ExamID
StudentID
CourseID
Grade
DateWritten
....
Why the Grade lookup table? IMHO, that's taking normalisation too far.
What goes in the Exam table?
Instructor_mm_course has a different naming standard to the rest of the DB and is missing a primary key.
Does that help?
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
January 7, 2008 at 2:32 am
Erm..should you really allow nulls in your relationship tables?
I don't think so because for the relationship record to exist both of the entities in the relationship must exist.
e.g. StudentCourse(Instructor) both the student and the course must exist and be related i.e. 'student A is taking course X' and it is implied by non-existant records that the student is not taking a particular course.
Nulls are not required.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 2:43 am
If you do as Gail suggests make StudentID/CourseID a compound key nulls would not be allowed anyway.:)
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 7, 2008 at 2:57 pm
GilaMonster (1/6/2008)
Nice DB. Beter than I've seen a lot of professionals do.Few points.
Bigints everywhere. Are you expecting more than 2.1 billion students?
Exams should (I would think) be linked to the StudentCourse table, not the course table. At the moment, there's no way to connect a student with an exam.
If that's changed, then inserting exam marks for students should be very easy.
So exams would have
ExamID
StudentID
CourseID
Grade
DateWritten
....
Why the Grade lookup table? IMHO, that's taking normalisation too far.
What goes in the Exam table?
Instructor_mm_course has a different naming standard to the rest of the DB and is missing a primary key.
Does that help?
Thank you GilaMonster for you help and your good words on my effort(Design) and Shaun McGuile too
You helped me a lot and my database is working very good.Now i have to enter some data and display them in an asp.net site 🙂
I am working as a part time database administrator and i am trying to start development
I included big integers just to cover all the case but i guess you are right i have to stick with integers
One last question if i want to create a query to show instructors and the students they have i have to
reference the instructor id in the exams? table?
And btw the exam table is for Final Mid-term and other exams
i have included my new schema here
Thanks Again
January 7, 2008 at 11:09 pm
Looks good. The main problem now is that there's no way to tell which instructor taught which student.
Perhaps, instead of the InstructorCourse table, have a table which stored the occurences of each course. Has the InstructorID, courseID, maybe a date, maybe a location (depending what your business requirements are) and the StudentCourse table references that, instead of the Course table itself.
The exam table's fine. It shouldn't matter who taught the student when to comes to exams. An exam mark is for a specific student and course only.
Another comment on data types. You're going way too big on the data types. StudentID, courseID as bigints? Bigint has a max value of 9,223,372,036,854,775,807
Do you really expect that many students? Or would int (max 2,147,483,647) or even smallint (max 32767) do? You should pick the smallest data types that fit your data.
Also the grade as nvarchar. Do you need unicode in there, or is it just A,B,C,D,E,F?
Additionally, a rule of thumb, don't use the variable-length data types (nvarchar, varchar, varbinary) if the length is less than 10. In this case, Grade should be char(2)
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
January 8, 2008 at 7:23 am
Hi,
It's not a major thing but you've misspelled InstructorSurname in the instructor table. It's one of those things that will ensure much scratching of heads when you do spell it correctly in your code/queries etc and you get back an unknown field error.
K.
January 8, 2008 at 5:29 pm
GilaMonster (1/7/2008)
Looks good. The main problem now is that there's no way to tell which instructor taught which student.Perhaps, instead of the InstructorCourse table, have a table which stored the occurences of each course. Has the InstructorID, courseID, maybe a date, maybe a location (depending what your business requirements are) and the StudentCourse table references that, instead of the Course table itself.
The exam table's fine. It shouldn't matter who taught the student when to comes to exams. An exam mark is for a specific student and course only.
Another comment on data types. You're going way too big on the data types. StudentID, courseID as bigints? Bigint has a max value of 9,223,372,036,854,775,807
Do you really expect that many students? Or would int (max 2,147,483,647) or even smallint (max 32767) do? You should pick the smallest data types that fit your data.
Also the grade as nvarchar. Do you need unicode in there, or is it just A,B,C,D,E,F?
Additionally, a rule of thumb, don't use the variable-length data types (nvarchar, varchar, varbinary) if the length is less than 10. In this case, Grade should be char(2)
Actually if you look this article also http://www.sqlservercentral.com/articles/Miscellaneous/designadatabaseusinganentityrelationshipdiagram/1159/
there is also no link between professors and students to be honest i am scratching my head to find a solution on this(i didn't understood very well what you have said so i didn't designed any new tables) i thought that a query will work
I believe that this would work in access(to link professors and students). i mean the database schema that we have created in sql server
i hear that is a good way to use nvarchar because in future version of sql server they are going to remove some data types lol... but i guess you are right
Thanks
January 8, 2008 at 11:25 pm
miltiadis (1/8/2008)
There is also no link between professors and students
Which is fine if you don't need to know who taught which student. If you do, you'll have to create some form of link. It depends on the business requirements you have. I haven't seen your requirements, so I don't know what you need.
Access is the same as SQL server. Diffrent app, same db design principles.
Z
i thought that a query will work
You can't query what's not there.
With the schema you have, you can tell that Sue, Bob, Jim and Pat all took the course Maths 1. You can also tell that Prof X, Dr Z and Mr K all teach Maths 1. That's because both Studens and Instructors have links to Course
You, however, have no way of telling that Sue sat in Prof X's class, while Jim and Pat sat in Dr Z's
You can change the tables as I suggested, or you can add InstructorID into the StudentCourse table (and rename it appropriatly). Either should work.
i hear that is a good way to use nvarchar because in future version of sql server they are going to remove some data types lol... but i guess you are right
Thanks
Yes, they are. Text, ntext and image. I highly doubt there's any plans to remove non-unicode strings. General rule. You use the smallest datatype that will fit your data. If your data types are unnecessarily large, your database will be far bigger than it needs to be.
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
January 9, 2008 at 2:54 am
Just a thought, can the StudentCourse table be extended to include the InstructorId column, and then remove InstructorCourse table?
January 9, 2008 at 11:47 pm
Thank you all guys especially SSCrazy
i think my database schema is good now and i am over exaggerating thinks in order to learn 🙂
Anyway after i will finish this project i will read this book
because college courses learn only the basic in design stuff i guess
I learn a lot of thinks from you guys i wish you to get the right help when you need it
Thanks
January 9, 2008 at 11:52 pm
miltiadis (1/9/2008)
Thank you all guys especially SSCrazyi think my database schema is good now and i am over exaggerating thinks in order to learn 🙂
Pleasure.
p.s. SSCrazy is a title, not a name.
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
January 10, 2008 at 1:57 am
And I thought it was a description of your way of life. 😀
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
January 10, 2008 at 2:06 am
Hmmm, that too!
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
January 11, 2008 at 4:28 am
Nice work. I too suggest renaming the associative table since there is no trace of the instructor in it.
Arthur
Arthur Fuller
cell: 647-710-1314
Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply