Database Concepts: Answers to Questions 1-4

This document provides answers to questions related to database concepts including functional dependencies, BCNF, SQL queries, and database design. It also includes a bibliography.

Running head: DATABASE CONCEPTS
Database concepts
Name of the Student:
Name of the University:
Author Note

1
DATABASE CONCEPTS
Answer to question number 1
1.1)
{LoanID}+ = {memberID, BookID}
1.2)
MemberID -> MemberFirstName, MemberLastName, Address, PostCode
Address  PostCode
LoanID  MemberID, BookID, LoanDate, ReturnDate
MemberID, BookID  LoanID, LoanDate, ReturnDate
BookID  Title, Year, PublisherID
PublisherID  PublisherName
AuthorID  AuthorFirstName, AuthorLastName
PublisherID, Title  Year
Therefore MemberID -> Address, PostCode
Therefore, MemberID is the candidate key.
LoanID  MemberID, BookID,
Therefore, LoanID is the candidate key.
BookID  PublisherID
Therefore, BookID is the candidate key.
AuthorID  AuthorFirstName, AuthorLastName
Therefore AuthorID is the candidate key.

2
DATABASE CONCEPTS
PublisherID, Title  Year
Therefore publisherID is the candidate key.
1.3)
BookID -> PublisherID
Loan ID -> BookID, memberID
The minimal basis for the functional dependencies are
{LoanID, AuthorID}
1.4)
The Relation is in BCNF as the database provides the extended transitive
dependency as per the description shown below:
Loan ID -> BookID, memberID
BookID -> PublisherID
MemberID -> MemberFirstName, MemberLastName, Address, PostCode
Address -> PostCode
LoanID -> MemberID, BookID, LoanDate, ReturnDate
MemberID, BookID -> LoanID, LoanDate, ReturnDate
BookID -> Title, Year, PublisherID
PublisherID -> PublisherName
AuthorID -> AuthorFirstName, AuthorLastName
PublisherID, Title -> Year

3
DATABASE CONCEPTS
1.5)
Writer is not minimized hence, the minimization:
Writer (WriterID, AuthorID, BookID)
Answer to question number 2
2.1)
Select Star.starName, COUNT(movStar.mvNumb) from
Star Inner Join movStar On Star.starNumb = MovStar.starNumb
Group By Star.starName
Order By Star.starName;
2.2)
a)
Select star.starName, COUNT(movStar.mvNumb) from
Star Left Outer Join movStar On Star.starNumb = MovStar.starNumb
Group By Star.starName
Order By Star.starName;
b)
Select StarName From Star
Where starNumb NOT IN (Select StarNumb from movStar) OR starNumb IN (Select
StarNumb from movStar);
2.3)

4
DATABASE CONCEPTS
Select Movie.mvNumb, Movie.mvTitle, COUNT(movStar.mvNumb) from
Movie Inner Join movStar On Movie.mvNumb = MovStar.mvNumb
HAVING COUNT(movStar.mvNumb)> 2 OR COUNT(movStar.mvNumb) = 2
Group By Movie.mvNumb, Movie.mvTitle
Order By Movie.mvNumb;
2.4)
Select Star.starName, movStar.mvNumb from
Star
Inner Join movStar On Star.starNumb = MovStar.starNumb
Inner Join (Select Star.starName, movStar.mvNumb from
Star Inner Join movStar On Star.starNumb = MovStar.starNumb) As a ON movStar.mvNumb
= a.mvNumb;
2.5)
Select * From (Select Director.dirName, COUNT(Movie. mvNumb) From Director
Inner Join Movie On Director.dirNumb = Movie.dirNumb
Group by Director.dirName
Order By COUNT(Movie. mvNumb) DESC)
Where ROWNUM = 1;
2.6)
Select Member.mmbNumb, Member.mmbName

5
DATABASE CONCEPTS
From Member
Inner Join Borrow On Borrow.mmbNumb = Member.mmbNumb
Where Borrow. mvNumb IN (Select mvNumb from Movie where yrMde > 1970);
Answer to question 3
Answer to question number 4
4.1)
Username -> password, address
Bidno -> date, time, amount, status
itemID -> description
itemID, username -> startingprice, closingDate
usersname -> state, country, businessNo
businessno -> address
title -> subcategory

6
DATABASE CONCEPTS
username,itemID -> rating, Comment
4.2)
Users (UserName (pk), Password, Address)
Buyer (BuyerID (pk), username (fk))
Seller (SellerID(pk), businessno (fk), username (fk), Sate, Country)
Bids (BidID (pk), date, time, amount, status, ItemID (fk), BuyerID (fk))
Items (ItemID (pk), description, categoryID (fk))
Category (CategoryID(pk))
SubCategory (SubcategoryID (pk), CategoryID (fk))
StorFront (businessno (pk), address)
Feedbacks (FeedbackID (pk), BuyerID (fk), ItemID (fk), rating, comment)
4.3) There is a problem with the Feedback table as there are no candidate key in the relation.
Hence a primary key FeedbackID and two foreign keys BuyerID and ItemID has been
developed in the relation to resolve the issue.

7
DATABASE CONCEPTS
Bibliography
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.