fork download
copy 
  1. CREATE TABLE customer (
  2. 	c_id CHAR(8) NOT NULL,
  3. 	c_name VARCHAR(128) NOT NULL,
  4. 	c_gender CHAR(1) NOT NULL,
  5. 	CONSTRAINT customer_pkey PRIMARY KEY (c_id)
  6. );
  7.  
  8. CREATE TABLE membership (
  9. 	m_id CHAR(8) NOT NULL,
  10. 	m_telephone VARCHAR(16) NOT NULL,
  11. 	m_alamat VARCHAR(256) NOT NULL,
  12. 	m_start_date DATE NOT NULL,
  13. 	m_expired_dat DATE NOT NULL,
  14. 	m_type VARCHAR(32) NOT NULL,
  15. 	customer_c_id CHAR(8) NOT NULL,
  16. 	CONSTRAINT membership_pkey PRIMARY KEY (m_id),
  17. 	CONSTRAINT mempership_fkey FOREIGN KEY (customer_c_id) REFERENCES customer(c_id)
  18. );
  19.  
  20. CREATE TABLE worker (
  21. 	w_id CHAR(8) NOT NULL,
  22. 	w_name VARCHAR(128) NOT NULL,
  23. 	w_alamat VARCHAR(256) NOT NULL,
  24. 	w_telephone VARCHAR(16) NOT NULL,
  25. 	w_gender CHAR(1) NOT NULL,
  26. 	CONSTRAINT worker_pkey PRIMARY KEY (w_id)
  27. );
  28.  
  29. CREATE TABLE product (
  30. 	p_id CHAR(8) NOT NULL,
  31. 	p_name VARCHAR(128) NOT NULL,
  32. 	p_price DECIMAL(8, 2) NOT NULL,
  33. 	p_stock INT NOT NULL,
  34. 	CONSTRAINT product_pkey PRIMARY KEY (p_id)
  35. );
  36.  
  37. CREATE TABLE personal_trainer (
  38. 	pt_id CHAR(8) NOT NULL,
  39. 	pt_name VARCHAR(128) NOT NULL,
  40. 	pt_alamat VARCHAR(258) NOT NULL,
  41. 	pt_telephone VARCHAR(16) NOT NULL,
  42. 	pt_gender CHAR(1) NOT NULL,
  43. 	pt_price_per_hour DECIMAL(10, 2) NOT NULL,
  44. 	CONSTRAINT personal_trainer_pkey PRIMARY KEY (pt_id)
  45. );
  46.  
  47. CREATE TABLE receipt (
  48. 	r_id CHAR(12) NOT NULL,
  49. 	r_type VARCHAR(64) NOT NULL,
  50. 	r_date TIMESTAMP NOT NULL,
  51. 	r_final_price DECIMAL(10, 2) NOT NULL,
  52. 	c_id CHAR(8) NOT NULL,
  53. 	w_id CHAR(8) NOT NULL,
  54. 	CONSTRAINT receipt_pkey PRIMARY KEY (r_id),
  55. 	CONSTRAINT receipt_fkey_c FOREIGN KEY (c_id) REFERENCES customer (c_id),
  56. 	CONSTRAINT receipt_fkey_w FOREIGN KEY (w_id) REFERENCES worker (w_id)
  57. );
  58.  
  59. CREATE TABLE receipt_product (
  60. 	r_id CHAR(12) NOT NULL,
  61. 	p_id CHAR(8) NOT NULL,
  62. 	rp_amount INT NOT NULL,
  63. 	rp_discount INT NOT NULL,
  64. 	CONSTRAINT receipt_product_pkey PRIMARY KEY (r_id, p_id),
  65. 	CONSTRAINT receipt_product_fkey_r FOREIGN KEY (r_id) REFERENCES receipt (r_id),
  66. 	CONSTRAINT receipt_product_fkey_p FOREIGN KEY (p_id) REFERENCES product (p_id)
  67. );
  68.  
  69. CREATE TABLE personal_trainer_receipt (
  70. 	pt_id CHAR(8) NOT NULL,
  71. 	r_id CHAR(12) NOT NULL,
  72. 	ptr_price_per_hour INT NOT NULL,
  73. 	ptr_hour_amount INT NOT NULL,
  74. 	ptr_discount INT NOT NULL,
  75. 	CONSTRAINT personal_trainer_receipt_pkey PRIMARY KEY (pt_id, r_id),
  76. 	CONSTRAINT personal_trainer_receipt_fkey_pt FOREIGN KEY (pt_id) REFERENCES personal_trainer (pt_id),
  77. 	CONSTRAINT personal_trainer_receipt_fkey_r FOREIGN KEY (r_id) REFERENCES receipt (r_id)
  78. );
  79.  
  80. CREATE TABLE training_session (
  81. 	ts_id CHAR(10) NOT NULL,
  82. 	ts_start_time TIMESTAMP NOT NULL,
  83. 	ts_end_time TIMESTAMP NOT NULL,
  84. 	c_id CHAR(8) NOT NULL,
  85. 	CONSTRAINT training_session_pkey PRIMARY KEY (ts_id),
  86. 	CONSTRAINT training_session_fkey FOREIGN KEY (c_id) REFERENCES customer (c_id)
  87. );
  88.  
  89. CREATE TABLE training_session_personal_trainer (
  90. 	ts_id CHAR(10) NOT NULL,
  91. 	pt_id CHAR(8) NOT NULL,
  92. 	CONSTRAINT training_session_personal_trainer_pkey PRIMARY KEY (ts_id, pt_id),
  93. 	CONSTRAINT training_session_personal_trainer_fkey_ts FOREIGN KEY (ts_id) REFERENCES training_session (ts_id),
  94. 	CONSTRAINT training_session_personal_trainer_fkey_pt FOREIGN KEY (pt_id) REFERENCES personal_trainer (pt_id)
  95. );
Success #stdin #stdout 0.01s 5288KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
https://ideone.com/EgHa8R
language:
SQLite (sqlite 3.27.2)
created:
1 day ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!