1. /* This is here because I've been doing the following to check for errors:
2. ​
3. mysql -u root -p < file.sql */
4. drop database `shop`;
5. ​
6. create schema if not exists `shop` default character set utf8;
7. ​
8. /* Use it. */
9. use `shop`;
10. ​
11. /* Product-category table. */
12. create table if not exists `category`
13. (
14. `key` int not null auto_increment,
15. `name` varchar (80) not null,
16. `num_products` int null, /* number of products in stock under this category. */
17. primary key (`key`)
18. );
19. ​
20. /* Product sub-category table. */
21. create table if not exists `sub_category`
22. (
23. `key` int not null auto_increment,
24. `name` varchar (80) not null,
25. `num_products` int, /* number of products in stock under this sub-category. */
26. `category_key` int, /* The category this sub-category belongs to. */
27. primary key (`key`),
28. foreign key (`category_key`) references category (`key`)
29. );
30. ​
31. /* Manufacturer table. */
32. create table if not exists `manufacturer`
33. (
34. `key` int not null auto_increment,
35. `name` varchar(40) not null,
36. `products` int, /* Products the shop buys from this manufacturer. */
37. primary key (`key`)
38. );
39. ​
40. /* Intermediate table, because I'd like to know the sub-categories that apply
41. to each manufacturer. */
42. create table if not exists `sub_categories_per_manufacturer`
43. (
44. `key` int not null auto_increment,
45. `sub_category_key` int,
46. `manufacturer_key` int,
47. ​
48. /* Number of products in stock from a given manufacturer/sub-category pair. */
49. `num_products` int,
50. ​
51. primary key (`key`),
52. foreign key (`sub_category_key`) references sub_category (`key`),
53. foreign key (`manufacturer_key`) references manufacturer (`key`)
54. );
55. ​
56. /* memory-series table. */
57. create table if not exists `memory_series`
58. (
59. `key` int not null auto_increment,
60. `name` varchar(40) not null,
61. `count` int, /* Number of products in stock. */
62. primary key (`key`)
63. );
64. ​
65. /* memory-technology table. */
66. create table if not exists `memory_technology`
67. (
68. `key` int not null auto_increment,
69. `name` varchar(40) not null,
70. `count` int, /* Number of products in stock. */
71. primary key (`key`)
72. );
73. ​
74. /* memory-size table. */
75. create table if not exists `memory_size`
76. (
77. `key` int not null auto_increment,
78. `name` varchar(40) not null,
79. `count` int, /* Number of products in stock. */
80. primary key (`key`)
81. );
82. ​
83. /* memory-organization table. */
84. create table if not exists `memory_organization`
85. (
86. `key` int not null auto_increment,
87. `name` varchar(40) not null,
88. `count` int, /* Number of products in stock. */
89. primary key (`key`)
90. );
91. ​
92. /* memory-interface table. */
93. create table if not exists `memory_interface`
94. (
95. `key` int not null auto_increment,
96. `name` varchar(40) not null,
97. `count` int, /* Number of products in stock. */
98. primary key (`key`)
99. );
100. ​
101. /* memory-frequency table. */
102. create table if not exists `memory_frequency`
103. (
104. `key` int not null auto_increment,
105. `name` varchar(40) not null,
106. `count` int, /* Number of products in stock. */
107. primary key (`key`)
108. );
109. ​
110. /* memory-write-cycle-time table. */
111. create table if not exists `memory_write_cycle_time`
112. (
113. `key` int not null auto_increment,
114. `name` varchar(40) not null,
115. `count` int, /* Number of products in stock. */
116. primary key (`key`)
117. );
118. ​
119. /* memory-access-time table. */
120. create table if not exists `memory_access_time`
121. (
122. `key` int not null auto_increment,
123. `name` varchar(40) not null,
124. `count` int, /* Number of products in stock. */
125. primary key (`key`)
126. );
127. ​
128. /* memory-voltage-supply table. */
129. create table if not exists `memory_voltage_supply`
130. (
131. `key` int not null auto_increment,
132. `name` varchar(40) not null,
133. `count` int, /* Number of products in stock. */
134. primary key (`key`)
135. );
136. ​
137. /* memory-operating-temperature table. */
138. create table if not exists `memory_operating_temperature`
139. (
140. `key` int not null auto_increment,
141. `name` varchar(40) not null,
142. `count` int, /* Number of products in stock. */
143. primary key (`key`)
144. );
145. ​
146. /* memory-mounting-type table. */
147. create table if not exists `memory_mounting_type`
148. (
149. `key` int not null auto_increment,
150. `name` varchar(40) not null,
151. `count` int, /* Number of products in stock. */
152. primary key (`key`)
153. );
154. ​
155. /* memory-package table.
156. ​
157. Not sure if this should be done as foreign keys into an `ic-package' table. */
158. create table if not exists `memory_package`
159. (
160. `key` int not null auto_increment,
161. `name` varchar(80) not null,
162. `count` int, /* Number of products in stock. */
163. primary key (`key`)
164. );
165. ​
166. /* memory-eeprom table. */
167. create table if not exists `memory_eeprom`
168. (
169. `key` int not null auto_increment,
170. ​
171. /*
172. * Part info.
173. */
174. ​
175. /* Arbritrary string assigned by us, giving such product's
176. part-number. */
177. `part_number` varchar(40) not null,
178. ​
179. /* Arbitrary integer assigned by the manufacturer, describing such product's
180. number. */
181. `manufacturer_part_number` varchar(40) not null,
182. ​
183. /* An excerpt of product's name, part-number, and most notable features. */
184. `description` varchar(40) not null,
185. ​
186. /* Long version of the above. */
187. `detailed_description` varchar(80) not null,
188. ​
189. /*
190. * Media options
191. */
192. ​
193. /* Product's image. */
194. `picture` mediumblob,
195. ​
196. /* Product's datasheet. */
197. `data_sheet` mediumblob,
198. ​
199. /* Some CAD model. */
200. `eda_model` mediumblob,
201. ​
202. /*
203. * Environmental options
204. */
205. ​
206. /* Whether this product is ROHS compilant. */
207. `rohs_compilant_p` boolean,
208. ​
209. /* Classified moisture-sensivity level. */
210. `moisture_sensitivity_level` char,
211. ​
212. /* I don't know what these stand for. */
213. `reach_status` varchar(40),
214. `eccn` varchar(40),
215. `htsus` varchar(40),
216. ​
217. /* Manufacturer or supplier's lead time for this product, in weeks. */
218. `lead_time` int,
219. ​
220. /* Bulk packaging, in units */
221. `standard_packaging` int,
222. ​
223. /*
224. * Stocking stuff
225. */
226. `stock_type` enum ('in-stock', 'normally-stocking', 'new-product'),
227. `quantity_available` int unsigned,
228. ​
229. /* Unitary price */
230. `price` decimal (10, 6),
231. ​
232. /* Foreign key into the `manufacturer's table. */
233. `manufacturer` int,
234. ​
235. /* Foreign key into the `memory-series' table. */
236. `series` int,
237. ​
238. /* Product status. */
239. `product_status` enum ('active', 'not-for-new-designs', 'obsolete', \
240. 'discontinued-by-us'),
241. ​
242. /* Foreign key into the `memory-technology' table. */
243. `technology` int,
244. ​
245. /* Foregin key into the `memory-size' table. */
246. `memory_size` int,
247. ​
248. /* Foreign key into the `memory-organization' table. */
249. `memory_organization` int,
250. ​
251. /* Foregin key into the `memory-interface' table. */
252. `memory_interface` int,
253. ​
254. /* Foreign key into the `memory-frequency' table. */
255. `clock_frequency` int,
256. ​
257. /* Foreign key into the `memory-write-cycle-time' table. */
258. `write_cycle_time` int,
259. ​
260. /* Foreign key into the `memory-access-time' table. */
261. `access_time` int,
262. ​
263. /* Foreign key into the `memory-voltage-supply'. */
264. `voltage_supply` int,
265. ​
266. /* Foreign key into the `memory-operating-temperature' table. */
267. `operating_temperature` int,
268. ​
269. /* Foreign key into the `memory-mounting-type' table. */
270. `mounting_type` int,
271. ​
272. /* Foreign key into the `ic-package' table. */
273. `package` int,
274. ​
275. /* Key definitions. */
276. primary key (`key`),
277. ​
278. foreign key (`manufacturer`) references manufacturer (`key`),
279. foreign key (`series`) references memory_series (`key`),
280. foreign key (`technology`) references memory_technology (`key`),
281. foreign key (`memory_size`) references memory_size (`key`),
282. foreign key (`memory_organization`) references memory_organization (`key`),
283. foreign key (`memory_interface`) references memory_interface (`key`),
284. foreign key (`clock_frequency`) references memory_frequency (`key`),
285. foreign key (`write_cycle_time`) references memory_write_cycle_time (`key`),
286. foreign key (`access_time`) references memory_access_time (`key`),
287. foreign key (`voltage_supply`) references memory_voltage_supply (`key`),
288. foreign key (`operating_temperature`) references memory_operating_temperature (`key`),
289. foreign key (`mounting_type`) references memory_mounting_type (`key`),
290. foreign key (`package`) references memory_package (`key`)
291. );