मैं इस समस्या के साथ घंटों बैठा हूं और पागल होने की कगार पर हूं। मैं चाहता हूं कि प्रत्येक छात्र अधिकतम 45 कोर्स क्रेडिट का ही अध्ययन कर सके। इस बिंदु पर यह मुझे कुल 45 से अधिक कोर्स क्रेडिट जोड़ने से रोकता है। मुझे फंक्शन चेकक्रेडिट्स को पुनर्व्यवस्थित कैसे करना चाहिए?

CREATE TABLE Student
(
    studentID VARCHAR(5),
    studentName VARCHAR(20),
    studentAddress VARCHAR(20),
    CONSTRAINT student_pk PRIMARY KEY(studentID)
)

CREATE TABLE Course
(
    courseID VARCHAR(5),
    courseCredits INT,
    courseName VARCHAR(20),
    CONSTRAINT course_pk PRIMARY KEY(courseID)
)

CREATE TABLE Studies
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    CONSTRAINT studies_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT studies_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT studies_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

CREATE TABLE HasStudied
(
    studentID VARCHAR(5),
    courseID VARCHAR(5),
    grade VARCHAR(1),
    CONSTRAINT has_studied_pk PRIMARY KEY(studentID,courseID),
    CONSTRAINT has_studied_fk_student FOREIGN KEY(studentID) REFERENCES Student(studentID) ON DELETE CASCADE,
    CONSTRAINT has_studied_fk_course FOREIGN KEY(courseID) REFERENCES Course(courseID) ON DELETE CASCADE
)

GO
CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
   DECLARE @returnvalue INT
   SELECT @returnvalue = (SELECT SUM(courseCredits) FROM Course c where c.courseID IN(
    SELECT s.courseID FROM Studies s JOIN Student st ON s.studentID = st.studentID OR c.courseID = s.courseID))
   RETURN @returnvalue
END;
GO

ALTER TABLE Studies 
ADD CONSTRAINT chkCredits CHECK (dbo.CheckCredits() <= 45);  



INSERT INTO Student VALUES('S1', 'Joe', 'Street')
INSERT INTO Student VALUES('S2', 'Joe', 'Street')
INSERT INTO Student VALUES('S3', 'Joe', 'Street')
INSERT INTO Student VALUES('S4', 'Joe', 'Street')

INSERT INTO Course VALUES('C1', 45, 'Biology')
INSERT INTO Course VALUES('C2', 15, 'History')
INSERT INTO Course VALUES('C3', 35, 'English')
INSERT INTO Course VALUES('C4', 20, 'Music')

INSERT INTO Studies VALUES('S1', 'C1')
INSERT INTO Studies VALUES('S2', 'C2')
INSERT INTO Studies VALUES('S3', 'C3')

0
Axel Görnebrand 17 सितंबर 2020, 18:25

1 उत्तर

सबसे बढ़िया उत्तर

प्रति छात्र अधिकतम क्रेडिट वापस करने के लिए अपना कार्य बदलें। ऐशे ही:

CREATE FUNCTION CheckCredits()
RETURNS INT
AS 
BEGIN
    DECLARE @returnvalue INT
    SELECT TOP 1 @returnvalue = SUM(courseCredits) 
    FROM Studies s 
    JOIN Course c ON s.courseID = c.courseID 
    GROUP BY s.studentID 
    ORDER BY SUM(courseCredits)  desc 

   RETURN @returnvalue
END;
GO
0
Wouter 17 सितंबर 2020, 17:44