Datenbankbeschreibung „HardWhere“
Werbung
Datenbankbeschreibung „HardWhere“ AnPr Name Datum Klasse Inhaltsverzeichnis 1 Allgemeines ............................................................................................................................................... 2 2 Ausgangssituation...................................................................................................................................... 2 3 Beschreibung Datenbank ........................................................................................................................... 2 3.1 Datenmodell ...................................................................................................................................... 2 3.2 Tabelle Adresse ................................................................................................................................. 3 3.3 Tabelle Bestellung ............................................................................................................................. 3 3.4 Tabelle BestPos ................................................................................................................................. 3 3.5 Tabelle Branche ................................................................................................................................. 3 3.6 Tabelle Firma..................................................................................................................................... 4 3.7 Tabelle Hersteller .............................................................................................................................. 4 3.8 Tabelle InfoTyp ................................................................................................................................. 4 3.9 Tabelle KontoUms............................................................................................................................. 4 3.10 Tabelle LagerPos ............................................................................................................................... 5 3.11 Tabelle Lieferung .............................................................................................................................. 5 3.12 Tabelle LieferZO ............................................................................................................................... 5 3.13 Tabelle Partner................................................................................................................................... 6 3.14 Tabelle Person ................................................................................................................................... 6 3.15 Tabelle Preis ...................................................................................................................................... 7 3.16 View PreisVonBis ............................................................................................................................. 7 3.17 Tabelle ProdInfo ................................................................................................................................ 7 3.18 Tabelle Produkt ................................................................................................................................. 8 3.19 Tabelle Storno ................................................................................................................................... 8 3.20 Tabelle Telefon .................................................................................................................................. 8 4 Installation ................................................................................................................................................. 8 5 Nutzung ..................................................................................................................................................... 9 6 Lizenz ...................................................................................................................................................... 10 Datennbank_HardWhere_V03.docx Seite 1 Datenbankbeschreibung „HardWhere“ 1 AnPr Allgemeines Die Daten in der Datenbank sind fiktiv und fast ausschließlich nach dem Zufallsprinzip erstellt worden. Jegliche Ähnlichkeiten mit realen Personen sind somit nicht gewollt und eben rein zufällig entstanden. Auch sind die entsprechenden Werte (Beträge etc.) rein beispielhaft und können nicht für reale Firmen als Vergleich dienen. 2 Ausgangssituation Die Firma HardWhere ist im Großhandel für einzelne IT Hardwarekomponenten tätig. Obwohl sie primär Firmen beliefert, gibt es auch vereinzelt Privatkunden in der Datenbank. Die Firma wurde Anfang 2003 gegründet und ist seitdem im Geschäft. Die Umsätze stiegen stetig. Der Datenbankabzug wurde im Februar 2013 gemacht und ist somit ein „Snapshot“ der Firmendatenbank. 3 Beschreibung Datenbank Im Folgenden finden Sie eine grobe Beschreibung der Datenbank. Sehen Sie sich zuerst das Datenmodell an und versuchen Sie das Konzept dahinter zu verstehen. Alles, was Sie daraus nicht eindeutig ersehen können, müssen Sie über die einzelnen Tabellenbeschreibungen herausfinden. Ein Verständnis des Datenmodells – auch aus fachlicher Perspektive – ist die Grundvoraussetzung für funktionierendes SQL! 3.1 Datenmodell *) Für den Zugriff auf „Preis“ wurde noch die View „PreisVonBis“ erstellt. In den Abfragen wird diese wie eine Tabelle genutzt. Seite 2 AnPr Datenbankbeschreibung „HardWhere“ 3.2 Tabelle Adresse Sämtliche Adressinformationen (jedoch ohne Namen) finden sich in dieser Tabelle. Dies beinhaltet sowohl Kunden, Alternative Lieferadressen, Mitarbeiter, Lieferanten und Hersteller. Spaltenname: AdresseID Struktur: INT (Primary Key) Ort Strasse PLZ Bundesland VARCHAR(50) VARCHAR(60) CHAR(5) CHAR(2) Inhalt: Eindeutige ID der Adresse. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Wohnort als Klartext. Straßenname inklusive Hausnummer als Klartext. Deutsche Postleitzahl. Bundeslandkürzel 3.3 Tabelle Bestellung Auflistung aller Bestellungen, welche von den Kunden getätigt wurden. Spaltenname: BestID Struktur: INT (Primary Key) PartnerID INT RechnungID INT Betrag Datum DECIMAL(9,2) DATE Porto DECIMAL(7,2) Inhalt: Eindeutige ID der Bestellung. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf Partner, welcher die Bestellung getätigt hat. Eindeutige ID der Rechnung, welche an den Kunden geschickt wurde. Diese wird im Rahmen der Überweisung referenziert. Bestellwert (abzüglich des Kundenrabatts). Datum, an dem die Bestellung eingegangen ist und die Rechnung zugestellt wurde (Rechnungsdatum) Betrag des Portos, welcher für die Gesamtbestellung zum Zeitpunkt der Bestellung ermittelt wurde. Dieser kann vom tatsächlichen Porto abweichen, sofern die Bestellung in mehreren Chargen versendet werden musste. 3.4 Tabelle BestPos Zuordnung zwischen Bestellung und einzelnem Produkt. Somit können mehrere Produkttypen in einer Bestellung zusammengefaßt werden. Spaltenname: BestPosID Struktur: INT (Primary Key) BestID ProduktID Anzahl INT INT INT Inhalt: Eindeutige ID der Bestellposition. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf die Bestellung. Referenz auf das bestellte Produkt. Angabe, wie oft das einzelne Produkt in der Bestellung vorkommt. 3.5 Tabelle Branche Gegenüberstellung des Branchencodes zur klartextlichen Branchenangabe. Spaltenname: BrancheID Struktur: INT (Primary Key) Bezeichnung VARCHAR(100) Inhalt: Eindeutige ID der Branche. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Klartextliche Bezeichnung der Branche. Seite 3 Datenbankbeschreibung „HardWhere“ AnPr 3.6 Tabelle Firma Allgemeine Angaben für die Firmenkunden. Spaltenname: FirmaID Struktur: INT (Primary Key) Name Rechtsform BranchenID UST_ID AdresseID VARCHAR(30) VARCHAR(5) INT CHAR(10) INT Inhalt: Eindeutige ID der Firma. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Name der Firma. Angabe der Firmenrechtsform (bsp. AG, GmbH) Referenz auf die Branchentabelle Eindeutige UmsatzsteuerID der Firma Referenz auf Adressentabelle – somit die Adresse unter der die Firma agiert (entspricht auch der Lieferadresse) 3.7 Tabelle Hersteller Allgemeine Angaben über den Hersteller – im Prinzip nur der primäre Lieferant des Herstellers. Spaltenname: HerstellerID Struktur: INT (Primary Key) PartnerID INT LieferantID INT Inhalt: Eindeutige ID des Herstellers. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf Partnertabelle – jeder Hersteller wird als Partner geführt. Referenz auf Partnertabelle – jeder Lieferant wird als Partner geführt. 3.8 Tabelle InfoTyp Klartextinformationen über die Produkteigenschaftskategorien. Die verschiedenen Produkte haben Eigenschaften, wobei jede Eigenschaftskategorie als ID in der Tabelle auftaucht. Hier stehen für jede Eigenschaftskategorie die klartextliche Bezeichnung der Eigenschaftskategorie. Spaltenname: InfoTypID Struktur: INT (Primary Key) Name VARCHAR(30) Inhalt: Eindeutige ID des Eigenschaftskategorie. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Name der Eigenschaftskategorie. 3.9 Tabelle KontoUms Umsätze der wichtigsten Arbeitskonten: 19334223: Konto für die eingehenden Zahlungen der Kunden 19334224: Konto für die Abwicklung der Portozahlungen Gewinne, Steuerzahlungen, Miete, Gehaltszahlungen und Sonderaufwände werden auf das Konto 19334225 abgeführt, wobei dieses nicht im Rahmen dieser Datenbank aufgeführt wird. Spaltenname: KontoUmsID Struktur: INT (Primary Key) KontoNr INT KuKontoNr KuBLZ Saldo Umsatz Name INT INT DECIMAL(10,2) DECIMAL(10,2) VARCHAR(40) Seite 4 Inhalt: Eindeutige ID des Kontoumsatzes. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Kontonummer des Arbeitskontos (der Firma HardWhere) Kontonummer des Kunden (bzw. Hersteller etc.) Bankleitzahl des Kunden (bzw. Hersteller etc.) Aktueller Kontostand des entsprechenden Kontos. Betrag des entsprechenden Umsatzes. Name des Kunden (bzw. Hersteller etc.) AnPr Datenbankbeschreibung „HardWhere“ Spaltenname: Datum Text Struktur: DATE VARCHAR(100) RechnungID INT Inhalt: Datum der Buchung. Überweisungstext. Dieser beinhaltet bei Rechnungsüberweisungen auch die RechnungsID. Vom HardWhere System automatisch aus dem Überweisungstext extrahierte RechnungsID. 3.10 Tabelle LagerPos Informationen, wo im Lager wie viele Produkte zu finden sind. Spaltenname: LagerPosID Struktur: INT (Primary Key) ProduktID Anzahl INT INT Inhalt: Eindeutige ID der Lageprosition. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf das entsprechende Produkt. Anzahl des entsprechenden Produkts im Lager. 3.11 Tabelle Lieferung Angabe über die einzelnen Lieferungen. Jede Bestellungen wird über eine oder mehrere Lieferungen zum Kunden versendet. Spaltenname: LieferID Struktur: INT (Primary Key) Lieferdatum AdresseID Vorname DATE INT VARCHAR(30) Nachname VARCHAR(30) Porto DECIMAL(7,2) Inhalt: Eindeutige ID der Lieferung. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Datum, an dem die Lieferung versendet wurde. Referenz auf Lieferadresse. Für den Fall, dass eine alternative Lieferadresse eingetragen wurde, so muss hier noch der entsprechende Vorname des Empfängers eingetragen werden. Für den Fall, dass eine alternative Lieferadresse eingetragen wurde, so muss hier noch der entsprechende Nachname des Empfängers eingetragen werden. Tatsächliches Porto, welches angefallen ist. 3.12 Tabelle LieferZO Zuordnungstabelle zwischen Bestellposition und Lieferung. Hiermit wird sichergestellt, dass die einzelnen Bestellpositionen in getrennten Lieferungen versendet werden können (wenn bspw. ein Produkt nicht auf Lager ist und nachgeliefert werden muss.). Spaltenname: LieferID BestPosID Struktur: INT INT Inhalt: Referenz auf die Lieferung. Referenz auf die Bestellposition Auf diese Tabelle hätte man auch verzichten, und die LieferID in die Bestellposition einbauen können. Dadurch würde aber in der Praxis die Situation entstehen, dass die Bestellpositionen im Bestellvorgang eingetragen werden würden und bei der Lieferung (also zu einem späteren Zeitpunkt) die LieferID erst feststehen würde. Damit würde die Notwendigkeit eines Updates auf der Bestellposition entstehen, was man im Regelfall vermeiden möchte. Seite 5 Datenbankbeschreibung „HardWhere“ AnPr 3.13 Tabelle Partner Informationen über Partner. Diese können aus den verschiedenen Kategorien sein: F: Firmenkunde PK: Privatkunde WK: Wunschkunde HS: Hersteller LF: Lieferant MA: Mitarbeiter der Firma HardWhere Spaltenname: PartnerID Struktur: INT (Primary Key) Kategorie FirmaID CHAR(2) INT PersonID INT BetreuerID INT Datum Rabatt DATE DECIMAL(5,2) Inhalt: Eindeutige ID des Partners. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Kategorie des Partners Wenn der Partner eine Firma ist, steht hier eine Referenz auf die entsprechende Firma in der Firmentabelle. Wenn der Partner eine Person ist, steht hier eine Referenz auf die entsprechende Person in der Personentabelle. Referenz auf die PartnerID des Mitarbeiters, welcher den Partner betreut. Bei Mitarbeitern, welche entsprechend auch in dieser Tabelle geführt sind, ist ein alternativer Ansprechpartner für den Mitarbeiter eingetragen (im Krankheitsfall etc.) Datum, seit wann der Partner im System ist. Angabe, wieviel Standardrabatt der Partner erhält. 3.14 Tabelle Person Sämtliche Personenrelevante Informationen. Personen sind entweder Firmenmitarbeiter, Privatpersonen (also Partner, welche als Privatperson agieren) oder Ansprechpartner von Firmen. Spaltenname: PersonID Struktur: INT (Primary Key) Vorname Nachname Geschlecht GebDatum FirmaID VARCHAR(30) VARCHAR(30) CHAR(1) DATE INT AdresseID INT Seite 6 Inhalt: Eindeutige ID der Person. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Vorname der Person. Nachname der Person. Code m: männlich, w: weiblich Geburtsdatum Wenn Person Ansprechpartner einer Firma ist, findet sich hier die Referenz auf die Firma. Referenz auf die Adresse, unter der die Person postalisch zu erreichen ist. AnPr Datenbankbeschreibung „HardWhere“ 3.15 Tabelle Preis Tabelle, welche sämtlichen Produktpreise über die Historie speichert. Spaltenname: PreisID Struktur: INT (Primary Key) ProduktID INT Preis Datum DECIMAL(7,2) DATE Inhalt: Eindeutige ID der Preisinformation. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf Produkt, für das der Preis angegeben wird. Produktpreis Datum, ab wann der Preis gilt. Sollte also ein weiterer Eintrag in der Tabelle mit jüngerem Datumswert vorliegen, so ist der Eintrag mit dem jüngerem Datum ausschlaggebend. 3.16 View PreisVonBis Um das Handling mit den Datumswerten zu vereinfachen, stellt die Datenbank eine View zur Verfügung, bei der neben dem Datum ab wann der Preis gilt auch das Datum angezeigt wird, bis wann der Preis gilt. Spaltenname: PreisID Struktur: INT (Primary Key) ProduktID INT Preis DatumVon DatumBis DECIMAL(7,2) DATE DATE Inhalt: Eindeutige ID der Preisinformation. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf Produkt, für das der Preis angegeben wird. Produktpreis Datum, ab wann der Preis gilt. Datum, bis wann der Preis gilt, wobei das DatumBis das erste nicht mehr gültige Datum darstellt. Tagesaktuelle Preise haben immer das aktuelle Datum + 1 Tag als Preis. 3.17 Tabelle ProdInfo Aufstellung der einzelnen Produkteigenschaften je Produkt. Spaltenname: ProdInfoID Struktur: INT (Primary Key) ProduktID InfoTypID INT INT Wert VARCHAR(30) Inhalt: Eindeutige ID der Produkteigenschaft. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf das entsprechende Produkt Referenz auf die klartextliche Eigenschaftskategorie (bspw. Anzahl Kerne) Wert der entsprechenden Eigenschaft (bspw. 4) Seite 7 Datenbankbeschreibung „HardWhere“ AnPr 3.18 Tabelle Produkt Allgemeine Informationen für jedes Produkt. Die Produkte sind in folgende Kategorien eingeteilt: CP: CPU FP: Festplatte MM: Speicher MB: Mainboard GC: Grafikkarte Spaltenname: ProduktID Struktur: INT (Primary Key) HerstellerID Bezeichnung Kategorie Gewicht Volumen INT VARCHAR(100) CHAR(2) DECIMAL(5,2) DECIMAL(7,2) Inhalt: Eindeutige ID des Produktes. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf die Herstellertabelle Vertriebsbezeichnung des Produktes Produktkategorie (siehe oben) Gewicht in Gramm Volumen in Kubikzentimeter 3.19 Tabelle Storno Wenn Einzelpositionen einer Bestellung storniert wurden, erhält diese Tabelle einen Eintrag. Spaltenname: StornoID Struktur: INT (Primary Key) BestPosID INT Kosten Datum DECIMAL(9,2) DATE Inhalt: Eindeutige ID der Stornierung. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Referenz auf die Bestellposition, welche storniert wurde. Kosten, welche für die Stornierung aufgetreten sind. Datum, wann die Stornierung eingegangen ist. 3.20 Tabelle Telefon Informationen darüber, wie welche Person per Telefon zu erreichen ist. Spaltenname: TelefonID Struktur: INT (Primary Key) Vorwahl Nummer Hinweis VARCHAR(10) VARCHAR(15) VARCHAR(30) PersonID INT 4 Inhalt: Eindeutige ID der Telefoninfo. Dieser Wert ist rein technisch und hat neben der Eindeutigkeit keine weitere Aussagekraft. Vorwahl des Anschlusses Durchwahl des Anschlusses Hinweis, ob es sich um den primären Anschluss, Handynummer etc. handelt. Referenz auf die entsprechende Person. Installation Um die Datenbank zu installieren, müssen Sie das ZIP File PraxisDB_HardWhere.zip auf Ihrem Rechner entpacken, MySQL starten und unter Windows das Batch File CreateDatabase.bat starten. Hier geben Sie anschließend das MySQL Passwort und danach das Installationsverzeichnis von mysql.exe an. Der Installationsvorgang kann aufgrund der großen Datenmenge durchaus einige Minuten dauern. Anschließend finden Sie auf Ihrer MySQL Installation eine neue Datenbank namens „HardWhere“. Sollten Sie das File PraxisDB_HardWhere.zip nicht haben, so können Sie es unter www.codeconcert.de im Bereich „Unterrichtsmaterial -> SQL“ finden. Seite 8 AnPr 5 Datenbankbeschreibung „HardWhere“ Nutzung Nach Eingabe von „USE HardWhere“ können Sie die SQL Befehle auf der Datenbank ausführen. Bitte beachten Sie aber, dass die Datenbank vergleichsweise viele Daten beinhaltet. Unbedachte SQL Befehle können zu langen Laufzeiten führen. Versuchen Sie also, Ihre SQL Befehle performant zu gestalten. Seite 9 Datenbankbeschreibung „HardWhere“ 6 AnPr Lizenz Diese(s) Werk bzw. Inhalt von Maik Aicher (www.codeconcert.de) steht unter einer Creative Commons Namensnennung - Nicht-kommerziell - Weitergabe unter gleichen Bedingungen 3.0 Unported Lizenz. Seite 10
