M359 Relational databases:

M359 Relational databases: theory and practice
TMA01 Fall 2016 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 FALL 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 FALL 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 the process of denormalization and how it is done.(4 marks).
ii. Explain why we may need to use denormalization (i.e. provide the motivation for denormalization) (2 marks)
iii. Explain when it is a good idea to use normalization (2 marks)
iv. Give an example of denormalization (4 marks)
v. Explain the drawbacks of denormalization (2 marks)
vi. Explain when denormalization should be avoided (2 marks)
16
Question 2 (16 marks)
Use the e-library and other resources to answer the following questions:
i. Explain what is meant by extensible relational database design (2 marks)
ii. Explain what is meant by the "Entity-Attribute-Value (EAV) " approach (2 marks)
iii. Explain how can the EAV approach be used to implement database extensibility (2 marks)
iv. Give an example of using the EAV approach to implement a simple relational database with extensibility features (4 marks)
v. Explain why we may need to use the EAV approach to implement relational database extensibility (i.e. describe the advantages of the EAV approach for implementing relational database extensibility) (2 marks)
vi. Explain why we may not sometimes wish to use the EAV approach to implement relational database extensibility (i.e. describe the disadvantages of using the EAV approach for implementing relational database extensibility) (2 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 an online pizza ordering database for a pizza shop.
For each customer, we need to keep a customer id, first name, middle name, last name, title, telephone number and address.
A customer may make zero or more orders. For each order, we maintain a unique order id, a date, a time, and any special discounts offered on the whole order.
16
TMA01 M359 FALL 2016/2017 4 of 6
Each order is made by exactly one customer, taken by exactly one employee, delivered by exactly one driver and consists of one or more line-items.
An order cannot contain more than 100 line items.
For each employee, we keep an employee id, a first name, a middle name, a last name, an address, a telephone number and a salary.
An employee can take many orders.
For each driver, we keep the driver id, name, telephone number and hourly salary.
A driver may deliver many orders.
For each Line item, we keep a line number, a quantity, and a sale price.
A line item must be associated with a single order and must also be associated with a single menu item.
A menu item is a main item that can be ordered by itself from the menu. There are two types of menu items: standard items, like standard specialty pizzas, French fries, salads, drinks, etc. and custom pizzas which you can customize yourself by choosing the crust type, the toppings, and so on.
For each menu item, we keep a menu number, a name, a description, a menu price, and customer special requests.
A menu item can participate in zero or many line items. Every menu item must be either a standard item or a custom pizza but not both.
For standard items, we keep the menu number, which indicates that the menu item having this menu number is a standard item and the portion size (small, medium or large).
Obviously, a standard item must be associated with a single menu item.
For custom pizzas, we keep pizza-diameter, type of crust and flavor of crust.
A custom pizza must also be associated with a single menu item and can have many toppings up to a maximum of 7 toppings.
For each topping, we keep a type, pizza-diameter and price. A custom pizza can only be supplied with a topping priced for the same diameter.
A toping may be used by many custom pizzas.
Note: you should expect about 10 entity types, 9 relationship types, 45 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 FALL 2016/2017 5 of 6
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. (12 marks)
relation Writer
writer_id: writer_ids
name: names
primary key writer_id
relation Reader
reader_id: reader_ids
name: names
primary key reader_id
relation Books
book_id: book_ids
title: titles
primary key book_id
relation Write
writer_id: writer_ids
book_id: book_ids
primary key (writer_id, book_id)
foreign key writer_id references Writer
foreign key book_id references Book
constraint ((project Writer over writer_id) difference (project Write over writer_id)) is empty
constraint ((project Books over book_id) difference (project Write over book_id)) is empty
relation Read
reader_id: reader_ids
book_id: book_ids
primary key (reader_id, book_id)
foreign key reader_id references Reader
foreign key book_id references Book
constraint ((project Reader over reader_id) difference (project Read over reader_id)) is empty
constraint ((project Books over boo_id) difference (project Read over book_id)) is empty
16
TMA01 M359 FALL 2016/2017 6 of 6
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: F  G
a. Show that relation R is in 2NF and not in 3NF explaining 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 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 actual output from your DBMS. You are required to show the actual output of your queries.
a) List patient details for all male patients in ward number 'w5'.
2
b) List the ward numbers of all occupied wards with no duplicates.
2
c) List the patient ids, the start date and reason for treatment for all patients undergoing a treatment for a reason that contains the word 'temperature' or the word 'fever' and who started their treatment after 5 December 2006.
2
d) List the names of all specialists along with their specialisms.
2
e) Get the ward numbers and ward names for all wards that have no patients.
2
f) Write a query to get the number of distinct patients undergoing each type of treatment (i.e. reason for treatment) in the database, ordered alphabetically by the reason for the treatment.
2
g) Write a query to get the names of all patients receiving a prescription whose daily dosage is higher than the average daily dosage of all prescriptions using the same drug.
2
h) Write a query to retrieve the ward number and average weight of patients for all wards having more than three patients, excluding ward w5.
2
 End of Questions 