Create a Product Catalog Table

Introduction

A local bookstore needs help setting up their product catalog database. As their database administrator, you need to create a table that will store information about their books. This challenge tests your ability to create a MySQL table with appropriate data types and constraints.

Skills Graph

Create the Books Table

Your task is to create a table that will store the bookstore's inventory information. The table needs to effectively store different types of data about each book.

Tasks

• Connect to MySQL as the root user
• Use the bookstore database
• Create a table named books with appropriate columns and data types to store the following information:
  • Book ID (should auto-increment)
  • Book title (maximum 200 characters, required)
  • Price (must support decimal values up to $999.99)
  • Publication date
  • Page count (whole numbers only)
  • Description (long text)
  • Quantity in stock (must be 0 or positive)

Requirements

• All operations must be performed in the ~/project directory
• The table name must be exactly books
• All column names must be in lowercase
• The primary key must be named id
• The title cannot be NULL
• The price must have 2 decimal places
• The quantity_in_stock must not allow negative values

Example

After creating the table correctly, describing it should show output similar to this:

DESCRIBE books;

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| title             | varchar(200) | NO   |     | NULL    |                |
| price             | decimal(5,2) | YES  |     | NULL    |                |
| publication_date  | date         | YES  |     | NULL    |                |
| page_count        | int(11)      | YES  |     | NULL    |                |
| description       | text         | YES  |     | NULL    |                |
| quantity_in_stock | int(11)      | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

Summary

In this challenge, you practiced creating a MySQL table with appropriate data types and constraints. The skills demonstrated include choosing suitable data types for different kinds of information, implementing auto-incrementing primary keys, setting up NOT NULL constraints, and using CHECK constraints to validate data. These fundamentals are essential for database design and will be used frequently when working with MySQL databases.