j8DuUaYz

1/*
2Created		2012-01-08
3Modified		2012-01-08
4Project		
5Model		
6Company		
7Author		
8Version		
9Database		mySQL 5 
10*/
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28drop table IF EXISTS DCT_Owns;
29drop table IF EXISTS DCT_Decks;
30drop table IF EXISTS KRT_Change;
31drop table IF EXISTS KRT_Owns;
32drop table IF EXISTS UST_Users;
33drop table IF EXISTS CRT_Cards;
34drop table IF EXISTS CRT_Type;
35
36
37
38
39Create table CRT_Type (
40	CRT_ID Int NOT NULL AUTO_INCREMENT,
41	CRT_Name Char(30),
42 Primary Key (CRT_ID)) ENGINE = MyISAM;
43
44Create table CRT_Cards (
45	CRK_ID Char(20) NOT NULL AUTO_INCREMENT,
46	CRK_Name Char(20),
47	CRK_CRT_ID Char(20),
48	KRO_USU_ID Int NOT NULL,
49	USU_ID Char(20) NOT NULL,
50	KRO_USU_ID Int NOT NULL,
51	KRO_CRK_ID Int NOT NULL,
52	USU_ID Char(20) NOT NULL,
53	CRT_ID Int NOT NULL,
54 Primary Key (CRK_ID,KRO_USU_ID,USU_ID,KRO_USU_ID,KRO_CRK_ID,USU_ID,CRT_ID)) ENGINE = MyISAM;
55
56Create table UST_Users (
57	USU_ID Char(20) NOT NULL AUTO_INCREMENT,
58	USU_Nick Char(20),
59	USU_Pass Char(20),
60 Primary Key (USU_ID)) ENGINE = MyISAM;
61
62Create table KRT_Owns (
63	KRO_USU_ID Int NOT NULL,
64	KRO_CRK_ID Int,
65	KRO_Amount Int,
66	USU_ID Char(20) NOT NULL,
67 Primary Key (KRO_USU_ID,USU_ID)) ENGINE = MyISAM;
68
69Create table KRT_Change (
70	KRO_USU_ID Int NOT NULL,
71	KRO_CRK_ID Int NOT NULL,
72	KRO_Amount Int,
73	USU_ID Char(20) NOT NULL,
74 Primary Key (KRO_USU_ID,KRO_CRK_ID,USU_ID)) ENGINE = MyISAM;
75
76Create table DCT_Decks (
77	DCK_ID Int NOT NULL,
78	DCK_CRK_ID Int NOT NULL,
79	DCK_Amount Int,
80 Primary Key (DCK_ID,DCK_CRK_ID)) ENGINE = MyISAM;
81
82Create table DCT_Owns (
83	DCO_ID Int NOT NULL,
84	DCO_DCK_ID Int,
85	DCO_USU_ID Int,
86	USU_ID Char(20) NOT NULL,
87	DCK_ID Int NOT NULL,
88	DCK_CRK_ID Int NOT NULL,
89 Primary Key (DCO_ID,USU_ID,DCK_ID,DCK_CRK_ID)) ENGINE = MyISAM;
90
91
92
93
94
95
96
97
98
99
100
101
102Alter table CRT_Cards add Foreign Key (CRT_ID) references CRT_Type (CRT_ID) on delete  restrict on update  restrict;
103Alter table KRT_Owns add Foreign Key (USU_ID) references UST_Users (USU_ID) on delete  restrict on update  restrict;
104Alter table DCT_Owns add Foreign Key (USU_ID) references UST_Users (USU_ID) on delete  restrict on update  restrict;
105Alter table KRT_Change add Foreign Key (USU_ID) references UST_Users (USU_ID) on delete  restrict on update  restrict;
106Alter table CRT_Cards add Foreign Key (KRO_USU_ID,USU_ID) references KRT_Owns (KRO_USU_ID,USU_ID) on delete  restrict on update  restrict;
107Alter table CRT_Cards add Foreign Key (KRO_USU_ID,KRO_CRK_ID,USU_ID) references KRT_Change (KRO_USU_ID,KRO_CRK_ID,USU_ID) on delete  restrict on update  restrict;
108Alter table DCT_Owns add Foreign Key (DCK_ID,DCK_CRK_ID) references DCT_Decks (DCK_ID,DCK_CRK_ID) on delete  restrict on update  restrict;
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124/* Users permissions */