在数据库LibraryDB中创建Reader表、Types表、inventory表、book表、borrow表。
时间: 2024-09-27 20:05:25 浏览: 76
在数据库`LibraryDB`中,为了管理和跟踪读者信息、书籍类型、库存、书籍以及借阅记录,可以创建以下几个表:
1. **Reader** 表(用于存储读者信息):
```sql
CREATE TABLE Reader (
reader_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(20)
);
```
2. **Types** 表(用于存储书籍类型):
```sql
CREATE TABLE Types (
type_id INT PRIMARY KEY,
type_name VARCHAR(50)
);
```
3. **Inventory** 表(表示库存信息,关联书籍和可用数量):
```sql
CREATE TABLE Inventory (
inventory_id INT PRIMARY KEY,
book_id INT,
type_id INT,
quantity INT,
FOREIGN KEY (book_id) REFERENCES book(book_id),
FOREIGN KEY (type_id) REFERENCES Types(type_id)
);
```
4. **Book** 表(存储书籍详细信息,包括ID和类型):
```sql
CREATE TABLE Book (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
year_published INT,
type_id INT,
FOREIGN KEY (type_id) REFERENCES Types(type_id)
);
```
5. **Borrow** 表(记录借阅历史,关联读者和书籍):
```sql
CREATE TABLE Borrow (
borrow_id INT PRIMARY KEY,
reader_id INT,
book_id INT,
borrow_date DATE,
due_date DATE,
FOREIGN KEY (reader_id) REFERENCES Reader(reader_id),
FOREIGN KEY (book_id) REFERENCES Book(book_id)
);
```
阅读全文