Question

3.

a. Design a simple normalized Library database for tracking “customers reserve books” with not more than 3 tables and answer the following questions about your database.

b. Discuss how your database is in third normal form using functional dependencies (FDs).

c. Create an instance of your database.

d. Provide one external level view query in English on your database.

Answer 1

3( a)

Database for tracking “customers reserve books” in library

Customer(Customer_ID,Customer_Type,Name,address,phone)
Book(Book_Id,Author,title,price,available)
Reserve(Book_Id,Customer_Id,Reserve_Date,expiry__Date)

3) b)

Normalization:

In DBMS, normalization is the process of arranging and adjusting data so that data does not contain insert,update and delete anomalies.

First normal form:

• For the table to be in 1NF, table should contain single atomic attributes. Which means each field does not contain multiple values.
• To decide the table to be in 1NF, table should contain data. Make sure while filling data enter automic values.

Second normal form:

• For the table to be in 2NF, that table must be in 1NF and it it has no partial dependency. Which means no non prime attribues are dependent on subset of candidate key or composite key.
• Composite key or Candidate key: Composite key is nothing but the combination of two or more keys in any table which can be used to identify the records uniquely.

Let us check weather tables are in 2NF:

Customer(Customer_ID,Customer_Type,Name,address,phone)

Candidate key: {Customer_ID}
Non_prime attributes:{Customer_Type,Name,address,phone}

In Customer table, non_prime attributes {Customer_Type,Name,address,phone} dependent on {Customer_ID} which is a complete candidate key.So that this table satisfies 2NF.

Book(Book_Id,Author,title,price,available)

Candidate key: {Book_Id}
Non_prime attributes:{Author,title,price,available}

In Book table, non_prime attributes {Author,title,price,available} dependent on {Book_Id} which is a complete candidate key.So that this table satisfies 2NF.

Reserve(Book_Id,Customer_Id,Reserve_Date,expiry__Date)

Candidate key: {Book_Id,Customer_Id,Reserve_Date}
Non_prime attributes:{expiry__Date}

In Reserver table, non_prime attribute {expiry__Date} dependent on {Book_Id,Customer_Id,Reserve_Date} which is a complete candidate key.So that this table satisfies 2NF. Here {Book_Id,Customer_Id } acts as both primary keys and foreign keys.

So above tables are in 2NF.

Third Normal form:

• For the table to be in 3NF, that table must be in 2NF and there it doesn't have any transitive dependency.
• Transitive dependency : If A-->B and B-->C then A-->C. So in 3NF tables doesn't contain such relations.
• After applying 2NF, resulted tables does not contain transitive dependency.So that these tables are satisfying 3 NF.

3 (c)Create an instance of your database:

Customer(Customer_ID,Customer_Type,Name,address,phone)
Book(Book_Id,Author,title,price,available)
Reserve(Book_Id,Customer_Id,Reserve_Date,expiry_Date)

Creating instace:

CREATE database customers_reserve_books;

Creating tables:

CREATE TABLE Customer(
Customer_ID int NOT NULL,
Customer_Type VARCHAR(40),
Name VARCHAR(80),
Address VARCHAR(255),
Phone VARCHAR(20),
PRIMARY KEY (Customer_ID)
);

CREATE TABLE Book(
Book_ID int NOT NULL,
Title VARCHAR(255),
Author VARCHAR(125),
Price REAL(6,2),
available CHAR(Y/N),
PRIMARY KEY (Book_ID)
);

CREATE TABLE Reserve(
Customer_ID int NOT NULL,
Book_ID int NOT NULL,
Reserver_Date DATE,
expirt_Date DATE,
PRIMARY KEY (Customer_ID),
PRIMARY KEY (Book_ID),
PRIMARY KEY (Reserver_Date),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID),
FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID )
);

d. Provide one external level view query in English on your database.

CREATE VIEW CUSTOMER_CONTACT AS SELECT Customer_Name, phone FROM  CUSTOMER;

Above statement is an example of views in database, I have created a view CUSTOMER_CONTACT which stores only Customer_Name & phone from Customer Table.