M359 2015-2016

1
M359 Relational databases: theory and practice
TMA01 Fall 2015
(Cut-off date will be announced)
1. Rules and Guidelines
This section contains general rules and guidelines for completing and submitting your
TMA.
1.1 General guidelines
The TMA requires that you demonstrate an understanding of course concepts and
techniques, and an ability to apply these to sample problems. Your tutor will be following
a detailed marking scheme, but he or she will particularly look for the following:
Using course concepts and terminology. It is not enough to give answers that
simply rely on knowledge you may have gained about a topic from previous studies or
from general reading. You must draw upon the content and terminology taught in M359
unless the question asks you to use other notations from external sources.
Using the e-library and other external sources. When asked to do so, you need to
search the e-library and the internet to identify relevant material. In particular, you are
urged to use the following sources, all of which are freely available to AOU students:
1. AOU’s subscribed e-library, accessible through the LMS which includes a
number of different resources
2. Google books
3. Google scholar
1.2 Submitting your TMA
You are required to submit your TMA through the Learning Management System (LMS)
provided by your branch. In case there are additional files to be submitted together with
your TMA, you need to put all the files in a single directory and compress it into one .rar
archive and submit it by the Cut-off date. Submit your TMA to the LMS system on (or
preferably before) the cut-off date shown above. Your tutor will mark your script and
post the grades to the LMS.
1.3 Answering SQL-based questions
You are required to use the course software (MySQL) to answer all SQL questions. It is
not permitted to use other SQL environments for this course.
1.4 Plagiarism
2
Use your own words. All work you submit must be in your own words. Your tutor has
tools available to him/her to allow the detection of plagiarism from the Internet as well as
from other colleagues. If you copy material that is not your own and submit it as your
own you are committing plagiarism. Plagiarism is a serious offence and if a case of
plagiarism is detected, the Arab Open University will apply severe penalties and
disciplinary procedures.
Quoting and Referencing. If you wish to quote other materials, including the M359
Blocks, then you must clearly acknowledge the source according to accepted rules of
citation and referencing. You can use the MSWord® referencing tool to facilitate
including references and citations in the proper format. The following link shows you
how to use this feature:
http://office.microsoft.com/en-us/word-help/create-a-bibliography-HA010067492.aspx
Note that it is not enough to simply post a reference at the end of the document without
explicitly stating which parts of your document are being quoted. Proper citation of
external sources must be included. Also, quoting is only used in limited fashion; to refer
to a point using the words of a well-recognized guru, for example. Large amounts of
materials copied into your TMA will not be accepted, even if properly quoted. If you
need to refer to large amount of external material, you can simply refer to the source.
Getting help and collaborating with colleagues. You can discuss the TMA with your
tutor. Your tutor will help explain unclear points in the TMA and will direct you to useful
and appropriate material in the course. However, you should not expect your tutor to
supply you with answers to TMA questions. Remember that answering the TMA is
ultimately your responsibility, not your tutor’s.
Sharing knowledge and information and holding discussions with your colleagues about
the course material is called group learning and is encouraged by the Arab Open
University. However, at the end, you should complete the TMA by yourself and answer
the TMA, in your own words. Collaborating in answering TMA questions is not allowed,
and is not the same as group learning. You are also not allowed to use the course
forum to post answers to TMA questions or to collaborate on answering TMA questions.
3
TMA Total 80
Question 1 (8 marks)
Contrast the modern relational database approach with the old file systems approach. Provide
a critical evaluation of the current technology with respect to the following questions:
i. What kind of interface does each approach provide?
ii. What are the advantages of the style of interface provided by the modern database
approach?
iii. What sort of database maintenance problems can be solved by the new database
approach?
iv. Explain how is the new style of database interface implemented in the modern
database approach?
8
Question 2 (8 marks)
Use the e-library and other resources to the answer the following questions about Big Data and
Big Data Analytics:
i. Give definitions of what Big data is and what is meant by big data analytics and what are its
benefits? You can quote experts to answer this part, but you need to state your reference.
ii What are the characteristics of big data?
iii what are some of the sources of big data today ?
iv. What are the different types of big data in terms of how they are structured? Give examples.
8
Question 3 (8 marks)
Develop a conceptual model for the following scenario:
It is desired to build a database for AOU. The following is a modified version of the database
scenario. Develop a complete Conceptual data Model for it. The data model should consist of
the usual 5 components: E-R diagram, Entity Types including entity type identifiers,
assumptions, additional constraints, and limitations.
1. AOU needs to keep details of its staff and students and courses. Details of the
performance of the students and their academic history should be made available as
well. Information about all semesters and all years should be kept.
2. Staff are employed by exactly one branch and students are admitted to exactly one
branch. Each branch is identified by a branch number in the range 00-99, inclusive.
Other branch details include its name, address and telephone number.
3. Personal information about each student are recorded initially when the student is first
40
4
admitted to the university. This includes the student's ID (issued at the time of
admission), name, address and date of admission. Each student is admitted in one
particular branch, but students may change their branch later on. Initially, admitted
students will not have any registered courses, but they can register courses in each
semester. Every time the student registers a course, the registration date is recorded.
4. Information recorded for each member of staff includes the staff number, name,
address, telephone number and email address and the branch in which the staff
member is employed.
5. Staff may teach zero or more course groups each semester but each group must be
assigned exactly one staff to teach it. Each staff member may or may not be assigned
the role of General Course Coordinator (GCC) for one or more courses. A staff member
may also be assigned the role of Branch Course Coordinator (BCC) for one or more
courses. Information about only the current GCC and BCC assignments need to be
kept.
6. Students may or may not register in any course groups during a particular semester.
Initially, groups may not have any registered students, so each group may have zero or
more student registrations at any given time. Students may register a maximum of 16
credit hours per semester. Student scores on each course (tma, mta, final and letter
grade) need to be kept. Students cannot register in any course group unless it has a
tutor assigned to it.
7. A course may have zero or more groups but each group must be associated with
exactly one course. Each group meets once weekly. It has a group number that is
unique only within the same course, same term and same year. The number may
repeat for a different course, a different term or a different year. A group also has a
meeting place (location), start time and end time. Each course group has an upper limit
on the number of students registered in it that is different for each course. This limit is
recorded in the database for each course and no group is allowed to exceed this limit.
Students cannot register in more than one group for the same course, during the same
year and the same term.
8. Each course is given a course code that consists of two letters, followed by three digits.
For example, CS100, CE234, and so on. Each course also has a title and a value for
credit hours – either 1, 3, 4, 5 or 8 credits.
9. Each course has a single Tutor Marked Assignment (TMA), a single Mid Term
Assessment (MTA), a single Final exam and, obviously, a single letter grade. The
scores of those assessment items will be entered in the database as they become
available. So, there could be a time after student registration and before the
assessments' marks are entered when those marks will not be available. Keep in mind,
however, that null values are not allowed in the database.
Note: you should expect about 10 entity types, 12 relationship types, 57 attributes, 13
additional constraints, 5 assumptions and 4 limitations in the CDM for this
application.
5
Question 4 (8 marks)
i. Convert the following relational representation back into a CDM using two relationships
and three entity types. Explain how the constraint is being represented in the CDM.
relation user
user_Id: user_Ids
name: Names
primary key user_Id
relation tweets
user_Id: user_Ids
tweet_Id: tweet_Ids
primary key tweet_Id
foreign key user_Id references user
foreign key tweet_Id references tweet
constraint ((project tweet over tweet_Id) difference (project tweets over tweets_Id)) is
empty
relation tweet
tweet_Id: tweet_Ids
date_time: date_time
tweet_text: text
primary key tweet_Id
ii. use the relation schema below to answer the following questions:
marriage (man_id, woman_id, marriage_date, man_name, woman_name)
fd1: man_id, woman_id  marriage_date, man_name, woman__name
fd2: man_id  man_name
fd3: woman_id  woman_name
a. what is the highest normal form to which the relation complies ? show why the above
relation conforms to the normal form you identified and why it does not comply with
the next higher form.
b. normalize the above relation into the next higher normal form
8
6
Question 5 (8 marks) 16
You will need to refer to the University database to answer this question. Write SQL queries
to perform the following operations. in each case show the SQL statement you used and the
output from MySQL.
a) List the student details for all students registered after 1st of January 2004.
2
b) List all region numbers for regions that have students registered in them (with no
duplicates).
2
c) List the staff no. and name only for all staff in regions 1, 2 and 3 only
2
d) return a list of student names and the titles of courses each student is enrolled in,
ordered by student names 2
e) get the ids of all students not currently enrolled in any courses
2
f) Write a query to get the number of courses enrolled by each student, orederd by
student name. 2
g) Write a query to get for each course, the ids of the students who achieved an
examination mark that is above the average examination mark of the course. 2
h) Write a query to retrieve the name of the students with the highest examination mark
for each course 2