Designing Relational Database Model and Queries

This assignment content outlines the relational model, integrity constraints, predicate logic of schema, queries (SQL and Relational Calculus), views, and active rules for a student database. The relational model consists of three tables: Student, Course, and Term, with primary keys and foreign keys establishing relationships between them. Integrity constraints ensure data consistency and reliability. Predicate logic shows the structure of each entity, while SQL and Relational Calculus queries are provided to retrieve specific information. Views are defined to show degrees with course names and courses taken by at least 4 students. An active rule is also defined to send a warning message when a course has less than 3 students.

job1 assignment solution
Student
1 OCTOBER, 2016

CONTENTS
RELATIONAL MODEL................................................................................................2
INTEGRITY CONSTRAINTS......................................................................................3
PREDICATE LOGIC OF SCHEMA.............................................................................3
QUERIES.................................................................................................................... 4
SQL..........................................................................................................................4
RELATIONAL CALCULUS......................................................................................4
VIEWS.........................................................................................................................4
ACTIVE RULES.......................................................................................................... 5
1 | P a g e

RELATIONAL MODEL
Predicate schema is:
Student (number, name, degree)
Course (code, cname)
Term (name, student_number, Course_code)
The relational schema is:
STUDENT NUMBER NAME DEGREE
COURSE CODE CNAME
TERM Name STUDENT_NUMBE
R
COURSE_CODE
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, number is a primary key to
identify each record uniquely. In the second table course, code is the primary key to
identify each record. And the third table ‘term’ has been designed to model the
‘taking’ relation between the tables. The term table has term name student number
and course code to identify which student has which course in each term. This
2 | P a g e

method is best has been done by normalizing the whole relation between student
and the course.
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 one or more: this is necessary to maintain the foreign
key 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
5. The term name, course code and student number together will be taken
together which will be a primary key to identify each record.
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.
PREDICATE LOGIC OF SCHEMA
Student entity
StudentD C Dom (Number) x Dom (Name) x Dom (Degree)
3 | P a g e

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.
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’.
4 | P a g e

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
Define as in the attached _le (Sources/L3.pdf) an active rule that sends a warning
message (to the external application) when a course has less than 3 students.
Explain each of its components.
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.
5 | P a g e