February 23, 2016 at 7:47 am
k here is my SUBJETCS table so far.
subject_id(pk) int ,
subject_name varchar(),
subjectTHET bit,
subjectTHPA bit,
subjectPRET bit,
subjectPRPA bit,
Each subject may have four types of grades associated with it.(subjectTHET, subjectTHPA, practicalET, practicalPA)
the four bit values decide whether a particular subject has the grades. If bit value is 1, then it has the grade . If bit value is 0, then the subject does not have that grade.
Now, each grade will have a full marks say 100 and pass mark say 40. how can i incorporate this in my database?
should i have four more columns in the table? but it does not seem so right.
something like this:
subject_id(pk) int ,
subject_name varchar(),
subjectTHET bit,
subjectTHPA bit,
subjectPRET bit,
subjectPRPA bit,
subjectTHETfull int,
subjectTHPApass int, and so on
That means i will have eight more columns, two for each type of grade. Please provide a solution because my approach does not seem right.
February 23, 2016 at 8:04 am
hlsc1983 (2/23/2016)
k here is my SUBJETCS table so far.subject_id(pk) int ,
subject_name varchar(),
subjectTHET bit,
subjectTHPA bit,
subjectPRET bit,
subjectPRPA bit,
Each subject may have four types of grades associated with it.(subjectTHET, subjectTHPA, practicalET, practicalPA)
the four bit values decide whether a particular subject has the grades. If bit value is 1, then it has the grade . If bit value is 0, then the subject does not have that grade.
Now, each grade will have a full marks say 100 and pass mark say 40. how can i incorporate this in my database?
should i have four more columns in the table? but it does not seem so right.
something like this:
subject_id(pk) int ,
subject_name varchar(),
subjectTHET bit,
subjectTHPA bit,
subjectPRET bit,
subjectPRPA bit,
subjectTHETfull int,
subjectTHPApass int, and so on
That means i will have eight more columns, two for each type of grade. Please provide a solution because my approach does not seem right.
I agree that your design does not seem right. Unfortunately I don't even begin to understand what you are trying to do here. It seems that you have some very serious normalization issues going on here but the super cryptic names you gave things make it even more challenging to figure out what is going on. From what you posted I suspect you need two tables (or maybe 3). One for the subject and another for the details of each "type" of grading scale or whatever it is you trying to capture.
If you can explain clearly what you are trying to do I will be happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 23, 2016 at 8:22 am
THET: theory end term grade
THPA: theory progressive assessment grade
PRET: practical end term grade
PRPA: practical progressive assessment grade
here is a sample of a record in the table:
java 1 1 1 1
Databases 1 0 1 1
this means that the subject Java has all four grades associated with it.
Whereas the subject Networking has only one grade i.e subjectTHET.
Now all these four grade will have full and pass marks.
i hope my query is more clear now.
February 23, 2016 at 8:38 am
hlsc1983 (2/23/2016)
THET: theory end term gradeTHPA: theory progressive assessment grade
PRET: practical end term grade
PRPA: practical progressive assessment grade
here is a sample of a record in the table:
java 1 1 1 1
Databases 1 0 1 1
this means that the subject Java has all four grades associated with it.
Whereas the subject Networking has only one grade i.e subjectTHET.
Now all these four grade will have full and pass marks.
i hope my query is more clear now.
What you have here is two elements. A subject and a grade type for that subject. This is a many to many type of relationship. The best way to deal with this is to have a table for each element. So you would have Subjects table and a GradeType table. Then you create a bridge table to handle the many to many relationship.
Here is a very basic script which demonstrates this. The idea here is that you make a composite key in the SubjectGradeType table. Additionally you would make a foreign key for each value to its respective base table. Does that make sense?
create table Subjects
(
SubjectID int
, SubjectName varchar(25)
)
create table GradeType
(
GradeTypeID int
, GradeTypeName varchar(25)
)
create table SubjectGradeType
(
SubjectID int
, GradeTypeID int
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 23, 2016 at 8:43 am
So yes one option would be to add 8 more columns to the subject table and that table is simple enough that might make more sense.
The theoretical best approach would be to normalize the grade types although that might be overkill if your data model is really that simple, so you might have a table that looks something like.
CREATE TABLE GRADE_TYPE(
subject_id,
grade type,
maximum grade,
passing grade
)
February 23, 2016 at 8:47 am
k what will be the records for the grades table? like this?
ID Name
1 THET
2 THPA
3 PRET
4 PRPA
and what about the pass and full marks for each subject? where to incorporate them/
February 23, 2016 at 9:07 am
hlsc1983 (2/23/2016)
k what will be the records for the grades table? like this?ID Name
1 THET
2 THPA
3 PRET
4 PRPA
and what about the pass and full marks for each subject? where to incorporate them/
In the GradeType table? That would be the most logical place to me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 23, 2016 at 9:12 am
There would be a table for grades. ZZMartin provided an example.
The idea here is to not get locked into changing tables constantly. You should have a table for the subject, a table for the students, and a table for the grades. You link them together with key columns. Students might have some ID or other marker in that table, which also exists in the grades table.
Same for subjects. This is normalization, and allows for flexibility in your application (more or less grades/subjects/students) without regularly altering the database.
February 23, 2016 at 9:13 am
Where you stick the minimum/maximum marks would probably depend on whether or not all grade types will have the same marks values for all subjects.
February 23, 2016 at 9:37 am
yes, all subjects do not have the same pass and full marks for theory and practical grades.
For example, Java subject has 100 and 40 as full marks and pass marks for Theory End Term grade respectively.
whereas Database subject has 75 and 30 as full marks and pass marks for Theory End Term grade respectively.
That means full marks and pass marks for these grades will differ from one subject to another.
therefore, i dont think this table will work.
CREATE TABLE GRADE_TYPE(
subject_id,
grade type,
maximum grade,
passing grade
)
Please provide a working solution. i know from normalization point of view my schema may not be correct, but if it does not pose a hindrance to the functioning of my application, i don't have any problems with it.
February 23, 2016 at 9:45 am
hlsc1983 (2/23/2016)
yes, all subjects do not have the same pass and full marks for theory and practical grades.For example, Java subject has 100 and 40 as full marks and pass marks for Theory End Term grade respectively.
whereas Database subject has 75 and 30 as full marks and pass marks for Theory End Term grade respectively.
That means full marks and pass marks for these grades will differ from one subject to another.
therefore, i dont think this table will work.
CREATE TABLE GRADE_TYPE(
subject_id,
grade type,
maximum grade,
passing grade
)
Please provide a working solution. i know from normalization point of view my schema may not be correct, but if it does not pose a hindrance to the functioning of my application, i don't have any problems with it.
Actually from a normalization standpoint this makes sense since you are defining the maximum points and what is a passing grade for each subject. It is still pretty rough because you don't have any flexibility for a teacher to define the grades themselves but maybe that is ok in your situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 23, 2016 at 11:34 am
Below is a more fully sketched out design. Hopefully this is not a homework or project assignment :-D.
CREATE TABLE dbo.subjects (
subject_id int IDENTITY(1, 1)
CONSTRAINT subjects__PK PRIMARY KEY,
name varchar(100) NOT NULL, --'Introduction to Java Programming'|...
short_name varchar(30) NULL, --'Java'|...
THET bit NULL, --optional: if used, must have trigger on subject_grade_types to insure accuracy!
THPA bit NULL, --optional: ...
PRET bit NULL, --optional: ...
PRPA bit NULL, --optional: ...
date_added date
--,...
)
CREATE TABLE dbo.grade_types (
grade_type_id smallint IDENTITY(1, 1)
CONSTRAINT grade_types__PK PRIMARY KEY,
name varchar(50) NOT NULL, --'Practical end term grade'|'Theory end term grade'|...
short_name char(4) NULL, --'PRET'|'THET'|...
date_added date
--,...
)
CREATE TABLE dbo.subject_grade_types (
subject_id int,
grade_type_id smallint,
maximum_grade decimal(5, 1) NOT NULL,
passing_grade decimal(5, 1) NULL,
CONSTRAINT subject_grade_types__PK
PRIMARY KEY ( subject_id, grade_type_id ),
CONSTRAINT subject_grade_types__FK_subject_id
FOREIGN KEY ( subject_id ) REFERENCES dbo.subjects ( subject_id ),
CONSTRAINT subject_grade_types__FK_grade_type_id
FOREIGN KEY ( grade_type_id ) REFERENCES dbo.grade_types ( grade_type_id )
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 23, 2016 at 12:09 pm
K now i am getting it. Thank u . But one doubt.
please elaborate how the trigger is to be used and what it's supposed to look like.. I have never used triggers, only know they exist.
And one more thing. How terrible would it be if I used only one table with a many columns like I said earlier. ? Of course it won't be normalized but how bad will it be.
February 23, 2016 at 12:35 pm
hlsc1983 (2/23/2016)
K now i am getting it. Thank u . But one doubt.please elaborate how the trigger is to be used and what it's supposed to look like.. I have never used triggers, only know they exist.
And one more thing. How terrible would it be if I used only one table with a many columns like I said earlier. ? Of course it won't be normalized but how bad will it be.
It doesn't seem bad now but it can quickly get out of hand(keep in mind you're already here asking about adding just 4 grade types with a couple data elements). What happens when a few years out and you've added more grade types and then someone comes along and asks if you can store say an effective date for the grade types?
February 23, 2016 at 12:46 pm
ZZartin (2/23/2016)
hlsc1983 (2/23/2016)
K now i am getting it. Thank u . But one doubt.please elaborate how the trigger is to be used and what it's supposed to look like.. I have never used triggers, only know they exist.
And one more thing. How terrible would it be if I used only one table with a many columns like I said earlier. ? Of course it won't be normalized but how bad will it be.
It doesn't seem bad now but it can quickly get out of hand(keep in mind you're already here asking about adding just 4 grade types with a couple data elements). What happens when a few years out and you've added more grade types and then someone comes along and asks if you can store say an effective date for the grade types?
+1
Also, consider how bad it would be if you have to add 10 more grade types suddenly. You will have to go back through every query all over the place to deal with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply