M359 Relational databases:
theory and practice
TMA
Spring 2013
Question 1
This question covers
Sections 1 and 2 of Block 1. It assesses your understanding of various concepts
covered in those sections and your ability to relate those concepts together.
Data quality issues are
described in section 2.3 of Block 1. Briefly explain the problems could you
face if each of the quality issues are not met in your SIS page.
Briefly describe the term
“Geographic Information System (GIS)”. Also provide two uses of GIS.
Describe the importance of
data warehousing and data mining in business organizations.
Question 2:
AOU is planning to create a
conference management system to keep
information about the
conferences such as papers presented in the
conference, d ate of
conference, location etc. The conferences are cond ucted
in any of the seven
branches. One branch may conduct several
conferences. Each
conference is id entified by a conference ID. Other
d etails to be stored are name, field, year, start
date, end d ate and the
branch name cond ucting the
conference. The branch d etails to be stored
are the organizing d
epartment, contact person, conference location, email
add ress, and telephone
number. The contact person could be the branch
director or the programme coord inator
A conference would receive
lots of paper to be submitted to the
conference, but only
selected number of these papers will be accepted.
Paper d etails are paper
ID, title, author name, and number of pages. The
paper may have secondary
authors. In ad d ition, each paper is given a type
(for example S: Survey, B:
Brief, F: Full). A paper can be submitted in only
one conference and many
papers may be submitted to the same
conference. Once a paper is
submitted in a conference, the system send s a
message to the author acknowledging the paper
submission.
If a paper is accepted , at
least one author should attend the conference to
present the paper. The
system needs to keep information about the author
who presented the paper in
the conference. Each author is id entified by a
unique author ID, Also has
a name, ad d ress, email ad dress, affiliation and
contact telephone number of
this affiliation. An author can write one or
more research papers and a
paper have one main author and one or more
secondary authors.
The system should also
store the number of papers submitted and
accepted for each
conference along with the paper ID, author ID,
conference date, number of
attend ees and the conference location for
archiving purpose.
Give a conceptual data model
(CDM) for this application using the Entity-
Relationship model,
including the complete E-R diagram, the entity types
and any required
constraints and assumptions. Give all degree and
participation conditions details.
AOU is planning to create a
conference management system to keep information about the conferences such as
papers presented in the conference, d ate of conference, location etc. The
conferences are conducted in any of the seven branches. One branch may conduct
several conferences. Each conference is identified by a conference ID. Other
details to be stored are name, field, year, start date, end d ate and the
branch name conducting the conference. The branch details to be stored are the
organizing department, contact person, conference location, email address, and
telephone number. The contact person could be the branch director or the
programme coordinator
A conference would receive
lots of paper to be submitted to the conference, but only selected number of
these papers will be accepted. Paper details are paper ID, title, author name,
and number of pages. The paper may have secondary authors. In addition, each
paper is given a type (for example S: Survey, B: Brief, F: Full). A paper can
be submitted in only one conference and many papers may be submitted to the
same conference. Once a paper is submitted in a conference, the system sends a message
to the author acknowledging the paper submission. If a paper is accepted , at
least one author should attend the conference to present the paper. The system
needs to keep information about the author who presented the paper in the
conference. Each author is identified by a unique author ID, Also has a name,
address, email ad dress, affiliation and contact telephone number of this
affiliation. An author can write one or more research papers and a paper have
one main author and one or more secondary authors.
The system should also
store the number of papers submitted and accepted for each conference along
with the paper ID, author ID, conference date, number of attendees and the
conference location for archiving purpose.
Give a conceptual data
model (CDM) for this application using the Entity-Relationship model, including
the complete E-R diagram, the entity types and any required constraints and
assumptions. Give all degree and participation conditions details.
Question 3
Give an E-R diagram and the
simplest possible representative occurrence diagram for each of the following
situations; State any assumptions you made.
Relationship between
AOU-ITC student and their graduation project (T471).
Relationship between
AOU-ITC tutors and graduation project (T471).
Relationship between
External Examiner and ITC graduation project (T471) in AOU
Relationship between
assessment (MTA, TMA and Final Exam) and graduation project (T471) in AOU
Question 4
Assume the following
relation R is taken from AOU database
R(StudentID,DepartmentID,TutorID,CourseID,EnrollmentDate,StartDate,
FinalExam)with the following FDs
StudentID, CourseID
EnrollmentDate
DepartmentID TutorID
DepartmentID,
EnrollmentDate StartDate
StudentID, StartDate
FinalExam
1. What are the candidate keys
of R?
2. Provide a BCNF
lossless-join decomposition for
R(StudentID,DepartmentID,TutorID,CourseID,EnrollmentDate,StartDate,FinalExam).
Show your steps.
Question 5
Given the following
relational schema for part of a University database
Tutor(TutorID, TutorName, Position, Salary)
Course(CourseCode,
CourseDescription, Credit)
CourseOffered(CourseCode,
Semester, TutorID)
Assumption:
Each course has only one
tutor in each semester. All tutors have different salaries, all tutors have
different names, all courses have different descriptions, and position can take
values FROM “Full”, “Part”, and “LabAssistant”.
Display those tutors’ ID
who is a tutor for ‘M359’ but not for ‘M257’.
Display those courses whose
tutors are only Part Time tutors.
List all the course names
for Prof.Mark in Spring 2012.
Display those courses that
have been offered in all semesters.
Display the names of full
time tutors of ‘M105’ course.
Question 6
Write SQL queries, and run
them using iSQL against the University database. Include in your answer BOTH
the SQL query you wrote and the output table that results. You MUST
use meaningful column names, which may require using column aliases in the SQL
query.
Write a SQL query to find
the ids for all students who achieved the lowest mark on the assignment among
all students in the course ‘C2’, along with the course code and the assignment
mark.
Write a SQL query to find
the number of students attended the exam for the courses c2 and c4, your result
should include the course codes also.
List the course code,
number of students registered in each course along with their tutor.
Write a SQL query to list
the student ID, student name, email address whose email address is in ‘.com’
domain and registered for course with course code C4. Output should show the
course code and the enrollment date.