2011 Praktijkopdracht Database Design Advanced 992H10
R.G. van Voorthuizen LOI Hogeschool 29-7-2011
Inhoud Requirementsanalyse................................................................................................................................................................................................................................................. 1 Wensen adminstrateur: ......................................................................................................................................................................................................................................... 1 Wensen ondernemer: ............................................................................................................................................................................................................................................ 1 Entiteiten & attributen: ......................................................................................................................................................................................................................................... 1 Conceptueel Datamodel ............................................................................................................................................................................................................................................ 2 Logisch ontwerp ......................................................................................................................................................................................................................................................... 3 Logisch datamodel ................................................................................................................................................................................................................................................. 3 Vertaling naar SQL.................................................................................................................................................................................................................................................. 3 Fysiek ontwerp ........................................................................................................................................................................................................................................................... 5 Performance .......................................................................................................................................................................................................................................................... 5 Ontsluiting .............................................................................................................................................................................................................................................................. 5 Beveiligingsmodel ...................................................................................................................................................................................................................................................... 5
Requirementsanalyse Wensen adminstrateur: -Maandelijks genereren van facturen voor de lessen van de afgelopen maand -2x per jaar inplannen van lesrooster -2x per jaar inplannen van lokaaltoewijzing, gekoppeld aan lesrooster -Genereren van overzichten deling der omzet tussen de ondernemers. Wensen ondernemer: -rapportage draaien van eigen leerlingen, omzet e.d. Entiteiten & attributen: ) 2 ondernemers huren samen 5 muzieklokalen en ieder huurt 0, 2 of 3 extra docenten in 1 ondernemer huurt 1 danslokaal
-Persoon: persoonsnr., voornaam, tussenvoegsel, achternaam, straatnaam, postcode, plaatsnaam, telefoonnummer, E-mailadres -Ondernemer: alle attributen van Persoon, ondernemernr. -Docent: alle attributen van Persoon, docentnr. , specialiteit -Leerling: alle attributen van Persoon, leerlingnr. , leeftijd -Administrateur: alle attributen van Persoon, administrateurnr. -Kantoorpand: straatnaam, postcode, plaatsnaam -Lokaal: lokaalnr, naam -Apparatuur: apparaatnr., naam -Les: lesnr. , lessoort, benodigdheden, kosten, tijdstip, duur
Conceptueel Datamodel
)
Logisch ontwerp Logisch
datamodel
)
Vertaling naar SQL
CREATE TABLE lesgegevens ( lessoort ENUM('Zang','Keyboard','Drum','Dans','Gitaar','Verhuur') NOT NULL, leskosten INTEGER ); ALTER TABLE lesgegevens ADD CONSTRAINT PK1 PRIMARY KEY(lessoort); CREATE TABLE les ( lesnr INTEGER NOT NULL, persoonnr INTEGER NOT NULL, lokaalnaam ENUM('Rood','Geel','Groen','Orange','Paars','Danslokaal') NOT NULL, lessoort ENUM('Zang','Keyboard','Drum','Dans','Gitaar','Verhuur') NOT NULL, periodevan DATETIME NOT NULL, //datum en tijdstip van begin van de les periodetot DATETIME NOT NULL //datum en tijdstip van eind van de les ); ALTER TABLE les ADD CONSTRAINT PK1 PRIMARY KEY(lesnr); CREATE TABLE deelname ( lesnr INTEGER NOT NULL, persoonnr INTEGER NOT NULL ); ALTER TABLE deelname ADD CONSTRAINT PK1 PRIMARY KEY(lesnr, persoonr); CREATE TABLE persoon ( persoonnr INTEGER NOT NULL, voornaam VARCHAR(50) NOT NULL, tussenvoegsel VARCHAR(10), achternaam VARCHAR(50) NOT NULL, geslacht ENUM('Man','Vrouw','Overig'), straatnaam VARCHAR(50), huisnummer INTEGER, postcode VARCHAR(6), woonplaats VARCHAR(50), maandsalaris INTEGER, rekeningnummer INTEGER ); ALTER TABLE persoon ADD CONSTRAINT PK1 PRIMARY KEY(persoonnr); CREATE TABLE lesbevoegdheid ( persoonnr INTEGER NOT NULL, lessoort ENUM('Zang','Keyboard','Drum','Dans','Gitaar','Verhuur') NOT NULL ); ALTER TABLE lesbevoegdheid ADD CONSTRAINT PK1 PRIMARY KEY(persoonnr); CREATE TABLE rollen ( ) persoonnr INTEGER NOT NULL, rol ENUM('Administrateur','Docent','Ondernemer','Leerling','Externe') NOT NULL ); ALTER TABLE rollen ADD CONSTRAINT PK1 PRIMARY KEY(persoonnr); CREATE TABLE inhuur ( ondernemernr INTEGER NOT NULL, persoonnr INTEGER NOT NULL ); ALTER TABLE inhuur ADD CONSTRAINT PK1 PRIMARY KEY(ondernemernr, persoonnr); CREATE TABLE lokaal ( lokaalnaam ENUM('Rood','Geel','Groen','Orange','Paars','Danslokaal') NOT NULL ); ALTER TABLE lokaal ADD CONSTRAINT PK1 PRIMARY KEY(lokaalnaam); CREATE TABLE lesmogelijkheid ( lessoort ENUM('Zang','Keyboard','Drum','Dans','Gitaar','Verhuur') NOT NULL, lokaalnaam ENUM('Rood','Geel','Groen','Orange','Paars','Danslokaal') NOT NULL ); ALTER TABLE lesmogelijkheid ADD CONSTRAINT PK1 PRIMARY KEY(lokaalnaam); ALTER TABLE lesmogelijkheid ADD CONSTRAINT FK1 FOREIGN KEY(lokaalnaam) REFERENCES lokaal; ALTER TABLE les ADD CONSTRAINT FK1 FOREIGN KEY(persoonnr) REFERENCES persoon; ALTER TABLE les ADD CONSTRAINT FK2 FOREIGN KEY(lokaalnaam) REFERENCES lokaal; ALTER TABLE les ADD CONSTRAINT FK3 FOREIGN KEY(lessoort) REFERENCES lesgegevens; ALTER TABLE deelname ADD CONSTRAINT FK1 FOREIGN KEY(lesnr) REFERENCES les; ALTER TABLE deelname ADD CONSTRAINT FK2 FOREIGN KEY(persoonnr) REFERENCES persoon; ALTER TABLE lesbevoegdheid ADD CONSTRAINT FK1 FOREIGN KEY(persoonnr) REFERENCES persoon; ALTER TABLE rollen ADD CONSTRAINT FK1 FOREIGN KEY(persoonnr) REFERENCES persoon; ALTER TABLE inhuur ADD CONSTRAINT FK1 FOREIGN KEY(ondernemernr) REFERENCES persoon(persoonnr); ALTER TABLE inhuur ADD CONSTRAINT FK2 FOREIGN KEY(persoonnr) REFERENCES persoon;
Fysiek ontwerp Performance DBMS zal zelf zorgen voor caching van vaak benaderde pages voor het lezen van gegevens. De database kan het beste “in de cloud” gehost worden, dus bij een bedrijf dat meerdere servers beheerd in datacenters. Dit geeft de beste en meest betrouwbare prestaties en veiligheid (dus continuïteit). Als het DBMS niet al automatisch een index creëert op de Primary Key, zullen de volgende statements nog gegeven moeten worden: CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE CREATE
UNIQUE UNIQUE UNIQUE UNIQUE UNIQUE UNIQUE UNIQUE UNIQUE UNIQUE
INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX
persoon_index USING BTREE ON persoon(persoonnr); lesnr_index USING BITMAP ON les(lesnr); lespersoon_index USING BTREE ON les(persoonnr); lesgegevens_index USING BTREE ON lesgegevens(lessoort); deelname_index USING BTREE ON deelname(persoonnr); lesbevoegdheid_index USING BTREE ON lesbevoegdheid(persoonnr); rollen_index USING BTREE ON rollen(persoonnr); inhuur_index USING BTREE ON inhuur(ondernemernr); lokaal_index USING BTREE ON lokaal(lokaalnaam);
Ontsluiting Ik heb er voor gekozen om uitsluitend storend procedures te gebruiken voor de ontsluiting van de database, om zodoende de applicatie volledig af te schermen van de interne database-structuur en zo een eenduidig beveiligingsmodel op te kunnen stellen. Wens Genereren van facturen voor de lessen van de afgelopen periode (maandelijks). Inplannen van lesrooster, 2x per jaar. Inplannen van lokaaltoewijzing, gekoppeld aan het lesrooster, 2x per jaar. Genereren van overzichten welke ondernemer welk deel van de omzet krijgt.
Stored Procedure Factuuroverzicht Lesplanning Lesplanning Omzetoverzicht / Mijnomzetoverzicht
CREATE PROCEDURE factuuroverzicht BEGIN SELECT persoon.persoonnr, persoon.voornaam, persoon.tussenvoegsel, persoon.achternaam, persoon.staatnaam, persoon.huisnummer, persoon.postcode, persoon.woonplaats FROM Persoon, Deelname, Les WHERE MONTH(Les.periodeVan) = ( MONTH(CURRENT_TIMESTAMP) – 1); //alle lessen vorige maand AND Les.lesnr = Deelname.lesnr //equi-join alle bijbehorende deelnames AND persoon.persoonnr = deelname.persoonnr //equi-join alle bijbehorende personen ) END CREATE PROCEDURE lesplanning ( IN docent INTEGER IN leerling INTEGER IN lesnr INTEGER IN lokaal ENUM(rood, geel, groen, oranje, paars, danslokaal) IN lessoort ENUM(zang, keyboard, drum, dans, gitaar, verhuur) IN PeriodeVan DATETIME IN PeriodeTot DATETIME ) BEGIN INSERT INTO Les (Persoonnr, lokaalnaam, lessoort, PeriodeVan, PeriodeTot) VALUES (@@docent, @@lokaal, @@lesoort, @@PeriodeVan, @@PeriodeTot) INSERT INTO Deelname (lesnr, persoonnr) VALUES (@@lesnr, @@leerling) END CREATE PROCEDURE omzetoverzicht ( IN docent INTEGER ) BEGIN SELECT COUNT leskosten FROM Lesgegevens, Les WHERE les.lessoort = lesgegevens.lessoort AND persoonnr = @@docent END CREATE PROCEDURE mijnomzetoverzicht BEGIN SELECT COUNT leskosten FROM Lesgegevens, Les WHERE les.lessoort = lesgegevens.lessoort AND persoonnr = @@huidige_gebruiker END
//@@huidige_gebruiker representeert dus de ingelogde gebruiker
CREATE PROCEDURE lesrooster BEGIN SELECT les.persoonnr docent, deelname.persoonnr leerling, les.lokaalnaam, les.lessoort, periodeVan, periodeTot FROM Les, Deelname WHERE deelname.lesnr = les.lesnr AND periodeVan > CURRENT_TIMESTAMP END
Beveiligingsmodel Omdat op databaseniveau geen rechten kunnen worden gekoppeld aan de inhoud van tabellen, heb ik ervoor gekozen om bevoegdheden te definiĂŤren op applicatieniveau. In de wensen staat namelijk gespecificeerd dat de 3 ondernemers elkaar weliswaar vertrouwen, maar geen inzicht aan elkaar willen geven in hun gegevens over leerlingen en omzet e.d. Op databaseniveau worden wel berperkingen aangebracht voor diverse tabellen, zodat tweetrapsbeveiliing ontstaat. Rollen: Docent Administrateur Websitegebruiker
Bevoegdheden: Eigen gegevens, lessen, omzet kunnen inzien Alle gegevens, lessen, omzet kunnen inzien en aanpassen Lesrooster kunnen inzien
Role based accces op functieniveau tbv wensen administrateur: GRANT SELECT, INSERT, DELETE, UPDATE ON Persoon, Les, Inhuur TO Administrateur Role based access op inhoud, tbv wensen van docenten en websitegebruikers: GRANT EXECUTE ON factuuroverzicht, lesplanning, omzetoverzicht TO Administrateur GRANT EXECUTE ON mijnomzetoverzicht TO Ondernemer GRANT EXECUTE ON lesrooster TO Administrateur, Docent, Ondernemer, Leerling, Externe
)