Book (ISBN, title, author, edition, year) BookCopy (copyNo, ISBN, available) Bor
ID: 3715459 • Letter: B
Question
Book (ISBN, title, author, edition, year) BookCopy (copyNo, ISBN, available) Borrower (borrowerNo, borrowerName, borrowerAddress) BookLoan (copyNo, dateOut, dateDue, borrowerNo)
where Book contains details of book titles in the library and the ISBN is the primary key. BookCopy contains details of the individual copies of books in the library and copyNo is the primary key. ISBN is a foreign key identifying the book title. Borrower contains details of library members who can borrow books and borrowerNo is the primary key. BookLoan contains details of the book copies that are borrowed by library members and copyNo/dateOut forms the primary key. borrowerNo is a foreign key identifying the borrower.
1. Create the Library schema and all corresponding tables in the schema. Include statements to drop the schema if they already exist. Include any constrains or default value that you think are necessary.
2. For each table you just created, add 5 rows to that.
3. Write an ALTER TABLE statement that adds one new column that provides phone number of the borrower to the Borrower table you already created.
4. Write an ALTER TABLE statement that modifies the Book table you already created so that the book title in each row has to be unique.
5. What happen when you try to insert a row with a duplicate book title to the Book table?
6. You are required to create a conceptual data model of the data requirements for a company that specializes in IT training. The Company has 30 instructors and can handle up to 100 trainees per training session. The Company offers five advanced technology courses, each of which is taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams or may be assigned to do research. Each trainee undertakes one advanced technology course per training session. (a) Identify the main entity types for the company. (b) Identify the main relationship types and specify the multiplicity for each relationship. State any assumptions you make about the data. (c) Using your answers for (a) and (b), draw a single ER diagram to represent the data requirements for the company.
7. Read the following case study, which describes the data requirements for a DVD rental company. The DVD rental company has several branches throughout the USA. The data held on each branch is the branch address made up of street, city, state, and zip code, and the telephone number. Each branch is given a branch number, which is unique throughout the company. Each branch is allocated staff, which includes a Manager. The Manager is responsible for the day-to-day running of a given branch. The data held on a member of staff is his or her name, position, and salary. Each member of staff is given a staff number, which is unique throughout the company. Each branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number, title, category, daily rental, cost, status, and the names of the main actors, and the director. The catalog number uniquely identifies each DVD. However, in most cases, there are several copies of each DVD at a branch, and the individual copies are identified using the DVD number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before hiring a DVD from the company, a customer must first register as a member of a local branch. The data held on a member is the first and last name, address, and the date that the member registered at a branch. Each member is given a member number, which is unique throughout all branches of the company. Once registered, a member is free to rent DVDs, up to maximum of ten at any one time. The data held on each DVD rented is the rental number, the full name and number of the member, the DVD number, title, and daily rental, and the dates the DVD is rented out and date returned. The rental number is unique throughout the company. (a) Identify the main entity types of the DVD rental company. (b) Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram. (c) Determine the multiplicity constraints for each relationship described in (b). Represent the multiplicity for each relationship in the ER diagrams created in (b). (d) Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER diagrams created in (c). (e) Determine candidate and primary key attributes for each (strong) entity type. (f) Using your answers (a) to (e) attempt to represent the data requirements of the DVD rental company as a single ER diagram. State any assumptions necessary to support your design.
Explanation / Answer
Book (ISBN, title, author, edition, year)
BookCopy (copyNo, ISBN, available)
Borrower (borrowerNo, borrowerName, borrowerAddress)
BookLoan (copyNo, dateOut, dateDue, borrowerNo)
--1. Create the Library schema and all corresponding tables in the schema.
-- Include statements to drop the schema if they already exist. Include any constrains or default value that you think are necessary.
DROP Database LIBRARY;
CREATE DATABASE LIBRARY;
CREATE TABLE Book (ISBN INT NOT NULL, title varchar(20), author varchar(20), edition varchar(20), year DATE,PRIMARY KEY (ISBN));
CREATE TABLE BookCopy (copyNo INT NOT NULL, ISBN INT, available INT,PRIMARY KEY (copyNo),FOREIGN KEY fk_ISBN(ISBN) REFERENCES Book(ISBN));
CREATE TABLE Borrower (borrowerNo INT NOT NULL, borrowerName VARCHAR(20), borrowerAddress VARCHAR(30),PRIMARY KEY (borrowerNo));
CREATE TABLE BookLoan (copyNo INT NOT NULL, dateOut DATE, dateDue DATE, borrowerNo INT NOT NULL,
FOREIGN KEY fk_BNO(borrowerNo) REFERENCES Borrower(borrowerNo),
FOREIGN KEY fk_BCP(copyNo) REFERENCES BookCopy(copyNo));
--2. For each table you just created, add 5 rows to that.
INSERT INTO Book VALUES (0618260307,'The Hobbit' ,'J. R. R. Tolkien','3rd','2010-10-01');
INSERT INTO Book VALUES (0908606664,'Slinky Malinki','Lynley Dodd','1st','2001-01-01');
INSERT INTO Book VALUES (1908606206 ,'Hairy Maclary from Donaldson''sDairy ','Lynley Dodd','4th','2104-12-2');
INSERT INTO Book VALUES (0393310728 ,'How to Lie with Statistics ','Darrell Huff','4th','2104-12-2')
INSERT INTO Book VALUES (0908783116 ,'Mechanical Harry','Bob Kerr','3rd','2010-10-01');
INSERT INTO BookCopy (23,0908783116,10)
INSERT INTO BookCopy (21,0618260307,14)
INSERT INTO BookCopy (22,1908606206,5)
INSERT INTO BookCopy (24,0393310728,0)
INSERT INTO BookCopy (25,0908606664,22)
INSERT INTO Borrower(123,'John','23 Jack Street')
INSERT INTO Borrower(112,'Betty','21 Rat bus Street')
INSERT INTO Borrower(100,'Dragon','Dragon Street')
INSERT INTO Borrower(007,'Bond','James Bond Street')
INSERT INTO Borrower(002,'Bean','Bean Teddy Street')
INSERT INTO BookLoan (002,'2018-01-01','2018-01-31',21)
INSERT INTO BookLoan (007,'2018-01-01','2018-01-31',22)
INSERT INTO BookLoan (123,'2018-01-01','2018-01-31',25)
INSERT INTO BookLoan (112,'2018-02-01','2018-02-15',21)
INSERT INTO BookLoan (100,'2018-02-01','2018-02-15',25)
--3. Write an ALTER TABLE statement that adds one new column that provides phone number of the borrower to the Borrower table you already created.
ALTER TABLE Borrower
ADD COLUMN phone INT;
--4. Write an ALTER TABLE statement that modifies the Book table you already created so that the book title in each row has to be unique.
ALTER TABLE BOOk
ADD CONSTRAINT title UNIQUE;
--5. What happen when you try to insert a row with a duplicate book title to the Book table?
If we insert duplicate row it will through an error, stating primary key violation and unique column constaint
/*AS PER CHEGG RULES WE CAN SOLVE ONLY ONE MAIN QUESTION AND ALL SUB QUESTION IN MAIN QUESTION. 6 AND 7 ARE NOT RELATED TO 1St QUESTION. PLEASE POST AS NEW QUESTION TO SOLVE> THANKS A LOT*/