SHARE
TWEET

Untitled

a guest Feb 17th, 2017 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE LibrarySystemGrupp12
  2. USE LibrarySystemGrupp12
  3.  
  4.  
  5. CREATE TABLE Category (
  6.     categoryID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
  7.     categoryName NVARCHAR(MAX) NOT NULL,
  8.     categoryAbbreviation NVARCHAR(5) NOT NULL,
  9.     maxLoanAllowance INTEGER NOT NULL
  10. )
  11.  
  12. CREATE TABLE Employee (
  13.     employeeID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
  14.     fName NVARCHAR(MAX) NOT NULL,
  15.     lName NVARCHAR(MAX) NOT NULL,
  16.     email NVARCHAR(MAX) NOT NULL,
  17.     phoneNumber VARCHAR(10) NOT NULL,
  18.     employeeType VARCHAR(MAX) NOT NULL
  19. )
  20.  
  21. CREATE TABLE LibraryUser (
  22.     userID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
  23.     fName NVARCHAR(MAX) NOT NULL,
  24.     lName NVARCHAR(MAX) NOT NULL,
  25.     email NVARCHAR(MAX) NOT NULL,
  26.     userAddress NVARCHAR(MAX) NOT NULL,
  27.     phoneNumber VARCHAR(10) NOT NULL,
  28.     userType VARCHAR(MAX) NOT NULL, --Student, teacher or researcher
  29.     maxItems INTEGER NOT NULL,
  30.     userCity NVARCHAR(MAX),
  31.     zipCode VARCHAR(5)
  32. )
  33.  
  34.  
  35. CREATE TABLE Loan (
  36.     loanID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
  37.     userID INTEGER FOREIGN KEY REFERENCES LibraryUser(userID) NOT NULL,
  38.     employeeID INTEGER FOREIGN KEY REFERENCES Employee(employeeID) NOT NULL,
  39.     dueDate DATE NOT NULL,
  40.     returnedDate DATE NULL,
  41.     returned INT NOT NULL --1 or 0 for true/false
  42. )
  43.  
  44.  
  45. CREATE TABLE Item ( --Details
  46.     itemID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
  47.     categoryID INTEGER FOREIGN KEY REFERENCES Category(categoryID) NOT NULL,
  48.     title NVARCHAR(MAX) NOT NULL,
  49.     itemDescription NVARCHAR(MAX) NOT NULL,
  50.     itemType NVARCHAR(MAX) NOT NULL, --book || dvd
  51.     releaseDate DATE NOT NULL,
  52.     author NVARCHAR(MAX) NOT NULL, --author for books, = director as well
  53.     publisher NVARCHAR(MAX) NULL,
  54.     keywords NVARCHAR(MAX) NOT NULL,
  55.     isbnNumber VARCHAR(13) NOT NULL,
  56.     avaliable INT NOT NULL --true/false (0,1)
  57.     section NVARCHAR(MAX) NOT NULL,
  58.     barcode VARCHAR(30) NOT NULL
  59. )
RAW Paste Data
Top