Database Concepts: Candidate Keys, Functional Dependencies, and Redundancy in Databases

In this question-answer we will discuss about database concepts and below are the summaries point:- The text discusses database concepts and includes questions, a table of contents, and a bibliography. Question 1 focuses on candidate keys and functional dependencies in a database. The text explores minimal basis of given functional dependencies and discusses redundant dependencies.

Running head: DATABASE CONCEPTS
Database Concepts
Name of the Student
Name of the University
Author’s note:

1DATABASE CONCEPTS
Table of Contents
Question 1:.......................................................................................................................................2
Question 2:.......................................................................................................................................6
Question 3:.......................................................................................................................................8
Question 4:.......................................................................................................................................8
4.1 Functional Dependency:........................................................................................................8
4.2 Relational Schema:................................................................................................................9
4.3 Potential Problem:...............................................................................................................10
Bibliography:.................................................................................................................................11

2DATABASE CONCEPTS
Question 1:
1.1 Give {LoanID}+: {LoanID, MemberFirstName, MemberLastName, Address,
PostCode, LoanDate, ReturnDate, Title, Year, PublisherName, Year, LoanDate, ReturnDate}
The closure of {LoanID} is in the above section.
1.2 Candidate Key:
MemberID – no redundant attribute
MemberFirstName - redundant attribute
MemberLastName – redundant attribute
Address – redundant attribute
PostCode – redundant attribute
LoanID – non redundant attribute
LoanDate – redundant attribute
ReturnDate – redundant attribute
MemberID, BookID – non redundant attribute
BookID – non redundant attribute
Title – non redundant attribute
Year – redundant attribute
PublisherID – non redundant attribute

3DATABASE CONCEPTS
PublisherName – redundant attribute
AuthorID – non redundant attribute
AuthorFirstName – redundant attribute
AuthorLastName – redundant attribute
PublisherID, Title – non redundant attribute
Year – redundant attribute
Therefore the candidate keys are as following.
Member relation - {MemberID}
Loan relation - {LoanID}, {MemberID}, {BookID}
Book relation - {BookID}, {Title}, {PublisherID}
Publisher relation - {PublisherID}
Writer relation - {AuthorID}, {BookID}
Author relation - {AuthorID}
1.3 Minimal Basis of Given FD:
First Step:
MemberID  MemberFirstName, MemberID  MemberLastName, MemberID 
Address, MemberID  PostCode
Address  PostCode

4DATABASE CONCEPTS
LoanID  MemberID, LoanID  BookID, LoanID  LoanDate, LoanID 
ReturnDate
MemberID, BookID  LoanID, MemberID, BookID  LoanDate, MemberID, BookID
 ReturnDate
BookID  Title, BookID  PublisherID, BookID  Year
PublisherID  PublisherName
AuthorID  AuthorFirstName, AuthorID  AuthorLastName
PublisherID, Title  Year
Second Step:
{MemberID, BookID}+: MemberID, BookID, LoanID, MemberFirstName,
MemberLastName, Address, PostCode, LoanDate, ReturnDate, Title, Year, PublisherName,
Year, LoanDate, ReturnDate}
{Adress}+: { Adress, PostCode}
MemberID  Address, MemberID  PostCode
Address  PostCode
This above dependency is redundant therefore it has to be removed.
BookID  Title, BookID  PublisherID, BookID  Year
PublisherID  PublisherName
PublisherID, Title  Year

5DATABASE CONCEPTS
{BookID}+: {BookID, Title, PublisherID, PublisherName, Year}
{PublisherID, Title}+: {PublisherID, PublisherName, Year}
{Title}+: {Title}
Therefore removing available extraneous attribute are as following.
BookID  Title, BookID  PublisherID, BookID  Year, BookID  PublisherName
MemberID  MemberFirstName, MemberID  MemberLastName, MemberID 
Address, MemberID  PostCode
Third Step:
As LoanID determines the MemberID and BookID the following FD
MemberID, BookID  LoanID, LoanDate, ReturnDate
Is shown as
LoanID  LoanID, LoanDate, ReturnDate, LoanDate, ReturnDate
Therefore the minimal cover is as following.
MemberID  MemberFirstName, MemberID  MemberLastName, MemberID 
Address, MemberID  PostCode
LoanID  LoanDate, LoanID  ReturnDate, LoanID  LoanDate, LoanID 
ReturnDate
BookID  Title, BookID  PublisherID, BookID  Year, BookID  PublisherName
AuthorID  AuthorFirstName, AuthorID  AuthorLastName

6DATABASE CONCEPTS
1.4 BCN: The above relation is in the BCNF.
1.5 BCNF/3NF Decomposition: The above relation is in the BCNF. The transitive
dependency is already removed from the loan relation.
Question 2:
1. List the name of each star (that has starred in a movie) and the total number of
movies they have starred in descending order: Select star.starName, Count(MovStar.starNumb)
From Star inner join MovStar On Star.starNumb = MovStar.starNumb Where
MovStar.starNumb IS NOT NULL Group By star.starName Order By
Count(MovStar.starNumb) Desc;
2a. Rewrite your solution to question 1 to include stars that have never starred in
movies as well. You must use a left/right join: Select star.starName, Count(MovStar.starNumb)
From Star left outer join MovStar On Star.starNumb = MovStar.starNumb Group By
star.starName Order By Count(MovStar.starNumb) Desc;
2b. Rewrite your solution to question 1 to include stars that have never starred in
movies as well. This time using a subquery instead (without using a left/right join): Select
star.starName, Count(MovStar.starNumb) From Star Where NOT EXISTS (Select
MovStar.starNumb from MovStar Where Star.starNumb = MovStar.starNumb) Order By
Count(MovStar.starNumb) Desc;
3. List movie number, movie title and the “total stars” of any movies that have two or
more stars in them. Order your result by increasing movie number: Select Movie.mvNumb,
mvTitle, Count(MovStar.mvNumb) From Movie inner join MovStar ON Movie.mvNumb =
MovStar.mvNumb Group By Movie.mvNumb Order By Count(MovStar.mvNumb) ASC;

7DATABASE CONCEPTS
4. List pairs of actors that have ever appeared together in the same movies. Each row
should represent a distinct set of actor pairs (ie no repeated pairs): select Distinct ms.mvNumb
from MovStar ms inner join Star s On ms.starNumb = s.starNumb group by ms.mvNumb having
count(ms.mvNumb) > 1;
5. List the name and “total movies” directed by the most prolific director. You must
NOT use the MAX aggregate function: Select Distinct m1.mvTitle, m1.Count(dirNumb) From
Movie m1 left join Movie m2 on m1.Count(dirNumb)> m2.Count(dirNumb) AND
m2.Count(dirNumb) IS NOT NULL Having m2.Count(dirNumb) IS NULL;
6. List member number and name of the member who has only ever borrowed movies
made after 1970. You must use the IN/NOT IN function: Select mmbNumb, mmbName From
Member inner join Borrow on Member.mmbNumb = Borrow.mmbNumb Where mvNumb IN
(Select m.mvNumb From Movie m Where m.yrMde>1970);

8DATABASE CONCEPTS
Question 3:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
Question 4:
4.1 Functional Dependency:
Username  password, address

9DATABASE CONCEPTS
bidNo  date, time, amount, status
username, itemID  rating, comment
itemID  description
username  country, state
username, itemID  startingPrice, closingDate
businessNo  address
title  title
4.2 Relational Schema:
Figure 2: Relational Schema Diagram
(Source: Created by Author)

10DATABASE CONCEPTS
4.3 Potential Problem:
The relationship is a many to many relationship. The diagram does not show this. This
issue can be resolved by showing the connector that shows many-to-many relation.

11DATABASE CONCEPTS
Bibliography:
Arasu, Arvind, Brian Babcock, Shivnath Babu, John Cieslewicz, Mayur Datar, Keith Ito, Rajeev
Motwani, Utkarsh Srivastava, and Jennifer Widom. "Stream: The stanford data stream
management system." In Data Stream Management, pp. 317-336. Springer, Berlin, Heidelberg,
2016.
Elmasri, Ramez, and Sham Navathe. Fundamentals of database systems. London: Pearson, 2016.
Yunus, Mohd Amin Mohd, Sonniaa KV Gopala Krishnan, Nazri Mohd Nawi, and Ely Salwana
Mat Surin. "Study on Database Management System Security Issues." JOIV: International
Journal on Informatics Visualization 1, no. 4-2 (2017): 192-194.
Zhang, Bohan, Dana Van Aken, Justin Wang, Tao Dai, Shuli Jiang, Jacky Lao, Siyuan Sheng,
Andrew Pavlo, and Geoffrey J. Gordon. "A demonstration of the ottertune automatic database
management system tuning service." Proceedings of the VLDB Endowment 11, no. 12 (2018):
1910-1913.