Vorschlag Datenbanksysteme für TI - Beuth Hochschule für Technik

Datenbanksysteme für TI
INHALT
Einführung
Architektur, Datenbankverbund
Benutzung
Konzepte/Begriffe
Datenbanklebenszyklus
Datenbankentwurf
Access als Entwicklungssystem für Anwendungen
mit Oberfläche, Programme, Datenbank
Relationales Datenmodell/SQL
Entwicklung
SQL86, SQL89, SQL92; SQL99
Standardbenutzeroberflächen
Datenbankprogrammierung lokal
GUI
Visualisierung
Datenbankprogrammierung im Internet
GUI mit HTML und JSP
TFH Berlin/Steyer
DBSTI
Ablauf SS 2007
+02.04.: Organisatorisches, Inhalt, Leistung
+09.04.: frei (Ostermontag)
+16.04.: Datenbankentwurf/Normalisierung/Aufgabe1
+23.04.: Datenbank-Architektur
+30.04.: frei (vor 1.5.)
+07.05.: Datenbank-Konzepte, Access-Anfang/Aufgabe2
-14.05.: Access: Datenbanken, Tabellen, Abfragen
-21.05.: Access: Formulare, Berichte, Makros, Module /Aufgabe3
-28.05.: frei (Pfingstmontag)
-04.06.: SQL in Access, einfaches SQL
-11.06.: komplexes SQL /Aufgabe4
-18.06.: Java-Anwendungsprogrammierung/GUI
-25.06.: Java-Anwendungsprogrammierung/Aufgabe5
-02.07.: Datenbank und Java im Internet
-09.07.: Klausurvorbereitung
-16.07.: Klausur
-23.07.: Klausurrückgabe
LEISTUNGEN
Vorübung: einloggen unter Windows, Access
Aufgabe1: Datenbankentwurf
Aufgabe2: Access-Anwendungssystem
Aufgabe3: einfaches SQL
Aufgabe4: komplexes SQL
Aufgabe5: Java-GUI-Anwendungsprogramm (Kontoabfrage)
Klausur
Jede Aufgabe ergibt maximal 5 Punkte. (6*5 = 30 Punkte)
Diue Klausur ergibt maximal 70 Punkte.
100 Punkte ergeben 1.0
51 Punkte ergeben 4.0
TFH Berlin/Steyer
TEILNEHMER DBS-TI SS2007
Name
Mat-Nr
Albrecht
Baumann
Becker
Berg
Choinka
736580
734861
734143
736845
740920
Dittwald
Eckstein
Erler
Essien
Gluszczynski
733626
736573
732207
735105
736807
Hampel
Haselbach
Havemann
Kranz
Korican
735100
733531
735123
736781
736174
Lamine
Lüpnitz
Miemietz
Naperkowski
Nguyen
736743
737812
744366
736760
732215
Paasch
Pries
Rosin
Schumann
Sidykh
734343
737803
736797
732303
740962
Siedler
Stapelfeld
Tsavdaridis
Zawadski
732295
732184
734785
732257
TFH Berlin/Steyer
Auf1 Auf2 Auf3 Auf4 Auf5 Auf6 SumAuf Klausur SumGes Note
Vorübung
- in XP einloggen
- in Access: Datenbank, Tabelle, Abfrage, SQL-Kommando-Anzeige.
- konkreter evtl. Access-Anwendungssystem mit Tabelle, Formular, Navigation
TFH Berlin/Steyer
Aufgabe 1: Datenbankentwurf
Erstellen Sie Entity-Relationship-Diagramme mit Objekten, einigen Attributen und Beziehungen für
folgende Weltausschnitte:
1 Reisebüro: Kunden buchen Hotels. Die Hotels haben Adressen.
2 Einzelhandel: Lieferanten liefern Artikel an Filialen.
3 Immobilien: Firmen planen, bauen, verkaufen, vermieten Häuser.
Entwerfen Sie mögliche Tabellen.
TFH Berlin/Steyer
Lösungen zu Aufgabe 1:
1 Reisebüro:
Kunde
Buchung
Hotel
Adressen
2 Einzelhandel:
Lieferant
Artikel
liefert
Filiale
3 Immobilien:
Firmen
planen
bauen
verkaufen
vermieten
create table kunde (knr integer, kname char(20))
create table buchung (bnr integer, knr integer, hnr integer)
create table hotel (hnr integer, hname char(20))
create table lieferant (lnr integer, lname char(20))
create table artikel (anr integer, aname char(20))
create table filiale (fnr integer, fname char(20))
create table liefert (linr integer, lnr integer, anr integer, fnr integer)
create table firmen (fnr integer, fname char(20))
create table häuser (hnr integer, hname char(20))
create table planen (pnr integer, fnr integer, hnr integer)
create table bauen (bnr integer, fnr integer, hnr integer)
create table verkaufen (vknr integer, fnr integer, hnr integer)
create table vermieten (vmnr integer, fnr integer, hnr integer)
TFH Berlin/Steyer
Häuser
Aufgabe 2: Access-Anwendungssystem
- Legen Sie eine Datenbank 'lager' an.
- Erzeugen Sie die Tabelle 'artikel': artikelnr artikelname menge
(Neue Tabelle, kein Primärschlüssel)
- Tragen Sie die Zeilen ein:
11
12
13
14
15
16
17
18
Fahrrad
Lenker
Rahmen
Laufrad
Nabe
Speichen
Felge
Schlauch
5
30
25
3
150
300
45
65
- Erstellen Sie eine Abfrage: alle Spalten ausgeben
nach artikelnr aufsteigend sortieren
keine Kriterien
- Erstellen Sie eine Abfrage: artikelname und menge ausgeben
nach menge absteigend sortieren
Kriterium >=5
- Formular zu Artikel
(Einspaltige Darstellung, alle Felder)
- Formular zu Artikel
(Tabellarische Darstellung, Felder artikelname menge)
- Bericht zu Artikel
(Einspaltige Darstellung, alle Felder, Sortierung nach artikelname)
- Bericht zu Artikel
(Tabellarische Darstellung, artikelname, menge, sortiert nach artikelname)
- Erzeugen Sie ein Formular "Artikelverwaltung" mit Kontrollelementen (Buttons), mit denen
man die erstellten Abfragen, Formulare und Berichte aufrufen kann.
- Legen Sie über das Formular "Artikelverwaltung" ein weiteres (Titel: "Firma"), das als
Unterpunkte noch "Kundenverwaltung" und "Lieferantenverwaltung" enthält. Geben
Sie für die noch nicht realisierten Zweige vernünftige Meldungen (x) aus.
- Legen Sie über das Formular "Firma" ein Log-in-Formular, das Benutzername und Passwort abfragt und bei
Button-Klick ihr Vorhandensein in einer Tabelle „User“ überprüft. Wenn ja, wird das Formular „Firma“ geöffnet,
sonst erscheint eine Fehlermeldung.
Login:
Firma:
Artikelverwaltung
Kundenverwaltung
X
TFH Berlin/Steyer
Lieferantenverwaltung
X
Aufgabe 3: Einfaches SQL
Tabelle/View/einfügen/ändern/löschen/lesen
- Erzeugen Sie eine Datenbank (z.B. mit Ihrem Namen), die Sie ab jetzt benutzen und immer wieder auswählen.
-
Erzeugen Sie dort die Tabelle 'kunde': kundennr nachname vorname kalter Tragen Sie die Zeilen ein:
21
22
23
24
25
-
Karl
Anna
Fritz
Monika
Hans
22
35
47
32
22
Erzeugen Sie die Tabelle 'artikel': artikelnr, artikelname, menge, nettopreis Tragen Sie die Zeilen ein:
11
12
13
14
-
Meier
Müller
Schulze
Lehmann
Schiller
Fahrrad
Lenker
Rahmen
Laufrad
20
30
25
45
400.00
70.00
120.00
50.50
Erzeugen Sie die Tabelle 'bestellt': kundennr, artikelnr, menge Tragen Sie die Zeilen ein:
Bestellungen soll es nur von existierenden Kunden für existierende Artikel geben.
21
22
22
25
12
12
13
14
4
7
12
20
- Erstellen Sie eine Abfrage zu kunde: vorname und nachname ausgeben
nach nachname absteigend sortieren, kalter zwischen 30 und 50
- Erstellen Sie eine Abfrage zu artikel: artikelname und menge ausgeben
nach menge absteigend sortieren, nur die Artikel mit der Menge < 40
- Lagerwert: Summe von Menge*Nettopreis für alle Zeilen der Artikeltabelle
- Join: Wer bestellt was (mit Kunden- und Artikelname, Menge).
- Die Preise für alle Artikel werden verdoppelt.
- Die Bestellung der Rahmen durch Müller wird gelöscht.
- Es wird eine View v1 angelegt mit einer neuen Spalte (nettopreis mit 16 % Aufschlag), nur die Spalten
artikelname (neue Überschrift: Artikel) und bruttopreis > 100 (neue Überschrift: Verkaufspreis)
TFH Berlin/Steyer
Artikel
Verkaufspreis
Fahrrad
Rahmen
464.00
139.20
Lösungen zu Aufgabe 3:
create table kunde (kundennr integer, nachname char(12), vorname char(12), kalter integer)
insert into kunde values (21, 'Meier', 'Karl', 22)
insert into kunde values (22, 'Müller', 'Anna', 35)
insert into kunde values (23, 'Schulze', 'Fritz', 47)
insert into kunde values (24, 'Lehmann', 'Monika', 32)
insert into kunde values (25, 'Schiller', 'Hans', 22)
create table artikel (artikelnr integer, artikelname char(12), menge integer, nettopreis currency)
insert into artikel values (11, 'Fahrrad', 20, 400.00)
insert into artikel values (12, 'Lenker', 30, 70.00)
insert into artikel values (13, 'Rahmen', 25, 120.00)
insert into artikel values (14, 'Laufrad', 45, 50.50)
create table bestellt (kundennr integer, artikelnr integer, menge integer)
insert into bestellt values (21, 12, 4)
insert into bestellt values (22, 12, 7)
insert into bestellt values (22, 13, 12)
insert into bestellt values (25, 14, 20)
select * from kunde
select * from artikel
select * from bestellt
select vorname, nachname
from kunde
where kalter between 30 and 50
order by nachname desc
select artikelname, menge from artikel
where menge < 40
order by menge desc
select sum (menge* nettopreis)
from artikel
select nachname, artikelname
from kunde, bestellt, artikel
where kunde.kundennr = bestellt.kundennr and
bestellt.artikelnr = artikel.artikelnr
update artikel set nettopreis = nettopreis*2;
delete from bestellt where kundennr = (select kundennr from kunde where nachname = 'Müller') and artikelnr =
(select artikelnr from artikel where artikelname = 'Rahmen');
create view v1 (Artikel,Verkaufspreis) as select artikelname, nettopreis*1.16 from artikel where nettopreis*1.16 >
100;
TFH Berlin/Steyer
Aufgabe 4: Komplexes SQL
Prozeduren/Trigger/materialisierte View
- Den letzten Befehl der Aufgabe 3 (Wer bestellt was (mit Kunden- und Artikelname, Menge)).
in eine (parameterlose) Prozedur verpacken (p1)
- Parameterlose Prozedur, die für jede Artikelnr die Menge verdoppelt (p2)
- Prozedur mit einem Parameter, die für alle Artikel den Nettopreis um einen bestimmten Prozentsatz erhöht (p3)
Erzeugen Sie einen Trigger zu der obigen Artikeltabelle, der beim INSERT ausser dem Tabelleneintrag noch
eine Zahl in der Tabelle „insertzähler“ hochzählt. Beginn ist bei 0.
Tragen Sie die folgenden Beispielzeilen ein.
15
16
17
18
Nabe
Speichen
Felge
Schlauch
150
300
45
65
12
3
24
8
Was steht in „insertzähler“ ?
View und materialisierte View:
- 1.Fall: Erzeugen Sie eine Sicht "Artikelbrutto1" mit allen Artikelspalten und der virtuellen Spalte "Mehrwertsteuer"
auf dem SQL Server.
- 2.Fall: Erzeugen Sie aus dieser Sicht eine Tabelle "Artikelbrutto2" mit allen Artikelspalten und der virtuellen Spalte
"Mehrwertsteuer" auf dem SQL Server.
Was muss im ersten Fall getan werden, wenn ein neuer Artikel hinzukommt ?
Schreiben Sie eine Prozedur (p4) für den 2. Fall, die die materialisierte View aktualisiert.
TFH Berlin/Steyer
Lösungen zu Aufgabe 4:
create procedure p1 as
select nachname, artikelname
from kunde, bestellt, artikel
where kunde.kundennr = bestellt.kundennr and
bestellt.artikelnr = artikel.artikelnr
create procedure p2 as
update artikel set menge = menge * 2
create procedure p3(@prozentsatz int)
update artikel set nettopreis = nettopreis+nettopreis/100*@prozentsatz;
create table insertzaehler (counter int);
insert into insertzaehler values (0);
create trigger t after insert on artikel for each row update insertzaehler set counter = counter+1;
create view artikelbrutto1 (artikelnr,artikelname,menge,nettopreis,bruttopreis) as
select artikelnr,artikelname,menge,nettopreis,nettopreis*1.16 from artikel;
create table artikelbrutto2 (artikelnr integer,artikelname char(12),menge integer,nettopreis decimal(5,2),bruttopreis
decimal(5,2));
insert into artikelbrutto2 select * from artikelbrutto1;
create procedure p4()
delete from artikelbrutto2
insert into artikelbrutto2 select * from artikelbrutto1
TFH Berlin/Steyer
Aufgabe 5: GUI (Java und Datenbank)
Schreiben Sie ein Javaprogramm, das eine GUI erzeugt und nach Eingabe von Kundenname und PIN den
Kontostand und die dazu addierte Überziehung anzeigt.
Legen Sie dazu in der Datenbank eine entsprechende Tabelle (s.u.) an und richten Sie dafür eine Datasource ein,
die das Javaprogramm benutzt.
TFH Berlin/Steyer
Lösung Aufgabe 5
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class verfuegbarSQLServer extends Frame {
Panel ein = new Panel(new GridLayout(0,2));
TextField nam = new TextField();
TextField pin = new TextField();
TextField kon = new TextField();
TextField ver = new TextField();
Button go;
String drv = "sun.jdbc.odbc.JdbcOdbcDriver";
public verfuegbarSQLServer() {
add("Center", ein);
ein.add(new Label("Name"));
ein.add(nam);
ein.add(new Label("PIN"));
ein.add(pin);
ein.add(new Label("Kontostand"));
ein.add(kon);
ein.add(new Label("verfügbar"));
ein.add(ver);
ein.add(go = new Button("ermittle"));
setSize(400,250);
setVisible(true);
try {
Class.forName(drv);
}
catch (Exception ex) {ex.printStackTrace();}
addWindowListener(new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0); }} );
go.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
ermittle();}});
}
private void ermittle() {
try {
Connection c = DriverManager.getConnection("jdbc:odbc:sqlserverdb", "", "");
Statement s =
c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs;
String q = "SELECT kundenname, pin, kontostand, kontostand+überziehung FROM kunde WHERE
kundenname = '" + nam.getText() + "' and pin = '" + pin.getText() + "'";
System.out.println(q);
rs = s.executeQuery(q);
rs.first();
nam.setText(rs.getString("Kundenname"));
pin.setText(rs.getString("PIN"));
kon.setText(rs.getString("Kontostand"));
ver.setText(rs.getString(4));
}
catch (Exception ex) {ex.printStackTrace();}
}
public static void main(String[] args) {
new verfuegbarSQLServer();
}
}
Steyer: Datenbanksysteme für TI
Vorschlag Datenbanksysteme für TI
1. Datenbankgrundlagen
Konzepte/Begriffe (Persistente Datenhaltung, Tabellen, Befehle, Transaktion, View, Benutzer, Datenintegrität,
Mehrbenutzerfähigkeit, Client/Server-Datenbanken)
2. Datenbankentwurf
Datenmodellierung (ER-Modellierung, Normalisierung)
3. Access als Gesamtsystem
Umfang eines Anwendungssystems (Oberfläche, Programme, Datenbank)
4. SQL
Standard-Benutzeroberflächen (SQL-Editor, Formulare, Berichte, Programme)
Zwei konkrete Systeme (mit Characters und Windows)
5. Anwendungsprogrammieren
C/C++ und embedded SQL
ODBC
Internet-Anwendungen
6. speziell für TI
Stücklisten in SQL
Sensoren/Aktoren ansprechen und archivieren
TFH Berlin/Steyer