M359
Relational databases: theory and practice
TMA Fall 2013-2014
Cut-off
date: 11, December 2013
|
Question 1
10
Marks
|
|
Briefly describe the term
“Cloud Database”. Your answer should include definition, different methods to
run cloud database, benefits and examples of cloud database services. You need to check your
e-library or external resources to provide an adequate answer to this
question. Zero mark will be awarded for the answers without proper
referencing.
|
|
Question 2
20 Marks
|
|
This
question assesses your understanding of the Conceptual Data Model (CDM)
presented in section 5 of Block 1.
|
|
Give a conceptual data model (CDM)
for the following given scenario using the Entity-Relationship model,
including the complete E-R diagram, the entity types and any required
constraints and assumptions.
A
social recreation club would like to create a database to keep track of club
members, committees and activities. Suppose that after requirements
collection and the analysis phase the database designers provide the
following descriptions to be represented in the database.
The
club forms committees for different activities. Committee is formed by
several members. Each committee has a unique name, a unique number, and a
particular member who manages the committee. The system keeps track of the
start date when that member began managing the committee. A committee
controls a number of activities, each of which has a unique name, a unique
number and description of the event. A committee may organize activities for
several events. The database stores the member’s unique identification
number, member name, address, gender and birth date. A member is assigned to
one committee, but may participate on several activities, which are not
necessarily controlled by the same committee. The system keeps track of the
direct head of team members (who is another member). For payment purpose the system also keeps
track of details of non-club members participating in the activities, in
special circumstances. Their details to be stored in the system are artist’s
first name, gender, birth date and the club member who recommended them to
the club.
|
|
Question 3
20 Marks
|
|
This question assesses your
understanding of E-R conceptual modeling as discussed in Section 5 of Block 1
and assesses your knowledge of Block 2, Sections 2 and 4, and in
particular, your understanding of the relational representations. Questions a
and b assess your ability to transform a simple E-R fragment into a
corresponding relational representation and questions c and d assess your
ability to transform relational representation into corresponding E-R
fragment
|
|
a.
Convert the following E-R
fragment into a relational representation.
Customer (CustomerID, CustomerName)
Students (InvoiceNo,
InvoiceDate)
|
|
b.
Convert the following E-R
fragment into a relational representation.
Professor (StaffID,
StaffName)
Conference
(ConferenceID,ConferenceTtile)
|
|
c.
Convert the following
relational representation back into a CDM using two relationships and three
entity types.
relation Author
AuthorID:AuthorIDs
AuthorName: AuthorNames
primary key AuthorID
relation Submits
AuthorID: AuthorIDs
ArticleNo: ArticleNos
primary key ArticleNo
foreign key AuthorID
references Author
foreign key ArticleNo references Article
constraint ((project
Article over ArticleNo) difference (project Submits over
ArticleNo)) is empty
relation Article
ArticleNo: ArticleNos
ArticleTitle: ArticleTitles
primary key ArticleNo
|
|
a.
Convert the following
relational representation back into a CDM using two relationships and two
entity types.
relation
Staff
StaffID:
StaffIDs
StaffName:
StaffNames
primary
key StaffID
relation
Project
ProjectID:
ProjectIDs
ProjectName:ProjectNames
StaffID:
StaffIDs
primary
key ProjectID
alternate
key StaffID
foreign key StaffID references Staff
|
|
Question 4
20 Marks
This
question assesses your understanding of manipulating relations and
constraints as discussed in Sections 3-5 of Block 2.
|
|
1.
Find all
studentIDs and courseIds of course ‘C2’ with registration date less than
12/12/2004
2.
Find
course_codes of courses taught by the tutor ‘Lai’
3.
Find the staff numbers and
names of all staff members who are not working as tutors.
4.
Find the ids and names of
all students in region number 4 together with the titles and enrollment date
of the courses they are currently enrolled in.
5.
Find the ids of all
students who are enrolled in courses with a quota 250 along with the course ID and their titles.
|
|
Question 5
10 Marks
|
|
This
question assesses your understanding of normal forms as discussed in Section
5 of Block 2.
|
|
Consider
the relation R = (A, B, C, D, E, F, G, H, I) and the set of functional
dependencies
A, B -> C
A
-> D, E
B
->F
F -> G, H
D
-> I, J
a.
What is the key for R?
b.
Decompose R into 2NF, then
3NF relations.
|
|
Question 6
20 Marks
|
|
This question is based on Section 1,2,3 and 4 of
Block 3. It assesses your ability to write and understand simple SQL queries
(as defined in the sections). To answer this question, you should use the
University database as it is provided in the course software and as
summarized in the University database cards.
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.
|
|
1.
Find all
studentIDs and courseIds of course ‘C2’ with registration date less than
12/12/2004
2.
Find
course_codes of courses taught by tutor ‘Lai’
3.
Find the staff numbers and
names of all staff members who are not working as tutors.
4.
Find the ids and names of
all students in region number 4 together with the titles and enrollment date
of the courses they are currently enrolled in.
5.
Find the ids of all students
who are enrolled in courses with a quota 250
along with the course ID and their titles.
|
