Database theorie‎ > ‎

Applicatieontwikkelaar Leerjaar 4 periode 1

Eindcase 

Lever deze opdracht: Eindcase DBT Applicatie ontwikkelaar.  voor a.s. Woensdag,  2 november in.

Antwoorden Les 4

DROP database IF EXISTS cijfers;
CREATE DATABASE cijfers;
USE cijfers;
CREATE TABLE cijfer (
  student CHAR(9),
  vak CHAR(4),
  resultaat DOUBLE,
  PRIMARY KEY (student,vak)
) ENGINE=innodb;

INSERT INTO cijfer (student, vak, resultaat) VALUES
('000000001','DBT', 2.5),('000000001','LNX', 3.4),('000000001','BCV', 5.1),('000000001','UML', 6.9),
('000000002','IHWP', 6.2),('000000002','IHWT', 7.8),('000000002','TASK', 5.6),('000000002','DBT', 7.2),
('000000003','DBT', 3.5),('000000003','LNX', 7.4),('000000003','UML', 6.9),('000000003','IHWP', 6.2),('000000003','IHWT', 7.8),
('000000004','TASK', 5.6),('000000004','DBT', 3.5),('000000004','LNX', 7.4),('000000004','BCV', 9.5),
('000000005','XML', 4.2),('000000005','DBT', 4.8),('000000005','LNX', 7.4),('000000005','UML', 6.9),('000000005','IHWP', 6.2);

select * from cijfers;

CREATE VIEW vak as SELECT UNIQUE vak as code FROM cijfer;
CREATE VIEW student as SELECT UNIQUE student as ov FROM cijfer;

SELECT student, count(resultaat) as aantal, sum(resultaat >= 6) as V, sum(resultaat < 6) as O  FROM cijfer GROUP BY student;


SELECT
  cijfer.student,
  cijfer.vak,
  cijfer.resultaat,
  sum(cijfer.resultaat >= andere.resultaat) / count(andere.resultaat) as percentage
FROM cijfer
LEFT JOIN cijfer as andere ON
  cijfer.vak = andere.vak
WHERE cijfer.vak='DBT'
GROUP BY
  cijfer.student,
  cijfer.vak;

SELECT
  cijfer.student,
  cijfer.vak,
  cijfer.resultaat,
  sum(cijfer.resultaat >= andere.resultaat) / count(andere.resultaat) as percentage
FROM cijfer
LEFT JOIN cijfer as andere ON
  cijfer.vak = andere.vak AND
  cijfer.student <> andere.student
WHERE cijfer.vak='DBT'
GROUP BY
  cijfer.student,
  cijfer.vak;

SELECT vak, avg(cijfer.resultaat) as gemiddelde FROM cijfer GROUP BY vak HAVING avg(cijfer.resultaat) < 6;


SELECT
  student,
  vak,
  resultaat,
  aantal
FROM cijfer LEFT JOIN
  (
  SELECT
     student AS ov,
     count(resultaat) AS aantal
  FROM cijfer
  GROUP BY student
  ) as nummer
  ON nummer.ov = cijfer.student ;


Les 4

Opdracht voor thuis:

LET OP: Onder aan de opdrachten voor deze week het antwoord op de vraag: "Hoe kan ik het aantal records per group toevoegen als veld aan een query?"
 
Zorg dat je MySQL tot je beschikking hebt en maak de database cijfers aan.
Als je iets van de SQL codes hieronder niet snapt, maak aantekeningen en vraag het a.s. woensdag.

DROP DATABASE IF EXISTS cijfers;
CREATE DATABASE cijfers;
USE cijfers;


Maak de tabel met resultaten aan.

CREATE TABLE cijfer (
   student CHAR(9),
   vak CHAR(4),
   resultaat DOUBLE,
   PRIMARY KEY (student,vak)
) ENGINE=innodb;

Voeg een paar records toe.

INSERT INTO cijfer (student, vak, resultaat) VALUES
('000000001','DBT', 2.5), ('000000001','LNX', 3.4), ('000000001','BCV', 5.1), ('000000001','UML', 6.9),
('000000002','IHWP', 6.2), ('000000002','IHWT', 7.8), ('000000002','TASK', 5.6), ('000000002','DBT', 7.2),
('000000003','DBT', 3.5), ('000000003','LNX', 7.4), ('000000003','UML', 6.9), ('000000003','IHWP', 6.2), ('000000003','IHWT', 7.8), ('000000004','TASK', 5.6), ('000000004','DBT', 3.5), ('000000004','LNX', 7.4), ('000000004','BCV', 9.5), ('000000005','XML', 4.2), ('000000005','DBT', 4.8), ('000000005','LNX', 7.4), ('000000005','UML', 6.9), ('000000005','IHWP', 6.2);

We maken ook een view met alle vakken en studenten.

CREATE view vak as SELECT DISTINCT vak as code from cijfer;
CREATE view student as SELECT DISTINCT student as ov from cijfer;

Kijk wat er in de tabel cijfers en de views student en vak staat.

SELECT * FROM vak;
SELECT * FROM student;
SELECT * FROM cijfer;

Als je een cijfer toevoegt voor een nieuwe student en of een nieuw vak dan worden de views automagisch bijgewerkt.

Opdracht 1 Group by:

Maak een query die per student laat zijn hoeveel voldoendes en hoeveel onvoldoendes hij heeft gescoord.

Even spieken bij de TIPs?

Opdracht 2 cartetisch produkt:

Door 2 tabellen of views te noemen zonder join kun je een verzameling maken van alle mogelijke combinaties van de 2 verzamelingen.
Bekijk bijvoorbeeld:

SELECT * FROM vak, student;
SELECT * FROM student, vak;

Aan die laatste kun je de tabel cijfer joinen zodat je voor elke student alle vakken ziet en alleen die cijfers die ook echt ingevuld zijn.
tip: om een join te maken op een cartetisch product moet je haakjes gebruiken dus.

...... FROM (student, vak) LEFT JOIN cijfer on .....

Schrijf een SELECT query die alle combinaties van vak en student weergeeft en de ingevulde cijfers. Op de plaats waar geen cijfer ingevuld is staat NULL.

Opdracht 3 self join:

Je kunt ook een join maken op dezelfde tabel. Bijvoorbeeld om de vraag te beantwoorden: hoeveel % van de studenten scoorde slechter op de toets DBT dan deze student voor dit vak?

Je wilt dus:
  • een SELECT van student, vak en cijfer FROM cijfer.
  • een left join op "cijfer as andere" (cijfer.vak = andere.vak)
  • de sum(andere.resultaat <= cijfer.resultaat) / count(andere.resultaat) as percentage
  • filteren op cijfer.vak = 'DBT'
  • Groeperen op cijfer.student en cijfer.vak
Maar hier zit een fout in. Je wilt n.l de resultaten van de gegeven student vergelijken met alle anderen en niet met zichzelf. Hoe zou je dat kunnen oplossen?

Succes.

Opdracht 4 having:

Met de volgende query kun je de gemiddelde score per vak berekenen:
SELECT vak, avg(cijfer.resultaat) FROM cijfer GROUP BY vak;

Schrijf nu een query die een lijst geeft van alle vakken waarvoor gemiddeld minder dan 6 gescoord is.
Gaat dit ook met WHERE? Wat is het verschil?

Antwoord:

En dan nu het antwoord op de vraag: "Hoe kan ik het aantal records per group toevoegen als veld aan een query?"
Behalve de oplossing met een VIEW of een tijdelijke tabel zoals we tijdens de les hebben besproken is er ook nog de volgende oplossing:

Je kunt een join maken op een subquery:

SELECT
   student,
   vak,
   resultaat,
   aantal
FROM cijfer LEFT JOIN
   (
      SELECT
         student AS ov,
         count(resultaat) AS aantal
      FROM cijfer
      GROUP BY student
   ) as nummer
   ON nummer.ov = cijfer.student ;

Het totaal tussen de haakjes ( SELECT ..... ) is een subquery die met "as nummer" de alias "nummer" krijgt.

Stof

Behalve NF3 heb ik vandaag een aantal basisbegrippen SQL besproken.

DML = Data Manipulation Language
DDL = Data Definition Language
DCL = Data Control Language

De laatste: TCL = Transaction Control Language bespreek ik later
Lees hier een samenvatting: http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

PAS OP! dit is Oracle en Oracle is niet bepaald ANSI:

Uit: http://en.wikipedia.org/wiki/SQL

-----------------------

The SQL standard has gone through a number of revisions, as shown below:

Year Name Alias Comments
1986 SQL-86 SQL-87 First formalized by ANSI.
1989 SQL-89 FIPS 127-1 Minor revision, adopted as FIPS 127-1.
1992 SQL-92 SQL2, FIPS 127-2 Major revision (ISO 9075), Entry Level SQL-92 adopted as FIPS 127-2.
1999 SQL:1999 SQL3 Added regular expression matching, recursive queries, triggers, support for procedural and control-of-flow statements, non-scalar types, and some object-oriented features.
2003 SQL:2003 SQL 2003 Introduced XML-related features, window functions, standardized sequences, and columns with auto-generated values (including identity-columns).
2006 SQL:2006 SQL 2006 ISO/IEC 9075-14:2006 defines ways in which SQL can be used in conjunction with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database and publishing both XML and conventional SQL-data in XML form. In addition, it enables applications to integrate into their SQL code the use of XQuery, the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.[26]
2008 SQL:2008 SQL 2008 Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers. Adds the TRUNCATE statement.[27]

Interested parties may purchase SQL standards documents from ISO or ANSI. A draft of SQL:2008 is freely available as a zip archive.[28]

-------------------

In de lessen zal ik niet verder gaan dan SQL 1999 standaard. Je kunt je alvast voorbereiden door alle lessen van vorig jaar (W3schools) te herhalen.
Maak vervolgens de bovenstaande opdrachten.

Les 3

Voorbereiding:

Opdracht ter voorbereiding op les drie:

Werk deze opdracht individueel uit. minimaal in een document evt. aangevuld met een online proof of concept. Mail het document voor dinsdag 13 september aan t.boose@rocleiden.nl

Een voetbal organisatie organiseert een competitie tussen F teams. Daarvoor is een spreadsheet in gebruik waarin alles verwerkt wordt.
LET OP!!! het spreadsheet bestaat uit 2 tabbladen, 1 met de teams en 1 met de wedstrijden.

Jij krijgt bij deze de opdracht om het spreadsheet om te zetten in een database die minimaal tot op 2NF niveau genormaliseerd is. Lees hiervoor: http://www.software-innovators.nl/2008/05/26/normaliseren/ of wikipedia.

De volgende zaken moeten in je model zijn verwerkt:

- Teams spelen alleen tegen teams uit dezelfde poule.
- De velden gewonnen, aantal gewonnen, aantal verloren en aantal gelijk moet berekent kunnen worden. Deze mogen niet opgeslagen worden.
- De adressen van de thuisclubs staan bij de wedstrijden vermeld en bij alle elftallen van die club. Dit is redundant. Los dat op.
- Er zijn kennelijk 2 scheidsrechters met dezelfde naam bij verschillende clubs. Los dit op.
- Niet alle velden bevatten atomaire gegevens. Los dit op.

Laat van je ontworpen database zien:
- Een logisch model (Voor de klant)
- Een uitgewerkt UML Database diagram (fysiek model)


Les 2

Uitwerkingen van de case:

Hieronder staan uiwerkingen van de case met bijbehorende feedback. Tijdens de les zal ik deze onderwerpen toelichten.

Uitwerking 1

- Alle relatie 1 op 1? of moet ik zelf de FK's volgen?

- De opdrachtgever staat in project en in factuur. Leidt dit niet tot mogelijke fouten, een factuur bij een project waarop een andere opdrachtgever staat dan de opdrachtgever van het project? Of is dat juist de bedoeling? Pietje betaald voor het project van Klaas.

- Een eigen_bedrijf = een opdrachtgever + logo.

Kun je dan niet beter zeggen eigen_bedrijf = FK opdrachtgever + logo? Zo kunnen eigen_bedrijven elkaar ook nog eens als opdrachtgever benaderen.

- Op 1 factuur staan meerdere uren. Niet anders om, toch? Of wil je graag fraude plegen?

- Project en contract zijn nu slecht via de uren gekoppeld aan elkaar. Dus zolang we geen uren maken weten we niet welk contract bij welk project hoort? Als ik nu een raamcontract heb maar ik heb nog geen uren gemaakt, Hoe weet ik dan aan wie ik de factuur moet sturen?

- Ik denk niet dat er verschillende tarieven per uur gerekend worden voor 1 project. Alle uren voor diensten van Asad hebben het zelfde BTW tarief. M.a.w. BTW is een vast percentage over alle bedragen die Asad in rekening brengt. Aangezien dit percentage maar zelden verandert en dan nog altijd aan het einde van een boekjaar vraag ik mij af of het nodig is om de btw apart op te slaan. Het risico op fouten wordt zo alleen maar groter.

De enige functie die dan nog over blijft voor de tarief tabel is het beperken van de mogelijke tarieven tot een lijstje vooraf vastgestelde mogelijkheden. Dit lijkt me meer iets voor een business logic laag dan voor een fysieke database laag en het kan makkelijk leiden tot gebruikersonvriendelijkheid; Een gebruiker die eerst een nieuw tarief moet maken voordat hij een afwijkend tarief in rekening kan brengen.

Uitwerking 2

- Wat doet de kolom column in contract?


In 'Contract' wil ik in de DataBase apart de informatie van de IT-er plaatsen. In het geval dat er in de toekomst een partnerschap ontstaat kan er makkelijk een tweede of misschien zelfs derde persoon aangemaakt worden. Gebeurt dit niet dan is alsnog de informatie makkelijk te vinden, op te splitsen door de verschillende velden, en kan later gelinkt worden aan een applicatie.

>> Doel je op een vorm van "vrije velden"? zoals b.v. bedoeld in http://www.exactsoftware.com/docs/DocView.aspx?DocumentID={d00927cf-0da3-4e36-ae42-343d9cb64c6d}

- Ik zie een tussentabel tussen Contract en Project. Dat houd in: Meerdere projecten per contract en meerdere contracten per Project. Is dit niet nodeloos complex? 


Hier stond ik ook even gek naar te kijken. Elke keer dat ik een link wilde trekken tussen 'Projecten' en 'Contract' sprong did ding ertussen. Maar toen ik deze goed bekeek leek het mij niet schadelijk aangezien het duidelijk zichtbaar weergeeft welke Primary Keys worden verbonden vanuit welke tabel.
Ik heb geprobeerd het te verwijderen maar dan was de relatie weg en als ik het opnieuw probeerde bleef hij terug komen.

>> Hier moet ik kennelijk aandacht aan besteden. Er moet een veld met een type, gelijk aan die van de brontabel, zijn in de verwijzende tabel om de 1 op N relatie te kunnen maken.

- Wat is FOB? en wat moet ik me bij verzendtijd en kosten, datum en via voorstellen?. Asad verkoop alleen maar uren dus de beschrijving kan weg blijven. Hij heeft ook geen verkopers in dienst dus die mag ook weg.

FOB staat voor "Free On Board". Toen ik wat research deed naar factures kwam deze onmisbaar langs dus dacht ik dat deze genoemd moest worden.
Ik zal de onnodige velden verwijderen.

>> Heeft idd. iets met leveringen te maken en is onmisbaar bij een handelsonderneming.

- Ga je bij iedere factuur opnieuw adresgegevens noteren?

Ik neem aan dat de gegevens van Assad zelf worden opgeslagen en terug geroepen kunnen worden (En zo nodig in de toekomst kan uitbreiden net als 'Contract' door middel van partnerschap). Door deze zo op te slaan kan het verspreid opgeroepen worden later, maar voor de klanten neem ik aan dat hier en daar gegevens wel steeds opnieuw ingevoerd moeten worden omdat deze verschillen per klant.

>> Precies, verschillen per klant maar niet perse per factuur. Vaak zie je dat er een "live" systeem is waarin klanten slechts 1 set actuele contactgegevens hebben. Op transactiemomenten; contracten, het maken van facturen en het schrijven van uren bijvoorbeeld, worden deze gegevens in een historische tabel weggeschreven omdat de gegevens i.d.d. nog wel eens willen veranderen en dan verstoren ze je statistiek. Als ik hier tijd voor heb zal ik het toelichten a.s. woensdag.

- Factuur is gekoppeld aan contract maar zolang er meerdere projecten per contract worden gehandhaafd, weet je dus niet hoeveel uur er aan een project besteed is. Verder zie ik ook nog geen mogelijkheid om aan te geven welke uren vooraf en welke achteraf gerekend moeten worden.


De betaling vooraf/achteraf en de uren had ik in gedachten in de tabel 'Projecten', waar 'TypeUrenRegistratie' aangeeft of er per uur wordt gerekend of dat het een dagdeel is, in 'TypeBetaling' is het vooraf of achteraf.

>> Uren die je vooraf factureert zijn nog niet concreet op datum met activiteit. Desondanks zul je ter verantwoording wel precies moeten bijhouden wanneer je hoeveel uur waar aan besteed. Van uren die je achteraf factureert kun je wel aangeven wat je gedaan hebt en wanneer. Als je model hier rekening mee houd is het goed.

Les 1

In deze les heb ik een toelichting gegeven op de onderstaande Case.

Case ZZP: Asad

Asad is ZZP-er en werkt op uurbasis aan projecten voor klanten. Er zijn 3 soorten contracten.

- Raamcontracten:

Als een opdrachtgever aangeeft een x aantal uren over een bepaalde periode te willen afnemen dan stelt Asad een raamovereenkomst op. Deze uren worden vooraf gefactureerd en geven Asad zekerheid van een basisinkomen. Daarom zijn deze uren in de regel goedkoper dan flexibele uren.

- 0 uren contracten:

Uren die Asad maakt op basis van een 0-uren contract worden achteraf gefactureerd en gaan telkens in overleg.

- Eigen projecten

Sommige projecten doet hij voor zichzelf, voor een buurtvereniging of samen met vrienden voor de lol. Toch wil hij ook van deze projecten bijhouden hoeveel tijd hij er aan besteedt.

- Urenregistratie:

Als Asad naar de klant toe moet brengt hij reiskosten in rekening. Werk bij de klant rekent hij af per dagdeel (4 uur, 's ochtends of 's middags). Voor werk dat hij thuis kan doen rekent hij het uurtarief. Voor 5 minuten werk rekent hij 1 uur en voor 1,5 uur werk rekent hij 2 uur enz enz. Voor thuiswerk rekent hij geen reiskosten.

- Functionaliteit:

Asad wil een systeem waarmee hij:

- Zijn contracten kan invoeren en afdrukken.
- Zijn uren kan registreren.
- Wekelijks zijn facturen kan printen (zowel de raamcontracten vooraf als de 0-uren uren achteraf).
- Een overzicht kan maken van de uren per project over een bepaalde periode.

Kijk voor de eisen die de belastingdienst stelt aan een factuur hier: http://belastingdienst.nl/zakelijk/ondernemen_btw/ondernemen_btw-14.html

Je hoeft geen rekening te houden met:
- intracommunautaire leveringen.
- diensten in het buitenland.
- een verleggingsregeling.
- een margeregeling.

Opdracht:

Stuur voor a.s. woensdag een mail aan t.boose@rocleiden.nl met het ontwerp van de fysieke databasestructuur die jij adviseert aan Asad.
Werk met Visual Paradigm for UML, community edition.

Comments