Re: What should be the Database Design in this case?

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 7 Dec 2002 20:11:05 -0800
Message-ID: <c0d87ec0.0212072011.69f5b94b@posting.google.com>

It is considered very rude to post accross mulitple newsgroups. In the SQL newsgroups, it is rude to fail to post DDL.

Your major problem is that you don't know what a key is, so you blindly keep putting a column called "id" on all the tables. What common attribute in each of these LOGICALLY different entities is modeled by that thing? NONE!! you are imitating a sequential file system in SQL.

Please read ISO-11179 standards for naming data elements.

CREATE TABLE Courses
(course_nbr INTEGER NOT NULL PRIMARY KEY,  course_name VARCHAR(20) NOT NULL,
 ...);

CREATE TABLE Subjects
(subject_code INTEGFER NOT NULL PRIMARY KEY,  subject_area VARCHAR(20) NOT NULL,
 ...);

CREATE TABLE CourseSubjects
(course_nbr INTEGER NOT NULL

            REFERENCES Courses(course_nbr)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
 subject_area INTEGER NOT NULL
           REFERENCES Subjects(subject_area),

 PRIMARY KEY(course_nbr, subject_area));

Where did you get the idea that the exam type is an entity and not an attribute of an exam??

CREATE TABLE Exams
(course_nbr INTEGER NOT NULL

            REFERENCES Courses(course_nbr)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
 exam_type VARCHAR() NOT NULL DEFAULT 'UnitTest'
           CHECK(exam_type IN ('UnitTest', 'HalfYear', 'Final')),
 

 exam_date DATE NOT NULL,
 ...
 PRIMARY KEY (course_nbr, exam_type));

>> I hope the above database schema is clear and also properly
normalized. <<

Neither.

>> Now I want to store Subject wise Maximum Marks for each course. <<

NO!! You want to query the data and find them. We do not store calculated values in a relational database. Put it in a VIEW, if you re-use the results a lot.

You now produce a data element, which does not appear anywhere in the schema caled "marks" -- what table are they in? HOw are tehy related to the exams? Etc.

If this posting is an attempt to get other people to do your homework for you at a University, we need to contact your school and have you dismissed for violation of the academic honor code. Received on Sat Dec 07 2002 - 22:11:05 CST