Database Design and Querying: Relational Model, SQL, and Active Rules

This document presents a comprehensive solution to a database design assignment. It begins by outlining the relational model, including the student, course, and term tables, and emphasizes the importance of primary keys and normalization. The solution then details integrity constraints, ensuring data consistency and reliability. Following this, the assignment delves into predicate logic, defining the schema for each entity. Subsequently, it provides SQL queries to retrieve specific data, such as students in a particular course and student numbers for students enrolled in a specific course and term. The solution then explores relational calculus, offering alternative query formulations. Furthermore, it covers the creation of views and defines active rules, including event, condition, and action components, to manage database events, such as issuing warning messages when a course has a low enrollment. The solution is designed to provide a thorough understanding of database design and querying principles.

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