Relational Database Design and Integrity Constraints

The assignment content describes the relational model for managing students, courses, and terms. It includes three tables: Student (number, name, degree), Course (code, cname), and Term (name, student_number, course_code). The purpose of normalization is to reduce redundancy and prevent update anomalies. Integrity constraints are also defined to maintain data consistency. The predicate logic schema for the entities Student, Course, and Term is also described. Additionally, queries in SQL and relational calculus are provided, along with views and active rules.

Q (a)
RELATIONAL MODEL
Predicate schema is:
Student (number, name, degree)
Course (code, cname)
Term (name, student_number, Course_code)
Taking (number, code, term)
The relational schema is:
STUDENT NUMBER NAME DEGREE
COURSE CODE CNAME
TERM Name STUDENT_NUMBE
R
COURSE_CODE
TAKING NUMBER CODE TERM

The purpose of the normalization is to reduce redundancy and prevent update
anomalies. In the above diagram we can see that through the ER diagram, 3 tables
have been designed in the relational model. In the first table Student where “number”
is a primary key to identify each record uniquely. In the second table Course where
code is the primary key to identify each record. And the third table ‘term’ Taking has
been designed to model the ‘taking’ relation between the tables. The ‘term’ ‘taking’
table has term name student number and course code as primary key to identify
which student has which course in each term. This method is best has been done by
normalizing the whole relation between student and the course. Because there will
not be any redundancy in those table since student data, course data and
relationship between student and course are being stored in separated table.
Q (b)
INTEGRITY CONSTRAINTS
The integrity constraints are:
1. Each Student will have a unique student ‘number’ which will be a primary key.
This constraint is necessary so that each student record can be identified
directly by the number and can be stored uniquely.
2. Each Course will have a unique course ‘code’ which will be a primary key.
This constraint is necessary so that each course info can be identified directly
by the code and can be stored uniquely

3. Each Student will have take at least one course and may take up to four
courses. This constraint is necessary to maintain the foreign key relationship
cardinality constraint between “Stundent” and “Taking” relationship
4. There will terms for each of the 4 courses: this is necessary to maintain the
(1,4) relationship between the course and term Each Course should have at
least one or more students to maintain the cardinality constraint between
“Course” and “Taking” relationship.
5. The term name, course code and student number together will be taken
together which will be a primary key to identify each record. The Taking table
unique combination of course code and student number which will be the
primary key of the table.
6. Each course term will have one or more students in it.
All these will help in eliminating redundancy, update anomalies, insert or delete
anomalies and will make the relational database more consistent and reliable.
Student number which appears in the Taking table must be in the Student table in
order to satisfy referential constraint.
7. Course code which appears in the Taking table must be in the Course table in
order to satisfy referential constraint.
Q (c)
PREDICATE LOGIC OF SCHEMA
Student entity
StudentD C Dom (Number) x Dom (Name) x Dom (Degree)

This predicate logic shows that Student is made up of 3 finite attributes. These
attributes are subset of the relation student.
Course entity
CourseD C Dom (Code) x Dom (Cname)
This predicate relation shows that the code and Cname are subset of the relation
course.
Term entity
TermD C Dom(Name) x Dom(Student_number) x Dom(Course_code)
Similarly term relations has attributes name, student_number and course code as its
part.
1. x∀ 1, y1,y2,z1,z2 Student(x1, y1, z1) ^ Student (x1, y2, z2) y1 = y2 ^ z1 = z2
That is, the student number is unique and this should not be duplicated
2. x∀ 1, y1,y2 Course(x1, y1) ^ Course (x1, y2) y1 = y2
That is, the course code is unique and this should not be duplicated
3. x∀ 1, y1, z1,z2 Taking(x1, y1, z1) ^ Student (x1, y1, z2) z1 = z2
That is, the combination of student number and course code is unique and the
combination should not be duplicated.
4. x∀ 1, y1,y2,y3, z1,z2 Taking(x1, y1, z1)  Student (x1, y2, z2) ^ Course(y1,y3)
Student number that appears in the Taking table should be in Student table
and course code that appears in the Taking table should be in Course table
QUERIES
SQL
1. All the students of computer science who are taking `introduction to CS".

SQL: SELECT S.* FROM STUDENT S, COURSE C, TERM T WHERE
C.CODE = T.COURSE_CODE AND S.NUMBER = T.STUDENT_NUMBER
AND C.CNAME = ‘introduction to CS’ AND S.DEGREE = ‘computer science’
2. Student numbers of students who take in Fall'15 the course COMP1805
SQL: SELECT T.STUDENT_NUMBER FROM COURSE C, TERM T WHERE
C.CODE = T.COURSE_CODE AND C.CNAME = ‘COMP1805’ AND T.NAME
= ‘Fall'15’.
RELATIONAL CALCULUS
1. All the students of computer science who are taking `introduction to CS".
RC:
2. Student numbers of students who take in Fall'15 the course COMP1805.
RC:
VIEWS
Define in predicate logic views (i.e. a new relational predicate name plus its
definition) showing:
1. The degrees with the course names (when there are students following the
former and taking the latter).
2. The courses that are taken by at least 4 students.
3. Use the views to express the query about the course names taken by biology
students.
ACTIVE RULES
Answer: the components of active rules are:

1. Event
2. Condition
3. Action
The components with reference to the above condition are:
Event: insertion of <name, student_number, course_code> into term table.
Condition:
Action: a warning message is issues.