Appendix 1: DB Project Example [616347]

Appendix 1: DB Project Example

UNIVERSITY “POLITEHN ICA” OF B UCHAREST
FACULTY OF ENGINEERI NG IN FOREIGN LANGUA GES
COMPUTERS AND INFORM ATION TECHNOLOGY ENG LISH STREAM

Databases Project
Design, implementation, and usage of
a Library db

Project coordinator Student: [anonimizat]. Prof. Dr.
Christian Mancas

Bucharest
201x

1
Table of Content s

1. Business Analysis
1.0 Description of the sub -universe of discourse
1.1 Entity -Relationship Diagrams
1.2 Associated Restrictions List
2. Mathematical Scheme
2.0 Initial Mathematical Scheme
2.1 First refinement algorithm : Sets, Functions, and Constraints
Design Assistance
2.1.1 Sets
2.1.2 Functions
2.1.3 Constraints
2.2 Second refinement algorithm: Keys Discovery Assistance
2.3 Third refinement algorithm: E -RD Cycles Analysis
2.4 Final Mathematical Scheme
3. Relational Scheme and Associated Non-relational Constraints List
3.1 Relational db scheme
3.2 Non-relational constraints list
4. Database Implementation
4.0 Technology choice
4.1 Access db
4.2 Oracle db
5. Non-relational Constraints I mplementation
5.1 Access Solutio ns
5.2 Oracle Solutions
6. Database Usage
6.1 Access Queries and Reports
6.2 Oracle Views and Stored Procedures
Conclusion
References

2
1. Business Analysis
1.0 Description of the sub -universe of discourse
The db should store data on books (title, writing year, first author, co -authors, and their order),
people (authors and/or library subscribers) e -mail ad dresses and first and last names, as well as
books copies borrows by sub scribers (borrow, due, and actual return dates).
Books are published by pu blishi ng houses, possibly in several editions even by same publishers
(in different years). Each edition may contain se veral volumes, each hav ing a number, a title, a
price, and an ISBN code. Vo lumes may contain se veral books.
The library owns several cop ies (uniquely identified by an in ventory code) of any volume and may
lend several copies for any bor row; not all bor rowed copies should be returned at a same date;
maximum lend ing pe riod is 300 days.
Last names, books titles, first authors, pu blisher names, editions pu blishers, first books, and titles,
as well as volumes num bers and prices, co pies inventory codes, borrows subscribers, dates, and
due re turn dates are com pulsory; for subscribers, first names and e -mail addresses are com pulsory
too.
People are uniquely identified by their first and last names, plus e -mail ad dress, books by their
first author, title, and writing year, publishers by their names, editions by their first book, pu blisher,
title, and year, vo lumes by corresp onding edition and volume num ber.
There may be at most :
– 1,000,000 persons, books, and editions,
– 2,000,000 co -authoring and volumes,
– 10,000 pu blishers,
– 4,000,000 book editions ,
– 32,000,000 copies,
– 100,000,000,000 borrows, and
– 1,000,000,000,000 borr ows of interest.

3
1.1 Entity -Relationship Diagrams

Fname LName e-mail

PERSONS

BTitle BOOKS BYear

PERSONS CO-AUTHORS BOOKS

PosInList
PUBLISHERS PubName

ETitle EDITIONS EYear
ISBN Title
Number VOLUMES Price

BOOKS VOLUMES_C ONTENTS VOLUMES

BookPos

InvNo COPIES
BORROWS BorrowDate

4
COPIES BORROWS_LISTS BORROWS

DueReturnDate ActualReturnDate

FirstAuthor
PERSONS BOOKS
Subscriber CO-AUTHORS
BORROWS VOLUMES_CONTENTS
First Book
BORROWS_LISTS Volume VOLUMES
Publisher Edition
COPIES PUBLISHERS EDITIONS
Figure A1.1 Structural E -RD
1.2 Associated Restrictions List
1. PERSONS (The set of books (co -)authors and subscribers of in terest to the library.)
a. Cardinality: max(card( PERSONS )) = 1,000,000 (RP0)
b. Data ranges:
FName: ASCII(128) (RP1)
LName: ASCII(64) (RP2)
e-mail: ASCII(255) (RP3)
c. Compulsory data: LName (RP4)
d. Unicity: e -mail  FName  LName (there may not
be two persons having sam e first and last name,
as well as same e -mail address) (RP5)
e. Other types restrictions:
FName and e-mail should be compulsory for subscribers( RP6)

2. BOOKS (The set of written works of interest to the library.)
a. Cardinality: max(card( BOOKS )) = 1,000,000 (RB0)
b. Data ranges:
BTitle: ASCII(255) (RB1)
BYear: [-2500, current year] (RB2)
c. Compulsory data: BTitle, FirstAuthor (RB3)
d. Unicity: FirstAuthor  BTitle  BYear (no author writes

5
two books with a same title in a same year) (RB4)

3. CO-AUTHORS = (PERSONS, BOOKS ) (The set of pairs
<b, p> storing the fact that book b was (also) written by
person p.)
a. Cardinality: max(card( CO-AUTHORS )) = 2,000,000 (RCA 0)
b. Data ranges: PosInList: [2, 16] ( p’s position in b’s
co-authors list) (RCA 1)
c. Compulsory data: Person, Book, PosInList (RCA 2)
d. Unicity: Person  Book (no author should appear more
than once in any book co -authors list) (RCA 3)

4. PUBLISHERS (The set of publishers of interest to the library.)
a. Cardinality: max(card( PUBLISHERS )) = 10,000 (RPB0)
b. Data ranges: PubName: ASCII(128) (RPB1)
c. Compulsory data: PubName (RPB2)
d. Unicity: PubName (there may not be two publishers
having same name) (RPB3)

5. EDITION S (The set of books editions of interest to the library.)
a. Cardinality: max(card( EDITIONS )) = 1,000,000 (RE0)
b. Data ranges:
ETitle: ASCII(255) (RE1)
EYear: [-2500, current year] (RE2)
c. Compulsory data: Publisher , FirstBook, ETitle (RE3)
d. Unicity: Publisher  FirstBook  EYear (no publis –
her publishes more than one edition with any gi ven
book in any given year) (RE4)

6. VOLUMES (The set of editions volumes of interest to the library.)
a. Cardinality: max(card( VOLUMES )) = 2,000,000 (RV0)
b. Data ranges:
ISBN: ASCII(16) (RV1)
VTitle: ASCII(255) (RV2)
VNo: [1, 255] (RV3)
VPrice: CURRENCY(8) (RV4)
c. Compulsory data: Edition, VNo, VPrice (RV5)
d. Unicity: ISBN (by definition, ISBN s are unique for any volume) (RV6)

6
7. VOLU MES_CONTENTS = (BOOKS, VOLUMES ) (The set of pairs < b, v> storing the fact
that book b is (also) included in volume v.)
a. Cardinality: max(card( VOLUMES_ CONTENTS )) = 4,000,000 ( RVC 0)
b. Data ranges: BookPos: [1, 16] ( b’s position in v’s table
of contents) (RVC 1)
c. Compulsory data: Book, Volume, BookPos (RVC 2)
d. Unicity: Volume  Book (no book should be included
more than once in any volume) (RVC 3)
e. Other types restrictions:
For any edition, its first book should be the first one
published in its first volume. (RVC 4)
No edition may contain same book more than once. ( RVC 5)

8. COPIES (The set of editions volumes copies that the
library possessed.)
a. Cardinality: max(card( COPIES )) = 32,000,000 (RC0)
b. Data ranges: InvNo: ASCII(32) (RC1)
c. Compulsory data: InvNo, Volume (RC2)
d. Unicity: InvNo: (by definition, inventory numbers are
unique for any copy) (RC3)

9. BORROWS (The set of editions volumes copies borrows
by subscribers.)
a. Cardinality: max(card( BORROWS )) =
100,000,000,000 (RBR0)
b. Data ranges: BorrowDate: [6/1/2011, SysDate ()]
(assuming, for example, that first bor row date of
interest is June 1st, 2011) (RBR1)
c. Compulsory data: BorrowDate, Subscriber (RBR2)
d. Unicity: BorrowDate  Subscriber (no subscriber may
simultaneously borrow several times) (RBR3)

10. BORROWS_LISTS = (BORROWS , COPIES ) (The set of
pairs < b, c> storing the fact that volume copy c was
(also) borrowed in borrow b.)
a. Cardinality: max(card( BORROWS_ LISTS )) =
1,000,000,000,000 (RBL0)
b. Data ranges:
DueReturnDate: [6/1/2011, SysDate () + 300] (assuming,
for example, that maxi mum borrow period is 300 days)( RBL1)

7
ActualReturnDate: [6/1/2011, SysDate ()] (RBL2)
c. Compulsory data: Borrow, Copy, DueReturnDate (RBL3)
d. Unicity: Borrow  Copy (no copy may be simultaneously
borrowed more than once) (RBL4)
e. Other types restrictions:
No copy may be borrowed less than 0 days or more
than 300 days. (RBL5)
No copy may be simultaneously borrowed to more than
one subscriber. (RBL6)
No copy may be returned before it was borrowed and
after 100 years since cor respond ing borrow date. (RBL7)
2. Mathematical Scheme
2.0 Initial Mathematical Scheme
By applying the algorithm for translating E -RDDs and restriction lists to ma thematical schemes,
the following initial scheme results:
PERSONS
x  NAT(6), total
FName  ASCII(128)
LName  ASCII(64), total
e-mail  ASCII(255 )
CP5: e-mail  FName  LName key

BOOKS
x  NAT(6), total
BTitle  ASCII( 255), total
BYear  [-2500, Year (SysDate ())]
FirstAuthor : BOOKS  PERSONS , total
CB4: FirstAuthor  BTitle  BYear key

CO-AUTHORS = (PERSONS, BOOKS )
x  NAT(7), total
PosInList  [2, 16], total

PUBLISHERS
x  NAT(4), total
PubName  ASCII(128) , total

EDITIONS
x  NAT(6), total

8
ETitle  ASCII(255) , total
EYear  [-2500, Year (SysDate ())]
Publisher : EDITIONS  PUBLISHERS , total
FirstBook : EDITIONS  BOOKS , total
CE4: Publisher  FirstBook  EYear key

VOLUMES
x  NAT(7), total
ISBN  ASCII(16)
VTitle  ASCII(255)
VNo  [1, 255] , total
VPrice  CURRENCY(8) , total
Edition : VOLUMES  EDITIONS , total

VOLUMES_CONTENTS = (BOOKS, VOLUMES )
x  NAT(7), total
BookPos  [1, 16] , total

COPI ES
x  NAT(8), total
InvN o  ASCII(32) , total
Volume : COPIES  VOLUMES , total

BORROWS
x  NAT(11), total
BorrowDate  [6/1/2011, SysDate ()], total
Subscriber : BORROWS  PERSONS , total
CBR3: BorrowDate  Subscriber key

BORROWS_LISTS = (BORROWS , COPIES )
x  NAT(12), total
DueReturnDate  [6/1/2011, SysDate () + 300] , total
ActualReturnDate  [6/1/2011, SysDate ()]
CBL4: Borrow  Copy key
2.1 First refinement algorithm: Sets, Functions, and Constraints Design Assistance
Here are the results of applying the desig n assistance algorithm for:
2.1.1 Sets
a.1 No set is semantically overloaded (so no structural refinements are ne eded from this point of
view). Please review chap ter 2, where this sub -universe modeling is discussed too contrastively,
as compared to several poorer so lution s!
a.2 No set is a subset of another set (so no inclusion constraints need to be ad ded to the scheme).

9
a.3 No association -type set has arity greater than two (so none has to be re placed by its equivalent
entity -type set and explici t structural keys and func tions cor responding to its canonical Cartesian
projections).
a.4 All binary associations are non -functional:
– CO-AUTHORS is not functional, because there are persons that wrote several books , as
well as books that were written by several persons.
– VOLUMES_CONTENTS is not functional, because there are volumes that contain several
books , as well as books that span across several volumes .
– BORROWS_LISTS is not functional, because there are borrows in cluding several copies ,
as well as copies borrowed several times .
a.5 No association -type object set is ill -defined :
– CO-AUTHORS is well defined according to restriction RCA 3;
– VOLUMES_CONTENTS is well defined according to restriction RVC 3;
– BORROWS_LISTS is well defined according to rest riction RBL4.
a.6 No association is homogeneous (so that we need not investigate re flexivity, irreflexivity,
symmetry, anti -symmetry, etc.).
2.1.2 Functions
b.1 All functions are well defined:
– people (be them authors or subscribe rs) have only one e-mail address (of in terest to the library) ,
first, and last name;
– books , editions , and volumes have only one respective title ;
– books and editions have only one respective year ();
– co-authors and volumes contents entries have only one associated respective position ;
– publishers have only one name;
– volumes have at most one ISBN value and price, as well as one number;
– copies have only one inventory number;
– borrows have only one associated calendar date;
– borrows lists entries have only one due an d actual return dates ;
– books have only one first author;
– editions have only one publisher and first book;

10
– volumes belong to only one edition;
– copies are instances of only one volume;
– finally, borrows are made by only one subscriber.
b.2 No other function (except for the unique ones) is one -to-one:
– FName : there may be several persons having sa me first name;
– LName : there may be several persons having same last name;
– e-mail : there may be several persons hav ing same e-mail address (for exam ple children and
parents, spouses, etc. );
– BTitle : there may be several books having same title (even wrote by same authors);
– FirstAuthor : there may be several books having a same first author ;
– PosInList : there may be several authors in the same co -authors list position ( in different co-
authors lists )
– ETitle : there may be several editions having same title (even published by same publishers);
– EYear : there may be several editions published in a same year (even from a same publisher) ;
– Publisher : there may be several editions published by a same publisher;
– FirstBook : there may be several editions having same first book (even from a same publisher);
– VTitle : there may be several volumes having same title (in different edi tions);
– VNo: there may be several volumes having same number (in different editions);
– VPrice : there may be several volumes having same price (even in same edi tions);
– Edition : there may be several volumes for a same edition;
– BookPos : there may be several books having same position within vo lumes (in different edi tions);
– Volume : there may be several copies of a same volume;
– BorrowDate : there may be several borrows in a same day (by different subscribers);
– DueReturnDate : there may be several borrowed books having sam e due return day (even for a
same borrow);
– ActualReturnDate : there may be several borrowed books having same actual return day (even for
a same borrow) .

11
On the contrary, all those declared as one -to-one are really one -to-one:
– PubName : there may not be two publishers having same name;
– ISBN : there may not be two volumes having same ISBN;
– InvNo: there may not be two copies having same inventory number .
b.3 No function is onto, except for Edition :
– Edition is onto, because for any edition there should be at least one corresponding volume .
Consequently, the following constraint has to be added to this db scheme:
Edition : VOLUMES  EDITIONS , total, onto
– Obviously, FName , LastName , e-mail, BTitle , PubName , ETitle , ISBN , VTitle , and InvNo
are not, as not all possible ASCII cha racter combinations (of maximum 255/128/64/32/16
characters ) should be first or last person, publisher , or ISBN actual values;
– just as no calendar dates (between June 1st 2011 and up to today or next year) or years
(between -2500 and th e current one) should (so BYear , EYear , BorrowDate ,
DueReturnDate , and ActualReturn Date are not onto either);
– just like no naturals (between 1/2 and 16/255) or rationals (bet ween 0 and 99,999,999.99)
should (so PosInList , VNo, VPrice , and Book Pos are n ot onto either) .
– FirstAuthor is not either, as not only that not all subscribers are au thors too (in fact, most
of them are not), but not even all authors are first authors .
– Publisher and FirstBook are not either, as it is not compulsory that all pos sible
publishers /books be editing (to the current db instance know ledge) / edited , respectively.
– Volume is not either, as there might be volumes (in the current db instance) for which there
are no copies .
– Subscriber is not either, as not all known (to the db instance) persons should have borrowed
books from the library (e.g. Homer, Shakespeare, etc.) .
b.4 There are no bijective functions.
b.5 There are no auto-functions (so that we need not investigate re flexivity, irreflexivity,
symmetry, anti -symmetry , etc.).
b.6 There are no canonical surjections (representative systems).
b.7 There are no object sets having no totally defined functions.
2.1.3 Constraints
c.1 There are six not formalized constraint s; here are their corresponding for malizations :
– RP6: FName and e-mail should be compulsory for subscribers.
CP6: (bBORROWS )(FName (Subscriber (b))  NULLS 

12
e-mail(Subscriber (b))  NULLS)
– RVC 4: For any edition, its first book should be the first one published in its first volume.
CVC4: (eEDITIONS )(vcVOLUMES_CONTENTS )
(e = Edition (Volume (vc))  FirstBook (e) = Book (vc)  BookPos (vc) = 1 )
– RVC 5: No edition may contain same book more than once.
CVC5: (eEDITIONS )(vc,vc’VOLUMES_CONTENTS )
(Edition (Volume (vc)) = Edition (Volume (vc’))  Book (vc)  Book (vc’))
– RBL5: No copy may be borrowed less than 0 days or more than 300 days.
CBL5: (blBORROWS_LISTS )
(0  DueReturnDate (bl) – BorrowDate (Borrow (bl))  300)
– RBL6: No copy may be simultaneously borrowed to more than one sub scriber.
CBL6: (bl, bl’BOR ROWS_LISTS ) (Copy (bl) = Copy (bl’)) 
(Actual ReturnDate (bl’)  NULLS  BorrowDate (Borrow (bl)  Actual ReturnDate (bl’) 
Actual ReturnDate (bl)  NULLS  BorrowDate (Borrow (bl’)  Actual ReturnDate (bl))
– RBL7: No copy may be returned before it was borrowed an d after 100 years since cor respond ing
borrow date.
CBL7: (blBORROWS_LISTS )
(0  Actual ReturnDate (bl) – BorrowDate (Borrow (bl))  36,500)
c.2 There are no other constraints that apply in this sub -universe too, bu t are mis sing from this
model.
2.2 Second r efinement algorithm: Keys Discovery Assistance
Applying the assistance algorithm for keys discovery yields the fol lowing:
PERSONS and BOOKS
n = 3, as there are three not one -to-one mappi ngs defined on both of them (FName , LName , and
e-mail for PERSONS , and BTitle , BYear , and FirstAuthor for BOOKS ), all being prime in
this context and form ing a (se mantic) key for each of these two object sets (according to
CP5 and CB4, respectively )  no other (semantic ) keys may exist for either of these sets .
PUBLISH ERS
n = 0  no other semantic key exist.
COPIES
n = 1 (nothing to do, as according to b.2 above, Volume is not one-to-one)  no other semantic
key exist.
BORROWS
n = 2 (nothing to do, as according to CBR 3 above, BorrowDate  Subscriber is minimally one -to-
one)  no other semantic key exist.

13
CO-AUTHORS
n = 3, as there are three not one -to-one mappings defined on it, all of them be ing prime : the two
canonical Cartesian projections Author and Book , plus PosIn List. Obviously, K = {Author
 Book }, as CO-AUTHORS is a well -defined binary association (see restriction RCA 3).
i = 2:
– Author  PosIn List key? No, because several authors (and even same ones) may occupy a same
position in different co -author lists .
– Book  PosIn List key? YES , because in any pos ition of any co -author list only one co -author
may appear .
Consequently, K = {Author  Book , Book  PosIn List}.

EDITIONS
n = 4, as there are four not one -to-one mappings defined on it, all being prime: ETitle , Publisher ,
FirstBook , and EYear . According t o CE4, K = {Publisher  FirstBook  EYear }, as this
product is minimally one-to-one.
i = 2
– ETitle  Publisher key? No, because even a same publisher may publish se veral editions, even
of a same book (although not necessarily), hav ing same title.
– ETitle  FirstBook key? No, because even a same publisher may publish se veral editions, even
of a same book (although not necessarily), hav ing same title and first book .
– ETitle  EYear key? No, because even a same publisher may publish se veral editions (of different
books, generally ) in a same year.
i = 3
– ETitle  Publisher  FirstBook key? No, because even a same publisher may publish se veral
editions, even of a same book (although not ne cessarily), hav ing same title and same first
book (in differe nt years) .
– ETitle  FirstBook  EYear key? No, because even a same publisher may publish se veral editions
(of different books ) hav ing same first book , in a same year .
– ETitle  EYear  Publisher key? No, because even a same publisher may publish se veral editions
(of different books, generally) in a same year , having same title .
Consequently, EDITIONS does not have any other (semantic) key.
VOLUMES
n = 3, as there are four not one -to-one mappings defined on it: VPrice , VTitle , VNo, and Edition ,
but, ob viously, VPrice is not prime: prices of volumes might never contribute to their
unique identification in this context. According to RV4, K = {ISBN }.

14
i = 2
– VTitle  VNo key? No, because even a same publisher may publish se veral editions including
volume s having same title and number.
– VTitle  Edition key? YES , because no edition may contain se veral vo lumes having same
title.
– VNo  Edition key? YES , because no edition may contain se veral vo lumes having same
number .
Consequently, K = {ISBN , VTitle  Edition , VNo  Edition }.

VOLUMES_CONTENTS
n = 3, as there are three not one -to-one mappings defined on it, all of them be ing prime: the two
canonical Cartesian projections Volume and Book , plus Book Pos. Obviously, K = {Volume
 Book }, as VOLUMES_CON TENTS is a well -defined binary association (see res triction
RVC3).
i = 2:
– Book  BookPos key? No, because same book may occupy a same po sition in different volumes
(of different editions) .
– Volume  BookPos key? YES , because in any position of any volume only one book may
appear .
Consequently, K = {Volume  Book , Volume  BookPos }.

BORROWS_LISTS
n = 4, as there are four not one -to-one mappings defined on it, all of them be ing prime: the two
canonical Cartesian projections Copy and Borrow, plus DueReturnDate and
ActualReturnDate . Obviously, K = {Copy  Borrow}, as BORROWS_LISTS is a well –
defined bi nary association (see restriction RBL4).
i = 2:
– Borrow  DueReturnDate key? No, because there may be for a same bor row several books that
need to be returned at a same date.
– Borrow  ActualReturnDate key? No, because there may be for a same bor row several books that
are returned at a same date.
– Copy  DueReturnDate key? No, because , for example, a same copy may be borrowed several
times withi n a same day for only some hours (i.e 0 days).1

1 Note that, obviously, if DueReturnDate is implemented as a time stamp (i.e. also stor ing the corresponding time, not
only the date), then this product is a key, but obviously, this should not happen as it does not make that much sense to
also ask for a time deadlin e in this context.

15
– Copy  ActualReturnDate key? No, because , for example, a same copy may be borrowed several
times within a same day for only some hours (i.e 0 days) and actually being returned the
same day at least twice.2
– DueReturnDate  ActualReturnDate key? No, because there may be , even for a same bor row,
several books that are both returned at a same date and due to be returned at a same date .
i = 3:
– Borrow  DueReturnDate  ActualReturnDate key? No, because ther e may be for a same bor row
several books that need to be returned at a same date and are returned at a same date .
– Copy  DueReturnDate  ActualReturnDate key? No, because, for exam ple, a same copy may be
borrowed several times within a same day for onl y some hours (i.e 0 days) and actually
being re turned the same day at least twice.
Consequently, BORROWS_LISTS does not have any other (semantic) key.
2.3 Third refinement algorithm: E -RD Cycles Analysis
By applying the initial step of the algorithm for E -RD cycles analysis, the fol lowing 6 cycles are
discovered in the structural E -RD shown in figure A.1.1 , out of which 3 are of the commutative
type and other 3 are of the ge neralized commutative type (see figures A.1.2 to A.1.7 below) :
2.3.1 First commutativ e-type cycle
FirstAuthor
PERSONS BOOKS
CO-AUTHORS
Figure A.1.2 First commutative -type cycle from A.1.1
Obviously, the corresponding commutativity question is: Coauthor ?= First Author  Book (should,
for any book, any coauthor be its firs t author?); trivially, the answer is no (as there may be several
coauthors for a book, but there is only one first coauthor for each book, and first authors should
appear only once in the coauthors lists, on the corresponding first po sition).
Dually, th e corresponding anti -commutativity question is: ( x  CO-AUTHORS ) (Coauthor (x) ?
First Author  Book (x)) (should, for any book, any coauthor be distinct than its first au thor?);
obviously, the answer is yes (as first authors should appear only once in the coauthors lists, on
the corresponding first po sition ). Consequently, the following con straint should be added to the
db scheme:

2 Note that, obviously, similar to DueReturnDate above, if Actual ReturnDate is im plemented as a time stamp (i.e. also
storing the corresponding time, not only the date, which would make sense), then this product is a key.

16
ACC1: (xCO-AUTHORS ) (Coauthor (x)  First Author  Book (x))
2.3.2 First generalized commutative -type cycle

PERSONS BOOKS
Subscriber CO -AUTHORS
BORROWS VOLUMES_CONTENTS

BORROWS_LISTS Volume VOLUMES

COPIES
Figure A .1.3 First generalized commutative -type cycle from A.1.1

This cycle has three sources ( CO-AUTHORS , VOLUMES_CON TENTS , and BORROWS_ LISTS )
and three destinations ( PERSONS , BOOKS , and VOLUMES ).
Let us consider any elements from the three source nodes: co from CO-AUTHORS , vc from VO-
LUMES_CON TENTS , and bl from BORROWS_ LISTS ; the corresponding three equality questions
(associated to the des tination nodes) are the following :
1. Author (co) ?= Subscriber (Borrow (bl)) (are there cases when co -authors of books should
also be borrowers?)
2. Book (co) ?= Book (vc)) (are there cases when co-authored books s hould also be published
in edition volumes ?)
3. Volume (vc) ?= Volume (Copy (bl)) (are there cases when published in edition volumes
should also have borrowed copies ?).
Corresponding answers are the following:
1. No: co -authors may be borrowers too, but this is nev er mandatory.
2. No: on one hand, co -authored (just like any other) books may not be published; on the
other, published volumes need not to be co -authored.
3. On one hand, no: published volumes need not even to be in the li brary’s possession; on the
other, YES: all borrowed copies have to have been previously published.
Consequently, at a first glance, the following constraint has to be added to the db too:
CBL8:(blBORROWS_LISTS )(vVOLUMES )(Volume (Copy (bl)) = v).
Thinking deeper, this constraint is implied by the following stronger one:

17
CC4: (cCOPIES )(vVOLUMES )(Volume (c) = v) (in order for a vo lume copy to exist, that
volume should have been published ),
which is obviously equivalent to the second half of RC2: Volume should be totally defined; as this
constraint is already present in the db scheme, no other implied constraint has to be added to it.
2.3.3 Second generalized commutative -type cycle
This cycle has two sources ( VOLUMES_CONTENTS and BORROWS_ LISTS ), as well as two
destinations ( PERSONS and VOLUMES ).
FirstAuthor
PERSONS BOOKS
Subscriber
BORROWS VOLUMES_CONTENTS

BORROWS_LISTS Volume VOLUMES

COPIES
Figure A .1.4 Second generalized commutative -type cycle from A.1.1
Let us consider any elements from the two source nodes: vc from VOLUMES CON TENTS and bl
from BORROWS_ LISTS ; the corresponding two e quality questions (associated to the des tination
nodes) are the fol lowing:
1. First Author (Book (vc)) ?= Subscriber (Borrow (bl)) (are there c ases when first authors of
books should also be borrowers?)
2. Volume (vc) ?= Volume (Copy (bl)) (are there cases when published in edition volumes
should also have borrowed copies?) .
Corresponding answers are the following:
1. No: first authors may be borrowers t oo, but this is never man datory.
2. On one hand, no: published volumes need not even to be in the li brary’s possession; on the
other, YES: all borrowed copies have to have been previously published.
Consequently, as per 2.3.2 above, no additional constr aint should be ad ded to the db scheme.

18
2.3.4 Second commutative -type cycle

BOOKS

VOLUMES_CONTENTS
FirstBook
VOLUMES
Edition
EDITIONS
Figure A .1.5 Second commutative -type cycle from A.1.1
Obviously, there is one source ( VOLUMES_CONTENTS ), one destination ( BOOKS ), and the
corresponding commutativity question is: Book ?= First Book  Edition  Volume (should, for any
volume, any included book be its first one?); trivially, the answer is no (a s there may be several
books for a volume , but there is only one such first book per volume ).
Dually, the corresponding anti -commutativity question is: (  x VOLUMES_CONTENTS )
(Book (x) ? First Book  Edition  Volume (x)) (should, for any volume , any included book be
distinct from its first one?); ob viously, the answer is no, as the first book is always equal to itself,
so no supplementary constraint should be added to the db scheme in connection with this cycle
(which is an uninteresting one).
2.3.5 Third ge neralized commutative -type cycle
This cycle has two sources ( CO-AUTHORS and BORROWS_ LISTS ), as well as two destinations
(PERSONS and BOOKS ).
Let us consider any elements from the two source nodes: ca from CO-AUTHORS and bl from
BORROWS_ LISTS ; the corresp onding two e quality questions (associated to the des tination nodes)
are the fol lowing:
1. Author (ca) ?= Subscriber (Borrow (bl)) (are there cases when co-authors of books should
also be borrowers?)
2. Book (ca) ?= FirstBook (Edition (Volume (Copy (bl))) (are there ca ses when co-authored
books should also have borrowed copies of volumes of their editions ?).
Corresponding answers are the following:
1. No: co-authors may be borrowers too, but this is never man datory (also see 2.3.2 above) .

19
2. On one hand, no: co-authored books need not even to be in the li brary’s possession; on the
other, no again : not all borrowed copies have to have been co-authored (some might only
have one author) .

PERSO NS BOOKS
Subscriber CO -AUTHORS
BORROWS
FirstBook
BORROWS_LISTS Volume VOLUMES
Edition
COPIES EDITIONS
Figure A .1.6 Third generalized commutative -type cycle from A.1.1
Consequently, no supplementary constraint should be added to the db scheme in connection with
this cycle (which is an uninteresting one).
2.3.6 Third commutative -type cycle
Obviously, there is one source ( BORROWS_LISTS ), one destination ( PERSONS ), and the
corresponding commutativity question is: Subscri ber  Borrow ?= First Auth or  First Book 
Edition  Volume  Copy (should subscribers borrowing copies be first authors of first books of
the edi tions to which that volume copies belongs ?); trivially, the answer is no (as any subscriber
may borrow books without being either th e first or any o ther co -other of the corresponding book ).
Dually, the corresponding anti -commutativity question is: ( x  BOR ROWS_LISTS ) (Subscri ber
 Borrow (x) ? First Author  First Book  Edition  Volume  Copy (x)) (should subscribers
borrowing copies n ever be first authors of first books of the edi tions to which that volume copies
belongs ?); ob viously, the answer is no (as any first author of a book may bor row that book too),
so no supplementary constraint should be added to the db scheme in connect ion with this cycle
(which is an uninteresting one too).

20
FirstAuthor
PERSONS BOOKS
Subscriber
BORROWS
FirstBook
BORROWS_LISTS Volume VOLUMES
Edition
COPIES EDITIONS
Figu re A.1.7 Third commutative -type cycle from A.1.1
2.4 Final Mathematical Scheme
By merging all above refinements with the initial scheme from section 2.0 above, the following
final mathematical scheme results:
PERSONS
x  NAT(6), total
FName  ASCII(1 28)
LName  ASCII(64), total
e-mail  ASCII(255)
CP5: e-mail  FName  LName key

BOOKS
x  NAT(6), total
BTitle  ASCII(255), total
BYear  [-2500, Year (SysDate ())]
FirstAuthor : BOOKS  PERSONS , total
CB4: FirstAuthor  BTitle  BYear key

CO-AUTHORS = (PERSONS, BOOKS )
x  NAT(7), total
PosInList  [2, 16] , total
CAK 2: Book  PosIn List key

PUBLISHERS
x  NAT(4), total
PubName  ASCII(128) , total

EDITIONS
x  NAT(6), total

21
ETitle  ASCII(255) , total
EYear  [-2500, Year (SysDate ())]
Publisher : EDITIO NS  PUBLISHERS , total
FirstBook : EDITIONS  BOOKS , total
CE4: Publisher  FirstBook  EYear key

VOLUMES
x  NAT(7), total
ISBN  ASCII(16)
VTitle  ASCII(255)
VNo  [1, 255] , total
VPrice  CURRENCY(8) , total
Edition : VOLUMES  EDITIONS , total , onto
VK1: VTitle  Edition key
VK2: VNo  Edition key

VOLUMES_CONTENTS = (BOOKS, VOLUMES )
x  NAT(7), total
BookPos  [1, 16] , total
VCK 2: Volume  BookPos key

COPIES
x  NAT(8), total
InvNo  ASCII(32), total
Volume : COPIES  VOLUMES , total

BORROWS
x  NAT(1 1), total
BorrowDate  [6/1/2011, SysDate ()], total
Subscriber : BORROWS  PERSONS , total
CBR3: BorrowDate  Subscriber key

BORROWS_LISTS = (BORROWS , COPIES )
x  NAT(12), total
DueReturnDate  [6/1/2011, SysDate () + 300] , total
ActualReturnDate  [6/1/201 1, SysDate ()]
CBL4: Borrow  Copy key

CP6: (bBORROWS )(FName (Subscriber (b))  NULLS 
e-mail(Subscriber (b))  NULLS)

CVC4: (eEDITIONS )(vcVOLUMES_CONTENTS )
(e = Edition (Volume (vc))  FirstBook (e) = Book (vc)  BookPos (vc) = 1)

22
CVC5: (eEDITIONS )(vc,vc’VOLUMES_CONTENTS )
(Edition (Volume (vc)) = Edition (Volume (vc’))  Book (vc)  Book (vc’))
CBL5: (blBORROWS_LISTS )
(0  DueReturnDate (bl) – BorrowDate (Borrow (bl))  300)
CBL6: (bl, bl’BORROWS_LISTS ) (Copy (bl) = Copy (bl’)) 
(Actual ReturnDate (bl’)  NULLS  BorrowDate (Borrow (bl)  Actual ReturnDate (bl’) 
Actual ReturnDate (bl)  NULLS  BorrowDate (Borrow (bl’)  Actual ReturnDate (bl))
CBL7: (blBORROWS_LISTS )
(0  Actual ReturnDate (bl) – BorrowDate (Borrow (bl))  36,500)
ACC1: (xCO-AUTHORS ) (Coauthor(x)  First Author  Book (x))
Note that, as compared to the initial one, 11 more constraints (out of which there are four keys and
seven non -relational ones) have been ad ded to this final one , namely: CAK 2; Edition : VOLUMES
 EDITIONS , onto; VK1; VK2; VCK 2; CVC4; CVC5; CBL5; CBL6; CBL7; and ACC1.
3. Relational Scheme and Associated Non-relational Constraints List
3.0 Relational db scheme
By applying the algorithm for translating mathematical schemes into re lational ones and non –
relational constraint lists , the following output is ob tained (with an example instance having at
least two tuples per table):
PERSONS (x, e-mail  FName  LName )
x FName LName e-mail
NAT(6) ASCII(128) ASCII(64) ASCII(255)
NOT NULL NOT NULL
1 Homer
2 William Shakespeare
3 Peter Buneman opb @ inf.ed.ac.uk

4 Serge Abiteboul
5 Dan Suciu suciu@cs.washington.edu

23
BOOKS (x, FirstAuthor  BTitle  BYear )
x FirstAuthor BTitle BYear
NAT(6) Im(PERSONS .x) ASCII(255) [-2500,
Year (SysDate ())]
NOT NULL NOT NULL NOT NULL
1 1 Odyssey -700
2 2 As You Like It 1600
3 4 Data on the Web: From Relations to
Semi structured Data and XML 1999

CO-AUTHORS (x, Person  Book , Book  PosInList )
x Book Person PosInList
NAT(7) Im(BOOKS .x) Im(PERSONS .x) [2, 16]
NOT NULL NOT NULL NOT NULL NOT NULL
1 3 3 2
2 3 5 3

PUBLISHERS (x, PubName )
x PubName
NAT(4) ASCII(128)
NOT NULL NOT NULL
1 Apple Academic Press
2 Springer Verlag
3 Morgan Kaufmann
4 Penguin Books

24
5 Washington Square Press

EDITIONS (x, Publisher  FirstBook  EYear )
x Publisher FirstBook ETitle EYear
NAT(6) Im(PUBLISHERS .x) Im(BOOKS .x) ASCII(255) [-2500,
Year (SysDate ())]
NOT NULL NOT NULL NOT NULL NOT NULL
1 4 1 The Odyssey
translated by
Robert Fagles 2012
2 5 2 As You L ike It 2011
3 3 3 Data on the
Web: From
Relations to
Semi structured
Data and XML 1999

VOLUMES (x, ISBN , Edition  VNo, Edition  VTitle )
x Edition VNo VTitle ISBN Price
NAT(7) Im(EDI-
TIONS .x) [1,
255] ASCII(255) ASCII(16) CUR RENCY(8)
NOT
NULL NOT
NULL NOT
NULL NOT NULL
1 1 1 0-670-82162 -4 $12.95
2 2 1 978-
1613821114 $9.99
3 3 1 978-
1558606227 $74.95

25
VOLUMES_CONTENTS (x, Volume  Book , Volume  BookPos )
x Volume Book BookPos
NAT(7) Im(VOLUMES .x) Im(BOOKS .x) [1, 255]
NOT NULL NOT NULL NOT N ULL NOT NULL
1 1 1 1
2 2 2 1
3 3 3 1

BORROWS (x, BorrowDate  Subscriber )
x Subscriber BorrowDate
NAT(11) Im(PERSONS .x) [6/1/2011, SysDate ()]
NOT NULL NOT NULL NOT NULL
1 5 10/29/2012

COPIES (x, InvNo )
x InvNo Volume
NAT(8) ASCII(32) Im(VOLUME S.x)
NOT NULL NOT NULL NOT NULL
1 H-O-1 1
2 H-O-2 1
3 S-AYLI -1 2
4 ABS -DW-1 3
5 ABS -DW-2 3

26
BORROWS_LISTS (x, Borrow  Copy )
x Borrow Copy DueRe turnDate ActualRe turnDate
NAT(12) Im(BORROWS .x) Im(COPIES .x) [6/1/2011,
SysDate () + 300] [6/1/2011,
SysDate ()]
NOT NULL NOT NULL NOT NULL NOT NULL
1 1 1 11/29/2012 11/23/2012
2 1 3 12/29/2012

3.1 Non-relational constraints list
The following 8 constraints are non -relational:
 Edition : VOLUMES  EDITIONS , onto;
 CP6:
(bBORROWS )(FName (Subscriber (b))  NULLS 
e-mail(Subscriber (b))  NULLS)
 CVC 4:
(eEDITIONS )(vcVOLUMES_CONTENTS )
(e = Edition (Volume (vc))  FirstBook (e) = Book (vc)  BookPos (vc) = 1)
 CVC 5:
(eEDITIONS )(vc,vc’VOLUMES_CONTENTS )
(Edition (Volume (vc)) = Edition (Volume (vc’))  Book (vc)  Book (vc’))
 CBL5:
(blBORROWS_LISTS )
(0  DueReturnDate (bl) – BorrowDate (Borrow (bl))  300)
 CBL6:
(bl, bl’BORROWS_LISTS ) (Copy (bl) = Copy (bl’)) 
(Actual ReturnDate (bl’)  NULLS  BorrowDate (Borrow (bl)  Actual ReturnDate (bl’) 
Actual ReturnDate (bl)  NULLS  BorrowDate (Borrow (bl’)  Actual ReturnDate (bl))
 CBL7:
(blBORROWS_LISTS )
(0  Actual ReturnDate (bl) – BorrowDate (Borrow (bl))  36,500)

27
 ACC 1:
(xCO-AUTHORS ) (Coauthor (x)  First Author  Book (x)).
4. Database Implementation
4.0 Technology choice
I have chosen MS Access because of the following reasons:
– being the only RDBMS installed on the faculty labs PCs, we did our DB labs in Access ;
– I have a copy of MS Office 2010 , including Access 2010 , installed on my PC;
– Access is a fine choice for small and me dium (up to 2 GB) dbs;
– any RDBMS is just a tool : you should exploit it at maximum, do not use its non -sense
facilities, and concentrate on the correctness and optimality of your solution.
I have chosen Oracle Database because of the following reasons:
– being the favorite RDBMS used in western universities DB labs, the DB Labs Notes by
Prof. Christian Mancas and Drd. Alina Di cu also include Oracle solutions, in parallel with
the Access ones;
– I have a freely downloadable copy of Oracle Xy installed on my PC;
– Oracle is a fine choice for medium and large dbs;
– any RDBMS is just a tool: you should exploit it at maximum, do not use its non -sense
facilities, and concentrate on the correctness and optimality of your solution.
4.1 Access db
By applying the algorithm for t ranslating rdb schemes into Access 2010 dbs, the following db was
obtained (note that a Boolean Author? column was added to PERSONS , for easing both non –
relational constraint en force ment and users interaction with the db):
4.1.0 DDL statements for creating and populating the db
CREATE TABLE PERSONS (x COUNTER PRIMARY KEY,
FName VARCHAR(128), LName VARCHAR(64) NOT NULL,
[e-mail] VARCHAR(255), [Author?] BIT,
CONSTRAINT PKey UNIQUE ([e -mail], LName, FName));

CREATE TABLE BOOKS (x COUNTER PRIMARY KEY,
FirstAut hor LONG NOT NULL , BTitle VARCHAR(255 ) NOT NULL,
BYear INT,
CONSTRAINT fkFA FOREIGN KEY (FirstAuthor) REFERENCES
PERSONS,
CONSTRAINT B Key UNIQUE ( BTitle, FirstAuthor , BYear));

CREATE TABLE [CO -AUTHORS] (x COUNTER PRIMARY KEY,
Person LONG NOT NULL , Book LONG NOT NULL,
PosInList BYTE NOT NULL ,

28
CONSTRAINT fkP FOREIGN KEY (Person) REFERENCES
PERSONS,
CONSTRAINT fkB FOREIGN KEY (Book) REFERENCES
BOOKS,
CONSTRAINT CAA Key UNIQUE ( Book, Person),
CONSTRAINT CAP Key UNIQUE ( Book, PosInList ));

CREATE TABLE P UBLISHERS (x COUNTER PRIMARY KEY,
PubName VARCHAR(128) NOT NULL UNIQUE);

CREATE TABLE EDITIONS (x COUNTER PRIMARY KEY,
Publisher LONG NOT NULL , FirstBook LONG NOT NULL,
ETitle VARCHAR(255 ) NOT NULL, EYear INT,
CONSTRAINT fkPub FOREIGN KEY (Publisher) REFERENCES
PUBLISHERS,
CONSTRAINT fkBk FOREIGN KEY (FirstBook) REFERENCES
BOOKS,
CONSTRAINT E Key UNIQUE ( FirstBook , Publisher, EYear ));

CREATE TABLE VOLUMES (x COUNTER PRIMARY KEY,
Edition LONG NOT NULL , VNo BYTE NOT NULL,
VTitle VARCHAR(255 ), ISBN VARCHAR(16 ) UNIQUE,
Price CURRENCY NOT NULL,
CONSTRAINT fk E FOREIGN KEY ( Edition) REFERENCES
EDITIONS ,
CONSTRAINT VNKey UNIQUE ( Edition, VNo),
CONSTRAINT VTKey UNIQUE ( Edition, VTitle));

CREATE TABLE VOLUMES_CONTENTS (x COUNTER PRIMARY KEY,
Volume LONG NOT NULL , Book LONG NOT NULL,
BookPos BYTE NOT NULL ,
CONSTRAINT fk V FOREIGN KEY ( Volume) REFERENCES
VOLUMES,
CONSTRAINT fk VB FOREIGN KEY (Book) REFERENCES
BOOKS,
CONSTRAINT VCBKey UNIQUE ( Volume, Book),
CONSTRAINT VCPKey UNIQUE ( Volume, BookPos));

CREATE TABLE BORROWS (x COUNTER PRIMARY KEY,
Subscriber LONG NOT NULL , BorrowDate DATE NOT NULL,
CONSTRAINT fk S FOREIGN KEY ( Subscriber ) REFERENCES
PERSONS,
CONSTRAINT B RKey UNIQUE ( BorrowDate , Subscriber ));

CREATE TABLE COPIES (x COUNTER PRIMARY KEY,
Volume LONG NOT NULL , InvNo VARCHAR(32) NOT NULL UNIQUE,
CONSTRAINT fkS FOREIGN KEY ( Volume) REFERENCES VOLUMES);

CREATE TABLE BORROWS_LISTS (x COUNTER PRIMARY KEY,
Borrow LONG NOT NULL , Copy LONG NOT NULL,
DueReturnDate DATE NOT NULL , ActualReturnDate DATE,
CONSTRAINT fk Bw FOREIGN KEY ( Borrow) REFERENCES
BORROWS,
CONSTRAINT fk Cy FOREIGN KEY ( Copy) REFERENCES
COPIES,
CONSTRAINT BLKey UNIQUE ( Copy, Borrow));

29
4.1.1 Tables Relationships

Figure A.1.8 Relationships between LibraryDB.accdb tables
4.1.2 Table Lookups SQL statements
4.1.2.1 PERSONS
Foreign keys BOOKS .FirstAuthor and CO_AUTHORS .Co-Author are using the following quer y:
SELECT x, IIf(IsNull([FName]),"",[FName] & " ") & [LName] &
IIf(IsNull([ e-mail]),""," " & [e-mail])
FROM PERSONS
WHERE [Author?]
ORDER BY IIf(IsNull([FName]),"",[FName] & " ") & [LName] &
IIf(IsNull([ e-mail]),""," " & [e-mail]);

Foreign key BOR ROWS .Subscriber is using the following query:
SELECT x, [e-mail] & " " & [FName] & " " & [LName]
AS [e-mail First and Last Names]
FROM PERSONS
ORDER BY [e -mail] & " " & [FName] & " " & [LName];
4.1.2.2 BOOKS
Foreign keys CO_AUTHORS .Book , EDITIONS .First Book , and VOLUMES _CON TENT S.Book are
using the following query:
SELECT BOOKS.x, IIf(IsNull([FName]),"",[FName] & " ") &
[LName] & ", " & [BTitle] & ", " & [BYear]
AS [FirstAuthor, Book Title, Year]
FROM PERSONS INNER JOIN BOOKS
ON PERSONS.x = BOOKS.FirstAuthor
ORDER BY IIf(IsNull([FName]),"",[FName] & " ") & [LName] &
", " & [BTitle] & ", " & [BYear];

30
4.1.2.3 PUBLISHERS
Foreign key EDITIONS .Publisher is using the following query:
SELECT x, PubName FROM PUBLISHERS ORDER BY PubName;
4.1.2.4 EDITIONS
Foreign key VOLUMES .Edition is using the following query:
SELECT EDITIONS.x, IIf(IsNull([FName]),"",[FName] & " ") &
[LName] & ", " & [E Title] & IIf(IsNull([eyear]),"",", " &
[EYear]) & ", " & IIf(IsNull([Publisher]),"",[PubName]) &
", " & [BTitle]
AS [First Author, Title, Year, Publisher, First Book] FROM PUBLISHERS RIGHT
JOIN (PERSONS INNER JOIN (BOOKS
INNER JOIN EDITIONS ON BOOKS.x = EDITIONS.FirstBook)
ON PERSONS.x = BOOKS.FirstAuthor)
ON PUBLISHERS.x = EDITIONS.Publisher
ORDER BY IIf(IsNull([FName]),"",[FName] & " ") & [LName] &
", " & [ETitle] & IIf(IsNull([eyear]),"",", " & [EYear])
& ", " & IIf(IsNull([Publi sher]),"",[PubName]) & ", " &
[BTitle];

4.1.2.5 VOLUMES
Foreign key COPIES .Volume is using the following query:
SELECT VOLUMES.x, IIf(IsNull([FName]),"",[FName] & " ") &
[LName] & ", " & [etitle] & ", " &
IIf(IsNull([eyear]),"",[eyear]) & ", " & [PubName] &
", v." & [VNo]
AS [First Author, EdTitle, EdYear, Publisher, VolNo],
VOLUMES.ISBN
FROM PUBLISHERS RIGHT JOIN (PERSONS RIGHT JOIN ((BOOKS
RIGHT JOIN EDITIONS ON BOOKS.x = EDITIONS.FirstBook)
RIGHT JOIN VOLUMES ON EDITIONS.x = VO LUMES.Edition)
ON PERSONS.x = BOOKS.FirstAuthor)
ON PUBLISHERS.x = EDITIONS.Publisher
ORDER BY IIf(IsNull([FName]),"",[FName] & " ") & [LName] &
", " & [etitle] & ", " & IIf(IsNull([eyear]),"",[eyear])
& ", " & [PubName] & ", v." & [VNo];
4.1.2.6 COPIES
Foreign key BORROWS_LISTS .Copy is using the following query:
SELECT COPIES.x, "Inv.No. " & [InvNo] & ", " &
IIf(IsNull([FName]),"",[FName] & " ") & [LName]
& ", " & [etitle] & ", " &
IIf(IsNull([eyear]),"",[eyear]) & ", " &
[PubName ] & ", v." & [VNo]
AS [InvNo, First Author, EdTitle, EdYear,
Publisher, VolNo]
FROM (PUBLISHERS RIGHT JOIN (PERSONS INNER JOIN
((BOOKS INNER JOIN EDITIONS

31
ON BOOKS.x = EDITIONS.FirstBook) INNER JOIN
VOLUMES ON EDITIONS.x = VOLUMES.Edition)
ON PERSONS.x = BOOKS.FirstAuthor)
ON PUBLISHERS.x = EDITIONS.Publisher)
INNER JOIN COPIES ON VOLUMES.x = COPIES.Volume ORDER BY
"Inv.No. " & [InvNo] & ", " &
IIf(IsNull([FName]),"",[FName] & " ") & [LName]
& ", " & [etitle] & ", " &
IIf(IsNull([eyear]),"",[eyear]) & ", " &
[PubName] & ", v." & [VNo];
4.1.2.7 BORROWS
Foreign key BORROWS_LISTS .Borrow is using the following query:
SELECT BORROWS.x, [FName] & " " & [LName] & ", "
& [e-mail] & ", " & [BorrowDate]
AS [Subscriber, e -mail, Borrow Date]
FROM PERSONS INNER JOIN BORROWS
ON PERSONS.x = BORROWS.Subscriber
ORDER BY [FName] & " " & [LName] & ", " &
[e-mail] & ", " & [BorrowDate];
4.1.3 Validation rules, Comments, and Default values
4.1.3.1 Enforcing (co -)domain (range) and tuple (check) constraints
Unfortunately, Access ’ SQL does not provide means for enforcing either (co -)domain (range) or
tuple (check) constraints. Fortunately, both of them can be enforced through its GUI3 by using
validation rules (and as sociated validation texts ).
Figure A.1.9 shows the result of enforcing restriction RB2, the co -domain of BYear : BOOKS 
[-2500, current year ].
Figure A.1.10 shows the result of enforcing restriction RBR1 , the co -domain of BorrowDate :
BORROWS  [6/1/2011 , SysDate ()].
Figure A.1.11 shows the result of enforcing restriction RB L1, the co -domain of DueReturn Date :
BORROWS_LISTS  [6/1/2011, SysDate () + 300 ].
Figure A.1.12 shows the result of enforcing restriction RBL2, the co -domain of ActualReturnDate
: BORROWS_LISTS  [6/1/2011, SysDate () + 900 ].
Figure A.1.13 shows the result of enforcing restriction RCA 1, the co-domain of PosInList : CO-
AUTHOR S  [1, 16].

3 They can also be manipulated programmatically, through VBA, DAO , and ADO .

32

Figure A.1.9 Enforcing restriction RB2

Figure A.1.10 Enforcing restriction RBR1 and adding default value to BorrowDate

33

Figure A.1.11 Enforcing restriction RBL1

Figure A.1.12 Enforcing restriction RBL2

34

Figure A.1.1 3 Enforcing restriction RCA1 and adding default value to PosInList
Figure A.1.14 shows the result of enforcing restriction R E2, the co -domain of EYear : EDITIONS
 [-2500, current year] .

Figure A.1.14 Enforcing restriction R E2 and adding EYear ’s default value
Figure A.1.15 shows the result of enforcing restriction RV4, the co -domain of VNo : VOLUMES
 [1, 255].

35

Figure A.1.1 5 Enforcing restriction RV3 and adding VNo’s default value
Figure A.1.16 shows the result of enforcing restriction R VC1 , the co -domain of BookPos :
VOLUMES _CONTENTS  [1, 16].

Figure A.1.16 Enforcing restriction RVC1 and adding BookPos ’ default value
There are no tuple (check) cons traints in this db; figure A.1.17 shows the pro perties of a table,
where, through its Validation rule (and associated text) such constraints may be enforced.

36

Figure A.1.17 Enforcing tuple (check) constraints
1.1.3.2 Adding comments
Comments describing sets (tab les) semantics are stored in the tables’ Description property (see
figure A.1.17). Those on functions (columns), in the homonym one of columns (fields, see figures
A.1.9 to A.1.16).
4.1.3.2 Adding default value s
Access too provides a Default Value property for functions (columns, fields), which should always
be used when appropriate to spare users re peatedely entering same most frequent values.
Figures A.1.10 and A.1.13 to A.1.16 show the default values added to this db scheme. No other
default values are appropriate.
4.2 Oracle db
5. Non-relational constraints enforcement
1.1 Access solutions
In order to enforce non -relational constraints in Access , forms have to be cre ated for all involved
tables, in order to associate to them VBA classes that host corresponding trigger -type event -driven
methods. These forms will then have to make part of a db application that should hide cor respond –
ing db tables and only allow data manipulation through forms.
Obviously, for this db, f orms are needed to this end for tables: EDITIONS , BORROWS ,
VOLUMES_CONTENTS , BORROWS_LISTS , and CO-AUTHORS .
5.1.1 Edition : VOLUMES  EDITIONS , onto
(y  EDITIONS )( x  VOLUMES )(Edition (x) = y) (Any edition should have at least one
volume .)

37
Ontoness is very easy to enforce: in this case, for example, each time a new edition is inserted, a
corresponding (first) volume should auto matically be inserted too and, dually, each time deletion
of the last remaining vo lume of an edition is successfully com mitted, the corresponding edition
should be automatically deleted too.
Consequently, this constraint has to be enforced both in the Form_EDI TIONS class, by using the
Form_AfterUpdate event (so that the new edi tion be already saved in the corresponding ta ble) and
in the Form_VO LUMES class, by using the Form_AfterDelConfirm event (so that the last vo lume
be actually deleted from the corresponding table).
Note that automatically adding a first volume for each new edition, as well as of the first book of
this first volume in its content (see constraint CVC4 below) is also an ergonomic feature of the
application.
Also note that volumes cannot be deleted unless their content is pre vious ly deleted too.4 Not only
as the the first book of the first volume of an y edi tion is automatically added, but especially for
ergonomical reasons, the ap plication is also automatically deleting volume contents of any de leta-
ble volume, if users agree with it.
Note too that for deleting an edition left without any volume the re are two pos sible solutions:
 a “tougher” one, consisting in deletion of any edition having no vo lumes (for which no
edition key is necessary)
 a “softer” one, consisting in deleting only the edition just left with out any volume (for
which its id should be previously stored in a variable, as, after successful deletion of its last
volume, it can not be anymore retrieved from the db)
Although it is a little bit more complicated, we chose the second approach mainly because,
anyhow, in order to establish whe ther or not a volume is de letable and then to also automatically
delete its content, programming of the Form_Delete method is required.
Finally, note that, for ergonomical reasons, after successful automatical deletion s and insertions of
both volumes and editions, all involved forms and combo -boxes are requeried, in order for users
not having to close and re -open them for refreshing their data sources.
Here is , first, the code of class Form_VOLUMES :
'Form_VOLUMES class

Option Compare Database
Option Expl icit

Private currEdition As Long

4 Obviously, volume cannot be deleted either if there are copies of them.

38

'*****************************************
Private Sub Form_Delete(Cancel As Integer)
'*****************************************
'Initializes global variable currEdition
'that is then used by Form_AfterDelConfirm.
'Moreover, prevents deletion when there are copies of the
'volume and deletes corresponding volume content if there
'are no copies of the volume and the user agrees with it, 'in order for
deletion to succeed.
On Error GoTo err_point
If vbCancel = MsgBox("Are y ou sure you want to delete " _
& "the current volume?", _
vbQuestion + vbOKCancel + vbDefaultButton2, _
"Please confirm or cancel your request…") Then
Cancel = True
Else
If Not IsNull(DLookup("x", "COPIES", "Volume=" & Me!x)) Then
Cancel = True
MsgBox "Request denied!", vbCritical, _
"There are copies of this volume…"
Else
If vbCancel = MsgBox("Are you sure you want to also " _
& "delete the content of this volume?", _
vbQuestion + vbOKCancel + vbDefa ultButton2, _
"Please confirm or cancel your request…") Then
Cancel = True
MsgBox "Request denied!", vbCritical, _
"To delete volumes, their content must be " _
& "deleted too…"
Else
currEdition = Me!Editi on
DoCmd.RunSQL "DELETE FROM VOLUMES_CONTENTS WHERE " _
& "Volume=" & Me!x
End If
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, _
"Error in method Form_VOLUMES.Form_Delete…"
Cancel = True
End Sub

'**************************************** ******************
Private Sub Form_BeforeDelConfirm(Cancel As Integer,
Response As Integer)
'**************************************** ******************
'prevents Access from displaying its standard deletion 'confirmation message
Response = acDataErrContinue
End Sub

'**************************************************
Private Sub Form_AfterDelConfirm(Status As Integer)
'**************************************************

39
'enforces ontoness o f Edition : VOLUMES -> EDITIONS
'(immediately after deletion of a last volume of an
'edition, also deletes corresponding edition)
On Error GoTo err_point
If Status = acDeleteOK Then
If IsNull(DLookup("x", "VOLUMES", "Edition=" _
& currEdition)) Then
DoCmd.RunSQL "DELETE FROM EDITIONS WHERE x=" _
& currEdition
MsgBox "Successfully deleted corresponding edition " _
& "too!", vbInformation, _
"Deleted volume was the only one of its edition…"
On Error Resume Next
Forms!EDITIONS .Requery
Forms!VOLUMES_CONTENTS.Requery
Forms!VOLUMES_CONTENTS!Volume.Requery
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_VOLUMES. " _
& "Form_AfterDelConfirm…"
End Sub
Secondly, here is the code of class Form_ EDITION S (obviously, not all of it enforces this
constraint, but, as during its enforcement the values of the variables v, b, and p are needed to,
corresponding code could not be un derstood without their defin itions and initializations ):
'Form_EDITIONS class

Option Compare Database
Option Explicit

Dim v, b, p As Long
Dim switchPos As Boolean

'*************************
Private Sub Form_Current()
'*************************
On Error GoTo err_point
switchPos = False
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_EDITIONS.Form_Current…"
End Sub

'****************************************************
Private Sub FirstBook_BeforeUpdate(Cancel As Integer)
'****************************************************
'enforces constraints CVC4 (for any edition, its first book should be
'the first one published in its first volume) and CVC5
'(no edition should include a book more than once)
Dim w, x As Variant

40
On Error GoTo err_point
If Me!FirstBook <> Me!FirstBook.OldValue Then
'CVC4: first book of any edition should be the first one
' of the edition first volume
If IsNull(Me!FirstBook) Then
Cancel = True
MsgBox "Please choose a not null value for Firs t " _
& "Book!", vbCritical, _
"First book of any edition is compulsory…"
Me!FirstBook.Undo
Else
v = DLookup("VNo", "VOLUMES", "Edition = " & Me!x & _
" AND VNo=1")
If IsNull(v) Then
insertFirstVolume
Else
'CVC5: n o edition shoul d include a book more than once
w = DLookup("x", "VOLUMES_CONTENTS", "Book=" & _
Me!FirstBook & " AND Volume IN (SELECT x " _
& "FROM VOLUMES WHERE Edition=" & Me!x & ")")
If IsNull(w) Then
DoCmd.RunSQL "UPDATE VOLUM ES_CONTENTS SET Book=" _
& Me!FirstBook & " WHERE Volume=" & v _
& " AND BookPos=1"
Else
x = DLookup("VNo", "VOLUMES", "x=" & _
DLookup("Volume", "VOLUMES_CONTENTS", _
"x=" & w))
If vbOK = MsgBox("Would you like to switch " & _
"positions between the previous and the " & _
"current books for this edition?", _
vbQuestion + vbOKCancel + vbDefaultButton2, _
"This book already exits in volume " & x & _
" of this edition…") Then
switchPos = True
p = DLookup("BookPos", "VOLUMES_CONTENTS", "x=" _
& w)
b = Me!FirstBook.OldValue
DoCmd.RunSQL "DELETE FROM VOLUMES_CONTENTS " &_
"WHERE x=" & w
DoCmd.RunSQL "UPDATE VOLUMES_CONTENTS SET Book" _
& "=" & Me!FirstBook & " WHERE BookPos = 1 " _
& "AND Volume=" & v
Else
Cancel = True
MsgBox "Please cho ose another value for " & _
"FirstBook!", vbCritical, "This book is " & _
"already known as belonging to volume number " _
& x & " of this edition…"
Me!FirstBook.Undo
End If
End If
End If
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _

41
vbCritical, "Error in method Form_EDITIONS. " & _
"FirstBook_BeforeUpdate…"
Cancel = True
End Sub

'*****************************
Private Sub Form_AfterUpdate()
'*****************************
'enforces ontoness of Edition : VOLUMES -> EDITIONS
'(immediately after insertion of a new edition,
'inserts a corresponding first volume of i t).
'Moreover, it also enforces constraint CVC4:
'for any edition, its first book should be
'the first one published in its first volume.
Dim w As Variant

On Error GoTo err_point
If switchPos Then
'enforces constraint CVC4
switchPos = False
DoCmd.Ru nSQL "INSERT INTO VOLUMES_CONTENTS (Volume, " _
& "Book, BookPos) VALUES (" & v & ", " & b & ", " _
& p & ")"
Else
'enforces ontoness of Edition : VOLUMES -> EDITIONS
w = DLookup("x", "VOLUMES", "Edition=" & Me!x)
If IsNull(w) Then
insertFirstVol ume
End If
End If
On Error Resume Next
Forms!VOLUMES.Requery
Forms!VOLUMES!Edition.Requery
Forms!VOLUMES_CONTENTS.Requery
Forms!VOLUMES_CONTENTS!Volume.Requery
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in met hod Form_EDITIONS. " _
& "Form_AfterUpdate…"
End Sub

'******************************
Private Sub insertFirstVolume()
'******************************
'called by Form_AfterUpdate
Dim v As Long

On Error GoTo err_point
DoCmd.RunSQL "INSERT INTO VOLUMES (Ed ition) VALUES (" _
& Me!x & ")"
v = DLookup("x", "VOLUMES", "Edition=" & Me!x & _
" AND VNo=1")
DoCmd.RunSQL "INSERT INTO VOLUMES_CONTENTS (Volume, Book" _
& ", BookPos) VALUES (" & v & ", " & Me!FirstBook & ", 1)"
Exit Sub

42
err_point: MsgBox Err. Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_EDITIONS. " _
& "insertFirstVolume…"
End Sub

5.1.2 CP6
(bBORROWS )(FName (Subscriber (b))  NULLS 
e-mail(Subscriber (b))  NULLS) (FName and e-mail should be com pulsory for subscr ibers.)
Obviously, enforcing this constraint needs not VBA code, as it can be sim ply done by adding a
corresponding filter to the SELECT statement of the Subscriber combo -box from BORROWS (also
see 4.1.2.1 above ):
SELECT x, [e-mail] & " " & [FName] & " " & [LName]
AS [e-mail First and Last Names]
FROM PERSONS
WHERE [e -mail] Is Not Null AND FName Is Not Null
ORDER BY [e -mail] & " " & [FName] & " " & [LName];
5.1.3 CVC4
(eEDITIONS )(vcVOLUMES_CONTENTS )
(e = Edition (Volume (vc))  FirstBook (e) = Book (vc)  BookPos (vc) = 1) (For any edition, its
first book should be the first one published in its first volume. )
As we’ve already seen, this constraint is enforced by methods FirstBook_ BeforeUpdate and
Form_Af terUpdate of class Form_EDITIONS (see 5.1.1 above).
5.1.4 CVC5
(eEDITIONS )(vc,vc’VOLUMES_CONTENTS )
(Edition (Volume (vc)) = Edition (Volume (vc’))  Book (vc)  Book (vc’))
(No edition may contain same book more than once. )
As we’ve already seen, this c onstraint is enforced by method FirstBook_ BeforeUpdate of class
Form_EDITIONS (see 5.1.1 above).
5.1.5 CBL5
(blBORROWS_LISTS )
(0  DueReturnDate (bl) – BorrowDate (Borrow (bl))  300) (No copy may be borrowed less
than 0 days or more than 300 days. )
Obviously, this constraint is best enforceable by the DueReturnDate_Be foreUpdate method of
class Form_ BORROWS_LISTS (Note that the cor responding f orm should never be used alone, but
only as a sub -form of BOR ROWS : otherwise, on blank lines Me!Borrow could not be always de –
fined !):
'********************* ***********************************
Private Sub DueReturnDate_BeforeUpdate(Cancel As Integer)
'********************************************************
'enforces constraint CBL5: no copy may be borrowed less

43
'than 0 days or more than maxBorrowDays (300) days.

Dim bDate As Date
Dim days As Long

On Error GoTo err_point
If Me!DueReturnDate <> Me!DueReturnDate.OldValue Then
If Not IsNull(Me!DueReturnDate) Then
bDate = DLookup("BorrowDate", "BORROWS", "x = " & _
Me!Borrow)
days = DateDiff("y", bDat e, Me!DueReturnDate)
If days < 0 Then
Cancel = True
MsgBox "Please specify a due return date greater " _
& "or equal to " & bDate & "!", vbCritical, _
"DueReturnDate value less than " _
& "the corresponding BorrowDate one.. ."
Me!DueReturnDate.Undo
ElseIf days > maxBorrowDays Then
Cancel = True
MsgBox "Please specify a due return date less or " _
& "equal to " & bDate + maxBorrowDays & "!", _
vbCritical, "DueReturnDate " & "value greater " _
& "than the corresponding BorrowDate one + " _
& maxBorrowDays & " days…"
Me!DueReturnDate.Undo
End If
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_BORROWS_LISTS." _
& "DueReturnDate_BeforeUpdate…"
Cancel = True
End Sub
5.1.6 CBL6
(bl, bl’ BORROWS_LISTS ) (Copy (bl) = Copy (bl’)) 
(Actual ReturnDate (bl’)  NULLS  BorrowDate (Borrow (bl)  Actual ReturnDate (bl’) 
Actual ReturnDate (bl)  NULLS 
BorrowDate (Borrow(bl’)  Actual ReturnDate (bl)) (No copy may be simultaneously borrowed
to more than one sub scriber.)
Obviously, this is a fine example of non -relational constraint whose logic for mula is much more
complicated than the corresponding enforcement code: in fact, each time that a copy is selected
for lending, all we have to do is to check whether or not there is another row in BORROWS_LISTS
for the same copy and having ActualReturnDate null (meaning that it has not been yet returned).
Trivially, the best e vent for enforcing this constraint is the Copy_Be foreUpdate one of class
Form_ BORROWS_LISTS ; unfortunately, although this solution works fine, Access is displaying
the following unexpected and unpleasant (both for users and programmers) error message:

44

Figure A.1.18 Unexpected unpleasant Access error message
The workaround for it is to postpone enforcement up to the corresponding Form_BeforeUpdate
event:
'***********************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
'***********************************************
'enforces constraint CBL6: No copy may be simultaneously
'borrowed to more than one subscriber.
Dim v As Variant

On Error GoTo err_point
If Me.NewRecord Or Me!Copy <> Me!Copy.OldValue Then
v = DLookup("DueR eturnDate", "BORROWS_LISTS", "Copy=" & _
Me!Copy & " AND ActualReturnDate Is Null AND x <>" _
& Me!x)
If Not IsNull(v) Then
Cancel = True
MsgBox "… so it cannot be lend: it is due to be " & _
"returned on " & v, vbCritical, _
"This copy is not yet returned…"
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_BORROWS_LISTS." _
& "Form_BeforeUpdate…"
Cancel = True
End Sub
5.1.7 CBL7
(blBORROWS_LISTS )
(0  ActualRet urnDate (bl) – BorrowDate (Borrow (bl))  36,500)
(No copy may be returned before it was borrowed and after 100 years since cor respond ing borrow
date.)
Obviously, this constraint is best enforceable by the Actual ReturnDate_ BeforeUpdate method of
class Form_ BORROWS_LISTS :
'*************************** *******************************
Private Sub ActualReturnDate_BeforeUpdate(Cancel As
Integer)
'*************************** *******************************
'enforces constraint CBL7: no copy may be retu rned before

45
'it was borrowed or after 100 years since corresponding
'borrow date.
Dim bDate As Date
Dim days As Long

On Error GoTo err_point
If Me!ActualReturnDate <> Me!ActualReturnDate.OldValue Then
If Not IsNull(Me!ActualReturnDate) Then
bDate = DLookup("BorrowDate", "BORROWS", "x = " & _
Me!Borrow)
days = DateDiff("y", bDate, Me!ActualReturnDate)
If days < 0 Then
Cancel = True
MsgBox "Please speci fy a due return date greater " _
& "or equal to " & bDate & "!", vbC ritical, _
"ActualReturnDate value less " _
& "than the corresponding BorrowDate one…"
Me!ActualReturnDate.Undo
ElseIf days > maxReturnDays Then
Cancel = True
MsgBox "Please specify a due return date less or " _
& "equal to " & bDate + maxReturnDays & "!", _
vbCritical, "ActualReturnDate value greater " _
& "than the corresponding " _
& "BorrowDate one + " & maxReturnDays & " days…"
Me!ActualReturnDate.Undo
End If
End If
End If
Exit Sub
err_poin t: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Error in method Form_BORROWS_LISTS." _
& "ActualReturnDate_BeforeUpdate…"
Cancel = True
End Sub
5.1.8 ACC1
(xCO-AUTHORS ) (Coauthor(x)  First Author  Book (x)) (First au thors should appear only
once in the coauthors lists, on the corresponding first po sition .)
Obviously, this constraint is best enforceable by the Co_author _BeforeUpdate method of class
Form_ CO_AUTHORS :
'*************************************************** *
Private Sub Co_author_BeforeUpdate(Cancel As Integer)
'****************************************************
'enforces constraint ACC1: First authors should appear only
'once in the co -authors lists, on the corresponding first 'position.
Dim v As Variant

On Error GoTo err_point
If Me![Co -author] <> Me![Co -author].OldValue Then
If IsNull(Me![Co -author]) Then
Cancel = True
MsgBox "Choose a non -null value for CoAuthor! ", _

46
vbCritical, "For each book CoAuthors are " _
& "compulsory…"
Me![Co-author].Undo
Else
v = DLookup("x", "Books", "x=" & Me!Book & _
" AND FirstAuthor=" & Me![Co -author])
If Not IsNull(v) Then
Cancel = True
MsgBox "This CoAuthor is already stored in BOOKS " _
& "as the " & "FirstAuth or!", vbCritical, _
"For any book each CoAuthor should appear " _
& " only once in the CoAuthors list…"
Me![Co-author].Undo
End If
End If
End If
Exit Sub
err_point: MsgBox Err.Source & " -> " & Err.Description, _
vbCritical, "Er ror in method Form_CO_AUTHORS. " _
& "Co_author_BeforeUpdate…"
Cancel = True
End Sub
5.2 Oracle solutions
6. Database Usage
6.1 Access Queries and Reports
6.1.1 Queries
6.1.1.1 Overdue borrows
Compute the set of all copies that should have been returned at least k (natural) days ago, in the
descending order of the overdue period and then as cending on borrowers e -mail addresses, last,
and first names , and, finally, copies inventory number s.
Solution :
Obviously, data on overdue copies is stored in table BOR ROWS_LISTS : any copy having a null in
the ActualReturnDate is not yet returned and by sub tracting from the current date the
DueReturnDate one and comparing the result to k the requested set is easily computable.
Of course that from the Copy foreign key th e corresponding InvNo may be obtained through a join
with table COPIES and that from the Borrow foreign key the corresponding e-mail, FName , and
LName may be ob tained through a join with tables BORROWS and then with table PERSONS (on
foreign key Subscri ber).
Consequently, the needed query is the following:

47
SELECT DateDiff("d",[DueReturnDate], Date()) AS OverdueDays,
[e-mail], FName, LName, InvNo
FROM PERSONS INNER JOIN (COPIES INNER JOIN (BORROWS
INNER JOIN BORROWS_LISTS
ON BORROWS.x = BORROWS_LISTS.Bo rrow)
ON COPIES.x = BORROWS_LISTS.Copy)
ON PERSONS.x = BORROWS.Subscriber
WHERE DateDiff("d",[DueReturnDate],Date())>=[k]
AND ActualReturnDate Is Null
ORDER BY DateDiff("d",[DueReturnDate],Date()) DESC,
[e-mail], LName, FName , InvNo;
Figure A.1.19 shows the result of running this query for k = 0:

Figure A.1.19 Result of running query OverdueDays for k = 0
6.1.1.2 Worse borrowers
Compute the set of borrowers that were late with at least k (natural) days in returning, at least for
n (natural) borrows, and at least m copies per bor row, in descending order of the sum of number
of late re turning copies per borrow, then of the number of borrows, and then ascending on e -mail
addresses, last, and first names.
Solution :
Obviously, all copies that are not yet returned and are overdue already with at least k days (that is
exactly the set computed by the above 6.1.1.1 pro blem) are satisfying the conditions of this
problem. Consequently, this set may be computed by the following query:
SELECT DateDiff("d",[DueRe turnDate], Date()) AS OverdueDays,
Borrow, Copy
FROM BORROWS_LISTS
WHERE DateDiff("d",[DueReturnDate],Date())>=[k]
AND ActualReturnDate Is Null;
Moreover, copies that were returned (that is their ActualReturnDate is not null) may have also
been returned with at least k days later than due; this subset is computable by the following query:

48
SELECT DateDiff("d",[DueReturnDate], Date()) AS OverdueDays,
Borrow, Copy
FROM BORROWS_LISTS
WHERE ActualReturnDate Is Not Null AND
ActualReturnDate >= DueReturnDate A ND
DateDiff("d", DueReturnDate , ActualReturnDate)>=[k];
The union of these two subsets (save it as OverdueCopies ) is the base set for computing the final
result:
SELECT DateDiff("d",[DueReturnDate], Date()) AS OverdueDays,
Borrow, Copy
FROM BORROWS_LISTS
WHERE DateDiff("d",[DueReturnDate],Date())>=[k]
AND ActualReturnDate Is Null
UNION
SELECT DateDiff("d",[DueReturnDate], Date()) AS OverdueDays,
Borrow, Copy
FROM BORROWS_LISTS
WHERE ActualReturnDate Is Not Null AND
ActualReturnDate >= DueReturnDate AND
DateDiff("d", DueReturnDate , ActualReturnDate)>=[k];
Next step is computable directly from the above computed set, by grouping on borrows (save it as
OverdueBorrowsAndCopyNo ): borrows containing at least m overdue copies; the corresponding
query is the fol lowing (where OverdueCopies is the name of the above query ):
OverdueBorrowsAndCopyNo :
SELECT Borrow, Count( Copy) AS OverdueCopiesNo
FROM OverdueCopies
GROUP BY Borrow
HAVING Count(Copy)>=[m];
For computing the number of borrows per subscriber and selecting only those subscribers that
have had at least n borrows, the needed query is:
SELECT Subscriber, Count(x) AS BorrowsNo
FROM BORROWS
GROUP BY Subscriber
HAVING Count( x)>=[n];
For further selecting from this set only those borrows that are computed by
Over dueBorrowsAndCopyNo , a join with it is necessary:
SELECT Subscriber, Count(x) AS BorrowsNo,
Sum(OverdueCopiesNo) AS SumOfOverdueCopiesNo
FROM BORROWS INNER JOIN OverdueBorrowsAndCopyNo
ON BORROWS.x = OverdueBorrowsAndCopyNo.Borrow
GROUP BY Subscriber
HAVING Count(x)>=[n];

49
The final result is obtainable from this one (save it as WorseBorrowers0 ) by replacing subscriber
with corresponding e -mail address, first, and last name (through a join with PERSONS ) and by
ordering it in the requested order:
WorseBorro wers:
SELECT SumOfOverdueCopiesNo, BorrowsNo, [e -mail],
FName, LName
FROM WorseBorrowers0 INNER JOIN PERSONS
ON WorseBorrowers0.Subscriber = PERSONS.x
ORDER BY SumOfOverdue CopiesNo DESC, BorrowsNo DESC,
[e-mail], LName, FName;
Figure A.1.20 shows the re sult of running this query for k = 0, n = m = 1:

Figure A.1.20 Result of running query WorseBorrow ers for k = 0, n = m = 1
6.1.2 Reports
Design and develop a report with the following data: subscribers e -mail ad dresses, first, and last
names, borrow, du e, and actual return dates, number of overdue days, publisher, editions year and
title, volumes num ber, ISBN, and title, copies in ventory number, volumes first books title, and
their first authors first and last names;
Solution :
The corresponding subjacent query is the following:
SELECT PERSONS.[e -mail], PERSONS.FName, PERSONS.LName,
BorrowDate, DueReturnDate, ActualReturnDate,
IIf(IsNull([ActualReturnDate]),
IIf(Date()>[DueReturnDate],
DateDiff("d",[DueReturnDate],Date()),0),
IIf([ActualRe turnDate]>[DueReturnDate],
DateDiff("d",[DueReturnDate],[ActualReturnDate]),
0)) AS OverdueDays, PUBLISHERS .PubName AS Publisher,
EDITIONS.EYear, EDITIONS.ETitle, VOLUMES.VNo, VOLUMES.ISBN,
VOLUMES.VTitle, COPIES.InvNo, BOOKS.BTitle, AUTHORS.FName AS AuthFName,
AUTHORS.LName AS AuthLName
FROM (((EDITIONS INNER JOIN PUBLISHERS
ON EDITIONS.Publisher = PUBLISHERS.x)
INNER JOIN VOLUMES
ON EDITIONS.x = VOLUMES.Edition) INNER JOIN

50
(PERSONS INNER JOIN (COPIES INNER JOIN (BORROWS
INNER JOIN BORROWS_LIS TS
ON BORROWS.x = BORROWS_LISTS.Borrow)
ON COPIES.x = BORROWS_LISTS.Copy)
ON PERSONS.x = BORROWS.Subscriber)
ON VOLUMES.x = COPIES.Volume) INNER JOIN
((BOOKS INNER JOIN PERSONS AS AUTHORS
ON BOOKS.FirstAuthor = AUTHORS.x) INNER JOIN
VOLUMES_CONTENTS ON BOOKS.x = VOLUMES_CONTENTS.Book)
ON VOLUMES.x = VOLUMES_CONTENTS.Volume
WHERE BookPos = 1;
The needed groupings are the following , in this order :
– On e-mail (with FName and LName too), ascendingly
– On BorrowDate , ascendingly
– On Publisher , ascendingly
– On EYear (with ETitle too), ascendingly
Sums of overdue days are computed for both editions, publishers, bor rows, subscribers, and
overall.
Figure A.1.21 shows the c orresponding report design view ; figures A.1.2 2 and A.1.23 present the
first and last part, respectively, of the results ob tained by running this report in preview mode.

Figure A.1.21 Borrows by subscribers report design view

51

Figure A.1. 22 Borrows by subscribers report preview start screen

Figure A.1.2 3 Borrows by subscribers report pr eview end screen

6.2 Oracle Views and Stored Procedures

52
7. Conclusion
I have chosen the subuniverse of a Library , for which I designed and im plemented a db meant to
be the foundation of a db software application for the management of books, subscribers, and
borrowing.
First, I have applied the algorithm for assisting the business analysis of this subuniverse, with
which I have obtained a corresponding informal des cription of it, a set of entity -relationship
diagrams, and a list of as sociated restrictions.
After applying the algorithm for translating entity -relationship diagrams and restriction lists into
mathematic schemes, I have refined the obtained ma thematical scheme by applying the following
algorithms:
 assistance of sets , functions , and constraints de sign
 assistance of keys discovery
 analysis of (structural) entity -relationship diagram cycles.
Next, I’ve applied the algorithm for translating mathematical schemes in to relational ones and non –
relational constraint lists and, finally, the ones for transl ating relational schemes into MS Access
2010 dbs and for en forcing non -relational constraints in MS Access 2010,
Finally, I have populated the obtained db with plausible demo data, I have de signed and developed
two parameterized queries and a report, wh ich would very much help librarians to extract the most
interesting data and in formation from this db, and I fully documented the whole process, from the
business analysis and design stages, up the implementation and usage ones.

53
8. Bibliography
1. Mancas, C. & Dicu, A. I. Databases L ab Notes, 2014, Bucharest Po lytechnic University.
2. Mancas, C. Databases Lecture N otes, 2014, Bucharest Polytech nic University.
3. Mancas, C. Conceptual Data Modeling and Database Design: A Fully Algorithmic Ap –
proach , Volume I: The Shortest Advisable Path , 2015 , Apple Academic Press , NJ.
4. Microsoft Corp. Access 2010 Help .
5. IT Services. Microsoft Access 2010 An Essential Guide (Level 1) , 2011, The University of
Reading (freely downloadable from
http://www.reading.ac.uk/web/files/its/AccessEssen2010.pdf ).
6. IT Services. Microsoft Access 2010 An Intermediate Guide (Level 2) , 2011, The University
of Reading (freely downloadable from
http://www.reading.ac.uk/web/files/its/AccessInter2010.pdf ).
7. McDonald, M . Access 2010 : The Missing Manual , 2010, O’Reilly Media (freely
downloadable from http://it -ebooks.info/book/104/ ).