Namen VARCHAR(30) NULL

TA.DMG.H0801
Datenmanagement
SQL Projekt
Gruppe 9
Studenten:
Thomas Stuber
Michael Studer
Modulverantwortlicher:
Thomas Olnhoff
Horw, 18.12.2008
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
1 ER-Diagramm
Seite 2 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
2 Tabellen
2.1 Grundtabellen
-- 06.11.2008 | DMG
--- Gruppe 9 | Michael Studer & Thomas Stuber
-CREATE TABLE Lieferanten
(
LieferantenNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Namen VARCHAR(30) NULL,
Adresse VARCHAR(50) NULL,
BankAccount VARCHAR(30) NULL
);
CREATE TABLE Mitarbeiter
(
MitarbeiterNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Namen VARCHAR(30) NULL,
Gehalt NUMERIC(6,2) NULL,
Geburtstag DATETIME NULL,
Adresse VARCHAR(50) NULL,
BankAccount VARCHAR(30) NULL
);
CREATE TABLE Maschine
(
MaschinenNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Beschreibung VARCHAR(50) NULL,
ServiceInterval INT NULL,
LastServiceMitarbeiterNr INT NULL REFERENCES Mitarbeiter
);
CREATE TABLE Teile
(
TeileNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Beschreibung VARCHAR(50) NULL,
Preis NUMERIC(9,2) NULL,
Groessenangabe INT NULL,
Stock INT NULL,
LieferantenNr INT NULL REFERENCES Lieferanten,
MaschinenNr INT NULL REFERENCES Maschine
);
CREATE TABLE ProduktionsProzess
(
ProduktionsNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Tag DATETIME NULL,
ProduktionsProzess INT NULL REFERENCES ProduktionsProzess,
ReservierteMitarbeiterNr INT NULL REFERENCES Mitarbeiter,
BestehtAusTeilen INT NULL REFERENCES Teile,
AnzTeile INT NULL
);
CREATE TABLE Kundenbestellungen
(
BestellNr INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
KundenNr INT NULL,
Liefertermin DATETIME NULL,
AnzTeile INT NULL,
ProduktionsNr INT NULL REFERENCES ProduktionsProzess
);
CREATE TABLE IsPartOf
(
Oberteil INT NOT NULL,
Unterteil INT NOT NULL,
Amount INT NULL,
FOREIGN KEY (Oberteil) REFERENCES Teile,
FOREIGN KEY (Unterteil) REFERENCES Teile,
PRIMARY KEY (Oberteil, Unterteil)
);
Seite 3 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
CREATE TABLE CanOperate
(
MitarbeiterNr INT NOT NULL,
MaschinenNr INT NOT NULL,
FOREIGN KEY (MitarbeiterNr) REFERENCES Mitarbeiter,
FOREIGN KEY (MaschinenNr) REFERENCES Maschine,
PRIMARY KEY (MitarbeiterNr, MaschinenNr)
);
2.2 Zusatztabelle: Arbeitstage
create table arbeitstag(
tag datetime primary key
)
Go
2.2.1
Füllen der Arbeitstage
CREATE procedure mkTage(@anz int, @start datetime)
BEGIN
DECLARE @i int
SET @i=1
AS
WHILE (@i<=@anz)
BEGIN
if ( datepart(weekday, @start + @i) between 2 and 6)
-- no workday on Saturday and Sunday
insert into arbeitstag(tag) values(@start + @i)
set @i = @i
+ 1
END
END
Go
-- DB-Prozedur ist kreiert. Nachfolgend wir die Tabelle mit Daten gefüllt
execute mkTage 100, {d'2008-12-01'}
go
select * from Arbeitstag
--Kontrolle der Tabelle
Seite 4 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
3 Ablauf
Seite 5 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
4 SQL Prozedur für Produktion
-- Prozedur für das Reservieren aller nötigen Komponenten
create procedure reservation(@teil int, @anz int, @dayDone datetime out, @firstProduction int
out) as
BEGIN
declare @tid int, @tanz int, @tag datetime, @mitarbeiter int,
@pos int, @oldpos int,@oneDay datetime;
declare rs cursor local for
with th(Oberteil, Unterteil, amountO,amountU,pos) as
(select Oberteil ,Unterteil, @anz , Amount, 1 from isPartOf where Oberteil=@teil
union all
select ip.Oberteil,ip.Unterteil, th.amountO* th.amountU,ip.Amount, pos+1 from
(isPartOf ip join th on th.Unterteil=ip.Oberteil) join Teile t on ip.Oberteil =
t.TeileNr
where t.LieferantenNr is NULL)
select Oberteil, amountO, pos from th, Teile t where th.Oberteil=t.TeileNr order by
th.pos desc;
set @firstProduction = null
open rs
fetch next from rs into @tid, @tanz, @pos -- Einmaliger fetch-Durchlauf
if @@FETCH_STATUS <> 0 return -- exit
select @tag= min(tag) from arbeitstag where tag>=current_timestamp -- frühstmöglicher Tag
set @dayDone = @tag
set @oldPos = @pos
WHILE @@FETCH_STATUS = 0
BEGIN
if (@oldPos<>@pos)
begin
set @tag = dateadd(day,1,@dayDone)
set @oldPos = @pos
end
select top 1 @mitarbeiter = c.MitarbeiterNr, @oneDay=a.tag
from arbeitstag a, canOperate c, Teile t
where t.TeileNr = @tid and t.MaschinenNr = c.MaschinenNr and a.tag >= @tag and
not exists (select null from ProduktionsProzess p where p.Tag = a.tag and
(p.BestehtAusTeilen = @tid or p.ReservierteMitarbeiterNr = c.MitarbeiterNr) )
order by a.tag;
if (@oneDay>@dayDone) set @dayDone = @oneDay
insert into ProduktionsProzess(Tag, ProduktionsProzess, ReservierteMitarbeiterNr,
BestehtAusTeilen,
AnzTeile ) values(@oneDay, @firstProduction, @mitarbeiter, @tid, @tanz)
set @firstProduction = @@IDENTITY
fetch next from rs into @tid, @tanz, @pos
END
-- alle teile reserviert
CLOSE rs
DEALLOCATE rs
END
go
-- test der Prodzedur
begin
declare @d datetime, @r int
execute reservation 1,2, @d out, @r out
print @d
print @r
end
Seite 6 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
5 Java Code
package ch.hslu.dmg;
import java.sql.*;
/**
* Diese Klasse erstellt eine DB-Connection sowie ruft die Produktionsprozedur auf.
* @author tstuber
*/
public class simpleJdbc {
public static Connection conn;
/**
* Main-Methode. In dieser gibt man den Kundenauftrag ein.
* @param args
* @throws java.sql.SQLException
*/
public static void main(String args[]) throws SQLException {
try {
Class.forName(
"com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(
"jdbc:sqlserver://localhost;Database=dmg_projekt", "sa", "xxx");
// db-server with database, user and password
System.out.println("database connection ok");
java.sql.Date d = //new java.sql.Date(new java.util.Date().getTime());
java.sql.Date.valueOf("2008-12-31");
dbProcCall(1, 2, d); //Typ ID (1 = Velo), Anzahl, Datum
conn.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
}
/**
* Methode zum Aufrufen des Produktionsvorgangen. Kann die Produktion aus
* bestimmten Gründen nicht vollzogen werden - z.B. da es zu spät produziert
* werden kann, wird ein Rollbak ausgeführt und die Transaktion wird rückgängig
* gemacht. Ansonsten folgt ein Commit, was die Daten in die Datenbank schreibt.
*
* @param teilId
ID des obersten zu produzierenden Teiles. In diesem
*
Fall steht die ID=1 für ein Velo.
* @param anz
Anzahl der zu erstellenden Oberteile resp. Anz. Velos
* @param dayDeliveryWanted Datum, an welchem man die Velos produziert haben möchte
*/
protected static void dbProcCall(int teilId, int anz, java.sql.Date dayDeliveryWanted) {
try {
conn.setAutoCommit(false); // global conn
//Reihenfolge: @teil int, @anz int, @dayDone datetime out, @ProduktionsProzess
int out
CallableStatement cs = conn.prepareCall("{call reservation(?,?,?,?)}");
cs.setInt(1, teilId);
cs.setInt(2, anz);
cs.registerOutParameter(3, java.sql.Types.DATE);
cs.registerOutParameter(4, java.sql.Types.INTEGER);
cs.execute();
//Auslesen der Rückgabewert der Prozedur:
// - Datum, bis wann produziert wurde
// - ID für den Produktionsauftrag
java.sql.Date dayProductionDone = cs.getDate(3);
Integer ProduktionsNr = cs.getInt(4);
//Wenn entsprechend dem Wunschdatum oder frühzeitiger, dann folgt
//ein Eintrag in die Kundenaufträge sowie anschliessend ein commit.
if (ProduktionsNr > 0) {
//java.sql.Date dayProductionDone = cs.getDate(3);
System.out.println("Prod. Fertig: " + dayProductionDone);
System.out.println("Lieferwunsch: " + dayDeliveryWanted);
if (dayDeliveryWanted.compareTo(dayProductionDone) >= 0) {
PreparedStatement ps = conn.prepareStatement(
"insert into Kundenbestellungen(KundenNr," +
Seite 7 von 8
TA.DMG.H0801 – SQL Projekt
Michael Studer | Thomas Stuber
"Liefertermin,AnzTeile,ProduktionsNr) " +
"values(666,?,?,?)");
ps.setDate(1, dayProductionDone);
ps.setInt(2, anz);
ps.setInt(3, ProduktionsNr);
ps.execute();
System.out.println("committing");
conn.commit();
return;
}
}
//Rollback falls etwas fehlschlägt
System.out.println("rolling back");
conn.rollback();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
}
Seite 8 von 8