Description
Database Processing for Large-Scale Analytics
Part 1
A) Write a python function that will accept a name of a text file containing multiple rows with comma separated numbers (for example “5, 6, 12, 55, 1”) and return the average of these numbers. Your function should compute and average all of the numbers in a file.
Note that your function should return the result of the computation (i.e., return res not print(res)).
B) Write a python function that is going to generate and return a SQL INSERT statement given a table name and value list as parameters. We have not covered SQL yet, so you don’t need to run these insert statements, just get your python code to return the required string. Do not hardcode the output.
For example,
print(generateInsert(‘Students’, [‘1’, ‘Jane’, ‘A-‘])) should print
INSERT INTO Students VALUES (1, Jane, A-);
All statements start from INSERT INTO, followed by the name of the table, followed by VALUES and the comma-separated list of supplied values inside parenthesis, terminated by a semi-colon
Make sure that your function returns the string rather than prints it.
If you are interested in additional challenge, modify your function to instead return (this modification is not required):
INSERT INTO Students VALUES (1, ‘Jane’, ‘A+’);
(i.e., put quotes around strings, but not around numbers).
Another example:
generateInsert(‘Phones’, [’42’, ‘312-556-1212’]) should return
INSERT INTO Phones VALUES (42, ‘312-556-1212’);
Do not hard-code the number of inserted table values so that both of the example calls work correctly.
Part 2
a) Define a relational schema with underlined (primary) keys and arrows connecting foreign keys and primary keys for a database containing the following information.
• Authors have LastName, FirstName, ID, and Birthdate (identified by ID)
• Publishers have Name, PubNumber, Address (identified by PubNumber)
• Books have ISBN, Title, Publisher (each book has a publisher and is identified by its ISBN).
• Authors Write Books; since many authors can co-author a book, we need to know the relative contribution of the author to a book, signified by their position in the author list (i.e. 1, 2, 3, etc.).
Create sample data for at least 2 books, at least 1 publisher and at least 2 authors (who co-authored the same book).
b) Define a relational schema for students, student advisors, and advisor departments
• Students have StudentID, First Name, Last Name, DOB, Telephone and a reference to their advisor
• Advisors have ID, Name, Address, Research Area, and a reference link to their Department
• Departments have Name, Chair, Endowment (identified by Name)
