1 M359 Relational databases: theory and practice TMA01 Spring 2016 Marking Guide (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

1
M359 Relational databases: theory and practice
TMA01 Spring 2016 Marking Guide
(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 TMA.
2
1.4 Plagiarism
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
ANSWER ALL QUESTIONS
TMA Total
80
Question 1 (16 marks)
Use the block notes and the supplied domain of discourse document for the Hospital database to answer the following questions:
i. What is a domain of discourse document and what is its purpose? Which enduring issue of data management does it relate to? (2 marks)
ii. Why can't we just add the information contained in the domain of discourse document to the scenario descriptions? (2 marks)
iii. Identify eight examples of useful pieces of information in the domain of discourse document for the Hospital database that could not be obtained from the scenario descriptions. (8 marks)
iv. How is the domain of discourse document organized? What is the purpose of the occurrence diagrams included in the second part of the domain of discourse document? (4 marks)
16
Question 2 (16 marks)
Use the e-library and other resources to answer the following questions about Object Oriented Database Management Systems (OODBMs):
i. Explain the main idea of OODBMSs (3 marks)
ii. Explain the main differences between OODBMs and Relational Database Management Systems (RDBMSs) (3 marks)
iii. What are the advantages and disadvantages of OODBMS vs. RDBMSs? (3 marks)
i. What types of applications that would better fit the OOBDs than the Relational model (3 marks)
You must provide at least two references in the Harvard style of referencing, excluding your course materials and Wikipedia. At least one of the references should be a recent reference (within the last two years) (4 marks).
16
Question 3 (16 marks)
Develop a conceptual model for the following scenario. The data model should consist of the usual 5 components: E-R diagram, Entity Types (including entity type identifiers), assumptions, additional constraints, and limitations:
A travel agency arranges package tours for tourists. It is desired to build a database to keep track of branches, staff, tourists, bookings, tour groups, tour packages and package segments. Obviously, a branch can make many bookings. The travel agency has multiple
16
4
branches.
1. Each branch hires a number of staff members. A branch is identified by a unique branch number from 00 to 99, and has a name, a location and a telephone number. Tourists can join package tour groups only through any one of the agency's branches and the booking branch should be recorded for each booking.
2. Each staff must be hired by exactly a single branch. A staff can be a package coordinator for a number of tour packages and may or may not be a tour guide for one or more tour groups at different times. Naturally, a staff cannot be a tour guide for more than one tour at the same time. The system must ensure that no staff is assigned to time-overlapping groups simultaneously. For each staff, the database should record the staff id, name, address, telephone and email.
3. Each tourist is assigned a unique id and the travel agency will keep the following information about each tourist: id, name, address, telephone, nationality and passport number. Each tourist can book a number of tour groups.
4. Each tourist booking must link a tourist with a package group and the branch at which the booking was made. For each booking, we also keep the date booked and the price charged.
5. Each group may have zero or more bookings. Each group must be guided by a single staff and must belong to a single package. Each group has a maximum capacity and the system must ensure that the number of bookings for a group does not exceed this maximum capacity. For each group, the database should record the unique group id, the start date, the maximum capacity and the group status. This status could be either open (meaning that tourists can make bookings in the group), closed (meaning that the group is full and not accepting anymore bookings) and cancelled (meaning that the tour group has been cancelled).
6. Each package must be coordinated by one staff. A package can have multiple groups
. Each package consists of a number (at least one) of package segments arranged in a certain order. This order could be indicated by a segment sequence number. Each package is identified by a unique package id consisting of three letters followed by three digits. In addition, the company will keep for each package a name and a description in the database.
7. Each package segment has a segment id, a duration in hours, a starting location, an ending location, a type and a recommended price. A segment may be part of zero or more packages.
Note: you should expect about 8 entity types, 9 relationship types, 33 attributes, 8 additional constraints, 5 assumptions and 3 limitations in the CDM for this application.
The marks will be allocated as follows:
4 marks for producing correct entity types (including attributes and identifiers)
4 marks for producing correct relationships (including their degree and participation conditions)
4 marks for producing correct additional constraint
2.5 marks for producing reasonable and correct assumptions
1.5 marks for producing correct limitations
5
Question 4 (16 marks)
i. Convert the following relational representation back into a CDM using three relationships and four entity types. Include the ER diagram, the entity types and the additional constraint sections only. (8 marks)
relation customer
customer_Id: customer_Ids
name: Names
primary key customer_Id
relation order
order_Id: order_Ids
customer_Id: customer_Ids
date: dates
primary key order_Id
foreign key customer_Id references customer
relation sale-item
order_Id: order_Ids
item_Id: item_Ids
quantity: integer
unit_price: decimal (5, 2)
primary key (order-Id, item-Id)
foreign key order_Id references order
foreign key item_Id references item
constraint ((project order over order_Id) difference (project sale-item over order-Id)) is empty
relation item
item_Id: Item_Ids
item_name: intem_names
primary key item_Id
16
6
ii. use the 1NF relation schema below to answer the following questions: (4 marks)
R (A, B, C, D, E, F, G)
fd1: (A, B)  C, D, E, F, G
fd2: B  C
fd3: D  E
fd4: F  B
a. Normalize the relation R into a set of 2NF relations (1 mark). Show the primary keys. (1 mark)
b. Further normalize the result of the previous step into a set of 3NF relations (1 mark). Show the primary keys (1 mark).
c. Further normalize the results of the previous step into a set of BCNF relations and show the primary keys (2 marks). Are there any problems with your answer that would require further action? (2 marks)
Question 5 (16 marks)
16
You will need to refer to the Hospital 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. You are required to include screenshots showing the actual output of your queries.
a) List the drug details for all Antibiotic drugs costing more than 1.
2
b) List the staff numbers of nurses who supervise by other nurses
2
c) List the patient id sand patient names for all female patients assigned to one of the wards (w2, w4 or w7).
2
d) return a list of patient names and the names of their treating doctors ordered by doctor names
2
e) get the ids and names of all patients not currently receiving any treatment
2
f) Write a query to get the number of patients treated by each doctor, ordered by doctor name.
2
g) Write a query to get the names of all patients who have a weight that is higher than the average weight of all other patients who are under the responsibility of the same doctor.
2
h) Write a query to retrieve the names of the drugs with the highest prices for each drug type
2