Solved

Have questions on normalization....

Posted on 2004-10-16
4
2,116 Views
Last Modified: 2008-10-09
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#,Semsester ==>Grade
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#,Semsester
FUNCTIONAL DEPENDENCY OF GRADES ON THOSE = 3rd normal form.

Does this make sense Or am I really not getting it?
0
Comment
Question by:MarcosStudent
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Jankit141180
Comment Utility
You could make different tables for all these fields. So that each and every detail could be easily maintained.

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.
0
 
LVL 1

Expert Comment

by:stewartwb
Comment Utility
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
0
 
LVL 1

Accepted Solution

by:
stewartwb earned 500 total points
Comment Utility
Marcos,

Upon reading your original question again, it seems to me that you are getting it.  The answers you propose are correct... the GRADES table is in 3rd normal form (no unnecessary redundancy in the table), it will have a composite primary key of StudentId, Course#, Semester#, and every row must have a unique trio of values.  

If the GRADES table included the following:
 StudentId, FirstName, LastName, Course#, CourseName, Semester#, Grade
It would not be fully normalized.  The fields FirstName, LastName, and CourseName should not appear in this table, because they are identical for each unique occurrence of <StudentId> and <Course#>, respectively.  Instead, the student's name should appear in the Student table, only once, with the <StudentId> linking all of the student's data back to the Student table.

You are concerned that these details are not in the GRADES table, since this means that the GRADES table is insufficent for painting a complete and meaningful picture of the student, course, and his grades.  However, when pulling data, a simple JOIN to the Student table and Course table will bring all relevant details together.  Example:

select s.SSN, s.FirstName, s.LastName, g.Semester#, g.Course#, c.CourseName, g.Grade
  from GRADES g
  join Student s
   on g.StudentId = s.StudentId
  join Course c
    on g.Course# = c.Course#
 order by s.SSN, g.Semester#, g.Course#

This query would present a full listing of all students, their courses, and grades, sorted by SSN, Semester, and Course.  Notice that we can leave the Student# out of the final output, only using it as a foreign key to pull the data together.

I hope this helps.
-- Brian
0
 

Author Comment

by:MarcosStudent
Comment Utility
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
(grades functional dependent on composite key)

I just want to see if limited to the GRADES relaton only! Am  I thus in 3NF
thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now