Για κάθε 1:Ν δυαδική (μη αδύναμη) συσχέτιση R μεταξύ δύο τύπων οντοτήτων που αντιστοιχούν στις σχέσεις Τ και S, δεν
δημιουργούμε νέα σχέση R. Στα ήδη υπάρχοντα γνωρίσματα της S προστίθενται: (α) τα γνωρίσματα της R, αν υπάρχουν, και (β) το πρωτεύον κλειδί της
T, το οποίο συμμετέχει ως
ξένο κλειδί στη σχέση S όπως φαίνεται στην Εικόνα
3.1.
Μετατροπή από Ε-R σε σχεσιακό για 1:N
Για κάθε Μ:Ν συσχέτιση R
μεταξύ τύπων οντοτήτων που αντιστοιχούν στις σχέσεις Τ, S, ο
χρήστης δημιουργεί μία νέα σχέση
(πίνακα) R με γνωρίσματα: (α) τα
γνωρίσματα της R, αν
υπάρχουν, και (β) τα γνωρίσματα του πρωτεύοντος κλειδιού κάθε συμμετέχουσας
σχέσης S και Τ,
τα οποία συμμετέχουν ως ξένα κλειδιά στη σχέση R όπως φαίνεται στην Εικόνα 3.2.
Για κάθε 1:1 δυαδική (μη αδύναμη) συσχέτιση R μεταξύ δύο τύπων
οντοτήτων που αντιστοιχούν στις σχέσεις T και S, δεν δημιουργείται νέα σχέση R αλλά επιλέγεται μία εκ των Τ και S, έστω την S. Στα ήδη υπάρχοντα γνωρίσματα της S προσθέτουμε: (α) τα γνωρίσματα της R, αν υπάρχουν, και (β) το πρωτεύον κλειδί της
T, το οποίο συμμετέχει ως
ξένο κλειδί στη σχέση S όπως στην περίπτωση 1:Ν.
Για την επιλογή T και
S, κριτήριο είναι η ολική
συμμετοχή του ενός ή του άλλου τύπου οντοτήτων στη συσχέτιση. H σχέση που αντιστοιχεί στον τύπο οντοτήτων
που συμμετέχει ολικά στη συσχέτιση, είναι αυτή (η σχέση S παραπάνω) που επιλέγεται να “φιλοξενήσει” ως
ξένο κλειδί το πρωτεύον κλειδί της άλλης (S). Αν δε υπάρχει ολική συμμετοχή από μία
πλευρά, τότε προσπαθούμε να μην έχουμε πολλές null τιμές, οπότε επιλέγουμε να προσθέτουμε ξένο
κλειδί στη σχέση που αντιστοιχεί στον τύπο οντοτήτων με τη μεγαλύτερη συμμετοχή
στη συσχέτιση.
Για τις 1:1, 1:Ν και
Ν:1 δυαδικές (μη αδύναμες) μπορούμε, εναλλακτικά, να ακολουθήσουμε το γενικό
κανόνα (δημιουργία νέας σχέσης με ξένα κλειδιά, τα πρωτεύοντα κλειδιά των
εμπλεκομένων σχέσεων), ειδικά όταν δε υπάρχει ολική συμμετοχή από καμία πλευρά.
Στην περίπτωση αυτή, κερδίζουμε ως προς την εννοιολογική καθαρότητα και
πληρότητα αλλά χάνουμε ως προς την αποδοτικότητα χώρου και τη επίδοση. Συνήθως
δε επιλέγεται αυτός ο τρόπος αλλά η ειδική αντιμετώπιση τους, όπως
παρουσιάστηκε παραπάνω.
Κάθε οντότητα θα γίνει πίνακας δηλ. σε πρώτο βήμα θα δημιουργηθούν οι πίνακες ΙΔΙΟΚΤΗΤΕΣ, ΑΚΙΝΗΤΑ, ΜΕΣΙΤΕΣ.
Στη συνέχεια οι συσχετίσεις που είναι Ν:Μ θα γίνουν νέοι πίνακες, δηλ. θα δημιουργηθεί ο πίνακας ΚΑΤΕΧΟΥΝ που έχει το ίδιο όνομα με την συσχέτιση.
Ακολουθεί η μετατροπή της συσχέτισης ΔΙΑΧΕΙΡΙΖΟΝΤΑΙ που είναι 1:Ν. Σε αυτή την περίπτωση το πρωτεύον κλειδί του πίνακα ΜΕΣΙΤΕΣ θα γίνει ξένο κλειδί στον πίνακα ΑΚΙΝΗΤΑ.
Τέλος για τα πλειότιμα γνωρίσματα θα δημιουργηθούν νέοι πίνακες όπως για το πεδίο ΤΗΛ ιδιοκτήτη θα γίνει νέος πίνακας με ξένο κλειδί το πρωτεύον κλειδί του πίνακα ΙΔΙΟΚΤΗΤΕΣ.
Οι ολικές συμμετοχές προαναγγέλλουν ότι τα ξένα κλειδιά που συμμετέχουν στη συσχέτιση θα παίρνουν υποχρεωτικά τιμές. Δηλ. αν δεν καταχωρηθεί τιμή σε αυτό το πεδίο δεν πρόκειται να προχωρήσει σε επόμενο πεδίο για καταχώριση τιμής. Τέτοια πεδία θα είναι το ΚΑ στον πίνακα ΚΑΤΕΧΟΥΝ.
Ο πίνακας δεν είναι σε 1ΚΜ διότι έχει μια ομάδα επαναλαμβανόμενων πεδίων και έρχεται σε αντίθεση με τον πρώτο όρο που πρέπει να έχει ένας πίνακας για να είναι σε 1ΚΜ που λέει ότι το όνομα κάθε γνωρίσματος είναι μοναδικό.
Τα βήματα που θα ακολουθηθούν για να επέλθει σε 1ΚΜ είναι να τον σπάσουμε σε δύο επί μέρους πίνακες ως εξής :
ΣΠΟΥΔΑΣΤΕΣ (ΑΜ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΟΔΟΣ, ΑΡ, ΤΚ, ΠΟΛΗ)
ΜΑΘΗΜΑΤΑ_ΒΑΘΜΟΙ (ΚΩΔ_ΜΑΘ, ΑΜ, ΜΑΘΗΜΑ, ΒΙΒΛΙΟ ΒΑΘΜΟΣ)
Κατ’ αρχήν εξετάζουμε το πρώτο πίνακα ΣΠΟΥΔΑΣΤΕΣ. Αυτός ο πίνακας είναι σε 2ΚΜ γιατί έχει ένα πεδίο ως πρωτεύον κλειδί. Οπότε όλα τα υπόλοιπα πεδία που δεν είναι κλειδιά προσδιορίζονται από ολόκληρο το πρωτεύον κλειδί.
Στον δεύτερο πίνακα όμως ΜΑΘΗΜΑΤΑ_ΒΑΘΜΟΙ παρατηρούμε ότι το πρωτεύον κλειδί είναι σύνθετο και αποτελείται από δύο πεδία τον Κωδικό μαθήματος και τον Αριθμό μητρώου του σπουδαστή ( ΚΩΔ_ΜΑΘ, ΑΜ). Οι συναρτησιακές εξαρτήσεις που προκύπτουν είναι οι παρακάτω:
ΚΩΔ_ΜΑΘ, ΑΜ à ΒΑΘΜΟΣ
ΚΩΔ_ΜΑΘ à ΜΑΘΗΜΑ
ΚΩΔ_ΜΑΘ à ΒΙΒΛΙΟ
Παρατηρούμε ότι οι δύο τελευταίες συναρτησιακές εξαρτήσεις παραβιάζουν τον κανόνα για τη δεύτερη κανονική μορφή.
Το επόμενο βήμα είναι ο διαχωρισμός του πίνακα ΜΑΘΗΜΑΤΑ_ΒΑΘΜΟΙ σε δύο πίνακες:
Οπότε προκύπτουν οι παρακάτω σχέσεις.
ΣΠΟΥΔΑΣΤΕΣ (ΑΜ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΟΔΟΣ, ΑΡ, ΤΚ, ΠΟΛΗ)
ΜΑΘΗΜΑΤΑ_ΒΑΘΜΟΙ (ΚΩΔ_ΜΑΘ, ΑΜ, ΒΑΘΜΟΣ)
ΜΑΘΗΜΑΤΑ (ΚΩΔ_ΜΑΘ, ΜΑΘΗΜΑ, ΒΙΒΛΙΟ)
Ο πίνακας ΣΠΟΥΑΔΣΤΕΣ δεν είναι κανονικοποιημένος ως προς 3ΚΜ διότι περιέχει μεταβατικές εξαρτήσεις στα παρακάτω πεδία:
ΑΜ à ΕΠΙΘΕΤΟ_ ΚΗΔΕΜΟΝΑ, ΟΝ_ ΚΗΔΕΜΟΝΑ
ΕΠΙΘΕΤΟ_ ΚΗΔΕΜΟΝΑ, ΟΝ_ ΚΗΔΕΜΟΝΑ à ΕΠΑΓΓΕΛΜΑ_ ΚΗΔΕΜΟΝΑ
ΕΠΙΘΕΤΟ_ ΚΗΔΕΜΟΝΑ, ΟΝ_ ΚΗΔΕΜΟΝΑ à ΤΗΛ_ ΚΗΔΕΜΟΝΑ
ΑΜ à ΕΠΑΓΓΕΛΜΑ_ ΚΗΔΕΜΟΝΑ
ΑΜ à ΤΗΛ_ ΚΗΔΕΜΟΝΑ
Για να εξαληφθεί η μεταβατική συνατρησιακή εξάρτηση των παραπάνω πεδίων διαιρούμε τον πίνακα σε δύο επι μέρους πίνακες.
ΣΠΟΥΔΑΣΤΕΣ (ΑΜ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΕΠΙΘΕΤΟ_ΚΗΔΕΜΟΝΑ ΟΝ_ ΚΗΔΕΜΟΝΑ, ΟΔΟΣ, ΑΡ, ΤΚ, ΠΟΛΗ)
ΚΗΔΕΜΟΝΕΣ (ΕΠΙΘΕΤΟ_ΚΗΔΕΜΟΝΑ, ΟΝ_ΚΗΔΕΜΟΝΑ, ΕΠΑΓΓΕΛΜΑ_ ΚΗΔΕΜΟΝΑ, ΤΗΛ_ ΚΗΔΕΜΟΝΑ , ΑΜ, ……)
Το πρωτεύον κλειδί του αρχικού πίνακα γίνεται ξένο κλειδί στον νέο πίνακα που προκύπτει από τον διαμελισμό του αρχικού πίνακα.
Εάν διαγραφόταν η εγγραφή με ΚωδΣυναλλαγής 2-234 θα χανόταν και το γεγονός ότι ο Δήμου διαχειρίζεται τις Προνομιακές Μετοχές.
Ο συνδυασμός ΚωδΣυναλλαγής, και ΤύποςΜετοχής συνθέτουν ένα υποψήφιο κλειδί γιατί χρησιμοποιώντας τον συνδυασμό αυτό μπορούμε να αναγνωρίσουμε με μοναδικό τρόπο μια πλειάδα μέσα στη σχέση.
Όμοια ο συνδυασμός ΚωδΣυναλλαγής, και Χρηματιστής συνθέτουν ένα υποψήφιο κλειδί για τι χρησιμοποιώντας τον συνδυασμό αυτό μπορούμε να αναγνωρίσουμε με μοναδικό τρόπο μια πλειάδα μέσα στη σχέση
Το πεδίο χρηματιστής μόνο του δεν είναι υποψήφιο κλειδί διότι με αυτό δεν μπορούμε να αναγνωρίσουμε με μοναδικό τρόπο μια πλειάδα μέσα στη σχέση
Εξετάζουμε εάν η σχέση ΠΙΝΑΚΑΣ_Α(ΚωδΣυναλλαγής, Τύπος Μετοχής, Χρηματιστής) βρίσκετε σε 1ΚΜ, 2ΚΜ ή 3 ΚΜ.
Εάν ορίσουμε ώς πρωτεύον κλειδί το ζεύγος γνωρισμάτων ΚωδΣυναλλαγής, Τύπος Μετοχής τότε σίγουρα είναι σε 1ΚΜ. Είναι και σε 2ΚΜ επειδή τα υπόλοιπα γνωρίσματα εξαρτώνται από το σύνολο του προτεύοντος κλειδίού και όχι μόνο από τμήμα του. Είναι και σε 3ΚΜ επειδή δεν υπάρχουν μεταβατικές εξαρτήσεις.
Στο προηγούμενο παράδειγμα ποια είναι η λύση:
ΠινακαςΑ (ΚωδΣυναλλαγής, Τύπος Μετοχής, Χρηματιστής)
Τα προσδιοριστικά γνωρίσματα είναι:
ΚωδΣυναλλαγής, Τύπος Μετοχής
ΚωδΣυναλλαγής, Χρηματιστής
Χρηματιστής
Ποια προσδιοριστικά γνωρίσματα μπορούν να λειτουργήσουν
σαν υποψήφια κλειδιά;
ΚωδΣυναλλαγής, Τύπος Μετοχής ΝΑΙ
ΚωδΣυναλλαγής, Χρηματιστής ΝΑΙ
Χρηματιστής ΟΧΙ
Δημιουργήστε μία νέα σχέση για κάθε συναρτησιακή
εξάρτηση:
ΠΙΝΑΚΑΣ_Α(Χρηματιστής, Τύπος Μετοχής)
ΠΙΝΑΚΑΣ_Β(ΚωδΣυναλλαγής, Χρηματιστής)
Σε αυτό το τελευταίο βήμα κρατήσαμε το προσδιοριστικό γνώρισμα " Χρηματιστής " στην αρχική σχέση ΠινακαςΑ.
Οι περιορισμοί που διαπιστώνονται στον παραπάνω πίνακα είναι:
Ο εργαζόμενος συμμετέχει σε πολλά έργα.
Ο εργαζόμενος επιβλέπει περισσότερους του ενός εργαζομένους.
Άρα οι συναρτησιακές εξαρτήσεις που προκύπτουν είναι:
ΕΡΓΑΖΟΜΕΝΟΣ à ΕΡΓΟ
ΕΡΓΑΖΟΜΕΝΟΣ à ΕΞΑΡΤΩΜΕΝΟΣ
Δηλαδή παρατηρούμε ότι :
1. Υπάρχουν τουλάχιστον 3 πεδία στην σχέση. ΕΡΓΑΖΟΜΕΝΟΣ, ΕΡΓΟ, ΕΞΑΡΤΩΜΕΝΟΣ
2. Ο ΕΡΓΑΖΟΜΕΝΟΣ μπορεί να καθορίσει πολλές τιμές για το ΕΡΓΟ επίσης ΕΡΓΑΖΟΜΕΝΟΣ μπορεί να καθορίσει πολλές τιμές για το πεδίου ΕΞΑΡΤΩΜΕΝΟΣ.
3. Ο ΕΞΑΡΤΩΜΕΝΟΣ και το ΕΡΓΟ είναι ανεξάρτητα μεταξύ τους.
Η λύση που προβλέπεται είναι δύο πίνακες που συσχετίζονται μεταξύ τους
ΕΡΓΑΖΟΜΕΝΟΣ |
ΕΡΓΟ |
|
|
|
ΕΡΓΑΖΟΜΕΝΟΣ |
ΕΞΑΡΤΩΜΕΝΟΣ |
ΠΑΠΑΣ |
Α |
|
|
|
ΠΑΠΑΣ |
ΓΙΑΝΝΗΣ |
ΠΑΠΑΣ |
Β |
|
|
|
ΠΑΠΑΣ |
ΣΟΦΙΑ |
ΔΗΜΟΥ |
Δ |
|
|
|
ΔΗΜΟΥ |
ΚΩΣΤΑΣ |
ΔΗΜΟΥ |
Α |
|
|
|
ΔΗΜΟΥ |
ΝΙΚΗ |
ΔΗΜΟΥ |
Β |
|
|
|
ΔΗΜΟΥ |
ΣΤΕΛΛΑ |
Οι προβολές πάνω στα πεδία του πίνακα είναι. ΑΒ, ΑΓ, ΑΔ ….
Οι πίνακες που προκείπτουν είναι:
R1(A,B), R2(A,Γ), R31(A,Δ), R4(A,Ε), R5(A,Ζ), R6(A,Η)
Η σύζευξη των πινάκων R1, R2,R3,R4,R5,R6 δίνει το ίδιο αποτέλεσμα με τον ΠΙΝΑΚΑ_Α. Δηλ. με την εντολη
SELECT R1.A, B,Γ,Δ,Ε,Ζ,Η
FROM R1,R2,R3,R4,R5,R6
WHERE R1.A=R2.A AND R1.A=R3.A AND
R1.A=R4.A AND R1.A=R5.A AND
R1.A=R6.A
Λύση 9
CREATE TABLE ATZENTA (
AA INT NOT NULL,
EPITHETO
VARCHAR(50),
ONOMA VARCHAR(50),
ON_PATROS VARCHAR(50),
HM_GENNHSIS SMALLDATETIME,
ODOS VARCHAR(50),
AR VARCHAR(50),
TK VARCHAR(50),
…………………………
………………………….. ,
PRIMARY KEY (AA))
Λύση 10
SELECT *
FROM ATZENTA
WHERE EPITHETO LIKE ‘K%’
Λύση 11
SELECT ΕPΙΤΗΕΤΟ, ΟΝΟΜΑ,THL
FROM ATZENTA
WHERE ΗΜ_GΕΝΝΗSIS
>’1-1-
SELECT POLH,
COUNT(AA) AS PLHTHOS
FROM ΑΤΖΕΝΤΑ
GROUP BY POLH
Λύση 13
SELECT *
FROM AZENTA
WHERE POLH = (SELCET POLH
FROM ATZENTA
WHERE EPITHETO=’Γεωργίου’)
Λύση 14
SELECT *
FROM AZENTA
WHERE POLH ‘Αθήνα’ OR
POLH ‘Θεσσαλονίκη
Λύση 15
INSERT INTO ATZENTA
VALUES(123, ‘ΔΗΜΗΤΡΙΟΥ’, ‘ΙΩΑΝΝΗΣ’, ‘ΓΕΩΡΓΙΟΣ’, #7-7-1974#, ‘ΕΡΜΟΥ’, 12, ‘62122’, ‘ΣΕΡΡΕΣ’, ‘23210-
Λύση 16
UPDATE ATZENTA
SET THL=
‘23210-
WHERE AA=1234
ALTER TABLE ATZENTA ADD EMAIL VARCHAR(20)
ALTER TABLE ATZENTA MODIFY EMAIL VARCHAR(50)
Λύση 19
ALTER TABLE ATZENTA DROP EMAIL
Λύση 20
DELETE FROM ATZENTA
Λύση 21
DROP TABLE ATZENTA
Λύση 22
Α.
CREATE TABLE KATEXOYN (
KI INT NOT NULL,
KA INT NOT NULL,
ΠΟΣΟΣΤΟ REAL ,
PRIMARY KEY (KI,KA),
FOREIGN KEY KI REFERENCES ΙΔΙΟΚΤΗΤΕΣ(ΚΙ),
FOREIGN KEY KΑ REFERENCES ΑΚΙΝΗΤΑ(ΚΑ) )
Β.
SELECT ΚΑΤΕΧΟΥΝ.KI, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΚΑΤΕΧΟΥΝ.
ΚΑ, ΘΕΣΗ, ΑΝΤ_ΑΞΙΑ, ΣΥΝΤΕΛ_ΔΟΜ, (ΕΚΤΑΣΗ * ΠΟΣΟΣΤΟ) AS ΙΔΙΟΚΤΗΤΗ_ΕΚΤΑΣΗ
FROM ΙΔΙΟΚΤΗΤΕΣ
INNER JOIN
(ΚΑΤΕΧΟΥΝ INNER JOIN
ΑΚΙΝΗΤΑ ΟΝ ΚΑΤΕΧΟΥΝ.ΚΑ=
ΑΚΙΝΗΤΑ.ΚΑ)
ON ΙΔΙΟΚΤΗΤΕΣ.ΚΙ=ΚΑΤΕΧΟΥΝ.ΚΙ
Στο ερώτημα αυτό δίνουμε προσοχή ότι η έκταση που
κατέχει ένας ιδιοκτήτης υπολογίζεται από το γινόμενο της ΕΚΤΑΣΗΣ του ακινήτου
επί το ΠΟΣΟΣΤΟ που κατέχει. Δηλ. από το 100% της έκτασης ενός ακινήτου 200τμ
ένας ιδιοκτήτης με ποσοστό 20% κατέχει (20 * 200) / 100 = 4000/100= 40 τμ.
Γ.
SELECT ΚΑΤΕΧΟΥΝ.KI,
SUM(ΕΚΤΑΣΗ
* ΠΟΣΟΣΤΟ) AS ΣΥΝΟΛ_ΙΔ_ΕΚΤΑΣΗ
FROM ΚΑΤΕΧΟΥΝ INNER JΟΙΝ ΑΚΙΝΗΤΑ
ΟΝ
ΚΑΤΕΧΟΥΝ.ΚΑ= ΑΚΙΝΗΤΑ.ΚΑ
GROUP BY ΚΑΤΕΧΟΥΝ.KI
Δ.
SELECT ΚΑΤΕΧΟΥΝ.KI, SUM(ΕΚΤΑΣΗ
* ΠΟΣΟΣΤΟ) AS ΣΥΝΟΛ_ΙΔ_ΕΚΤΑΣΗ
FROM ΚΑΤΕΧΟΥΝ INNER JΟΙΝ ΑΚΙΝΗΤΑ
ΟΝ
ΚΑΤΕΧΟΥΝ.ΚΑ= ΑΚΙΝΗΤΑ.ΚΑ
GROUP BY ΚΑΤΕΧΟΥΝ.KI
HAVING SUM(ΕΚΤΑΣΗ
* ΠΟΣΟΣΤΟ) >= 10.000
Ε.
SELECT MESITES.KM, SUM(ANT_AΞΙΑ) AS
ΣΥΝΟΛ_ΑΞΙΑ
FROM ΜΕΣΙΤΕΣ
INNER JΟΙΝ
ΑΚΙΝΗΤΑ
ΟΝ
ΜΕΣΙΤΕΣ.ΚΜ= ΑΚΙΝΗΤΑ.ΚΜ
GROUP BY ΜΕΣΙΤΕΣ.ΚΜ
HAVING SUM(ANT_AΞΙΑ) >=
1.000.000
Στ.
SELECT ΘΕΣΗ
FROM ΑΚΙΝΗΤΑ
WHERE ANT_ΑΞΙΑ IN (SELECT MAX(ANT_ΑΞΙΑ)
FROM AKINHTA
Z.
Για να υλοποιήσουμε αυτό το ερώτημα θα πρέπει να βρούμε
πρώτα πόσα ακίνητα διαχειρίζεται κάθε μεσίτης και μετά να βρούμε ποιος είναι
αυτός που έχει τα περισσότερα. Για να το πετύχουμε αυτό θα χρησιμοποιήσουμε δύο
όψεις.
1. CREATE VIEW V1
AS
SELECT KM, COUNT(KA) AS ΠΛΗΘΟΣ1
FROM AKINHTA
GROUP BY KM
2. CREATE
VIEW V2 AS
SELECT ΜΕΣΙΤΕΣ.KM,
ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΠΛΗΘΟΣ1
FROM ΜΕΣΙΤΕΣ INNER JOIN V1 ON ΜΕΣΙΤΕΣ.KM=V1.KM
WHERE ΠΛΗΘΟΣ1 ΙΝ (SELECT
MAX(ΠΛΗΘΟΣ1)
FROM V1)
H.
Για να υλοποιήσουμε και αυτό το ερώτημα θα πρέπει
να βρούμε πρώτα πόσα ακίνητα κατέχει κάθε
ιδιοκτήτης και μετά να βρούμε ποιος είναι αυτός που κατέχει τα περισσότερα. Για
να το πετύχουμε αυτό θα χρησιμοποιήσουμε δύο όψεις.
1. CREATE VIEW V1
AS
SELECT ΚΙ, COUNT(KA) AS ΠΛΗΘΟΣ2
FROM ΚΑΤΕΧΟΥΝ
GROUP BY KΙ
2. CREATE
VIEW V2 AS
SELECT ΙΔΙΟΚΤΗΤΕΣ.KΙ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΠΛΗΘΟΣ2
FROM ΙΔΙΟΚΤΗΤΕΣ INNER
JOIN V1
ON ΙΔΙΟΚΤΗΤΕΣ.KI=V1.KI
WHERE ΠΛΗΘΟΣ2 ΙΝ (SELECT MAX(ΠΛΗΘΟΣ2)
FROM V1)
Θ.
Για να υλοποιήσουμε και αυτό το ερώτημα θα πρέπει
να βρούμε πρώτα πόσα ακίνητα κατέχει κάθε
ιδιοκτήτης και μετά να ταξινομήσουμε σε φθίνουσα σειρά το πλήθος των ακινήτων.
Για να το πετύχουμε αυτό θα χρησιμοποιήσουμε πάλι δύο όψεις.
1. CREATE
VIEW V1 AS
SELECT ΚΙ, COUNT(KA) AS ΠΛΗΘΟΣ2
FROM ΚΑΤΕΧΟΥΝ
GROUP BY KΙ
2. CREATE
VIEW V2 AS
SELECT ΙΔΙΟΚΤΗΤΕΣ.KΙ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ, ΠΛΗΘΟΣ2
FROM ΙΔΙΟΚΤΗΤΕΣ INNER
JOIN V1
ON ΙΔΙΟΚΤΗΤΕΣ.KI=V1.KI
ORDER BY ΠΛΗΘΟΣ2 DESC
Ι.
Για να υλοποιήσουμε και αυτό το ερώτημα θα πρέπει
να βρούμε πρώτα πόσα ακίνητα κατέχει
κάθε ιδιοκτήτης σε μια παραγόμενη σχέση
και μετά να ταξινομήσουμε σε φθίνουσα σειρά το πλήθος των ακινήτων.
SELECT KΙ, ΜAX( ΠΛΗΘΟΣ2)
FROM (SELECT ΚΙ, COUNT(KA) AS ΠΛΗΘΟΣ2
FROM ΚΑΤΕΧΟΥΝ
GROUP BY KΙ)
AS NEW_TABLE(KI,ΠΛΗΘΟΣ2)
ORDER BY ΠΛΗΘΟΣ2 DESC
Λύση 23
Για να δώσει αποτελέσματα σωστά αυτό το ερώτημα θα
πρέπει οι δύο αυτοί πίνακες να έχουν όμοια πεδία ίδιου τύπου δεδομένων, ώστε να
γίνει η ένωση των συνόλων.
(SELECT ΕΠΙΘΕΤΟ,
ΟΝΟΜΑ, ΤΗΛ
FROM ΙΔΙΟΚΤΗΤΕΣ
WHERE ΠΟΛΗ=’ΣΕΡΡΕΣ’)
(SELECT ΕΠΙΘΕΤΟ,
ΟΝΟΜΑ, ΤΗΛ
FROM ΜΕΣΙΤΕΣ
WHERE ΠΟΛΗ=’ΣΕΡΡΕΣ’)
Λύση 24
IF (SELECT COUNT(*)
FROM ΒΑΘΜΟΛΌΓΙΑ
WHERE ΚΜ = ‘403‘ AND ΒΑΘΜΟΣ >= 5
GROUP BY ΑΜ ) > 100
PRINT ‘Ο αριθμός των σπουδαστών που πέρασαν το μάθημα 403 είναι πάνω από 100'
ELSE BEGIN
PRINT ‘Οι σπουδαστές που πέρασαν στο μάθημα 403 είναι:’
SELECT ΣΠΟΥΔΑΣΤΕΣ.ΑΜ, ΕΠΙΘΕΤΟ, ΟΝΟΜΑ
FROM ΣΠΟΥΔΑΣΤΕΣ, ΒΑΘΜΟΛΟΓΙΑ
WHERE ΣΠΟΥΔΑΣΤΕΣ.ΑΜ = ΒΑΘΜΟΛΟΓΙΑ.ΑΜ AND ΚΜ=‘403’
END
Λύση 25
WHILE (SELECT SUM(ΠΡΟΫΠΟΛΟΓΙΣΜΟΣ)
FROM ΕΡΓΟ) < 100.000
BEGIN
UPDATE ΕΡΓΟ SET ΠΡΟΫΠΟΛΟΓΙΣΜΟΣ = ΠΡΟΫΠΟΛΟΓΙΣΜΟΣ *1.5
IF (SELECT ΠΡΟΫΠΟΛΟΓΙΣΜΟΣ)
FROM ΕΡΓΟ) > 50.000 BREAK
ELSE CONTINUE
END
Η εκτέλεση του παραδείγματος δίνει την πχ παρακάτω έξοδο:
(3 rows affected)
(2 rows affected)
Λύση 26
DECLARE @ΜΕΣΗ_ΤΙΜΗ_ΑΓ REAL, @ΑΥΞ_ΤΙΜΗΣ
REAL
SET @ΑΥΞ_ΤΙΜΗΣ = 10
SELECT @ΜΕΣΗ_ΤΙΜΗ_ΑΓ = AVG(ΤΙΜΗ_ΑΓ) FROM ΑΠΟΘΗΚΗ
IF (SELECT ΤΙΜΗ_ΑΓ
FROM ΑΠΟΘΗΚΗ
WHERE ΚΩΔ_ΕΙΔΟΥΣ=‘ΑΑ05') < @ΜΕΣΗ_ΤΙΜΗ_ΑΓ
BEGIN
UPDATE ΑΠΟΘΗΚΗ
SET ΤΙΜΗ_ΑΓ = ΤΙΜΗ_ΑΓ + @ΑΥΞ_ΤΙΜΗΣ
WHERE ΚΩΔ_ΕΙΔΟΥΣ =‘ΑΑ05'
PRINT ‘Η ΤΙΜΗ ΑΓΟΡΑΣ ΑΥΞΗΘΗΚΕ ΚΑΤΑ @ΑΥΞ_ΤΙΜΗΣ'
END
ELSE PRINT ‘Η ΤΙΜΗ ΑΓΟΡΑΣ ΔΕΝ ΑΛΑΞΕ’
Λύση 27
CREATE PROCEDURE ΑΥΞΗΣΗ_ΛΙΑΝ_ΤΙΜΗΣ
(@ΠΟΣΟΣΤΟ REAL=5)
AS UPDATE ΑΠΟΘΗΚΗ
SET TIMH_ΛΙΑΝ=ΤΙΜΗ_ΛΙΑΝ*(1+@ΠΟΣΟΣΤΟ/100)
CREATE PROCEDURE ΑΥΞΗΣΗ_bathmon ( @ΤΙΜΗ REAL=0.5)
AS UPDATE [ΒΑΘΜΟΙ_ΣΠΟΥΔΑΣΤΩΝ]
SET
ΒΑΘΜΟΣ=ΒΑΘΜΟΣ+@ΤΙΜΗ
WHERE ΒΑΘΜΟΣ >=4 ΑΝD ΒΑΘΜΟΣ <5
EXECUTE ΑΥΞΗΣΗ_ΛΙΑΝ_ΤΙΜΗΣ 15
EXECUTE ΑΥΞΗΣΗ_bathmon 3
CREATE FUNCTION computecosts (©percent INT =10)
RETURNS DECIMAL(16,2) AS
BEGIN
DECLARE @additional_costs DEC (14,2), @sum_budget deed 6,2)
SELECT @sum_budget = SUM (budget) FROM project
SET @additional_costs = @sum_budget * @percent/100
RETURN @additional_costs
END
Λύση 31
CREATE FUNCTION ΣΠΟΥΔΑΣΤΕΣ_ΜΑΘΗΜΑ (@ΚΩΔ_ΜΑΘ VARCHAR(4))
RETURNS TABLE AS RETURN
(SELECT ΕΠΙΘΕΤΟ, ΟΝΟΜΑ, ΤΗΛ
FROM ΣΠΟΥΔΑΣΤΕΣ, ΒΑΘΜΟΛΟΓΙΑ
WHERE ΣΠΟΥΔΑΣΤΕΣ.ΑΜ = ΒΑΘΜΟΛΟΓΙΑ.ΑΜ
AND ΚΜ = @ΚΩΔ_ΜΑΘ)
εκτέλεση της
παραπάνω συνάρτησης
SELECT * FROM ΣΠΟΥΔΑΣΤΕΣ_ΜΑΘΗΜΑ (‘403ΒΔ_Θ')
Το αποτέλεσμα είναι
ΕΠΙΘΕΤΟ ΟΝΟΜΑ ΤΗΛ
ΔΗΜΗΤΡΙΟΥ ΙΩΑΝΝΗΣ 23456
ΠΑΠΠΑ ΕΙΡΗΝΗ 34567
ΒΡΥΖΑΣ ΖΗΣΗΣ 45678
CREATE TABLE LOG_AUTO(
AUTO_NUM VARCHAR(10),
USER_ID
VARCHAR(20),
HMEROMHNIA
DATETIME,
OLD
FLOAT,
NEW
FLOAT)
CREATE
TRIGGER TRIG_LOG ON AYTOKINHTA
AFTER
UPDATE AS
IF
UPDATE(TIMH_ENOIKIASIS)
BEGIN
DECLARE @TIMH_old FLOAT
DECLARE @TIMH_new
FLOAT
DECLARE @auto_number VARCHAR(10)
SELECT @TIMH_old = (SELECT TIMH_ENOIKIASIS FROM
deleted)
SELECT @TIMH_new = (SELECT TIMH_ENOIKIASIS FROM
inserted)
SELECT @auto_number = (SELECT KA FROM deleted)
INSERT INTO LOG_AUTO VALUES
(@auto_number,USER_NAME(),GETDATE(),@TIMH_old,
@TIMH_new)
END
To παράδειγμα αυτό δείχνει πώς μπορούν να χρησιμοποιηθούν οι σκανδάλες για να υλοποιήσουν ένα ημερολόγιο παρακολούθησης της κίνησης των συναλλαγών των χρηστών πάνω σε ένα πίνακα. Σε κάθε τροποποίηση της στήλης TIMH_ENOIKIASIS χρησιμοποιώντας την πρόταση UPDATE ενεργοποιείται η σκανδάλη. Έτσι, οι τιμές των γραμμών των εικονικών πινάκων deleted και inserted εκχωρούνται στις αντίστοιχες μεταβλητές @TIMH_old, @TIMH_new και @auto_number. Οι εκχωρημένες τιμές, μαζί με το όνομα χρήστη και την τρέχουσα ημερομηνία και ώρα, θα εισαχθούν κατόπιν στον πίνακα LOG_AUTO.
Αν εκτελεστεί η παρακάτω πρόταση SQL:
UPDATE AYTOKINHTA
SET
TIMH_ENOIKIASIS=200 WHERE KA=1'
Τα περιεχόμενα του πίνακα LOG_AUTO θα είναι ως εξής:
AUTO_NUM USER_ID HMEROMHNIA OLD NEW
1 dbo 2008-06-06
T1: BEGIN
R(Χ), X=Χ-Κ, W(X), R(Y), Y=Y+Κ, W(Y),
END
T2: BEGIN R(X) X=X+M, W(X) END
Οι παραπάνω συναλλαγές θα μπορούσαν πιο γενικά να γραφούν όπως παρακάτω χωρίς να χάσουν το νόημά τους.
Τ1: R(X) W(X)
R(Y) W(Y) C
T2: R(X)
W(X) C
Αυτή η ακολουθία πράξεων εκφράζει μια συγκεκριμένη εκτέλεση ενός συνόλου Συναλλαγών
Οι πράξεις των Συναλλαγών εμφανίζονται σε ένα χρονοπρόγραμμα με τη σειρά που εκτελούνται.
S2: R2(X) R1(X) W1(X) R1(Y) W2(X) C2 W1(Y) C1
S3: R2(X) W2(X) C2 R1(X) W1(X) R1(Y) W1(Y) C1
S4: R2(X) R1(X) W1(X) R1(Y) W1(Y) C1 W2(X) C2
Στην περίπτωση αυτή όπως φαίνεται από το παρακάτω σχήμα η συναλλαγή Α την χρονική στιγμή t1 κάνει ανάγνωση μιας συστοιχίας Χ, η συναλλαγή Β την χρονική στιγμή t2 κάνει ανάγνωση της ίδιας συστοιχίας Χ, η συναλλαγή Α την χρονική στιγμή t3 κάνει ενημέρωση της συστοιχίας Χ, η συναλλαγή Β την χρονική στιγμή t4 κάνει ενημέρωση της ίδιας συστοιχίας Χ. Η ενημέρωση της συναλλαγής Α χάνεται τη χρονική στιγμή t4, επειδή η συναλλαγή Β την αντικαθιστά χωρίς ούτε καν να την κοιτάξει.
Α Β
-----------------------------------------------------------
R(X) t1 -
- t2 R(X)
W(X) t3 -
- t4 W(X)
Στην περίπτωση αυτή όπως φαίνεται από το παρακάτω σχήμα η
συναλλαγή Β την χρονική στιγμή t1
κάνει ενημέρωση μιας
συστοιχίας Χ, η συναλλαγή Α την χρονική στιγμή t2 κάνει ανάγνωση της ίδιας συστοιχίας Χ, η συναλλαγή Α
την χρονική στιγμή t3
κάνει ενημέρωση της
συστοιχίας Χ, η συναλλαγή Β την χρονική στιγμή t4 κάνει ακύρωση της ενημέρωσης της συστοιχίας Χ.
Οπότε η πρώτη συναλλαγή θα έχει δει κάποια δεδομένα που τώρα δεν υπάρχουν πια
(και κατά μία έννοια, "ποτέ" δεν υπήρξαν).
Α Β
--------------------------------------------------------
t1
W(X)
R(X) t2 -
W(X) t3
- t4 ABORT
Η συναλλαγή Α λειτουργεί λοιπόν με μια εσφαλμένη παραδοχή, συγκεκριμένα, την παραδοχή ότι η συστοιχία X έχει την τιμή που είδε τη στιγμή t2, ενώ στην πραγματικότητα έχει την όποια τιμή απέκτησε πριν από τη στιγμή t1. Ως αποτέλεσμα, η συναλλαγή Α μπορεί να δώσει λανθασμένο αποτέλεσμα. Σημειώστε, με την ευκαιρία, ότι η ανασκευή (ABORT ή ROLLBACK) της συναλλαγής Β μπορεί να μην οφείλεται σε σφάλμα της Β, μπορεί, για παράδειγμα, να οφείλεται σε μια κατάρρευση συστήματος. (Επίσης, η συναλλαγή Α μπορεί να έχει ήδη τερματιστεί εκείνη τη στιγμή, οπότε η κατάρρευση δε θα προκαλούσε ανασκευή και για την Α.
Ας εξετάσουμε την Εικόνα 8.3, που δείχνει δύο συναλλαγές, τις Α και Β, οι οποίες επενεργούν σε συστοιχίες λογαριασμών (RecA): Η συναλλαγή Α αθροίζει υπόλοιπα λογαριασμών, και η συναλλαγή Β μεταφέρει ένα ποσό 10 από το λογαριασμό RecA 3 στο λογαριασμό RecA 1.
Το αποτέλεσμα που παράγεται από την Α, το ποσό 410, είναι προφανώς λανθασμένο. Aν η Α προχωρούσε στην καταγραφή αυτού του αποτελέσματος στη βάση δεδομένων, η πράξη αυτή θα άφηνε τη βάση δεδομένων σε ασυνεπή κατάσταση. Λέμε ότι η Α είδε μια ασυνεπή κατάσταση της βάσης δεδομένων και γι' αυτό πραγματοποίησε μια ασυνεπή ανάλυση. Προσέξτε τη διαφορά ανάμεσα σε αυτό το παράδειγμα και το προηγούμενο: εδώ, δεν υπάρχει ζήτημα εξάρτησης της Α από μια ανεπικύρωτη μεταβολή, αφού η Β επικυρώνει (COMMIT) όλες τις ενημερώσεις της πριν δει η Α το λογαριασμό RecA 3.
[RecA 1=140, RecA2=150, RecA3 =130]
Α Β
--------------------------------------------------------------
R(X) RecA1
sum=40 t1 -
R(X) RecA2
sum=290 t2 -
- t3 R(X) RecA3
- t4 W(X) RecA3 130à120
- t5 R(X) RecA1
- t6 R(X) RecA1 140à150
- t7 COMMIT
R(X) RecA3 t8 -
sum=410 και όχι 420
Έστω μια συστοιχία ρ έστω ότι η συναλλαγή Α κατέχει αυτή τη στιγμή ένα κλείδωμα στην ρ, όπως δείχνουν οι καταχωρίσεις στις επικεφαλίδες των στηλών (παύλα = χωρίς κλείδωμα)', και έστω ότι κάποια άλλη συναλλαγή Β υποβάλλει αίτηση για ένα κλείδωμα στην ρ, όπως δείχνουν οι καταχωρίσεις στην αριστερή πλευρά (για πληρότητα, συμπεριλάβαμε πάλι την περίπτωση "χωρίς κλείδωμα").
|
X |
S |
- |
X |
O |
O |
N |
S |
O |
N |
N |
- |
N |
N |
N |
Το "Ο" (όχι) σημαίνει σύγκρουση (conflict δηλαδή η αίτηση της Β δεν μπορεί να ικανοποιηθεί και η Β περνάει σε κατάσταση αναμονής), ενώ το "Ν" (ναι) σημαίνει συμβατότητα (η αίτηση της Β ικανοποιείται). Το μητρώο είναι προφανώς συμμετρικό.
Α Β
---------------------------------------------------------------
R(p) S t1 -
- t2 R(p) S
W(p) X t3 -
αναμονή t4 W(p) X
αναμονή αναμονή
Η πράξη ενημέρωσης της συναλλαγής Α τη χρονική στιγμή t3 δε γίνεται δεκτή, γιατί είναι έμμεση αίτηση για ένα κλείδωμα Χ στην p, και μια τέτοια αίτηση έρχεται σε σύγκρουση με το κλείδωμα S που ήδη κατέχει η συναλλαγή Β, έτσι, η Α περνάει σε κατάσταση αναμονής. Για ανάλογους λόγους, η Β περνάει σε κατάσταση αναμονής τη χρονική στιγμή t4. Τώρα, και οι δύο συναλλαγές δεν μπορούν να προχωρήσουν, και έτσι δεν υπάρχει ζήτημα απώλειας κάποιας ενημέρωσης. Έτσι, το κλείδωμα λύνει το πρόβλημα της χαμένης ενημέρωσης, ανάγοντας το σε ένα άλλο πρόβλημα! Όμως τουλάχιστον λύνει το αρχικό πρόβλημα. Το νέο πρόβλημα ονομάζεται αδιέξοδο (deadlock).
Α Β
--------------------------------------------------------------------
t1 W(p) X
R(p) t2 -
ΑΝΑΜΟΝΗ
ΑΝΑΜΟΝΗ t3 Abort/ Commit
Συνέχεια R( p) S t4
Η πράξη που εκτελεί η συναλλαγή Α τη χρονική στιγμή t2 δε γίνεται δεκτή, επειδή είναι υπονοούμενη αίτηση για ένα κλείδωμα στην p, και μια τέτοια αίτηση έρχεται σε σύγκρουση με το κλείδωμα Χ που ήδη κατέχει η συναλλαγή Β, έτσι, η Α περνάει σε κατάσταση αναμονής.
Παραμένει σε αυτή την κατάσταση αναμονής μέχρι να φτάσει η Β στον τερματισμό της (είτε με COMMIT είτε με ABORT), οπότε το κλείδωμα της Β απελευθερώνεται και η Α μπορεί να προχωρήσει.
Σε εκείνο το σημείο, η Α βλέπει μια επικυρωμένη τιμή (είτε την τιμή πριν από την Β, αν η Β τερματίστηκε με ABORT, είτε την τιμή μετά από την Β, στην αντίθετη περίπτωση). Και στις δύο περιπτώσεις, η Α δεν είναι πια εξαρτημένη από την ανεπικύρωτη ενημέρωση. Δηλαδή το πρόβλημα αυτό λύθηκε τελείως με την χρήση κλειδωμάτων ανάγνωσης και εγγραφής.
Εφαρμόζοντας κλειδώματα εγγραφής και ανάγνωσης στο πρόβλημα της ασυνεπούς ανάλυσης φαίνεται ότι λύνεται το πρόβλημα άλλα δημιουργείται ένα νέο πρόβλημα
Η πράξη Write(p) της συναλλαγής Β τη χρονική στιγμή t6 δε γίνεται δεκτή, επειδή είναι υπονοούμενη αίτηση για ένα κλείδωμα Χ στο λογαριασμό RecA1, και μια τέτοια αίτηση έρχεται σε σύγκρουση με το κλείδωμα S που ήδη κατέχει η Α. Έτσι, η Β περνάει σε κατάσταση αναμονής
[RecA 1=140, RecA2=150, RecA3 =130]
Α Β
-------------------------------------------------------------------------------------------------
R(p) RecA1 S
sum=40 t1 -
R(p) RecA2
S sum=290 t2 -
- t3 R(p) RecA3 S
- t4 W(p) RecA3 X 130à120
- t5 R(p) RecA1 S
- t6 W(p) RecA1 140à150
R(p) RecA3 t7 Αναμονή
Αναμονή t8 Αναμονή
Αντίστοιχα, η Write(p) της συναλλαγής Α τη χρονική στιγμή t7 επίσης δε γίνεται αποδεκτή, επειδή είναι υπονοούμενη αίτηση για ένα κλείδωμα S στο λογαριασμό RecA 3, και μια τέτοια αίτηση έρχεται σε σύγκρουση με το κλείδωμα Χ που ήδη κατέχει η Β. Έτσι, η Α περνάει και αυτή σε κατάσταση αναμονής. Και πάλι λοιπόν, το κλείδωμα λύνει το αρχικό πρόβλημα (το πρόβλημα της ασυνεπούς ανάλυσης, σε αυτή την περίπτωση) προκαλώντας ένα αδιέξοδο.
Η παρακάτω Εικόνα 8 δείχνει ένα αδιέξοδο όπου εμπλέκονται δύο συναλλαγές, αλλά είναι δυνατό να υπάρξουν και αδιέξοδα όπου εμπλέκονται τρεις, τέσσερις, ή περισσότερες συναλλαγές, τουλάχιστον στη θεωρία. Στην πράξη σχεδόν ποτέ δεν εμπλέκονται στα αδιέξοδα περισσότερες από δύο συναλλαγές.
Α Β
---------------------------------------------------------------
Lock p1 X t1 -
- t2 Lock p2 X
Lock p2 X t3 -
αναμονή t4 Lock p1 X
αναμονή αναμονή
Παρακάτω φαίνεται το μητρώο συμβατότητας, διευρυμένο ώστε να περιλαμβάνει τα
προτιθέμενα κλειδώματα. (Ν=Ναι, Ο=Όχι).
GRANT
INSERT, SELECT ON Student TO
Alex
GRANT DELETE ON Student
TO Grammateia WITH GRANT OPTION
GRANT UPDATE (THL) ON Student TO User1
Ο χρήστης User1 μπορεί να τροποποιεί (μόνο) το γνώρισμα THL της σχέσης Student .
Αντικείμενα (π.χ., πίνακες, όψεις, πλειάδες)
Υποκείμενα (π.χ., χρήστες, προγράμματα)
Κλάσεις ασφάλειας:
Άκρως Απόρρητη (TS), Απόρρητη (S), Εμπιστευτική (C), Αδιαβάθμητη (U):
TS > S > C > U
Σε κάθε αντικείμενο και υποκείμενο ανατίθεται μια κλάση.
Το υποκείμενο S μπορεί να διαβάσει το αντικείμενο O μόνο αν class(S) >= class(O) (Απλή Ιδιότητα Ασφάλειας)
Το υποκείμενο S μπορεί να γράψει το αντικείμενο O μόνο αν class(S) <= class(O) (Ιδιότητα)
Η ιδέα είναι ότι πληροφορία δεν περνά ποτέ από μια υψηλή κλάση ασφάλειας σε μια χαμηλή κλάση ασφάλειας
Η υποχρεωτική προστασία εφαρμόζεται επιπρόσθετα της επιλεκτικής προστασίας.
Στον παρακάτω πίνακα καταχωρούν δεδομένα οι χρήστες με διαφορετικά προνόμια και διαφορετικές κλάσεις πρόσβασης.
Χρήστες με επίπεδο δικαιοδοσίας S και TS μπορούν να δουν και τις δυο γραμμές, οι χρήστες με C βλέπουν μόνο 2η γραμμή; ενώ χρήστες με U δε βλέπουν καμία.
Αν ένας χρήστης με C προσπαθήσει να εισάγει <101,VW Golf, Blue,C>, τι θα συμβεί;
Αν επιτρέψουμε την εισαγωγή, γίνετε παραβίαση του περιορισμού κλειδιού.
Αν δεν του επιτρέψουμε να εισάγει δεδομένα αποκαλύπτουμε ότι υπάρχει ένα άλλο αντικείμενο με κλειδί 101 και κλάση C. Και τελικά αποκαλύπτουμε ότι η κλάση είναι μέρος του κλειδιού ενώ δεν θα έπρεπε.
Το πρόβλημα που παρουσιάζεται το ονομάσαμε κανάλι διαρροής (covert channel). Η λύση σε αυτή την διαρροή πληροφόρησης σε μη εξουσιοδοτημένα άτομα μπορεί να δοθεί με την δημιουργία πολλαπλών στιγμιότυπων
Έστω ότι το απλό κείμενο που καλούμαστε να κρυπτογραφήσουμε είναι το παρακάτω με ένα αυθαίρετο κλειδί κρυπτογράφησης:
Απλό κείμενο (PLAINTEXT)= TMHMA PLHROFORIKHS
ΚΛΕΙΔΙ= TEST
Αλγόριθμος
1. Διαίρεση του κειμένου σε ίσα τμήματα με το κλειδί και αντικατάσταση του κενού με το σύμβολο +
TMHM A+PL HROF ORIK HS++
2. Αντικατάσταση χαρακτήρων με αριθμούς από 00-26.
Για λόγους απλότητας λαμβάνουμε
υπόψη μόνο τους λατινικούς χαρακτήρες.
Δηλ. το πεδίο ορισμού που μπορεί να πάρει ο κάθε χαρακτήρας είναι μόνο 27
λατινικά γράμματα. Στην πράξη πεδίο ορισμού θα μπορούσε να είναι το σύνολο των ASCII χαρακτήρων ή των Uniform χαρακτήρων.
Αντικαθιστούμε κάθε γράμμα με τον διψήφιο αριθμό του. Δηλ.
Κενό=00 Α=01, Β=02, C=03……Z=26
TMHM A+PL HROF
κείμενο 20130813 01001612 ……….
κλειδί 20051920 20051920 20051920
40182733 21133532 …………..
3. Άθροιση των παραπάνω
και διαίρεση του κάθε διψήφιου αριθμού με το 27
4.Το υπόλοιπο της διαίρεσης το κρατάμε
Στην πράξη αφαιρούμε το 27 από κάθε αριθμό που είναι μεγαλύτερος
του 27 και Κρατάμε την διαφορά. Διαφορετικά κρατάμε τον ίδιο τον αριθμό.
Αποτέλεσμα 13180006 21130805 …………..
5. Αντικατάσταση κάθε αριθμού με τον χαρακτήρα που αντιστοιχεί
ΜR+F UMHE ………...
Έστω p= 3 και q = 5, τότε r = 15, και το γινόμενο (p -1) * (q -1) =8
Έστω e = 11
Το e είναι ένας πρώτος αριθμός μεγαλύτερος και από το p και από το q και ο πλησιέστερος προς το γινόμενό τους.
Για να υπολογίσουμε το d, πρέπει να βρούμε το εξής: Ποίος είναι αυτός ο αριθμός ο οποίος αν πολλαπλασιαστεί με το e=11 και διαιρεθεί με το (p -1) * (q -1)=8 θα δώσει υπόλοιπο διαίρεσης ίσο με την μονάδα;
(d * 11) modulo 8 = 1
από το οποίο προκύπτει d = 3.
Έστω τώρα ότι το απλό κείμενο Ρ αποτελείται από το γράμμα Μ του προηγούμενου κώδικα των 27 χαρακτήρων που αντιστοιχεί στον ακέραιο αριθμό 13.
Τότε, το κρυπτογραφικό κείμενο C προκύπτει από τις παρακάτω πράξεις:
C = Pe modulo r = 1311 modulo 15 =
1.792.160.394.037 modulo 15 = 7
Τώρα, το αρχικό απλό κείμενο Ρ προκύπτει από τις πράξεις:
Ρ = Cd modulo r
= 73 modulo 15
= 343 modulo 15
= 13
Έστω ότι οι αλγόριθμοι κρυπτογράφησης είναι οι ECA και ECB (για την κρυπτογράφηση των μηνυμάτων που θα στέλνονται στον Α και στον Β, αντίστοιχα)
Έστω ότι οι αντίστοιχοι αλγόριθμοι αποκρυπτογράφησης είναι οι DCA και DCB, αντίστοιχα.
Οι αλγόριθμοι ECA και DCA είναι αντίστροφοι μεταξύ τους, όπως και οι ECB και DCB.
Ο Α εφαρμόζει πρώτα τον αλγόριθμο αποκρυπτογράφησης DCA στο Ρ, και στη συνέχεια κρυπτογραφεί το αποτέλεσμα και το μεταδίδει ως κρυπτογραφικό κείμενο C:
C = ECB ( DCA ( P ) )
Μόλις πάρει το C, ο χρήστης Β εφαρμόζει τον αλγόριθμο αποκρυπτογράφησης DCB και στη συνέχεια τον αλγόριθμο κρυπτογράφησης ECA, ώστε να προκύψει το τελικό αποτέλεσμα Ρ:
ECA ( DCB ( C ) )
= ECA ( DCB ( ECB ( DCA ( Ρ ) ) ) )
= ECA ( DCA ( Ρ ) ) επειδή τα DCB και ECB αναιρούνται
= Ρ επειδή τα ECA και DCA αναιρούνται
Τώρα, ο Β ξέρει ότι το μήνυμα προέρχεται πραγματικά από τον
Α, επειδή ο αλγόριθμος ECA θα δώσει το Ρ μόνο αν χρησιμοποιήθηκε ο αλγόριθμος
DCA στη διαδικασία κρυπτογράφησης, και αυτός ο αλγόριθμος είναι γνωστός μόνο στον
Α. Κανένας, ούτε ακόμα και ο Β, δεν μπορεί να πλαστογραφήσει την υπογραφή του
Α.
<ΒΙΒΛΙΟΘΗΚΗ>
<ΒΙΒΛΙΑ>
<ISBN> 0-07-228363-7 </ISBN>
<ΤΙΤΛΟΣ> Συστήματα Βάσεων Δεδομένων </ΤΙΤΛΟΣ>
<ΣΥΓΓΡΑΦΕΑΣ> Korth,Silberschatz </ ΣΥΓΓΡΑΦΕΑΣ>
</ΒΙΒΛΙΑ>
<ΑΡΘΡΑ>
<ΚΩΔ> 15-15-1001 </ΚΩΔ>
<ΤΙΤΛΟΣ> Storing and Querying XML </ΤΙΤΛΟΣ>
<ΣΥΓΓΡΑΦΕΑΣ> FLorescou, Kossman </ ΣΥΓΓΡΑΦΕΑΣ>
<ΕΚΔΟΣΗ> IEE Data, 1999 </ΕΚΔΟΣΗ>
</ΑΡΘΡΑ>
</ΒΙΒΛΙΟΘΗΚΗ>
<!DOCTYPE CLIENTS
[
<!ELEMENT CLIENTS (CODE,NAME,THL)*>
<!ELEMENT CODE ( #PCDATA)>
<!ELEMENT NAME (#PCDATA)>
<!ELEMENT THL (#PCDATA)>
<!ENTITY CLIENTS
“1234”
“ΙΟΑΝΝΙΔΗΣ ΣΩΤΗΡΗΣ”
“2321055555” >
]
>
Η οντότητα CLIENTS περιέχει δεδομένα χαρακτήρων τα οποία δηλώνονται με το μοντέλο περιεχομένου #PCDATA(parsed character data-αναλυμένα δεδομένα χαρακτήρων). Το όνομα του DTD συμπίπτει με το όνομα της οντότητας. Αυτό μη σας προβληματίζει διότι δεσμεύουν διαφορετικούς χώρους ονομάτων. Το σύμβολο * στο τέλος της παρένθεσης υποδηλώνει ότι τα γνωρίσματα (στοιχεία) της οντότητας μπορούν να επαναληφθούν καμία ή περισσότερες φορές. Συνεπώς μπορούμε να δηλώσουμε για παράδειγμα δύο αριθμούς τηλεφώνου ή και κανένα.
Θα τη μετατρέψουμε σε κώδικα xml ως εξής:
1.Δημιουργούμε
το έγγραφο xml μέσα σε
έναν κειμενογράφο π.χ στο σημειωματάριο, το οποίο θα αποτελείται από τον
πρόλογο και το βασικό στοιχείο. Έχουμε λοιπόν:
Student.xml
<? xml version=”1.0”?>
<!
-- File Name : Student.xml -- >
<? xml
–stylesheet type =”text/css” href=”Student.css”?>
<School>
<Student>
<am>
1234</am>
<name>
PAPAS NIKOS </name>
<address>
Delfon 13 </address>
<age>
21 </age>
<thl> 2321099999 </thl>
</Student>
<Student>
<am> …</am>
<name>
… </name>
<address> … </address>
<age> … </age>
<thl> ….. </thl>
</Student>
</School>
Ξεκινάμε με ένα σχόλιο στο οποίο αναφέρουμε το όνομα του εγγράφου με το οποίο θα το αποθηκεύσουμε. Έπειτα παραθέτουμε το στοιχείο του εγγράφου(εδώ χάριν απλότητας αναφέρουμε μόνο ένα στοιχείο) με τους κανόνες που θέλουμε να ακολουθήσουν κατά την εμφάνιση του στοιχείου αυτού στον Internet Explorer.
Όπως παρατηρούμε οι κανόνες αυτοί περιέχονται μέσα σε άγκιστρα και διαχωρίζονται μεταξύ τους με ελληνικό ερωτηματικό.
Ο πρώτος κανόνας λέει να εμφανιστεί κάθε στοιχείο με περιθώριο 12 στιγμών από πάνω(margin-top:12pt) και με μια αλλαγή γραμμής πάνω και κάτω(display:block) σε γραμματοσειρά 10 στιγμών (font-size:10pt) όπου να εμφανίζεται με έντονους(font-weight:bold) και πλάγιους χαρακτήρες(font-style:italic).
<?xml
version="1.0"?>
<!DOCTYPE
note [
<!ELEMENT note (to,from,heading,body)>
<!ELEMENT to (#PCDATA)>
<!ELEMENT from (#PCDATA)>
<!ELEMENT heading (#PCDATA)>
<!ELEMENT body (#PCDATA)>
]>
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this
weekend</body>
</note>
Βήμα 1 . Για κάθε σύνθετο στοιχείο, δημιουργείται ένας πίνακας και ένα αυτόματο πρωτεύον κλειδί . Π.χ Δημιουργία Πίνακων Order, Item, Part και των αντίστοιχων PK: OrderPK, ItemPK, PartPK .
Βήμα 2 . Για κάθε απλό στοιχείο δημιουργείται μια στήλη στον αντίστοιχο πίνακα. Π.χ Δημιουργία των στηλών. OrderNum, Date, CustNum (πίνακας Order) . ItemNum,Quantity (πίνακας Item) . PartNum, Price (πίνακας Part) .
Βήμα 3 . Παραγωγή ξένων κλειδιών για αναφορά στα σύνθετα στοιχεία . Π.χ Δημιουργία ξένου κλειδιού OrderFK (πίνακας Item), ItemFK (πίνακας Part)
Σε κάθε block χωράνε SB/SA=4 πλειάδες
Μέγεθος αρχείου δεδομένων SF= 10000/4=2500 blocks
Κατασκευάζουμε το πρωτεύον ευρετήριο ως εξής:
Έστω ότι το μέγεθος του πεδίου κλειδιού είναι SK = 10 bytes
Και το μέγεθος δείκτη block P = 6 bytes
Σε κάθε block ευρετηρίου χωράνε [SB/(SK+P)]=[1024/16]=64 εγγραφές
Μέγεθος αρχείου ευρετηρίου SI=[2500/64]= 40 blocks
Κόστος αναζήτησης
Αν θεωρήσουμε ότι το αρχείο δεδομένων είναι διατεταγμένο ως προς το πεδίο αναζήτησης μπορούμε να εφαρμόσουμε δυαδική αναζήτηση.
Χωρίς το ευρετήριο:
Θα πρέπει να φορτώσουμε:log2(SF)
blocks = log2(2500)=11,28 à12 blocks αρχείου και να συγκρίνουμε τις πλειάδες.
Αν υπάρχει ευρετήριο: Θα πρέπει να φορτώσουμε:log2(SΙ)
blocks = log2(40)=5,32à 6 blocks ευρετηρίου και 1 block αρχείου (αυτό που θα μας υποδείξει η αναζήτηση στο ευρετήριο).
Χωρίς ευρετήριο: log2(2500)=12
Με ευρετήριο: log2(40)+1=7
Κόστος ενημέρωσης
Κατά την εισαγωγή εγγραφής σε πίνακα μιας βάσης δεδομένων με πρωτεύον ευρετήριο συνεπάγονται αλλαγές στο αρχείο άρα και στο ευρετήριο. Οι αλλαγές μπορούν να αποθηκεύονται σε ξεχωριστό αρχείο υπερχείλισης (χωρίς διάταξη) ή σε συνδεδεμένη λίστα εγγραφών υπερχείλισης και το ευρετήριο να ενημερώνεται περιοδικά.
Στην περίπτωση διαγραφής μιας εγγραφής συνεπάγονται ξανά αλλαγές στο ευρετήριο. Συνήθως η περίπτωση αυτή αντιμετωπίζεται με σημάδια διαγραφής στην συγκεκριμένη εγγραφή.
Σε κάθε block χωράνε 1024/250= 4 πλειάδες
Μέγεθος αρχείου δεδομένων SF= 10000/4=2500 blocks
Κατασκευάζουμε ευρετήριο συστάδων (μη εκτεινόμενη καταχώρηση):
Έστω μέγεθος του πεδίου ευρετηρίασης είναι SK = 10 bytes
Μέγεθος δείκτη block P = 6 bytes
500 διαφορετικές τιμές στο πεδίο ευρετηρίασης με ομοιόμορφη κατανομή των πλειάδων σε αυτές
Σε κάθε block ευρετηρίου χωράνε όπως πριν 1024/16=64 εγγραφές
Μέγεθος αρχείου ευρετηρίου SI= 500/64 =7,8 à8 blocks
Κόστος αναζήτησης
Θεωρούμε ότι το αρχείο δεδομένων είναι διατεταγμένο ως προς το πεδίο αναζήτησης
μπορούμε να εφαρμόσουμε δυαδική αναζήτηση. Τα στοιχεία που έχουμε είναι: 500 διαφορετικές τιμές, 10000/500=20 πλειάδες με την ίδια τιμή πεδίου αναζήτησης
20/4=5 block αντιστοιχούν σε κάθε τιμή
Χωρίς το ευρετήριο: Θα πρέπει να φορτώσουμε
log2(SF) blocks = log2(2500)=11,28à 12 blocks αρχείου και 5 block για τις πλειάδες που έχουν την τιμή αναζήτησης
Αν υπάρχει ευρετήριο: Θα πρέπει να φορτώσουμε
log2(SΙ) blocks = log2(8)=5,32à 3 blocks ευρετηρίου και 5 block αρχείου (για τις πλειάδες που έχουν την τιμή αναζήτησης)
Χωρίς ευρετήριο: log2(2500)+5=17
Με ευρετήριο: log2(8)+5=8
Σε κάθε block χωράνε 1024/250= 4 πλειάδες
Μέγεθος αρχείου δεδομένων SF= 10000/4=2500 blocks
Κατασκευάζουμε δευτερεύον ευρετήριο (μη εκτεινόμενη καταχώρηση):
Έστω μέγεθος του πεδίου ευρετηρίασης είναι SK = 10 bytes
Μέγεθος δείκτη block P = 6 bytes
Σε κάθε block ευρετηρίου χωράνε όπως πριν 1024/16=64 εγγραφές
Μέγεθος αρχείου ευρετηρίου SI= 10000/64 =156,25à157 blocks
Κόστος αναζήτησης
Αναζήτηση χωρίς ευρετήριο (σειριακή αναζήτηση, γιατί το αρχείο δεδομένων δεν είναι
ταξινομημένο): 2500/2 = 1250 blocks
Αναζήτηση με ευρετήριο: Δυαδική αναζήτηση στο δευτερεύον ευρετήριο
Ανάγνωση του block από το αρχείο δεδομένων
log 2(157)+ 1 = 9 blocks