Assignment 1-A

Create Your First Database

Build a complete database schema for a small business. You'll create tables, define relationships, set constraints, and populate the database with sample data. This assignment covers all Module 1 concepts: DDL statements, data types, and database environment setup.

3-4 hours
Beginner
50 Points
What You'll Practice
  • CREATE DATABASE and USE statements
  • CREATE TABLE with proper data types
  • PRIMARY KEY and FOREIGN KEY constraints
  • INSERT statements for sample data
  • Basic database documentation
Contents
01

Assignment Overview

In this assignment, you will create a complete database schema for a Bookstore Management System. This project requires you to apply all concepts from Module 1: database creation, table design, data types selection, constraint implementation, and data insertion.

Skills Applied: This assignment tests your understanding of SQL Basics (Topic 1.1), Data Types (Topic 1.2), and Environment Setup (Topic 1.3) from Module 1.
Database Design

CREATE DATABASE, USE, schema organization

Table Creation

CREATE TABLE, data types, constraints

Data Population

INSERT statements, sample data

02

The Scenario

PageTurner Bookstore

You have been hired to design the database for PageTurner Bookstore, a local bookshop that wants to digitize their inventory and sales tracking.

"We need a database to track our books, authors, customers, and sales. Can you set up the foundation so we can start recording our business data?"

Required Tables

  • authors - Store author information
  • books - Store book details with author references
  • customers - Store customer information
  • orders - Track customer purchases
  • order_items - Store individual items in each order
03

Requirements

1
Create the Database

Create a database named bookstore_db and select it for use.

2
Create Authors Table
  • author_id - INT, PRIMARY KEY, AUTO_INCREMENT
  • first_name - VARCHAR(50), NOT NULL
  • last_name - VARCHAR(50), NOT NULL
  • birth_year - INT
  • nationality - VARCHAR(50)
3
Create Books Table
  • book_id - INT, PRIMARY KEY, AUTO_INCREMENT
  • title - VARCHAR(200), NOT NULL
  • author_id - INT, FOREIGN KEY to authors
  • isbn - VARCHAR(20), UNIQUE
  • price - DECIMAL(10,2), NOT NULL
  • stock_quantity - INT, DEFAULT 0
  • published_date - DATE
4
Create Remaining Tables

Design and create the customers, orders, and order_items tables with appropriate columns, data types, and relationships.

5
Insert Sample Data

Populate each table with at least 5 sample records demonstrating proper data insertion.

04

Submission Guidelines

GitHub Repository
  • Create a GitHub repository named sql-assignment-1-database
  • Include a schema.sql file with all CREATE statements
  • Include a data.sql file with all INSERT statements
  • Add a README.md with database description and ER diagram
05

Grading Rubric

Criteria Points
Database and table creation syntax 15
Appropriate data types selection 10
Primary and foreign key constraints 10
Sample data insertion 10
Code organization and documentation 5
Total 50