SQL Query from database schema with functional dependences. Normalization

Question

I'm supposed to write a SQL query from given schema:

• BookAuthors (ISBN, authorName, gender, title, yearPublished, pubId, pubName, phone)
• FD = { ISBN -> title, pubId, yearPublished; authorName -> gender; pubId -> pubName, phone }

Here's what I wrote:

CREATE TABLE Authors
(
    authorName VARCHAR(64) PRIMARY KEY,
    gender CHAR(1)
);

CREATE TABLE Publishers
(
    pubId VARCHAR(32) PRIMARY KEY,
    pubName VARCHAR(64),
    phone NUMERIC(10)
);
GO

CREATE TABLE Books
(
    ISBN VARCHAR(32) PRIMARY KEY NOT NULL,
    title VARCHAR(32),
    pubId VARCHAR(32) FOREIGN KEY REFERENCES Publishers(pubId) NOT NULL,
    yearPublished NUMERIC(4)
);

Is this the correct answer? I'm worried about not having any connections between author and the book.

Answer 1

The problems come from the fact that in your functional dependencies is missing a dependence from ISBN to AuthorName: this is because each book uniquely determines its author. So the dependences should be:

ISBN -> title, pubId, yearPublished,  authorName
authorName -> gender
pubId -> pubName, phone

So, the third normal form is the following:

Books(ISBN authorName pubId title yearPublished) ,
{ ISBN → authorName pubId title yearPublished } >

Authors (authorName gender) ,
{ authorName → gender } >

Publishers (pubId pubName phone) ,
{ pubId → pubName phone } >

And this should be the set of relations that are to be defined. Of course, you should add the right constraints, like primary keys and foreign keys (and the solution of IrateB is correct as well).

I think this is an exercise, otherwise, you should add an AuthorId to your original data, and modify the functional dependencies.