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 th

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