Library-Management-System/Library_Management_System(Creating Tables SQL).sql at main · MakCoder-2004/Library-Management-System · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
CREATE DATABASE Library_Management_System;

-- Table: dbo.Books
CREATE TABLE Books (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Date DATE,
    OLB VARCHAR(50),
    Publisher VARCHAR(100),
    ISBN VARCHAR(20),
    AuthorName VARCHAR(100),
    Category VARCHAR(50),
    Location VARCHAR(50),
    Amount INT,
    Price FLOAT
);

-- Table: dbo.Members
CREATE TABLE Members (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    SSN VARCHAR(14),
    Address VARCHAR(200),
    Phone1 VARCHAR(20),
    Phone2 VARCHAR(20),
    Email VARCHAR(30),
    BirthDate DATE,
    MembershipStatus BIT,
    BorrowState BIT
);

-- Table: dbo.Borrow
CREATE TABLE Borrow (
    BorrowId INT IDENTITY(1,1) PRIMARY KEY,
    MemberId INT,
    BookId INT,
    BorrowDate DATETIME,
    DueDate DATETIME,
    FOREIGN KEY (MemberId) REFERENCES Members(id),
    FOREIGN KEY (BookId) REFERENCES Books(id)
);

-- Table: dbo.Buy
CREATE TABLE Buy (
    Purchaseld INT IDENTITY(1,1) PRIMARY KEY,
    BookId INT,
    CustomerName VARCHAR(100),
    CustomerContact VARCHAR(20),
    PurchaseDate DATETIME,
    TotalPrice FLOAT,
    FOREIGN KEY (BookId) REFERENCES dbo.Books(id)
);

-- Table: dbo.Fines
CREATE TABLE Fines (
    FineId INT IDENTITY(1,1) PRIMARY KEY,
    MemberId INT,
    FineAmount FLOAT,
    FineType VARCHAR(20),
    ReturnDate DATETIME,
    BookId INT,
    ReturnId INT,
    FOREIGN KEY (MemberId) REFERENCES dbo.Members(id),
    FOREIGN KEY (BookId) REFERENCES dbo.Books(id)
);

-- Table: dbo.Returning
CREATE TABLE Returning (
    ReturnId INT IDENTITY(1,1) PRIMARY KEY,
    StaffId INT,
    MemberId INT,
    BookId INT,
    ReturnDate DATETIME,
    BorrowId INT,
    FOREIGN KEY (StaffId) REFERENCES Staff(id),
    FOREIGN KEY (MemberId) REFERENCES Members(id),
    FOREIGN KEY (BookId) REFERENCES Books(id)
);

-- Table: dbo.Staff
CREATE TABLE Staff (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    SSN VARCHAR(14),
    Address VARCHAR(200),
    Phone1 VARCHAR(20),
    Phone2 VARCHAR(20),
    BirthDate DATE,
    WorkType VARCHAR(30),
    HireDate DATE,
    Email VARCHAR(30)
);

-- Table: dbo.User_Pass
CREATE TABLE dbo.User_Pass (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Username VARCHAR(20) NOT NULL,
    Password VARCHAR(20) NOT NULL
);