QuestionQuestion

Transcribed TextTranscribed Text

Instructions Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema below. Specify the keys, the referential integrity, and the check constraints that may apply. Use SQL DML statements to populate the database with 5 rows in each table, and then implement the following requests: 1. List the books published by a given publisher (specified by name) in alphabetical order of their titles 2. List all the borrowers living in Baltimore in descending order of their phone numbers 3. List the books with a title starting or ending with the letter 'A' Submit the SQL statements as a text file (Notepad) following the document naming convention FirstnameLastnameHW1.txt. Grading: this homework is awarded 100 points (40 points for creating the tables with all the required constraints, 30 points for populating the tables correctly, and 10 points for each of the 3 requests). BOOK Book_ id Title Publisher_name 444 BOOK_AUTHORS Book_ Author_name PUBLISHER Name Address Phone 4 BOOK_COPIES Book_ Branch_ No_ of_copies BOOK_LOANS Book_ id Branch Card_no Date_out Due_date LIBRARY_BRANCH Branch Branch_name Address 44 BORROWER Card_no Name Address Phone

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

CREATE TABLE PUBLISHER
   ( NAME VARCHAR(20) NOT NULL ,
ADDRESS VARCHAR(50),
PHONE VARCHAR(20),
CONSTRAINT PUBLISHER_PK PRIMARY KEY (NAME)
   ) ;


CREATE TABLE BORROWER
   ( CARD_NO NUMBER NOT NULL ,
NAME VARCHAR(20),
ADDRESS VARCHAR(20),
PHONE VARCHAR(20),
CONSTRAINT BORROWER_PK PRIMARY KEY (CARD_NO)
   ) ;


CREATE TABLE BOOK
   ( BOOK_ID NUMBER NOT NULL ,
TITLE VARCHAR(20),
PUBLISHER_NAME VARCHAR(20),
CONSTRAINT BOOK_PK PRIMARY KEY (BOOK_ID)
,
CONSTRAINT BOOK_FK1 FOREIGN KEY (PUBLISHER_NAME)
REFERENCES PUBLISHER (NAME)
   ) ;



CREATE TABLE BOOK_AUTHOR...
$50.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats