Creating a BookStore Database in SQL Server

Summary: in this tutorial, you will learn how to create a BookStore database in SQL Server.

Step 1. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server.

Step 2. Create a new query and run the following script to create a BookStore database:

-- Create the database
CREATE DATABASE BookStore;
GO

-- Use the database
USE BookStore;
GO

-- Create Authors table
CREATE TABLE Authors (
    AuthorID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    BirthDate DATE
);
GO

-- Create Books table
CREATE TABLE Books (
    BookID INT IDENTITY(1,1) PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Publisher VARCHAR(255) NOT NULL,
    ISBN VARCHAR(25) NOT NULL,
    PublishedDate DATE NOT NULL
);
GO


-- Create BookAuthors table
CREATE TABLE BookAuthors (
	BookID INT,
	AuthorID INT,
	FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE,
	FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) ON DELETE CASCADE
);

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    Address VARCHAR(255) NOT NULL
);
GO

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME NOT NULL,
    TotalAmount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
GO

-- Create OrderDetails table
CREATE TABLE OrderDetails (
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    BookID INT,
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID) ON DELETE CASCADE
);
GO

-- Create Inventories table
CREATE TABLE Inventories(
   BookID INT PRIMARY KEY,
   Qty INT,
   FOREIGN KEY (BookID) REFERENCES Books(BookID)
);
GOCode language: SQL (Structured Query Language) (sql)

Step 3. Create a new user that has all permissions on the BookStore database. Please change the YourPassword to your strong password:

-- Create a SQL Server login
CREATE LOGIN joe WITH PASSWORD = 'YourPassword';
GO

-- Use the BookStore database
USE BookStore;
GO

-- Create a user in the BookStore database
CREATE USER joe FOR LOGIN joe;
GO

-- Grant the user db_owner role for full permissions on the BookStore database
ALTER ROLE db_owner ADD MEMBER joe;
GOCode language: SQL (Structured Query Language) (sql)