Database Processing for Large-Scale Analytics Assignment 1 Part 1 a) Create a relational schema with underlined (primary) keys and arrows connecting foreign keys and primary keys for a database containing the following information. If you have any difficulty drawing arrows, you can write foreign key information in a sentence instead. Authors have Last Name, Firstname, ID, and Birthdate (identified by ID) Publishers have Name, ID, address (identified by ID) Books have ISBN, Title, Publisher (each book has a unique publisher and can be identified by ISBN). Authors Write Books; since many authors can co-author a book, we need to know the rank of an author contributing to a book, stored in this table (i.e. a number 1, 2, 3; for single author books, this number is 1). NOTE: Part 2 has some sample data which may be helpful. b) Create a relational schema for students and student advisors Students have First Name, Last Name, DOB, Telephone and a reference to their advisor Advisors have ID, Name, Address, Research Area Part 2 1) Using your logical schema from Part1-a, write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key. You can make reasonable assumptions regarding the attribute domains (note that uniformly setting every column to VARCHAR2(100) is not reasonable). 2) Using logical schema from Part1-b write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key. For Students table, clearly state the assumptions you have made when choosing a primary key. You can make reasonable assumptions regarding the attribute domains. 3) Write SQL INSERT statements to populate your database from Part1-a with the following data (NOTE: remember that strings would need to use single quotes, e.g., 'Asimov') a) (King, Stephen, 2, September 9 1947) b) (Asimov, Isaac, 4, January 2 1920) c) (Verne, Jules, 7, February 8 1828) d) (Rowling, Joanne, 37, July 31 1965) e) (Bloomsbury Publishing, 17, London Borough of Camden) f) (Arthur A. Levine Books, 18, New York City) g) (1111-111, Databases from outer space, 17) h) (2222-222, Dark SQL, 17) i) (3333-333, The night of the living databases, 18) j) k) l) m) n) o) (2, 1111-111, 1) (4, 1111-111, 2) (4, 2222-222, 2) (7, 2222-222, 1) (37, 3333-333, 1) (2, 3333-333, 2) Part 3 Write a python function that is going to generate and return a SQL INSERT statement given a table name and value list as parameters. For example, generateInsert('Students', ['1', 'Jane', 'A-']) should return \"INSERT INTO Students VALUES (1, Jane, A-);\". It would be even better, but not required if your function returned the more proper \"INSERT INTO Students VALUES (1, 'Jane', 'A-');\" (i.e., put quotes around strings, but not numbers). Another example of how the function should work: generateInsert('Phones', ['42', '312-555-1212']) would produce \"INSERT INTO Phones VALUES (42, 312-555-1212);\" For simplicity, you can assume that every entry in the list of values is given as a string, even if it is a number in practice. You should submit your python code and SQL output for this part (you may copy everything into your Word document submission, or submit it as separate files). Part 4 Consider a MEETING table that records information about meetings between clients and executives in the company. Each record contains the names of the client and the executive's name as well as the office number, floor and the building. Finally, each record contains the city that the building is in and the date of the meeting. The table is in First Normal Form and the primary key is (Client, Office). (Date, Client, Office, Floor, Building, City, Executive) You are given the following functional dependencies: Building City Office Floor, Building, City Client Executive Client, Office Date a. Remove any existing partial dependencies and convert the logical schema to the Second Normal Form. Please remember that when performing schema decomposition you need to denote primary key for every new table as well as the foreign key that will allow us to reconstruct the original data. b. Remove any existing transitive dependencies to create a set of logical schemas in Third Normal Form. Again, remember to denote primary keys and foreign keys (including which primary key those foreign keys point to). Part 5 Consider a table that stores information about students, student name, GPA, honors list and the credits that the student had completed so far. (First, Last, GPA, Honor, Credits) You are given the following functional dependencies First, Last GPA, Honor, Credits GPA Honor a. Is this schema in Second Normal Form? If not, please state which FDs violate 2NF and decompose the schema accordingly. b. Is this schema in Third Normal Form? decompose the schema accordingly. If not, please state which FDs violate 3NF and