IE 332 - Homework #2
Read Carefully. Important!
As outlined in the course syllabus this homework is worth 7% of your final grade. The maximum attainable
mark on this homework is 140. As was also outlined in the syllabus, there is a zero tolerance policy
for any form of academic misconduct. The assignment can be done individually or in pairs.
By electronically uploading this assignment to Blackboard you acknowledge these statements and accept
any repercussions if in any violation of ANY Purdue Academic Misconduct policies. You must upload
your homework on time for it to be graded. No late assignments will be accepted. Only the last
uploaded version of your assignment will be graded.
NOTE: You should aim to submit no later than 30 minutes before the deadline, as there could be last
minute network traffic that would cause your assignment to be late.
When submitting your assignment it is assumed that every student considers the below checklist, as there
are grading consequences otherwise (e.g., not submitting a cover sheet is an automatic grade of ZERO).
� Attach a cover sheet (see Blackboard) as the first page of your submission.
� Submit page i of this assignment as the second page of your submission.
� Your solutions have style (see Q1 in the assignment).
� All of your solutions (program code, etc.) are included in the submission.
� All of your source code is included as requested (.R, etc.).
� You have not included any screen shots, photos, etc. (plots from R should be intermediately saved
as .png files).
� All math notation and expressions are created using an equation editor (no pictures, handwritten
solutions, etc.).
� If using Word or other text processor, convert the output to .pdf and ensure that it does not
contain any aesthetic or other e
ors. Submit only the pdf version.
� If using LATEX, the source code is separately submitted in a .zip file. If using LATEX co
ectly,
there is a 7 point bonus. Failure to submit the source code voids potential bonus.
� If submitting with a partner, BOTH must have uploaded the SAME assignment by the due date.
� Watch videos on creating pseudocode if you need a refresher or quick reference to the idea. These
are good starter videos:
www.youtube.com/watch?v=4jLO0vXPktU
www.youtube.com/watch?v=yGvfltxHKUU
Page i of i
https:
www.youtube.com/watch?v=4jLO0vXPktU
https:
www.youtube.com/watch?v=yGvfltxHKUU
IE 332 Homework #2 Due: Mar XXXXXXXXXX
1. (0 points) These are style points meant to enforce the skill of communicating technical information in
a precise, concise and easily interpretable way. You are penalized for (a) using poor gramma
spelling,
(b) disorganized presentation of solutions (including organizing your code into functions, as appro-
priate), (c) not commenting well your source code, (d) not using meaningful variable names in you
code. At the discretion of the TA (who should be grading this “hard”). The presumption is that you
do not do any of these things, and so doing them will cost addition points (up to -10). Your goal is
to get 0/0 on this question.
The assignment should have margins between 0.5 and 0.75 inches wide, with font size no larger than
12pt (11pt for code) and no smaller than 10pt - su
superscripts, figure and plot captions excluded,
ut should be clearly legible. Clearly label each question.
If a question requires more than 40% of the page to answer, then that is the only answer on that
page. If multiple pages are required, this rule applies to the last of those pages.
2. Consider a Natural Market that specializes in selling spices from all over the world. Spices are
produced from an origin plant that are processed to produce the spices. It is possible to obtain
different types of products from one plant such as red pepper flakes and grounded red pepper. Spices
can be categorized as one of hot, sweet or plain. Hot and sweet spices can be used to make medicines at
home to heal or soothe some ailments. Each of these medications has a unique recipe that indicates the
amount of spices needed. The Natural Market keeps track of their customers purpose for purchasing
products and their illnesses in order to suggest to them better future shopping experiences. The
Market also keeps their employee records in the same database since an employee can also be a
customer. Customers don’t necessarily buy a product for a medication, they can also use them fo
cooking or other purposes. The database can be detailed as follow:
• Plants have a name, type and unique ID numbe
• There are predetermined plant processing methods {ground, flakes, minced, clove, other}, each
having a unique ID
• Spices can be {Hot, Sweet, Plain}. Hot spices are stored by the hot-level of the product, sweet
and plain ones by aroma type. Also, the price of the spices is kept in the database and can be
assumed constant.
• Medications have a type (example: tea, cream, ...) and unique ID numbe
• Illnesses affect body parts
• Customers have a name, address, phone, and e-mail
• Employee SSNs are stored along with personal information such as phone, e-mail, address, and
name
(a) (25 points) Create a representative ER Diagram using Crow’s Foot notation. Be sure to clearly
and appropriately indicate any keys, weak entities, relationship constraints, etc.
3. (20 points) Consider the following webpage snapshot (source code is on Blackboard!). The cu
ent
version of this webpage is using only HTML, but this creates many problems with the overall look-
and-feel across multiple pages, as well as the ease to modify the formatting. To fix these concerns
we should use Cascading Style Sheets (CSS). So, recreate the given webpage but using the most
appropriate mix of HTML and CSS that keep the style exactly the same style. You will submit you
updated .html and .css files separately, as well as embed the code into your submitted pdf/doc.
IE 332 Homework #2 Page 2 of 8
4. After taking the course IE 332 at Purdue, some students decided to create an online monthly sub-
scription service called 332X to teach some of the subjects they learned to new students. Since they
are very busy preparing the contents of the videos, they asked you to help organize their database.
So far, they have the following schema, where the underlined values indicate the primary key:
Subscribers(SubscriberID:integer, Email:varchar(100), Name:varchar(100),
Address:varchar(100), City:varchar(100), State:enum, age:int)
* Each subscriber is identified by their ID, has an email address, a name, an address (with city and
state) and an age.
Orders(SubscriberID:varchar(100), SubscriptionID:integer, PricePerMonth:real,
StartDate:date, CancellationDate:date)
* Each order has a subscriber (identified by their ID) and a subscription (identified by its ID),
a price per month, a start date and a cancellation date (which is set to NULL until the custome
cancels the subscription). Each customer is charged the same amount per month for the same type
of subscription.
Subscriptions(SubscriptionID:integer, Type:varchar(20))
* Each subscription has an ID and a type (cu
ently a user can choose between basic and premium,
ut a premium plus subscription type will be released soon).
Videos(VideoID:integer, Name:varchar(100), LastUpdated:date, Instructor1:varchar(100),
Instructor2:varchar(100))
* Each video lecture has an ID, a name, a date when it was last updated, and at least one instructor.
If there is just one instructor, the column Instructor2 will be set to NULL, but there may be more
than one for the same video.
WatchHistory(WatchID:integer, VideoID:integer, SubscriberID:integer, WatchDate:date,
WatchHour:time)
* Each time a subscriber watches a video of a lecture, an entry with an unique ID is created, indicating
the video’s ID, the subscriber’s ID, the date and the hour watched.
(a) (12 points) Provide all the CREATE TABLE statements needed to create the database.
(b) Provide SQL statements to answer the following questions from the database above:
i. (3 points) What is the number of videos that have “SQL” or “database” in their name?
ii. (5 points) List the names of all the users whose subscription was more than $10 per month.
iii. (6 points) List the ID of each video that had more than 1000 views in the last quarter of
2019, and the number of times it was watched in the same period. Order by the most popula
video to the least popular one. (Hint : Use BETWEEN to obtain dates between October 1st,
2019 and December 31st, 2019.)
iv. (6 points) List the number of subscribers that have an active subscription in the month of
January of 2020 for each type of subscription. Order from the type with most active users
to the one with least. (An active subscription in this month means that the user started
a subscription before January 1st, 2020 and did not cancel the subscription before or on
January 31st, 2020.)
IE 332 Homework #2 Page 3 of 8
v. (6 points) What is the name of the 10 most watched videos on the platform, and how many
times each of them was watched?
(c) (6 points) Does the schema violate any of the Normal Form (NF) rules? If yes, indicate
1. where a rule was violated,
2. which of the NF rules was violated and why, and
3. how it could be fixed.
IE 332 Homework #2 Page 4 of 8
5. Consider the schema given in Question 4. For each English statement below indicate whether the
associated query is co
ect. If it is inco
ect provide a fix to the query. If it is co
ect, simplify the
query to achieve the same result but with less complicated SQL code.
(a) (5 points) What are the subscribers (identified by their ID) that have a premium subscription
and live in West Lafayette, Indiana?
SELECT SubscriberID, SubscriptionID
FROM Subscribers
RIGHT JOIN Subscriptions
WHERE SubscriptionID = (SELECT SubscriptionID
FROM Subscriptions
WHERE type="Premium")
OR City = "West Lafayette" AND State = "IN"
GROUP BY SubscriberID
(b) (5 points) What is the name of all instructors and the name of the video lecture they teach? If
any instructor teaches more than one video lecture, then the instructor’s name should appea
matched to the name of each video she teaches.
SELECT DISTINCT(Instructor1), Name
FROM Videos
UNION
SELECT DISTINCT(Instructor2), Name
FROM Videos
(c) (5 points) How many times each video (identified by its ID) was watched after it was last
updated?
SELECT v.VideoID, COUNT(v.VideoID) AS times_watched
FROM WatchHistory as wh
RIGHT JOIN Videos as v
WHERE wh.WatchDate > v.LastUpdated
GROUP BY times_watched
(d) (5 points) What type of subscription is more popular among subscribers with 30 years or less?
SELECT SubscriptionID, MAX(Count)
FROM (SELECT o.SubscriptionID, COUNT(o.SubscriptionID) AS Count
FROM Orders AS o
LEFT JOIN Subscribers AS s
GROUP BY o.SubscriptionID
HAVING s.Age <= 30) AS temp
IE 332 Homework #2 Page 5