Database Systems Consider the integrity constraints of the database part of the

Question

Database Systems Consider the integrity constraints of the database part of the Initial Requirement Document of the Student Registration System (section 4.8.1 (II) of the textbook). Write the integrity constraints using database notations. Implement the integrity constraints as SQL attribute-based constraints, tuple-base constraints, or general assertions. Below is the sreenshot of section 4.8.1(11) from the textbook.) Integrity constraints. The database shall satisfy the following integrity constraints. A. Id numbers are unique. B. If in item I. B. 2 (or I. B. 3), a student is listed as enrolled (registered) for a course, that course must be indicated in item 1. C. 2 as offered this semester (or next semester). C. In item I.C.4, the number of students registered or enrolled in a course cannot be larger than the maximum enrollment. D). The count of students enrolled (registered) in a course in item 1.B.2 (or 1.B.3) must equal the current enrollment (registration) indicated in item l.C.4. E. An instructor cannot be assigned to two courses taught at the same time in the same semester. F. Two courses cannot lie taught in the same room at the same time in a given semester. G. If a student is enrolled in a course, the corresponding record must Indicate that the student has completed all prerequisite courses with a grade of at least C. H. A student cannot be registered (enrolled) in two courses taught at the same hour I. A student cannot be registered for more than 20 credits in a given semester. J. The room assigned to a course must have at least as many seats as the maximum allowed enrollment for the course. K. Once a letter grade of A, B, C, D, or F has been assigned for a course, that grace cannot later be changed to an I.

Explanation / Answer

A.Create unique constraint on ID column.
B.Create Referential Integrity constraint.Suppose there is a table Student having column student_id,course_id and another table
course having column course_id,course_name.Create course_id in course table as primary key and course_id in student table
as foreign key.
C.Create Check Constraint.Suppose,there is a table course having column course_id,course_name,told_enrollment.Then,create
check constraint
create table(course_id int,
course_name varchar(20),
total_enrollment int check (total_enrollment<=sum(total_enrollment))

D.Create Check Constraint.Similar as in step C.
E.Create unique Constraint.Suppose there is a table instructor having column inst_id,inst_name,course_id,timing.Create
unique constraint on timing column.
F.Create unique Constraint.Suppose there is a table course having column course_id,course_name,room_no,timing.Create unique
constraint on room_no and timing column.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.