SHARE
TWEET

db alt

a guest Nov 12th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- data definition language
  2. -- sql stub
  3. -- wird erweitert am 31.10.
  4.  
  5. -- Tabellen löschen zu Beginn des Skripts
  6. -- beachten Sie die Lösch-Reihenfolge
  7.  
  8.  
  9. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Gast]') AND type in (N'U'))
  10. DROP TABLE [Gast]
  11.  
  12. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt_hat_Zutat]') AND type in (N'U'))
  13. DROP TABLE [Produkt_hat_Zutat]
  14.  
  15. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer_hat_Rechte]') AND type in (N'U'))
  16. DROP TABLE [BE-Nutzer_hat_Rechte]
  17.  
  18. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung_Schreiben]') AND type in (N'U'))
  19. DROP TABLE [Bewertung_Schreiben]
  20.  
  21. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer_Freund]') AND type in (N'U'))
  22. DROP TABLE [FE-Nutzer_Freund]
  23.  
  24. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Student]') AND type in (N'U'))
  25. DROP TABLE [Student]
  26.  
  27. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Mitarbeiter]') AND type in (N'U'))
  28. DROP TABLE [Mitarbeiter]
  29.  
  30. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FH-Angehörige]') AND type in (N'U'))
  31. DROP TABLE [FH-Angehörige]
  32.  
  33. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zutat]') AND type in (N'U'))
  34. DROP TABLE [Zutat]
  35.  
  36. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Rechte]') AND type in (N'U'))
  37. DROP TABLE [Rechte]
  38.  
  39. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kategorie]') AND type in (N'U'))
  40. DROP TABLE [Kategorie]
  41.  
  42. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Kaufen]') AND type in (N'U'))
  43. DROP TABLE [Kaufen]
  44.  
  45. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[FE-Nutzer]') AND type in (N'U'))
  46. DROP TABLE [FE-Nutzer]
  47.  
  48. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Zahlung]') AND type in (N'U'))
  49. DROP TABLE [Zahlung]
  50.  
  51. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Preis]') AND type in (N'U'))
  52. DROP TABLE [Preis]
  53.  
  54. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Produkt]') AND type in (N'U'))
  55. DROP TABLE [Produkt]
  56.  
  57. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bewertung]') AND type in (N'U'))
  58. DROP TABLE [Bewertung]
  59.  
  60. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Bild]') AND type in (N'U'))
  61. DROP TABLE [Bild]
  62.  
  63. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[BE-Nutzer]') AND type in (N'U'))
  64. DROP TABLE [BE-Nutzer]
  65.  
  66. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Auth]') AND type in (N'U'))
  67. DROP TABLE [Auth]
  68.  
  69.  
  70.  
  71. -- Tabellen definieren
  72. --Auth
  73. --Algo, Stretch, Hash, Salt
  74. CREATE TABLE [Auth] (
  75.     ID INT IDENTITY(1,1) PRIMARY KEY,
  76.     Algo VARCHAR(6) NOT NULL CHECK(Algo='Sha1' OR Algo='Sha256'),
  77.     Stretch INT NOT NULL,
  78.     Salt CHAR(32) NOT NULL,
  79.     [Hash] CHAR(24) NOT NULL,
  80.     )
  81.  
  82. -- BE-Nutzer
  83. -- ID, EMail, Lastlogin smalldatetime, Admin
  84. CREATE TABLE [BE-Nutzer](
  85.     ID INT IDENTITY (1,1) PRIMARY KEY,
  86.     EMail VARCHAR(50) NOT NULL,
  87.     Lastlogin smalldatetime NOT NULL,
  88.     [Admin] BIT NOT NULL,
  89.     Auth_ID INT NOT NULL REFERENCES Auth(ID),
  90.     )
  91.  
  92. -- Rechte
  93. -- ID, Tabelle
  94. CREATE TABLE [Rechte](
  95.     ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  96.     Tabelle VARCHAR(5000) NOT NULL CHECK (Tabelle='Kategorie' OR Tabelle='Produkt' OR Tabelle='Bild' OR Tabelle='Preis' OR Tabelle='Zutat'),
  97.     )
  98.  
  99. -- Bewertung
  100. -- ID, Note, Sichtungen, Bemerkungen
  101. CREATE TABLE [Bewertung](
  102.     ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  103.     Note INT NOT NULL,
  104.     Sichtungen INT NOT NULL,
  105.     Bemerkungen VARCHAR(250) NOT NULL,
  106.     [BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
  107.     )
  108.  
  109. -- Bild
  110. -- ID, Binärdaten, AltText, Title, Unterschrift
  111. CREATE TABLE [Bild](
  112.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  113.     Binärdaten [VARBINARY](MAX) NOT NULL,
  114.     AltText VARCHAR(50) NOT NULL,
  115.     Title VARCHAR(50) NOT NULL,
  116.     Unterschrift VARCHAR(50),
  117.     )
  118.  
  119. -- Kategorie
  120. -- ID, Bezeichnung
  121. CREATE TABLE [Kategorie](
  122.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  123.     Bezeichnung VARCHAR(50),
  124.     Bild_ID INT NOT NULL REFERENCES BILD (ID),
  125.     Oberkategorie INT NOT NULL REFERENCES Kategorie(ID),
  126.     )
  127. -- Produkte
  128. -- ID, Kategorie_ID-FK, Name, Beschreibung, von timestamp, bis timestamp
  129.  
  130. CREATE TABLE [Produkt] (
  131.     ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  132.     Name VARCHAR(50) NOT NULL,
  133.     Beschreibung VARCHAR(50) NOT NULL,
  134.     von DATETIME NOT NULL,
  135.     bis DATETIME NOT NULL,
  136.     Bild_ID INT NOT NULL REFERENCES Bild(ID),
  137. )
  138.  
  139. -- Zutat
  140. -- ID, Bezeichnung
  141. CREATE TABLE [Zutat](
  142.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  143.     Bezeichnung VARCHAR(50) NOT NULL,
  144.     )
  145. -- Preis
  146. -- ID, Rolle, Preis
  147. CREATE TABLE [Preis](
  148.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  149.     Rolle VARCHAR(50) NOT NULL CHECK(Rolle='Gast' OR Rolle='Student' OR Rolle='Mitarbeiter'),
  150.     Preis INT NOT NULL,
  151.     Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
  152.     )
  153.  
  154. -- Zahlung
  155. -- ID, Status, Zeitstempel, Betrag
  156. CREATE TABLE [Zahlung](
  157.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  158.     [Status] VARCHAR(50) NOT NULL CHECK ([Status]>=10 AND [STatus]<=30),
  159.     Zeitstempel TIME NOT NULL,
  160.     Betrag INT NOT NULL,
  161.     AuthServer VARCHAR(50) NOT NULL,
  162.     )
  163.  
  164. -- FE-Nutzer
  165. -- ID, lastlogin smalldatetime, Aktiv??, Auth??
  166. CREATE TABLE [FE-Nutzer](
  167.     Aktiv BIT NOT NULL,
  168.     ID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
  169.     lastlogin smalldatetime NOT NULL,
  170.     Benutzer_ID INT NOT NULL REFERENCES [BE-Nutzer](ID),
  171.     Auth_ID INT NOT NULL REFERENCES Auth(ID),
  172.     )
  173.  
  174. -- FH Angehörige
  175. -- Email, Name, Fachbereich
  176. CREATE TABLE [FH-Angehörige](
  177.     EMail VARCHAR(50) NOT NULL UNIQUE,
  178.     Name VARCHAR(50) NOT NULL,
  179.     Fachbereich INT,
  180.     ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
  181.     )
  182.  
  183. -- Gast
  184. -- Name, von DATETIME, bis DATETIME
  185. CREATE TABLE [Gast](
  186.     Name VARCHAR(50) NOT NULL,
  187.     von DATETIME NOT NULL,
  188.     bis DATETIME NOT NULL,
  189.     ID INT NOT NULL PRIMARY KEY REFERENCES [FE-Nutzer](ID),
  190.     )
  191.  
  192. -- Mitarbeiter
  193. -- MA_Nummer, Büro
  194. CREATE TABLE [Mitarbeiter](
  195.     MA_Nummer INT NOT NULL,
  196.     Büro VARCHAR(50) NOT NULL,
  197.     [FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
  198.     )
  199.  
  200. --Student
  201. -- Matrikelnummer, Studiengang
  202. CREATE TABLE [Student](
  203.     Matrikelnummer INT NOT NULL CHECK(Matrikelnummer>='99999' AND Matrikelnummer <='999999999') UNIQUE,
  204.     Studiengang VARCHAR(50) NOT NULL,
  205.     [FH-Angehörige_ID] INT NOT NULL PRIMARY KEY REFERENCES [FH-Angehörige](ID) ON DELETE CASCADE,
  206.     )
  207.  
  208. --Referenztabellen
  209. CREATE TABLE [BE-Nutzer_hat_Rechte] (
  210.     [BE-Nutzer_ID] INT NOT NULL REFERENCES [BE-Nutzer](ID),
  211.     Rechte_ID INT NOT NULL REFERENCES Rechte(ID),
  212.     )
  213.  
  214. CREATE TABLE [Produkt_hat_Zutat] (
  215.     Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
  216.     Zutat_ID INT NOT NULL REFERENCES Zutat(ID),
  217.     )
  218.  
  219. CREATE TABLE [FE-Nutzer_Freund] (
  220.     ID INT NOT NULL PRIMARY KEY,
  221.     [FE-Nutzer1] INT NOT NULL REFERENCES [FE-Nutzer](ID),
  222.     [FE-Nutzer2] INT NOT NULL REFERENCES [FE-Nutzer](ID),
  223.     )
  224.  
  225. CREATE TABLE [Bewertung_Schreiben] (
  226.     ID INT NOT NULL PRIMARY KEY,
  227.     [FH-Angehörige_ID] INT NOT NULL REFERENCES [FH-Angehörige](ID),
  228.     Bewertung_ID INT NOT NULL REFERENCES Bewertung(ID),
  229.     Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
  230.     )
  231.  
  232. CREATE TABLE [Kaufen] (
  233.     ID INT NOT NULL PRIMARY KEY,
  234.     Endpreis MONEY,
  235.     [FE-Nutzer_ID] INT NOT NULL REFERENCES [FE-Nutzer](ID),
  236.     Produkt_ID INT NOT NULL REFERENCES Produkt(ID),
  237.     Zahlung_ID INT NOT NULL REFERENCES Zahlung(ID),
  238.     )
  239.  
  240. -- Tabellen ändern (fügen Sie hier CHECK und andere Constraints, falls Sie sie nicht bei der Definition verwenden können)
  241.  
  242. -- ALTER TABLE [beispiel] ADD CONSTRAINT [constrname] CHECK (1=1)
  243.  
  244. ALTER TABLE [Zahlung] ADD CONSTRAINT [Status] Check(Status='10' OR Status='20' OR Status='30')
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
 
Top