IE 332 – Homework #2
Due: March 14th, 11:59pm EST
Read Carefully. Important!
As outlined in the course syllabus this homework is worth 9% of your final grade. The maximum attainable mark on this homework is 217. 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.
Copyright By PowCoder代写 加微信 powcoder
By electronically uploading this assignment to Gradescope 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, resulting in a grade of zero.
You must use the provided LATEX template on Brightspace to submit your assignment. No exceptions.
Page i of i
IE 332 Homework #1 Due: March 14th 2022
1. Santa Claus is working to upgrade his list from a handwritten one to a more modern, digital solution. As you likely already know, he must keep track of each child, their naughty/nice status, their location, position on his route, the toys they asked for, the elves assigned to make those toys, the elves’ working schdule, their favorite payment methods, and keep track of their production as the year progresses, to make sure nobody will be left out this Christmas. Keep in mind, he also needs a way to check his list twice. The following bulleted list reiterates what we’ve already discussed as requirements.
• Children, their naughty/nice status (two of these for checking the list twice), location, route position (i.e. what order their presents are delivered in), requested toys.
• List of elves, production assignments (which toys for which children), preferred payment methods (candy, candy-canes, candy corn, syrup), their work schdedules (assume 3, 8 hour shifts).
• List of presents, whether or not the presents have been produced yet, whether or not they will be delivered (depending on whether the child is naughty or nice).
(a) (30 points) Using any flowchat software of your choice (we suggest lucid chart or LaTeX+TiKz, the latter of which doesn’t have object limits), construct an ERD using crow’s foot notation that encapuslates this problem, while being sure to follow the normal form guidelines of 3NF. Be sure to include any keys, weak entities, relationship constraints, cardinalities, etc. You can make realistic assumptions about any of the variables mentioned above.
(b) (15 points) Convert your ERD to the appropriate set of CREATE TABLE statements in SQL.
(c) (5 points) Write a SQL Query to display the percentage of completed presents.
(d) (20 points) Santa decided to partner with his German counterpart and have Krampus punish some naughty kids this year. We’ll need to update the ERD to include a punish boolean for the children, as well as some number of tables for Krampus to keep track of his punishments for each child, and other details such as duration, type, etc. Add this to your original ERD, and write the new create table stat- ments. Did retroactively changing the ERD cause any issues with your original model that you had to fix, or did things work out nicely?
2. Take a look at the following ERD which is based on the schema in question 3. It has a lot of both conceptual, labeling, and normal form violations that need to be addressed. These may include incorrect connections, cardinalities, unlabled relationship tables, unlabled relationships, and more.
(a) (30 points) Report any errors you find and how to address them; use a list and annotate the ERD image in red.
(b) (10 points) Address the issues you found, and write the appropriate CREATE TABLE statements that will allow you to correct the issues and reconstruct the DB from your updated ERD.
3. Consider the following schema. It describes a simplified multi-brand, car dealership’s sales and inventory system, with its primary keys indicated by underlines:
Vehicles(VehicleID:int,
VehicleName:varchar(50),
BrandID:int, BrandName:varchar(50),
CategoryID:int, CategoryName:varchar(50),
Price:real,
Discounts:real,
Mileage:real,
Quantity:int,
ManufactureYear:int)
• Each Vehicle has a unique ID, belongs to only one Brand and Category (both identified by ID and name), has name, price, discounts, mileage (which is 0 for new cars), quantity in stock, and ManufactureYear. Salesperson(SalespersonID:int, SalespersonName:varchar(100), Phone:varchar(50), Address:varchar(100), YearsofEmployment:int
• Each salesperson is identified by their ID and name, and has a certain amount of years of employment with the dealership, a phone and an address. Sales(SaleID:Integer, SaleDate:date, SalespersonID:varchar(100), VehicleID:int,
Mileage:real, SaleTotal:real, Tax:real, Status:varchar(50), FinancingMethod:varchar(50))
• Each Sale has a unique ID, a date, a corresponding Salesperson (identified by ID), a Vehicle (and its Mileage), a Sale total (excluding tax), an amount of tax charged on the Sale, a status (InPrepara- tion, Ready, Delivered), and a financing method. If there are multiple vehicles in the same sale, as can be the case for corporate sales, there will be a row for this SaleID for each of the Vehicles sold (where Sale total/tax remain the same in all of those rows). Services(ServicingTask:varchar(50), SaleID:varchar(50), VehicleID:int, Employee:varchar(50),
ServicingDate:date)
• When the vehicle(s) in a sale are being prepared to be delivered (being serviced for washing, cleaning, oil and coolant checks etc.), a servicing number is issued. Each servicing task performed has a description (“Washing”, “Cleaning”, “OilCheck”, etc.), is performed by a servicer (distinct from a salesperson) and has a servicing date. Note that a single vehicle can be subject to different services. If there are multiple services in the same sale, there will be a row for the Sale ID for each of the services performed.
(a) Provide SQL statements to answer the following questions regarding the schema above:
i. (1 point) How many brands of vehicles does the company sell?
ii. (1 point) Provide a list of the distinct years of employment for the salespeople employed at the firm.
iii. (5 points) List how many Sales each employee (not salesperson) provided services on in the year of
2020 where the sale total was greater than $30000.
iv. (5 points) List the ID of the Salespeople that made more than 50 sales in 2015, with the number
of sales that each of these Salespeople made. Order the list from highest number of sales to lowest number of sales.
Homework #2 Page 2 of 3
v. (5 points) List the name, ID and Mileage of the 10 Vehicles that sold the most in the Brand Ferrari in 2021 that have “F” on the name. Order in decreasing order of the Mileage when sold and break ties in alphabetical order by Vehicle name.
(b) (5 points) Does the schema violate any of the Normal Form (NF) rules? If yes, indicate the where a rule was violated, explaining which of the NF rules was violated and why, and how it could be fixed.
4. Does the query provide the answer for the English statement?
(a) (5 points) List the IDs of the salespeople who sold either the “Beetle” or the “Diablo”. Query:
SELECT c.SalespersonID
FROM Salesperson c
LEFT JOIN Sales o ON c.SalespersonID=o.SalespersonID
LEFT JOIN Vehicles p ON o.VehicleID=p.VehicleID
WHERE VehicleName=”Beetle” AND VehicleName=”Diablo”;
(b) (5 points) English statement: List the ID of the sale and the number of services performed in that sale for all the sales that involved more than four servicing tasks. Order the list from highest number of services to lowest number of services. Query:
SELECT o.SaleID, Count(o.SaleID)
FROM Sales o
INNER JOIN Services s ON o.SaleID=s.SaleID
GROUP BY o.SaleID
ORDER BY o.SaleID DESC;
5. Considering we’re looking at web related content at this point in the semester; we think it would be useful to help you see some issues with web security. This will also be important for your project, as we will try to break certain parts of your website, so be thorough here.
(a) (15 points) Look into how websites have inappropriately stored passwords in the past and report your findings. What issues did you come across, and why do you think this is incorrect?
(b) (15 points) Look into data breaches and losses; what methods did you find that accomplished this?
(c) (20 points) How do you take steps to sanitize input to prevent some of the problems you discovered in part b?
(d) (25 points) What methods and functions are used to prevent stored passwords from being compromised, even in the event that the data is breached? Can you give an example of how to implement such a strategy in R?
Homework #2 Page 3 of 3
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com