Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE LibrarySystemGrupp12
- USE LibrarySystemGrupp12
- CREATE TABLE Category (
- categoryID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
- categoryName NVARCHAR(MAX) NOT NULL,
- categoryAbbreviation NVARCHAR(5) NOT NULL,
- maxLoanAllowance INTEGER NOT NULL
- )
- CREATE TABLE Employee (
- employeeID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
- fName NVARCHAR(MAX) NOT NULL,
- lName NVARCHAR(MAX) NOT NULL,
- email NVARCHAR(MAX) NOT NULL,
- phoneNumber VARCHAR(10) NOT NULL,
- employeeType VARCHAR(MAX) NOT NULL
- )
- CREATE TABLE LibraryUser (
- userID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
- fName NVARCHAR(MAX) NOT NULL,
- lName NVARCHAR(MAX) NOT NULL,
- email NVARCHAR(MAX) NOT NULL,
- userAddress NVARCHAR(MAX) NOT NULL,
- phoneNumber VARCHAR(10) NOT NULL,
- userType VARCHAR(MAX) NOT NULL, --Student, teacher or researcher
- maxItems INTEGER NOT NULL,
- userCity NVARCHAR(MAX),
- zipCode VARCHAR(5)
- )
- CREATE TABLE Loan (
- loanID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
- userID INTEGER FOREIGN KEY REFERENCES LibraryUser(userID) NOT NULL,
- employeeID INTEGER FOREIGN KEY REFERENCES Employee(employeeID) NOT NULL,
- dueDate DATE NOT NULL,
- returnedDate DATE NULL,
- returned INT NOT NULL --1 or 0 for true/false
- )
- CREATE TABLE Item ( --Details
- itemID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
- categoryID INTEGER FOREIGN KEY REFERENCES Category(categoryID) NOT NULL,
- title NVARCHAR(MAX) NOT NULL,
- itemDescription NVARCHAR(MAX) NOT NULL,
- itemType NVARCHAR(MAX) NOT NULL, --book || dvd
- releaseDate DATE NOT NULL,
- author NVARCHAR(MAX) NOT NULL, --author for books, = director as well
- publisher NVARCHAR(MAX) NULL,
- keywords NVARCHAR(MAX) NOT NULL,
- isbnNumber VARCHAR(13) NOT NULL,
- avaliable INT NOT NULL --true/false (0,1)
- section NVARCHAR(MAX) NOT NULL,
- barcode VARCHAR(30) NOT NULL
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement