图书管理系统数据库设计实验

以下是一个简单的图书管理系统数据库设计实验，包括创建数据库、创建基本表和多对多联系的实现：

1. 创建数据库

CREATE DATABASE library;

2. 创建基本表

CREATE TABLE book (
  book_id INT PRIMARY KEY,
  book_name VARCHAR(50) NOT NULL,
  author VARCHAR(50) NOT NULL,
  publisher VARCHAR(50) NOT NULL,
  publish_date DATE NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE librarian (
  librarian_id INT PRIMARY KEY,
  librarian_name VARCHAR(50) NOT NULL,
  phone_number VARCHAR(20) NOT NULL
);

CREATE TABLE book_librarian (
  book_id INT,
  librarian_id INT,
  PRIMARY KEY (book_id, librarian_id),
  FOREIGN KEY (book_id) REFERENCES book(book_id),
  FOREIGN KEY (librarian_id) REFERENCES librarian(librarian_id)
);

3. 插入数据

INSERT INTO book VALUES
  (1, 'Database System Concepts', 'Abraham Silberschatz, Henry F. Korth, S. Sudarshan', 'McGraw-Hill', '2010-04-09', 999.00),
  (2, 'Operating System Concepts', 'Abraham Silberschatz, Greg Gagne, Peter B. Galvin', 'Wiley', '2018-01-01', 899.00),
  (3, 'Computer Networks', 'Andrew S. Tanenbaum, David J. Wetherall', 'Pearson', '2010-07-01', 799.00);

INSERT INTO librarian VALUES
  (1, 'John Smith', '123456789'),
  (2, 'Mary Johnson', '987654321'),
  (3, 'David Lee', '555555555');

INSERT INTO book_librarian VALUES
  (1, 1),
  (1, 2),
  (2, 2),
  (3, 1),
  (3, 3);

4. 查询数据

-- 查询所有图书信息
SELECT * FROM book;

-- 查询所有图书管理员信息
SELECT * FROM librarian;

-- 查询图书和图书管理员的联系
SELECT book.book_name, librarian.librarian_name
FROM book_librarian
JOIN book ON book_librarian.book_id = book.book_id
JOIN librarian ON book_librarian.librarian_id = librarian.librarian_id;