Assignment 2 comp2350 - assignmnet 1 PDF

Preview

Summary

assignmnet 1...

Description

COMP2350/6350 2020-Assignment 2

DEPARTMENT OF COMPUTING

COMP2350/6350 2020 S2 – ASSIGNMENT TWO (10%) Draft Due: 11:55pm Tuesday 29 September 2020

Due: 11:55pm Friday 02 October 2020 (Week 8) Database Design & Manipulation

Please Print Clearly In CAPITALS BUSHRA

Surname First Name

LAMIA

Student ID

45711704

Signature

LAMIA

Student Code of Conduct Macquarie University students have a responsibility to be familiar with the Student Code of Conduct: https://students.mq.edu.au/study/getting-started/student-conduct

Student Support Macquarie University provides a range of support services for students. For details, visit http://students.mq.edu.au/support/

Task 1: Functional Dependencies 1

COMP2350/6350 2020-Assignment 2



Explain in a short paragraph what non-trivial Functional Dependencies (FDs) are.

Non-trivial functional dependency helps to understand the relationship between two characteristics. Moreover, it delivers required information that is not previously given. The relation will be treated as non-trivial dependence when A->B is true and B is not a subset of A.



Identify the non-trivial FDs on the relation Abnormal_Rel. Supplement your description with diagram(s). BookID->BookTitle BookID->Author BookID->Publisher BookID->PubYear BookID->Bookstatus BookID->BookPrice LibBranchID->BranchSuburb MemberID->MemberName MemberID->MemberAddress MemberID->MemberExpDate BookIssueID->DateBorrowed BookIssueID->DateReturned BookIssueID->ReturnDueDate



Identify the Candidate key(s) of Abnormal_Rel.

2

COMP2350/6350 2020-Assignment 2

The Candidate Key is a collection of characteristics that classify rows in a table individually. Candidate keys are chosen from the set of super keys. Mainly, the candidate key is the super key without any repeated features. That is why they are also known as the minimum super key. In the schema, the Candidate Key is BookID , LIbBranchID , MemberID and BookIssueID

Task 2: Anomalies Note. How you structure your answer is flexible. One possible structure is given below.



Explain in a short paragraph Anomalies of Form_1

o o

determine if the relation Abnormal_Relis susceptible to it Support your determination with a small example (instance of the relation Abnormal_Rel).

Insertion anomaly: Insertion anomaly happens when a user wants to join certain attributes in the relationship, but it cannot be added because of the absences of the necessary attributes for the certain relationship. For example, A and B is two attributes and the user wants to insert B. to insert B, the user also needs to insert A. Otherwise B will not be inserted. Similarly, in the schema if anyone intends to insert address of a member whose name is not in the database yet he or she needs MemberId without that it cannot insert a new member into database.



Explain in a short paragraph Anomalies of Form_2

o o

determine if the relation Abnormal_Relis susceptible to it Support your determination with a small example (refer to the same or a different instance of the relation Abnormal_Rel).

Deletion anomaly: Deletion anomaly occurs when a user wants to remove certain attributes in the relationship, but it deletes several important details about other relationships immediately .According to the schema, if we remove one book and the book’s author has just one book, then author will be deleted from the database.

3

COMP2350/6350 2020-Assignment 2



Explain in a short paragraph Anomalies of Form_3

o o

determine if the relation Abnormal_Relis susceptible to it Support your determination with a small example (refer to the same or a different instance of the relation Abnormal_Rel).

Update anomaly: Update anomaly occurs when a user wants to upgrade a database. But for this anomaly, the user needs to modify the attributes at various locations in spite of updating it at just one place otherwise it may affect mistake in the database. According to the scenario the database needs to update book return due date then it needs to change it at multiple places.

Task 3: Normalization 

What is the highest NF that the relation Abnormal_Rel satisfies? Explain why.

It has multivalued columns MemberName, MemberAddress breaching 1NF thus the following one is within UNF . Non-key attributes are those attributes which are not fully functional dependent on the entire primary composite keys (BookID ,LIbBranchID , MemberID and BookIssueID) therefore there are partial dependencies in violation of 2NF. Transitive dependencies are in violation of 3NF that means, certain non-key characteristics such as pubyear are transitively based on candidate keys such as Publisher that are not part of the primary keys. Abnormal_Rel(BookID,BookTitle,Author,Publisher,PubYear,LibBranchID,BranchSuburb, BookStatus,BookPrice,MemberID,MemberName,MemberAddress,MemberExpDate, BookIssueID,DateBorrowed,DateReturned,ReturnDueDate )



Normalize/decompose Abnormal_Rel until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work.

Book

4

COMP2350/6350 2020-Assignment 2

Boo kID

Book Title

Aut hor

Publi sher

Pub Year

BookSt atus

BookP rice

Borroweddata_Bo Member_me mberID okIssueID

Member MemberID

MemberName

MemberAddress

MemberExpDate

Branch LibBranchID

BranchSuburb

Borrowed Data BookIssueID

DateBorrowed

DateReturned

ReturnDueDate

Book_has_Branch Book_BookID



Branch_BranchID

Check if the resultant relations are in BCNF. If not, decompose them as necessary until you get all of them in BCNF. The resultant relations are already in BCNF. BCNF is a normal from which is used to normalization the database and it is a updated version of 3NF.If BCNF is in relational schema then every redundancy which is based on functional dependency is eliminated,but there might also be other forms of redundancy.3NF table which does not have various overlapping candidate keys those are within BCNF.

Task 4: Table Creation and Population 

Copy and paste your DDL code for creating each table/relation in BCNF obtained in Task 3.

CREATE TABLE IF NOT EXISTS `comp2350`.`BorrowedData` ( `BookIssueID` INT NOT NULL, `DateBorrowed` DATE NULL, `DateReturned` DATE NULL, `ReturnDueDate` DATE NULL, PRIMARY KEY (`BookIssueID`))

5

COMP2350/6350 2020-Assignment 2

ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `comp2350`.`Member` ( `MemberID` INT NOT NULL, `MemberName` VARCHAR(45) NULL, `MemberAddress` VARCHAR(45) NULL, `MemberExpDate` DATE NULL, PRIMARY KEY (`MemberID`)) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `comp2350`.`Book` ( `BookID` INT NOT NULL, `BookTitle` VARCHAR(45) NULL, `Author` VARCHAR(45) NULL, `Publisher` VARCHAR(45) NULL, `PubYear` YEAR(4) NULL, `BookStatus` VARCHAR(45) NULL, `BookPrice` DECIMAL(2) NULL, `BorrowedData_BookIssueID` INT NOT NULL, `Member_MemberID` INT NOT NULL, PRIMARY KEY (`BookID`, `BorrowedData_BookIssueID`, `Member_MemberID`), INDEX `fk_Book_BorrowingInformation1_idx` (`BorrowedData_BookIssueID` ASC) VISIBLE, INDEX `fk_Book_Member1_idx` (`Member_MemberID` ASC) VISIBLE, CONSTRAINT `fk_Book_BorrowingInformation1`

6

COMP2350/6350 2020-Assignment 2

FOREIGN KEY (`BorrowedData_BookIssueID`) REFERENCES `comp2350`.`BorrowedData` (`BookIssueID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Book_Member1` FOREIGN KEY (`Member_MemberID`) REFERENCES `comp2350`.`Member` (`MemberID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `comp2350`.`Branch` ( `LibBranchID` INT NOT NULL, `BranchSuburb` VARCHAR(45) NULL, PRIMARY KEY (`LibBranchID`)) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS comp2350.`Book_has_Branch` ( Book_BookID INT NOT NULL, Branch_BranchID INT NOT NULL, PRIMARY KEY (Book_BookID, Branch_BranchID), INDEX fk_Book_has_Branch_Branch1_idx (Branch_BranchID ASC) VISIBLE, INDEX fk_Book_has_Branch_Book_idx (Book_BookID ASC) VISIBLE, CONSTRAINT fk_Book_has_Branch_Book FOREIGN KEY (Book_BookID)

7

COMP2350/6350 2020-Assignment 2

REFERENCES comp2350.`Book` (BookID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_Book_has_Branch_Branch1 FOREIGN KEY (Branch_BranchID) REFERENCES comp2350.`Branch` (LibBranchID) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;



Copy and paste your SQL code for inserting at least five rows of data into each of these table.

INSERT INTO MEMBER VALUES (1, 'Bob Marley', 'Epping', '2023-02-04'); INSERT INTO MEMBER VALUES (2, 'Kawsar Hamid', 'Ryde', '2022-04-03'); INSERT INTO MEMBER VALUES (3, 'Leena Ben', 'Bankstown', '2022-03-01'); INSERT INTO MEMBER VALUES (4, 'Krishna Chondon', 'Hurtsville', '2024-05-01'); INSERT INTO MEMBER VALUES (5, 'Kylie Lan', 'Cabramatta', '2021-02-01');

INSERT INTO BORROWEDDATA VALUES (1, '2020-01-02', '2020-02-04', '2020-03-03'); INSERT INTO BORROWEDDATA VALUES (2, '2020-03-03', NULL, '2020-07-03'); INSERT INTO BORROWEDDATA VALUES (3, '2020-04-01', NULL, '2020-07-12'); INSERT INTO BORROWEDDATA VALUES (4, '2020-04-09', '2020-04-27', '2020-08-07'); INSERT INTO BORROWEDDATA VALUES (5, '2020-06-01', NULL, '2020-09-07');

INSERT INTO BOOK VALUES (1, 'Anna Karenina', 'James Lam', 'Twilight', '2010', 'Borrowed',43.0,1,1); INSERT INTO BOOK VALUES (2, 'Dan Marphey', 'Jenifer Cowen', 'KBC ltd', '2003', 'Borrowed', 35.0, 2,2);

8

COMP2350/6350 2020-Assignment 2

INSERT INTO BOOK VALUES (3, 'Twister', 'Christina Robert', 'Herald Corp.', '2012', 'Borrowed', 34.0, 3,3); INSERT INTO BOOK VALUES (4, 'Keulen', 'Samuel Dan', 'Leonel Barista', '2014', 'Borrowed', 54.0, 4,4); INSERT INTO BOOK VALUES (5, 'RoboCop', 'Lampard Kun', 'PinPon Press', '2012', 'Borrowed', 65.0, 5,5);

INSERT INTO BRANCH VALUES(1, 'St. Peters'); INSERT INTO BRANCH VALUES(2, 'Hornsby'); INSERT INTO BRANCH VALUES(3, 'Gordon'); INSERT INTO BRANCH VALUES(4, 'Penrith'); INSERT INTO BRANCH VALUES(5, 'St Mary');

INSERT INTO book_has_branch VALUES (1,1); INSERT INTO book_has_branch VALUES (2,2); INSERT INTO book_has_branch VALUES (3,3); INSERT INTO book_has_branch VALUES (4,4); INSERT INTO book_has_branch VALUES (5,5);



Copy and paste the SELECT * query to display the content of each table above, and screenshot of the content as displayed.

Select * from Member;

Select * from BorrowedData;

9

COMP2350/6350 2020-Assignment 2

Select * from Book;

Select * from Branch;

Select * from book_has_branch;

Task 5: SQL Queries

Copy and paste the SQL queries followed by their results (screenshot) for each of the following query

10

COMP2350/6350 2020-Assignment 2

[Query 1] List the name of members who have ever borrowed the book with the title Anna Karenina. SELECT MEMBERNAME FROM MEMBER, BOOK WHERE BOOK.MEMBER_MEMBERID=MEMBER.MEMBERID AND BOOKTITLE='Anna Karenina';

[Query 2] List each member (ID and name) who has books currently overdue against them, together with the ID and title of those books. SELECT MEMBERID, MEMBERNAME, BOOKID, BOOKTITLE from BOOK, MEMBER, BORROWEDDATA WHERE BOOK.MEMBER_MEMBERID=MEMBER.MEMBERID AND BOOK.BORROWEDDATA_BOOKISSUEID=BORROWEDDATA.BOOKISSUEID AND DATERETURNED > RETURNDUEDATE;

[Query 3] List the five most popular book(s) owned by SL. SELECT BOOK.TITLE FROM BOOK, BOOK_HAS_BRANCH, BRANCH WHERE BOOK.BOOKID = BOOK_HAS_BRANCH.BOOK_BOOKID AND BOOK_HAS_BRANCH.BRANCH_LIBBRANCHID = BRANCH.LIBBRANCHID AND MAX(BookID);

[Query 4] How many books were lent out from SL since Covid-19 related lockdown started (assume it to be March 01, 2020), but have not been returned yet? SELECT COUNT(BOOKID) AS BOOKSLENT FROM BOOK,BORROWEDDATA WHERE BOOK.BORROWEDDATA_BOOKISSUEID = BORROWEDDATA.BOOKISSUEID AND DATEBORROWED >= '2020-03-01' AND DATERETURNED IS NULL;

11

COMP2350/6350 2020-Assignment 2

12...