Online Bookstore Database Schema

Task Overview

This document outlines the creation of a database schema for an online bookstore that manages customers, books, and orders. The schema includes entities for customers, books, and orders, along with relationships and cardinality specifications.

Objectives

1. Create the database schema.
2. Save the schema in a file named db.sql.
3. Import the file into the models.
4. Suitably organize the schema.
5. Define relationships and cardinality ratios.

1. Database Creation

The first step is to create a database for the online bookstore.

2. Schema Design

Schema Overview

The scheme is designed to manage three main entities:

• Customer
• Book
• Order
• OrderDetails (Weak entity)

Each entity will have specific attributes, and their relationships will be defined accordingly.

Entity Descriptions

2.1 Customer

• CustomerID: Unique identifier for each customer (Primary Key).
• FirstName: Customer’s first name.
• LastName: Customer’s last name.
• EmailAddress: Unique email address for the customer.
• Shipping Address: Includes street, city, state, and zip code.
• Phone Number: Contact number for the customer.

2.2 Book

• BookID: Unique identifier for each book (Primary Key).
• Title: Title of the book.
• Author: Author of the book.
• Price: Price of the book.
• StockQuantity: Number of copies available.
• Publisher: Publisher of the book.

2.3 Order

• OrderID: Unique identifier for each order (Primary Key).
• OrderDate: Date when the order was placed.
• CustomerID: Links to the customer who placed the order (Foreign Key).
• TotalAmount: Total price of the order.

2.4 OrderDetails

• OrderID: Links to the order (Foreign Key).
• BookID: Links to the book (Foreign Key).
• Quantity: Number of copies of the book ordered.
• Subtotal: Total price for the quantity ordered.

3. Entity-Relationship Diagram (ERD)

To visualize the schema, use MySQL Workbench’s reverse engineering feature. After importing the schema:

Go to the File section and select “New Model.”

Import using a reverse engineer MySQL script.

After this give the path and check both boxes:

Execute and follow the wizard to complete the process and generate the ERD.

4. Relationships:

• Customer to Order: One customer can place multiple orders (1relationship).
• Order to OrderDetails: One order can include multiple books through OrderDetails (1relationship).
• Book to OrderDetails: A book can be part of multiple orders (1 relationship).

5. Cardinality Ratios:

• Customer (1): Order (M)
• Order (1) : OrderDetails (M)
• Book (1) : OrderDetails (M)

Company Database

Purpose

The company database manages employee, department, project, and dependent information for an organization. The schema supports tracking employee assignments, departmental relationships, and project work.

Database Creation

Table Descriptions:

create an employee table to store employee information including personal and organizational details:

Insert employee data into the EMPLOYEE table:

Table to store department information:

Insert department data into the DEPARTMENT table:

Table to store department location(s):

Insert department location data into the DEPT_LOCATIONS table:

Table to store project information:

Insert project data into the PROJECT table:

Table to store information on which employees work on which projects:

Insert data into the WORKS_ON table, linking employees to projects with worked hours:

Table to store information on employee dependents:

Insert dependent data into the DEPENDENT table, specifying dependents for each employee:

Key Queries:

select * from employee;

select * from DEPARTMENT;

Select Employees from a Specific Department Retrieve employees who belong to a specific department (e.g., Research):

SELECT Fname, Lname, Dno

FROM EMPLOYEE

WHERE Dno = (SELECT Dnumber FROM DEPARTMENT WHERE Dname = ‘headquarters’);

List department names and their respective managers:

SELECT Dname, CONCAT(Fname, ‘ ‘, Lname) AS Manager

FROM DEPARTMENT

JOIN EMPLOYEE ON DEPARTMENT.Mgr_ssn = EMPLOYEE.Ssn;

Retrieve a list of all projects and their respective locations:

SELECT Pname, Plocation

FROM PROJECT;

Retrieved employees working on the ‘ProductX’ project and the hours they worked:

SELECT E.Fname, E.Lname, W.Hours

FROM EMPLOYEE E

JOIN WORKS_ON W ON E.Ssn = W.Essn

JOIN PROJECT P ON W.Pno = P.Pnumber

WHERE P.Pname = ‘ProductZ’;

Retrieving all dependents of the employee ‘John Smith’:

SELECT Dependent_name, Relationship, Bdate

FROM DEPENDENT

WHERE Essn = (SELECT Ssn FROM EMPLOYEE WHERE Fname = ‘John’ AND Lname = ‘smith’);

Calculate the total hours worked on each project:

SELECT Pname, SUM(Hours) AS Total_Hours

FROM WORKS_ON W

JOIN PROJECT P ON W.Pno = P.Pnumber

GROUP BY Pname;

Retrieve employees along with their department name and project assignments:

SELECT E.Fname, E.Lname, D.Dname, P.Pname

FROM EMPLOYEE E

JOIN DEPARTMENT D ON E.Dno = D.Dnumber

JOIN WORKS_ON W ON E.Ssn = W.Essn

JOIN PROJECT P ON W.Pno = P.Pnumber;

List projects along with their respective department and manager:

SELECT P.Pname, D.Dname, CONCAT(E.Fname, ‘ ‘, E.Lname) AS Manager

FROM PROJECT P

JOIN DEPARTMENT D ON P.Dnum = D.Dnumber

JOIN EMPLOYEE E ON D.Mgr_ssn = E.Ssn;

Count how many employees work in each department:

SELECT D.Dname, COUNT(E.Ssn) AS Employee_Count

FROM EMPLOYEE E

JOIN DEPARTMENT D ON E.Dno = D.Dnumber

GROUP BY D.Dname;

Find projects that do not have any employees assigned:

SELECT Pname

FROM PROJECT

WHERE Pnumber NOT IN (SELECT Pno FROM WORKS_ON);

Retrieve employees who have at least one dependent:

SELECT E.Fname, E.Lname

FROM EMPLOYEE E

WHERE EXISTS (SELECT 1 FROM DEPENDENT D WHERE D.Essn = E.Ssn);

Retrieve managers along with the departments and projects they manage:

SELECT CONCAT(E.Fname, ‘ ‘, E.Lname) AS Manager, D.Dname, P.Pname

FROM EMPLOYEE E

JOIN DEPARTMENT D ON E.Ssn = D.Mgr_ssn

JOIN PROJECT P ON D.Dnumber = P.Dnum;

ERD Diagram of the database:

Relationships and Diagrams

• Employee to Department: An employee belongs to one department.
• Project to Department: Projects are managed by departments.
• Employee to Works_On: Employees can work on multiple projects.

Conclusion

This document outlines the database schemas for an Online Bookstore and a Company Database. The Online Bookstore schema efficiently manages customers, books, and orders, ensuring smooth inventory tracking and order management. The Company Database handles employees, departments, projects, and dependents, supporting complex organizational data structures and project assignments.

Both schemas are designed for scalability, data integrity, and efficient querying, making them suitable for real-world applications focusing on clear relationships and robust performance.