Database System Concepts
Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
Bartleby Related Questions Icon

Related questions

bartleby

Concept explainers

Question

What are the SQL statements that will produce the tables and columns identified below (including relationships/keys/constraints)? Also, could you tell me the SQL statements to populate at least 5 records in each of the tables?

Thanks!!

 

 

 

 

Book

  • book_id (primary key)
  • title
  • author_id (foreign key to Author table)
  • year_of_release
  • number_of_pages
  • genre
  • location (in library, checked out)

Author

  • author_id (primary key)
  • name

Customer

  • customer_id (primary key)
  • name
  • age
  • phone_number
  • join_date

BookCheckout

  • book_id (foreign key to Book table)
  • customer_id (foreign key to Customer table)
  • checkout_date
  • due_date
  • return_date

Relationships:

  • Book to Author (one-to-many)
  • Book to Customer (many-to-many)
  • Customer to BookCheckout (one-to-many)

Keys and Constraints:

  • Primary keys:
    • book_id (Book table)
    • author_id (Author table)
    • customer_id (Customer table)
  • Foreign keys:
    • author_id (Book table) -> author_id (Author table)
    • book_id (BookCheckout table) -> book_id (Book table)
    • customer_id (BookCheckout table) -> customer_id (Customer table)
  • Constraints:
    • A book cannot have more than one checkout record at a time.
    • A customer cannot check out more than one copy of the same book at a time.
Expert Solution
Check Mark
Step 1: Introduction

This qus is from the subject SQL . Here we have to put light on the three important type of queries. These queries will be based on all Table creation, putting constraints as per the given instructions and inserting values into the created tables. All this work is done in the following steps.

Knowledge Booster
Background pattern image
Computer Science
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education