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

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),

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 Sun Dec 08 2002 - 05:11:05 CET