cs homework

IE 332 - Homework #2 Due: Nov 4, 2015 before 12:30pm (i.e., in class)

Important!

As outlined in the course syllabus this homework is worth 7% of your final grade. The maximum attainable mark on this homework is 70. As was also outlined in the syllabus, there is a zero tolerance policy for any form of academic misconduct. This is an individual assignment.

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.

Page i of ii

Grade Distribution

Question Points Score

1 10

2 10

3 15

4 20

5 10

6 5

Total: 70

Comments:

Page ii of ii

IE 332 Homework #2 Due: Nov 4 2015

1. Marco Annunziata is the Chief Economist at General Electric, and he has some very insight- ful comments concerning the industrial Internet! Within the context of his talk, answer the questions below.

www.ted.com/talks/marco annunziata welcome to the age of the industrial internet#t-658544

(a) (1 point) What does the industrial Internet bring together?

(b) (1 point) Approximately when did the average sensor selling price and storage cost per gigabyte on the cloud converge?

(c) (1 point) What is beginning to be deployed throughout industrial systems?

(d) (1 point) With respect to machines, what does the new software-defined infrastructure allow, and how is it accomplished?

(e) (1 point) What does the software-defined machine infrastructure allow to automatically happen?

(f) (1 point) What are we already able to do with this new infrastructure?

(g) (1 point) What are two examples of the implications of zero unplanned downtime?

(h) (1 point) How fast is industrial data growing, by 2020 what percentage of all digital in- formation communication will this account for?

(i) (1 point) What does Marco say about these new technologies taking over jobs?

(j) (1 point) What average wage estimate over the next 15 years does he suggest?

2. (10 points) Encode and decode your first and last name (separated by a ‘ ’) using the RSA algorithm shown in class, for p = 2143 and q = 3257. Be sure to show each step of the encryption and decryption process, as well as the key generation phase (noting that key generation is only needed to be performed once for the entire message). Given the number of digits in the numbers being calculated it is suggested to use a powerful calculator such as www.wolframalpha.com. Use the following table to translate between characters and their integer representation.

A B C D E F G H I J K L M 00 01 02 03 04 05 06 07 08 09 10 11 12 13

N 0 P Q R S T U V W X Y Z 14 15 16 17 18 19 20 21 22 23 24 25 26

3. (15 points) Translate the following ER diagram into a relational schema.

IE 332 Homework #2 Page 2 of 4

4. The ability to query a database in order to find the subset of important information is a very important skill. In this question you will provide the SQL code to select the requested information from the given tables. It may be helpful, but is not necessary, if you create the database in MySQL or MS Access and populate it with data in order to test your answers.

A listing of general data types: www.w3schools.com/sql/sql datatypes general.asp

-- Contains all departments at the university

CREATE TABLE department (

dcode CHARACTER(3) PRIMARY KEY,

dname VARCHAR(30) NOT NULL);

-- Student information, where sid is the student number, sex is either ’M’ or

-- ’F’, yearofstudy is ’Freshman’, ’Sophomore’, ’Junior’, or ’Senior’.

CREATE TABLE student (

sid INTEGER PRIMARY KEY,

lname CHARACTER(20) NOT NULL,

fname CHARACTER(20) NOT NULL,

sex CHARACTER(1) NOT NULL,

age INTEGER NOT NULL,

dcode CHARACTER(3) NOT NULL,

yearofstudy CHARACTER(10) NOT NULL,

FOREIGN KEY (dcode) REFERENCES department(dcode));

-- Courses offered

CREATE TABLE course (

cid INTEGER,

dcode CHARACTER(3) REFERENCES department(dcode),

cname CHARACTER(20) NOT NULL,

PRIMARY KEY (cid, dcode));

-- Contains the sections that are offered for courses for each semester

-- of each year. Semester values are ’9’ Fall, ’1’ Winter, and ’5’ Summer.

CREATE TABLE courseSection (

csid INTEGER PRIMARY KEY,

cid INTEGER NOT NULL,

dcode CHARACTER(3) NOT NULL,

year INTEGER NOT NULL,

semester INTEGER NOT NULL,

section CHARACTER(5) NOT NULL,

FOREIGN KEY (cid, dcode) REFERENCES course(cid, dcode),

UNIQUE (cid, dcode, year, semester, section));

-- Courses a student has enrolled in, and their grade from 0 to 100.

CREATE TABLE studentsInCourse (

sid INTEGER REFERENCES student(sid),

csid INTEGER REFERENCES courseSection(csid),

IE 332 Homework #2 Page 3 of 4

grade NUMERIC(5,2) NOT NULL DEFAULT 0.00,

PRIMARY KEY (sid, csid));

(a) (5 points) Find the number of junior-year female students who are in the Industrial En- gineering department.

(b) (7 points) Find the year between 2008 and 2014 in which the Industrial Engineering de- partment had the highest course enrollment, compared to other years. Course enrollment for a year is calculated as the sum of the total number of students enrolled in each course for all courses in all semesters for that year.

(c) (8 points) Find all industrial engineering courses that are taught ONLY in the summer.

5. (10 points) Draw the associated Crow’s Foot Notation diagram for the following hospital ap- pointment and payment system:

• A patient can schedule one or more appointments. • Each appointment is scheduled between one doctor and one patient. • A doctor can be scheduled for many appointments, or none at all. • An appointment must generate one bill, a bill is generated by only one appointment. • One payment is applied to one bill, and one bill can be paid over time by several payments. • A bill can be outstanding, having nothing yet paid on it. • One patient can make many payments, but a single payment is made by only one patient. • For patients with insurance, they can only carry insurance with one company. • For patients with insurance, the insurance company will make payments, each single pay-

ment is made by exactly one insurance company.

• An insurance company can have many patients.

6. (5 points) Draw an ER diagram (in Chen notation) that could represent the relationships indicated in the following create table statements. Be sure to mark any key and participation constraints.

CREATE TABLE Bag (

X REAL PRIMARY KEY,

Y INTEGER NOT NULL UNIQUE,

Z REAL,

FOREIGN KEY (Y) REFERENCES Box(Y));

CREATE TABLE Box (

Y INTEGER PRIMARY KEY);

IE 332 Homework #2 Page 4 of 4