Assignment 1 (Due Oct 31, 2018)
There are 5 questions in this assignment.
Question 1 – Database Concepts (10 marks)
Give an example of a realistic source of data for which the relational model does not work very well. Note that this question is about structuring data, not computing things.
Question 2 – Schema Design (25 marks)
The purpose of this question is to gain some experience in designing a database schema from a given paper form. In PolyU, the PolyU Community Service Fund Student Service-Learning
Grants aims to support student self-initiated service-learning projects at PolyU. Interested student teams can submit a detailed project proposal (including project objective, service content,
implementation details, budgeting, etc.) with the completed application form.
a. Develop an ERD including data types of the attributes and indication of the primary keys of the application form. The form. can be accessed at
https://www.polyu.edu.hk/osl/index.php?option=com_contentview=articleid=119Itemid=303.
b. Suppose the chronological information of past service activities as well as the
budget/resources consumed in the service project would need to be recorded. That information is not currently asked in the form. Suggest the changes to be made in the
form. and the corresponding final ERD afterwards.
Question 3 - Relational Algebra (15 marks)
You are given a relation Employees(name, department, salary). For each of the following queries,
either write the query in relational algebra and explain informally why your answer works, or explain why it cannot be written in relational algebra. You may use only the following operators
of relational algebra: union, intersection, difference, selection, projection, products, joins, divide and renaming.
a. Find all department(s) with at least two employees of different names.
b. Find the department(s) with the most people. c. Find the names of employee(s) with the highest salary.
Question 4 - Schema Design (35 marks)
FreeFeet is a manufacturer in Asia which makes running shoes. It needs to develop a database of
all the running shoe types that it offers for sale. Each city has a number of retail shops and the sale figures for each shop are to be maintained. Shoes are designed to tailor specific market
requirements so that a style. of shoe sold in one market may remain approximately the same but have external stylistic additions or a different sole added. For example, Shanghai has a large
jogging shoe market, while Singapore has a huge market of hiking. The design of these 2 types of shoes is basically the same but the jogging design has added the air-cushion in it.
Shoe models may create a new line of show products, or may substitute an older model that is
taken off the market. For every model, the designer information including name, address, phone number and the date it was introduced should be kept. For models that substitute an older model
there is a need to maintain the relationship between the new and the substituted model, also the date it was introduced. When a model is an adaption of another one, it is necessary to capture the
relationship between the two, the date when the adaption was introduced and the country for which the adaption is intended.
Some shoes are having different gradations of the same class of shoes, i.e., the stitching may be a
little less well done to save money or a cheaper form. of foam cushioning or reinforcement plastic may be built into the cheaper shoe.
For each shoe model, its name, the older model it has replaced (if any), manufactured cost, retail
price and shipping costs in each city it is sold, design type, soles type, cushion support type and the major sports it is intended for are to be recorded. In cases where a model is adapted for a
particular country, the database needs to store a model name, the name of the adapted model, the designated country and climate type for which the model is designed. In addition, the database
needs to store yearly sales by city, including number of pairs sold, price, and net profit, as well as climate type for each city.
a) Design an ERD for the requirements described above.
b) Transform. the schema which you designed in (a) into a set of tables and provide their primary key and foreign keys correspondingly.
c) Create the tables in MySQL and populate them with some pseudo data. d) Provide SQL statements to answer the following queries and test them out in MySQL.
i. What shoe model has the longest history for badminton sports? ii. Provide the profit made for different shoe models sold in Shanghai?
iii. What are the top 2 cities of annual sales for the model “Y2Jump” last year? iv. What is the total profit from the sales of “Y2Jump” in 2017?
Question 5 - SQL (15 marks)
Consider the four queries given below. Give an example of a database schema that guarantees two of the queries return only one record, and the other two return two records. Provide the
tables, sample data as well as the resulting records for each query. Explain how you come up with such design. If the schema cannot be provided, explain why it is not possible.
a. SELECT A.X FROM A NATURAL JOIN B WHERE A.X NOT IN (SELECT A1.X FROM A A1 WHERE A1.Y = 500)
b. SELECT B.Y FROM B WHERE B.Z > ALL (SELECT B1.Z
FROM B B1 WHERE B1.Z > B.Z)
c. SELECT DISTINCT A.X, SUM(B.Z) FROM A, B WHERE A.Y=B.Y
GROUP BY A.X HAVING COUNT(B.Z) > 1
d. SELECT A1.X FROM A A1
WHERE A1.Y < 600 UNION
SELECT A.X FROM A NATURAL JOIN B