SHARE
TWEET

Untitled

a guest Dec 9th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. CREATE TABLE SHOPS
  3. ( ShopID INT not null
  4. , FloorsNumber INT DEFAULT 10
  5. , EmployeesNumber INT CHECK (EmployeesNumber>0)
  6. , Surface INT DEFAULT 20000
  7. , PhoneNumber INT CHECK (PhoneNumber LIKE ('987[0-9][0-9][0-9][0-9][0-9][0-9]'))
  8. , Zip INT not null
  9. , Street VARCHAR (25) not null
  10. , City VARCHAR (25) not null
  11. , CONSTRAINT PK_SHOPS PRIMARY KEY(ShopID)
  12. );
  13.  
  14. CREATE TABLE EMPLOYEES
  15. ( SSN INT not null
  16. , NameEmp VARCHAR not null
  17. , SurnameEmp VARCHAR not null
  18. , Birthday DATE not null
  19. , EmailAdress NVARCHAR (25)
  20. , PhoneNumber INT CHECK (PhoneNumber LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
  21. , BankAccount VARCHAR (24)
  22. , Street VARCHAR (50)
  23. , Zip INT
  24. , Sex CHAR CHECK (Sex IN ('M','F'))
  25. , EmployeeDNI INT not null CHECK (EmployeeDNI LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'))
  26. , HoursWeek INT DEFAULT 40
  27. , YearsInCompany INT DEFAULT 0
  28. , Category VARCHAR (20) not null CHECK (Category IN ('Manager','Customer supporter','Dependent','Security worker','Web designer'))
  29. , CONSTRAINT PK_EMPLOYEES PRIMARY KEY(SSN)
  30. );
  31.  
  32. CREATE TABLE PRODUCTS
  33. ( UPC INT not null
  34. , NmePro VARCHAR (50)
  35. , Quantity INT
  36. , Brand VARCHAR (50)
  37. , Price MONEY CHECK (Price<10000)
  38. , CONSTRAINT PK_PRODUCTS PRIMARY KEY(UPC)
  39. );
  40.  
  41. CREATE TABLE DEPARTMENTS
  42. ( DepartmentCode INT not null
  43. , Employees# INT not null
  44. , DepartmentName VARCHAR (10) not null CHECK (DepartmentName IN ('Food','Technology','Sports','Cosmetics','Clothes'))
  45. , CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DepartmentCode)
  46. );
  47.  
  48. CREATE TABLE MANAGER
  49. ( ManagerSSN INT not null
  50. , TitleOfUniversity VARCHAR(50) DEFAULT 'Collage'
  51. , CONSTRAINT PK_MANAGER PRIMARY KEY (ManagerSSN)
  52. , CONSTRAINT FK_MANAGER FOREIGN KEY (ManagerSSN) REFERENCES EMPLOYEES(SSN)
  53. );
  54.  
  55. CREATE TABLE DEPENDENTEMP
  56. ( DependentSSN INT not null
  57. , ManagerSSN INT not null
  58. , DependentPasswd VARCHAR (4) not null CHECK (DependentPasswd LIKE ('[0-9][0-9][a-z][0-9]'))
  59. , LanguageLevel VARCHAR (2) DEFAULT 'B1'
  60. , DepartmentName VARCHAR (10) CHECK (DepartmentName IN ('Food','Technology','Sports','Cosmetics','Clothes'))
  61. , CONSTRAINT PK_DEPENDENTS PRIMARY KEY (DependentSSN)
  62. , CONSTRAINT FK_DEPENDENTS FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
  63. , CONSTRAINT FK_DEPENDENTS2 FOREIGN KEY (DependentSSN) REFERENCES EMPLOYEES(SSN)
  64. );
  65.  
  66. CREATE TABLE SALES
  67. ( ShopID INT not null
  68. , DependentSSN INT not null
  69. , Datesale DATE not null DEFAULT getdate()
  70. , Quantity INT DEFAULT 1
  71. , CONSTRAINT PK_SALES PRIMARY KEY (ShopID, DependentSSN, Datesale)
  72. , CONSTRAINT FK_DEPENDENTSSN FOREIGN KEY (DependentSSN) REFERENCES DEPENDENTEMP(DependentSSN)
  73. , CONSTRAINT SK_QUANTITY CHECK (Quantity<1000)
  74. );
  75.  
  76. CREATE TABLE CUSTOMERSUPPORTER
  77. ( CusSuppSSN INT not null
  78. , ManagerSSN INT not null
  79. , CustomerSuppPasswd VARCHAR (4) not null CHECK (CustomerSuppPasswd LIKE ('[0-9][0-9][a-z][0-9]'))
  80. , LanguageLevel VARCHAR (2) DEFAULT 'B2'
  81. , CONSTRAINT PK_CUSSUPPSSN PRIMARY KEY (CusSuppSSN)
  82. , CONSTRAINT FK_CUSTOMERSUPPORTER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
  83. , CONSTRAINT FK_CUSTOMERSUPPORTER2 FOREIGN KEY (CusSuppSSN) REFERENCES EMPLOYEES(SSN)
  84. );
  85.  
  86. CREATE TABLE WEBDESIGNER
  87. ( WebDesignerSSN INT not null
  88. , ManagerSSN INT not null
  89. , TitleOfUniversity VARCHAR (50)
  90. , ExtraHours INT CHECK (ExtraHours<4 AND ExtraHours>0)
  91. , CONSTRAINT PK_WEBDESIGNER PRIMARY KEY (WebDesignerSSN)
  92. , CONSTRAINT FK_WEBDESIGNER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
  93. , CONSTRAINT FK_WEBDESIGNER2 FOREIGN KEY (WebDesignerSSN) REFERENCES EMPLOYEES(SSN)
  94. );
  95.  
  96. CREATE TABLE SECURITYWORKER
  97. ( SecWorkerSSN INT not null
  98. , ManagerSSN INT not null
  99. , CONSTRAINT PK_SECURITYWORKER PRIMARY KEY (SecWorkerSSN)
  100. , CONSTRAINT FK_SECURITYWORKER FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
  101. , CONSTRAINT FK_SECURITYWORKER2 FOREIGN KEY (SecWorkerSSN) REFERENCES EMPLOYEES(SSN)
  102. );
  103.  
  104. CREATE TABLE PAYROLLS
  105. ( SSN INT not null
  106. , DatePayroll DATE not null DEFAULT getdate()
  107. , Amount INT null
  108. , CONSTRAINT PK_PAYROLLS PRIMARY KEY (SSN, DatePayroll)
  109. , CONSTRAINT FK_PAYROLLS FOREIGN KEY (SSN) REFERENCES EMPLOYEES(SSN)
  110. );
  111.  
  112. CREATE TABLE WAREHOUSE
  113. ( WarehouseCode INT not null
  114. , ShopID INT not null
  115. , CONSTRAINT PK_WAREHOUSE PRIMARY KEY (WarehouseCode, ShopID)
  116. , CONSTRAINT FK_WAREHOUSE FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
  117. );
  118.  
  119. CREATE TABLE SUPPLIERS
  120. ( SuppliersCode INT not null
  121. , BankAccount VARCHAR (24)
  122. , PhoneNumber INT CHECK (PhoneNumber LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
  123. , EmailAddress NVARCHAR (25)
  124. , Zip INT not null
  125. , Street VARCHAR (50) not null
  126. , City VARCHAR (25) not null DEFAULT 'Madrid'
  127. , Price MONEY
  128. , Category CHAR not null CHECK (Category IN ('I','N'))
  129. , CONSTRAINT PK_SUPPLIERS PRIMARY KEY (SuppliersCode)
  130. );
  131.  
  132. CREATE TABLE CLIENTS
  133. ( ClientID INT not null
  134. , EmailAdress NVARCHAR (25)
  135. , PhoneNumber INT
  136. , BankAccount VARCHAR (24)
  137. , City VARCHAR (25) not null
  138. , Street VARCHAR (50) not null
  139. , Zip INT not null
  140. , ClientDNI VARCHAR (9) not null CHECK (ClientDNI LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'))
  141. , Nationality VARCHAR (30)
  142. , ClientsType VARCHAR (2) not null CHECK (ClientsType IN ('C','P','PP','PN'))
  143. , CONSTRAINT PK_CLIENTS PRIMARY KEY(ClientID)
  144. );
  145.  
  146. CREATE TABLE ORDERS
  147. ( UPC INT not null
  148. , ManagerSSN INT not null
  149. , SuppliersCode INT  not null
  150. , OrderWeight INT CHECK (OrderWeight<1000 AND OrderWeight>0)
  151. , Price MONEY DEFAULT 10000.00
  152. , DateUpc DATE not null DEFAULT getdate()
  153. , Quantity INT DEFAULT 50
  154. , CONSTRAINT PK_ORDERS PRIMARY KEY (UPC, ManagerSSN, SuppliersCode, DateUpc)
  155. , CONSTRAINT FK_MANAGERSSN FOREIGN KEY (ManagerSSN) REFERENCES MANAGER(ManagerSSN)
  156. , CONSTRAINT FK_SUPPLIERSCODE FOREIGN KEY (SuppliersCode) REFERENCES SUPPLIERS(SuppliersCode)
  157. , CONSTRAINT FK_UPC FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
  158. );
  159.  
  160. CREATE TABLE COMPANY
  161. ( CompanyClientID INT not null
  162. , Name_company VARCHAR (30)
  163. , CONSTRAINT PK_COMPANY PRIMARY KEY(CompanyClientID)
  164. , CONSTRAINT FK_COMPANYCLIENTID FOREIGN KEY (CompanyClientID) REFERENCES CLIENTS(ClientID)
  165. );
  166.  
  167. CREATE TABLE PRIVATE_CLI
  168. ( PrivateClientID INT not null
  169. , DiscountCard INT
  170. , CONSTRAINT PK_PRIVATE PRIMARY KEY(PrivateClientID)
  171. , CONSTRAINT FK_PRIVATECLIENTID FOREIGN KEY (PrivateClientID) REFERENCES CLIENTS(ClientID)
  172. );
  173.  
  174. CREATE TABLE PREMIUM_PRI_CLI
  175. ( PrivatePremiunID INT not null
  176. , DiscountCard INT
  177. , CONSTRAINT PK_PREMIUM_PRI_CLI PRIMARY KEY (PrivatePremiunID)
  178. , CONSTRAINT FK_PREMIUM_PRI_CLI FOREIGN KEY (PrivatePremiunID) REFERENCES PRIVATE_CLI(PrivateClientID)
  179. );
  180.  
  181. CREATE TABLE NOPREMIUM_PRI_CLI
  182. ( PrivateNoPremiunID INT not null
  183. , CONSTRAINT PK_NOPREMIUM_PRI_CLI PRIMARY KEY (PrivateNoPremiunID)
  184. , CONSTRAINT FK_NOPREMIUM_PRI_CLI FOREIGN KEY (PrivateNoPremiunID) REFERENCES PRIVATE_CLI(PrivateClientID)
  185. );
  186.  
  187. CREATE TABLE OFFERS
  188. ( OfferCode INT not null
  189. , IniciationDate Date DEFAULT getdate()
  190. , ExpirationDate Date
  191. , Discount INT CHECK (Discount<70)
  192. , CONSTRAINT PK_OFFERS PRIMARY KEY(OfferCode)
  193. );
  194.  
  195. CREATE TABLE COSMETIC
  196. ( CosmeticUPC INT not null
  197. , ComseticType VARCHAR
  198. , CONSTRAINT PK_COSMETICS PRIMARY KEY(CosmeticUPC)
  199. , CONSTRAINT FK_COSMETICS FOREIGN KEY (CosmeticUPC) REFERENCES Products(UPC)
  200. );
  201.  
  202. CREATE TABLE FOOD
  203. ( FoodUPC INT not null
  204. , FoodType VARCHAR
  205. , CONSTRAINT PK_FOOD PRIMARY KEY(FoodUPC)
  206. , CONSTRAINT FK_FOOD FOREIGN KEY (FoodUPC) REFERENCES Products(UPC)
  207. );
  208.  
  209. CREATE TABLE TECHNOLOGY
  210. ( TechnoUPC  INT not null
  211. , TechnoType VARCHAR
  212. , CONSTRAINT PK_TECHNOLOGY PRIMARY KEY(TechnoUPC)
  213. , CONSTRAINT FK_TECHNOLOGY FOREIGN KEY (TechnoUPC) REFERENCES Products(UPC)
  214. );
  215.  
  216. CREATE TABLE CLOTHES
  217. ( ClothesUPC INT not null
  218. , ClothesType VARCHAR
  219. , CONSTRAINT PK_CLOTHES PRIMARY KEY(ClothesUPC)
  220. , CONSTRAINT FK_CLOTHES FOREIGN KEY (ClothesUPC) REFERENCES Products(UPC)
  221. );
  222.  
  223. CREATE TABLE SPORTS
  224. ( SportUPC INT not null
  225. , SportType VARCHAR
  226. , CONSTRAINT PK_SPORTS PRIMARY KEY(SportUPC)
  227. , CONSTRAINT FK_SPORTS FOREIGN KEY (SportUPC) REFERENCES Products(UPC)
  228. );
  229.  
  230. CREATE TABLE CLISHOP
  231. ( ClientID INT not null
  232. , ShopID INT not null
  233. , CONSTRAINT PK_CLISHO PRIMARY KEY(ClientID, ShopID)
  234. , CONSTRAINT FK_CLISHO FOREIGN KEY (ClientID) REFERENCES Clients(ClientID)
  235. , CONSTRAINT FK_CLISHO2 FOREIGN KEY (ShopID) REFERENCES Shops(ShopID)
  236. );
  237.  
  238. CREATE TABLE PREOFFPRO
  239. ( OfferCode INT not null
  240. , ClientID INT not null
  241. , UPC INT not null
  242. , DatePreoffpro DATE not null DEFAULT getdate()
  243. , CONSTRAINT PK_PREOFFPRO PRIMARY KEY (OfferCode, ClientID, UPC, DatePreoffpro)
  244. , CONSTRAINT FK_PREOFFPRO FOREIGN KEY (OfferCode) REFERENCES OFFERS(OfferCode)
  245. , CONSTRAINT FK_PREOFFPRO2 FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
  246. , CONSTRAINT FK_PREOFFPRO3 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
  247. );
  248.  
  249. CREATE TABLE INCOMES
  250. ( DateIncomes DATE not null DEFAULT getdate()
  251. , ShopID INT not null
  252. , IncomeName VARCHAR
  253. , Quantity INT
  254. , CONSTRAINT PK_INCOMES PRIMARY KEY (DateIncomes, ShopID)
  255. , CONSTRAINT FK_INCOMES FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
  256. );
  257.  
  258. CREATE TABLE TYPE
  259. ( DepartmentCode INT not null
  260. , UPC INT not null
  261. , DependentSSN INT not null
  262. , CONSTRAINT PK_TYPE PRIMARY KEY(DepartmentCode, UPC, DependentSSN)
  263. , CONSTRAINT FK_TYPE FOREIGN KEY (DepartmentCode) REFERENCES DEPARTMENTS(DepartmentCode)
  264. , CONSTRAINT FK_TYPE2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
  265. , CONSTRAINT FK_TYPE3 FOREIGN KEY (DependentSSN) REFERENCES DEPENDENTEMP(DependentSSN)
  266. );
  267.  
  268. CREATE TABLE CLIPRO
  269. ( ClientID INT not null
  270. , UPC INT not null
  271. , DateCliPro DATE not null DEFAULT getdate()
  272. , CONSTRAINT PK_CLIPRO PRIMARY KEY (ClientID, UPC, DateCliPro)
  273. , CONSTRAINT FK_CLIPRO FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
  274. , CONSTRAINT FK_CLIPRO2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
  275. );
  276.  
  277. CREATE TABLE COMPLAINTS
  278. ( CustomerSupporterSSN INT not null
  279. , ClientID INT not null
  280. , ComplaintID INT not null
  281. , Date_comp DATE not null DEFAULT getdate()
  282. , Desc_complaints VARCHAR (250)
  283. , Resolved CHAR CHECK (Resolved IN ('Y','N'))
  284. , CONSTRAINT PK_COMPLAINTS PRIMARY KEY(CustomerSupporterSSN,ClientID,Date_comp)
  285. , CONSTRAINT FK_COMPLAINTS FOREIGN KEY (CustomerSupporterSSN) REFERENCES CUSTOMERSUPPORTER(CusSuppSSN)
  286. , CONSTRAINT FK_COMPLAINTS2 FOREIGN KEY (ClientID) REFERENCES CLIENTS(ClientID)
  287. );
  288.  
  289.  
  290. CREATE TABLE INVENTORY
  291. ( Quantity INT not null
  292. , ShopID INT not null
  293. , UPC INT not null
  294. , DateIn DATE DEFAULT getdate()
  295. , CONSTRAINT PK_INVENTORY PRIMARY KEY(Quantity,ShopID)
  296. , CONSTRAINT FK_INVENTORY FOREIGN KEY (ShopID) REFERENCES SHOPS(ShopID)
  297. , CONSTRAINT FK_INVENTORY2 FOREIGN KEY (UPC) REFERENCES PRODUCTS(UPC)
  298. )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top