Database Concepts Assignment Solution - Database Systems Course

This document presents a comprehensive solution to a database concepts assignment, addressing various aspects of database systems. The solution begins by exploring functional dependencies, candidate keys, and normalization, including BCNF, within a relational database context. It then delves into SQL queries, providing solutions for retrieving and manipulating data from multiple tables using JOIN operations, aggregate functions (COUNT, GROUP BY), and subqueries. The assignment also covers database design principles, including the creation of entity-relationship diagrams and the identification of primary and foreign keys to model relationships between entities. Finally, the document includes a discussion on database schema design, with a focus on ensuring data integrity and efficiency. The solutions are well-structured and provide clear explanations of the database concepts and SQL queries, supported by relevant examples and a bibliography for further reading.

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.