M359 Relational databases: theory and practice

M359 Relational databases: theory and practice
TMA01 Spring 2017 TMA
(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.
TMA01 M359 Spring 2016/2017 2 of 6
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.
TMA01 M359 Spring 2016/2017 3 of 6
ANSWER ALL QUESTIONS
TMA Total
80
Question 1 (16 marks)
Use the block notes to answer the following questions:
i. Explain what is meant by decision support systems, data warehousing and data mining and the relationship between them. (6 marks).
ii. Explain the difference between strategic decision making and operational decision making (2 marks)
iii. Explain the difference between the nature and source of data required to facilitate strategic and operational decision making. (2 marks)
iv. Explain how the nature of the database systems required to support strategic decision making differs from the nature of database systems required to support operational procedures. What terms are used to refer to each of those two types of systems?(2 marks)
v. Explain using an example the Multidimensional data model and how it enables decision makers to view data from different multiple perspectives to support decision making. (4 marks)
16
Question 2 (16 marks)
Use the e-library and other resources to answer the following questions:
i. Explain what is meant by NoSQL technology and why it was introduced? (2 marks)
ii. Explain how does the NoSQL database technology differ from relational database technology (4 marks)
iii. Explain what is meant by the "Key-value store" approach and what are its major advantages? (4 marks)
iv. Give two different use cases for Key-value stores. (4 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 four years) (2 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:
It is desired to develop a company database.
For each Employee, we need to keep the employee id, first name, middle name, last name, gender, title, telephone number and address.
An employee may take many Leaves. No employee may take more than a total of more
16
TMA01 M359 Spring 2016/2017 4 of 6
than 45 days of leave per year.
Each Leave is taken by taken by exactly one employee.
An employee may have zero or more dependents.
An employee must be hired by exactly one Department.
An Employee may or may not manage any department but can manage a maximum of a single department.
An employee may additionally work on a number of projects.
For each Leave, we maintain a unique leave id, a start date, an end date, and the type of leave taken (regular, emergency, medical or official assignment).
A leave must be taken by a single employee.
A Dependent is a person who is related to an employee (father, mother, son, daughter, husband or wife.). For each dependent, we keep a dependent Id, name, date of birth, gender and relationship to employee.
A dependent must be related to a single employee. The value of the relationship must match the dependent gender. For example a female cannot be the son of an employee.
For each Department, we keep the department id, name and website address.
a department can hire many employees. Each department must also be managed by a single employee.
For each Project, we keep a project id, name and mission.
Each Project must have at least one employee working on it but possibly many. A Project may also make multiple PurchaseOrders.
A PurchaseOrder has an order id and a date.
Each PurchaseOrder is made by exactly one project.
A PurchaseOrder must have at least one LineItem, but possibly many.
For each LineItem, we keep a line item number that is unique within the purchase order, and the purchase order id and a quantity for the purchased item.
Each LineItem must be related to a single Item.
For each Item, we keep an item id and description.
An item may participate in multiple line items.
Note: you should expect about 9 entity types, 9 relationship types, 34 attributes, 8 additional constraints, 4 assumptions and 2 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 marks for producing reasonable and correct assumptions that match your ER diagram
2 marks for producing correct limitations
TMA01 M359 Spring 2016/2017 5 of 6
Question 4 (16 marks)
i. Convert the following relational representation back into a CDM using 4 relationships and 4 entity types. Include the ER diagram, the entity types and the additional constraint sections only. (12 marks)
relation Person
person_id: person_ids
name: names
primary key person_id
relation Movies
movie_id: movie_ids
title: titles
primary key movie_id
relation Participate
person_id: person_ids
movie_id: movie_ids
primary key (person_id, movie_id)
foreign key person_id references Person
foreign key movie_id references Movie
constraint ((project Person over person_id) difference (project Participate over person_id)) is empty
constraint ((project Movies over movie_id) difference (project Participate over movie_id)) is empty
relation Watch
person_id: person_ids
movie_id: movie_ids
primary key (person_id, movie_id)
foreign key person_id references Person
foreign key movie_id references Movie
ii. Consider the 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: C  A, B, D, E, F, G
fd3: D  E
fd4: G  B
16
TMA01 M359 Spring 2016/2017 6 of 6
a. Show that relation R is in 2NF and not in 3NF and explain why (1 mark).
b. Normalize the relation R 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 if not already in BCNF and show the primary keys (1 mark).
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 actual output from your DBMS. You are required to show the actual output of your queries.
a) List drug details for all antibiotics with a price greater than 1.0.
2
b) List the team code and the names and positions of all members of team t29.
2
c) For each ward, list the ward number and its occupancy (= the actual number of patients in the ward).
2
d) List the name of every nurse along with the name of her supervisor ordered by nurse name.
2
e) get all the details of any doctor who does not provide any treatments.
2
f) Write a query to get for each drug type, the type and the number of drugs in the type in the database, ordered alphabetically by the drug type.
2
g) Write a query to get the details of all drugs whose price is above the average price of all drugs of the same type.
2
h) Write a query to retrieve the ward number and maximum height of patients for all wards having less than 6 patients, excluding wards w3, w5 and w7.
2
Note: Both the standard SQL and the MySQL implementation of SQL are acceptable.
 End of Questions 