Description
Part 1
Create a set of relational schemas 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.
Part 2
• Using your logical schema from Part1, 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.
• Write SQL INSERT statements to populate your database with the following data (NOTE: remember that strings would need to use single quotes, e.g., ‘Asimov’)
o (King, Stephen, 2, September 9 1947)
o (Asimov, Isaac, 4, January 2 1920)
o (Verne, Jules, 7, February 8 1828)
o (Rowling, Joanne, 37, July 31 1965)
o (Bloomsbury Publishing, 17, London Borough of Camden)
o (Arthur A. Levine Books, 18, New York City)
o (1111-111, Databases from outer space, 17)
o (2222-222, Dark SQL, 17)
o (3333-333, The night of the living databases, 18)
o (2, 1111-111, 1)
o (4, 1111-111, 2)
o (4, 2222-222, 2)
o (7, 2222-222, 1)
o (37, 3333-333, 1)
o (2, 3333-333, 2)
• 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: 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.
You should submit your SQL and python code for this part (you may copy everything into your Word document submission, or submit it as separate files).
Part 3
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 4
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? If not, please state which FDs violate 3NF and decompose the schema accordingly.
Be sure that your name and “Assignment 1” appear at the top of your submitted file.
