r99xrSe9

1DROP DATABASE IF EXISTS `it_projekt`;
2CREATE DATABASE IF NOT EXISTS `it_projekt`;
3USE `it_projekt`;
4
5# USER Tabelle anlegen
6 
7CREATE TABLE USER (
8ID INT(10) NOT NULL,
9g_mail VARCHAR(255) NOT NULL,
10PRIMARY KEY(ID)
11);
12
13# BusinessObject Tabelle anlegen
14 
15CREATE TABLE BusinessObject (
16bo_ID INT(10) NOT NULL,
17user_ID INT(10) NOT NULL,
18creationDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
19modificationDate TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
20PRIMARY KEY(bo_ID),
21FOREIGN KEY(user_ID) REFERENCES USER(ID)
22);
23 
24# Contact Tabelle anlegen
25 
26CREATE TABLE Contact (
27ID INT(10) NOT NULL,
28PRIMARY KEY(ID),
29FOREIGN KEY(ID) REFERENCES BusinessObject(bo_ID)
30);
31 
32# ContactList Tabelle anlegen
33 
34CREATE TABLE ContactList (
35ID INT(10) NOT NULL,
36description VARCHAR(255) NOT NULL,
37PRIMARY KEY(ID),
38FOREIGN KEY(ID) REFERENCES BusinessObject(bo_ID)
39);
40 
41# Property Tabelle anlegen
42 
43CREATE TABLE Property (
44ID INT(10) NOT NULL,
45description VARCHAR(255) NOT NULL,
46PRIMARY KEY(ID),
47FOREIGN KEY(ID) REFERENCES BusinessObject(bo_ID)
48);
49 
50# PropertyValue Tabelle anlegen
51 
52CREATE TABLE PropertyValue (
53ID INT(10) NOT NULL,
54contact_ID INT (10) NOT NULL,
55VALUE VARCHAR(255) NOT NULL,
56PRIMARY KEY(ID),
57FOREIGN KEY(contact_ID) REFERENCES property(ID),
58FOREIGN KEY(ID) REFERENCES BusinessObject(bo_ID)
59);
60
61
62 
63# n:m Beziehungstabelle Contact zu Contactlist Variante 1
64 
65 
66# n:m Beziehungstabelle Contact zu Contactlist Variante 2
67 
68CREATE TABLE Contact_ContactList(
69Contact_ID INT (10) NOT NULL,
70ContactList_ID INT (10) NOT NULL,
71PRIMARY KEY(Contact_ID, ContactList_ID),
72FOREIGN KEY(ContactList_ID) REFERENCES ContactList(ID),
73FOREIGN KEY(Contact_ID) REFERENCES Contact(ID)
74);
75 
76# n:m Beziehungstabelle Contact zu PropertyValue Variante 2
77 
78CREATE TABLE Contact_PropertyValue(
79Contact_ID INT (10) NOT NULL,
80PropertyValue_ID INT (10) NOT NULL,
81PRIMARY KEY(Contact_ID, PropertyValue_ID),
82FOREIGN KEY(PropertyValue_ID) REFERENCES propertyValue(ID),
83FOREIGN KEY(Contact_ID) REFERENCES Contact(ID)
84);
85 
86# n:m Beziehungstabelle USER zu BusinessObject Variante 2
87 
88CREATE TABLE User_BusinessObject(
89BusinessObject_ID INT (10) NOT NULL,
90User_ID INT (10) NOT NULL,
91PRIMARY KEY(BusinessObject_ID, User_ID),
92FOREIGN KEY(BusinessObject_ID) REFERENCES BusinessObject(bo_ID),
93FOREIGN KEY(User_ID) REFERENCES USER(ID)
94);