MarcosStudent
asked on
Have questions on normalization....
Want to make sure I understand a question about normalization. I have read about and seem to understand it in my mind but a bit confused on question.
Choose a key and write the dependencies for the following relation:
GRADES (StudentID, Course#, Semester#,Grade)
In what normal form is this relation.
From what I understand above: I would need to have StudentID,Course#,Semseste
The first 3 have to be the composite key or the table would have duplicate data.
I know a student can retake a course (my own experience) and get a grade of "W" then have to take the course again but another semester.
example:
1234, Course345, SemsesterFall2003, W
1234, Course345, SemsesterSpring2004, A
What is stupid about this excercise to me is:
1) THere is not student name here
2) no Course description
3) no Professor that teaches the course
Due to above I would assume GRADES is 1 relation of many wihtin larger database.
Thus GRADES with PRIMARY KEY: StudentID,Course#,Semseste
FUNCTIONAL DEPENDENCY OF GRADES ON THOSE = 3rd normal form.
Does this make sense Or am I really not getting it?
Choose a key and write the dependencies for the following relation:
GRADES (StudentID, Course#, Semester#,Grade)
In what normal form is this relation.
From what I understand above: I would need to have StudentID,Course#,Semseste
The first 3 have to be the composite key or the table would have duplicate data.
I know a student can retake a course (my own experience) and get a grade of "W" then have to take the course again but another semester.
example:
1234, Course345, SemsesterFall2003, W
1234, Course345, SemsesterSpring2004, A
What is stupid about this excercise to me is:
1) THere is not student name here
2) no Course description
3) no Professor that teaches the course
Due to above I would assume GRADES is 1 relation of many wihtin larger database.
Thus GRADES with PRIMARY KEY: StudentID,Course#,Semseste
FUNCTIONAL DEPENDENCY OF GRADES ON THOSE = 3rd normal form.
Does this make sense Or am I really not getting it?
Marcos,
One of the major benefits of of normalization is that data is stored only once in the database at a level of granularity that makes sense. Here's how I would approach modelling your data:
Table: Student
StudentId int identity, -- Integer value, unique identifier, surrogate key (not shown to anyone in a report)
<Additional fields to store all relevant data about the student... examples follow>
StudentIdCardNumber varchar(30), -- Number from the student's ID card (what the student knows)
Gender char(1), -- M/F
FirstName varchar(30),
MiddleName varchar(30),
LastName varchar(30),
Suffix varchar(10), -- Jr. Sr. etc.
PreferredName varchar(30), -- Nickname, etc.
SSN char(9),
. . .
Table: Course
CouseId int identity, -- Again, a surrogate key
CourseNum varchar(20), -- e.g. CPS1127 for a Computer Science course
Level char(1), -- Remedial, Undergrad, Grad (first letter)
LabBln char(1), -- Boolean 0/1 (False/True) - does the course have a lab
Hours numeric(5,2), -- Hours of credit the class is worth
LabHours numeric (5,2), -- Hours of credit for associated lab
. . .
Table: Grade
GradeCd char(1), -- A - F, W, I, etc.
Name varchar(20),
Description varchar(80),
PassBln char(1), - True/false - passing = 1, failing = 0
. . .
Table: PeriodType
PeriodTypeId smallint, -- (another surrogate key - 1, 2, 3, 4)
Name varchar(20), -- Fall Semester, Spring Semester, Summer 1, Summer 2
Description varchar(80)
. . .
Now that we have tables to store all of the basic entities, here is the table in which we store each student's grades:
Table: StudentGrade
StudentGradeId int identity, -- Another surrogate key, ensures rows are unique
StudentId int, -- Foreign key to the Student table
AcademicYear char(4), -- '2001', '2002', etc.
PeriodTypeId smallint, -- Foreign key to the PeriodType table
CourseId int, -- Foreign key to the Course table
GradeCd char(1) -- Foreign key to the Grade table
Additional tables could store the teacher assignments for each class / year / period type, and could be used to monitor the teacher's performance in each class.
Beneifts:
This sort of structure simplifies building data entry applications, in that codes stored in data tables are defined in other tables with a Name (field label or drop-down text) and Description (ToolTip text, verbose text). It minimizes the burden of maintenance by minimizing the number of rows updated as data is revised. For example, if you stored the student's real-world Student ID number as the student's key into every table, you'd have to update every single student record if the student lost his/her ID and had a new one issued. In this model, you would simply update the one record in the Student table. Also, most databases work faster with tight Integer keys and indexes, since the data size is compact. If we were to store a long character value as a primary key, performance would suffer simply due to larger I/O demands tied to every operation.
Some would say that the data you are modelling don't really dictate this sort of rigor in database design. However, if every "small system" on a server is built with a loose, denormalized data model, the entire server will suffer from the aggregate inefficiency.
Finally, don't forget that Normalization is not a strict requirement for "good" database design. Normalization is one consideration, and it is not appropriate in all cases. The best example where normalization is a bad design choice is a Data Warehouse. Data Warehouses are not designed for constant small updates and maintenance, but primarily for rapid query performance for "What If?" queries. Normalized databases require a number of Join operations when data is queried, which can be more complex to write and usually execute slower than a single-table query. DataWarehouse design eschews 3rd order normalization for flat, wide tables.
I hope this clarifies the concepts surrounding database normalization for you.
-- Brian
One of the major benefits of of normalization is that data is stored only once in the database at a level of granularity that makes sense. Here's how I would approach modelling your data:
Table: Student
StudentId int identity, -- Integer value, unique identifier, surrogate key (not shown to anyone in a report)
<Additional fields to store all relevant data about the student... examples follow>
StudentIdCardNumber varchar(30), -- Number from the student's ID card (what the student knows)
Gender char(1), -- M/F
FirstName varchar(30),
MiddleName varchar(30),
LastName varchar(30),
Suffix varchar(10), -- Jr. Sr. etc.
PreferredName varchar(30), -- Nickname, etc.
SSN char(9),
. . .
Table: Course
CouseId int identity, -- Again, a surrogate key
CourseNum varchar(20), -- e.g. CPS1127 for a Computer Science course
Level char(1), -- Remedial, Undergrad, Grad (first letter)
LabBln char(1), -- Boolean 0/1 (False/True) - does the course have a lab
Hours numeric(5,2), -- Hours of credit the class is worth
LabHours numeric (5,2), -- Hours of credit for associated lab
. . .
Table: Grade
GradeCd char(1), -- A - F, W, I, etc.
Name varchar(20),
Description varchar(80),
PassBln char(1), - True/false - passing = 1, failing = 0
. . .
Table: PeriodType
PeriodTypeId smallint, -- (another surrogate key - 1, 2, 3, 4)
Name varchar(20), -- Fall Semester, Spring Semester, Summer 1, Summer 2
Description varchar(80)
. . .
Now that we have tables to store all of the basic entities, here is the table in which we store each student's grades:
Table: StudentGrade
StudentGradeId int identity, -- Another surrogate key, ensures rows are unique
StudentId int, -- Foreign key to the Student table
AcademicYear char(4), -- '2001', '2002', etc.
PeriodTypeId smallint, -- Foreign key to the PeriodType table
CourseId int, -- Foreign key to the Course table
GradeCd char(1) -- Foreign key to the Grade table
Additional tables could store the teacher assignments for each class / year / period type, and could be used to monitor the teacher's performance in each class.
Beneifts:
This sort of structure simplifies building data entry applications, in that codes stored in data tables are defined in other tables with a Name (field label or drop-down text) and Description (ToolTip text, verbose text). It minimizes the burden of maintenance by minimizing the number of rows updated as data is revised. For example, if you stored the student's real-world Student ID number as the student's key into every table, you'd have to update every single student record if the student lost his/her ID and had a new one issued. In this model, you would simply update the one record in the Student table. Also, most databases work faster with tight Integer keys and indexes, since the data size is compact. If we were to store a long character value as a primary key, performance would suffer simply due to larger I/O demands tied to every operation.
Some would say that the data you are modelling don't really dictate this sort of rigor in database design. However, if every "small system" on a server is built with a loose, denormalized data model, the entire server will suffer from the aggregate inefficiency.
Finally, don't forget that Normalization is not a strict requirement for "good" database design. Normalization is one consideration, and it is not appropriate in all cases. The best example where normalization is a bad design choice is a Data Warehouse. Data Warehouses are not designed for constant small updates and maintenance, but primarily for rapid query performance for "What If?" queries. Normalized databases require a number of Join operations when data is queried, which can be more complex to write and usually execute slower than a single-table query. DataWarehouse design eschews 3rd order normalization for flat, wide tables.
I hope this clarifies the concepts surrounding database normalization for you.
-- Brian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THe answers above are not dealing with the queston posed.
No new relations are being asked to be created or related .
The relation stand as it is. ( granted it is not a good example standing alone)
Assumption 1: this relation exists in database and the other relations as described by experts probably exist somewhere else in database
Assumption 2: just need to finalize the GRADES relation. The attributes are in there and choose key. my example knows from experience that StudentID, Course#, Semester have make up composoite key.
thus StudentID,Course#,Semester
(grades functional dependent on composite key)
I just want to see if limited to the GRADES relaton only! Am I thus in 3NF
thanks
No new relations are being asked to be created or related .
The relation stand as it is. ( granted it is not a good example standing alone)
Assumption 1: this relation exists in database and the other relations as described by experts probably exist somewhere else in database
Assumption 2: just need to finalize the GRADES relation. The attributes are in there and choose key. my example knows from experience that StudentID, Course#, Semester have make up composoite key.
thus StudentID,Course#,Semester
(grades functional dependent on composite key)
I just want to see if limited to the GRADES relaton only! Am I thus in 3NF
thanks
Say we want GRADES (StudentID, Course#, Semester#,Grade
and here
* StudentId comes from Student (let's say for now) table with StudentId having as primary key and we will refer this id in GRADES table.
* We could maintain Course table with CourseId(PK) individually and refer CourseId (FK) in GRADES tables.
* In the same way we can maintain Semester# table wiht SemesterId (PK) individually and can refer its SemesterId in GRADES table.
* Also same for the Grade table with GradeId (PK) in GRADES table.
So ultimately our GRADES table will be having relationship with all the required data. and this will be easy to maintain each detail and will reflect at each record . No doubt record display will be a bit lengthy but the better solution with keeping in mind development.