Part 1 a)Crea te a relational schema with underlined (primary) keys and arrows connecting foreign keys and primary keys for a database containing the...

Part 1

Authors

Publishers

Books

Write

Students

Advisors 

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)     (2, 1111-111, 1)

k)   (4, 1111-111, 2)

l)     (4, 2222-222, 2)

m)  (7, 2222-222, 1)

n)   (37, 3333-333, 1)

o)   (2, 3333-333, 2)

Part 3

You want to cr eate a relation representing US presidents. Suppose that the following is true of the data you want to represent:

•   No two presidents have the same name and year of birth

•   No two presidents have the same inauguration date

•   All presidents have a name, a year of birth, and have been inaugurated into office

•   Not all presidents are affiliated with a political party

Wr ite a valid create table statement for the relation.

Part 4

Let R(ABCDEFGH) satisfy the following functional dependencies:

A → B, CH → A, B → E, BD → C, EG → H, DE → F.

Use transitive rule to find additional F.D.s that are satisfied by R? 

Part 5

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

1. 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.

1. Remove any existing transitive dependencies to cr eate 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 6

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

1. Is this schema in Second Normal Form? If not, please state which FDs violate 2NF and decompose the schema accordingly.

1. Is this schema in Third Normal Form? If not, please state which FDs violate 3NF and decompose the schema accordingly.