Advertisement
dilyara1322

rieltor_system_db

May 26th, 2019
132
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.16 KB | None | 0 0
  1. CREATE DATABASE rieltor_system_db
  2. USE rieltor_system_db
  3.  
  4. CREATE TABLE apartments_tbl (
  5.   id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  6.   owner_id int NOT NULL,
  7.   status bit NOT NULL,
  8.   city varchar(30) NOT NULL,
  9.   address varchar(255) NOT NULL,
  10.   floor int NOT NULL,
  11.   rooms int NOT NULL,
  12.   price int NOT NULL,
  13.   addition text
  14. )
  15.  
  16. CREATE TABLE clients_tbl (
  17.   id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  18.   lastname varchar(30) NOT NULL,
  19.   firstname varchar(30) DEFAULT NULL,
  20.   role bit NOT NULL,
  21.   telephone int UNIQUE NOT NULL,
  22.   passport varchar(30) UNIQUE NOT NULL
  23. )
  24.  
  25. CREATE TABLE deals_tbl (
  26.   id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  27.   buyer_id int NOT NULL,
  28.   seller_id int NOT NULL,
  29.   apart_id int NOT NULL,
  30.   d_date date NOT NULL,
  31.   price int NOT NULL
  32. )
  33.  
  34. CREATE TABLE employees_tbl (
  35.   id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  36.   login varchar(30) UNIQUE NOT NULL,
  37.   password varchar(30) NOT NULL,
  38.   lastname varchar(30) NOT NULL,
  39.   firstname varchar(30) NOT NULL,
  40.   passport varchar(30) NOT NULL,
  41.   role varchar(30) NOT NULL,
  42.   salary int NOT NULL
  43. )
  44.  
  45. CREATE TABLE requests_tbl (
  46.   id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  47.   client_id int NOT NULL,
  48.   apart_id int NULL,
  49.   realisation bit NOT NULL,
  50.   empl_id int NOT NULL,
  51.   status bit NOT NULL,
  52.   d_date date NOT NULL,
  53.   city varchar(30) NULL,
  54.   floor int NULL,
  55.   rooms int NULL,
  56.   price int NULL,
  57.   addition text
  58. )
  59.  
  60. ALTER TABLE requests_tbl
  61.   ADD CONSTRAINT client_ref
  62.   FOREIGN KEY (client_id) REFERENCES clients_tbl(id)
  63.  
  64. ALTER TABLE requests_tbl
  65.   ADD CONSTRAINT req_apart_ref
  66.   FOREIGN KEY (apart_id) REFERENCES apartments_tbl(id)
  67.  
  68. ALTER TABLE requests_tbl
  69.   ADD CONSTRAINT req_empl_ref
  70.   FOREIGN KEY (empl_id) REFERENCES employees_tbl(id)
  71.  
  72. ALTER TABLE deals_tbl
  73.   ADD CONSTRAINT buyer_ref
  74.   FOREIGN KEY (buyer_id) REFERENCES requests_tbl(id)
  75.  
  76. ALTER TABLE deals_tbl
  77.   ADD CONSTRAINT seller_ref
  78.   FOREIGN KEY (seller_id) REFERENCES requests_tbl(id)
  79.  
  80. ALTER TABLE deals_tbl
  81.   ADD CONSTRAINT deal_apart_ref
  82.   FOREIGN KEY (apart_id) REFERENCES apartments_tbl(id)
  83.  
  84. ALTER TABLE apartments_tbl
  85.   ADD CONSTRAINT owner_ref
  86.   FOREIGN KEY (owner_id) REFERENCES clients_tbl(id)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement