SEN1oR C1T1zENs MoB1LE L1BRARY (SCML)

The local council runs a Senior Citizens Mobile Library that travels to various aged-care residences lending books to the elderly residents.

Currently, all the lending records are kept in an Excel spreadsheet, but as SCML is expanding, this is becoming increasingly cumbersome and so SCML is moving to an SQLite database.

They have already started their implementation and have designed some of the tables they will need, but they need advice on data integrity and on techniques to keep the database internally consistent.

Currently, the database has four tables, namely BookEdition, BookCopy, loan and Client which have the following structure:

The table BookEdition

The table stores data about editions of a book (not individual physical copies of a book).

Each row of BookEdition is uniquely identified by its ISBN and contains the following additional infor-

mation about the book edition: the title, author, publication date and main genre.

An example of a tuple might be:

('9780593607695',  'Any  other  family',  'Eleanor  Brown',  2022,  'Family')

indicating that the ISBN 9780593607695 refers to a 2022 edition of the book Any Other Family written by Eleanor Brown and that it is in the genre Family.

(Real ISBNs are often displayed in a stylised form with hyphens or spaces separating various groups of digits, but it is only the digits that count.)

The table BookCopy

This table stores data about the actual physical books in the SCML collection.

As their clientele are spread across many residences, SCML tends to have many copies of their most popular books.  So if SCML has, say, 10 copies of a book from the same edition, then the 10 books are numbered from 1 to 10, and this “copy number” is stamped inside the front cover of the book.

Each individual book is then represented by a row in the database containing the book’s ISBN and copy number.  Therefore a row of BookCopy is uniquely determined by the combination of ISBN and copyNumber.

SCML realises that they buy too many copies of some books, and too few of others, and so they want to keep statistics on how often the books are actually on loan.  They have added an additional column daysLoaned to the table BookCopy, to keep track of the total cumulative number of days on which this individual book copy has been out on loan.  They wish to analyse this data in the future and hope to keep these values updated by updating this field every time a book is returned from a loan.

The table BookEdition contains many more books than SCML can possibly buy, and so SCML may have zero, one or more actual copies of each of the books listed in BookEdition.

The table loan

Each row of loan stores details for a single loan of a particular physical copy of a book to a particularclient.

The information stored about the loan is the clientId identifying the client loaning the book, the two fields ISBN, copyNumber needed to identify the physical book, and then two fields dateOut and dateBack, giving the dates (in YYYY-MM-DD form) that the book went out to the client and came back from the client.

A new tuple is entered into the table loan on the day that the book is loaned out, with the value NULL assigned to the column dateBack (because this value is not known).

When the book is returned, the row is updated by a statement of the form:

At this stage,  the volunteer is meant to calculate the number of days in this loan and update the daysLoaned column in the table BookCopy.

The table Client

This table records the details for each SCML client.

Each client has a unique clientId and SCML also keeps information about the client’s name and their

aged-care residence. A sample tuple in Client might be

(112,  'Jill  Borowicz',  'Shady  Acres  Subiaco')

indicating that Jill Borowicz is client number 112 and that she lives in the Shady Acres Subiaco aged-care residence.

A client can be registered in the Client table even if they have not (yet) borrowed any books.

The tasks

As a database developer, you have been called in to improve the integrity of the database. You will not be changing any of the tables or columns, but just adding database features to improve the integrity and usability of the database.

You are asked to submit seven files:

ERD .png

BookEdition .sql

BookCopy .sql

loan .sql

Client .sql

loanTrigger .sql

ReadingHistory .sql

according to the following specifications:

1.  An entity-relationship diagram                                     (5 marks)

The first task is to get a visual representation of the existing structure of the database, in order to facilitate further discussions with SCML. Your ERD should have

(a)  Entity sets, attributes, relations and relationship attributes that reflect the current structure

of the database.

(b)  The key,  participation and cardinality constraints that should be added according to the

descriptions of the four tables given above.

The intention is for you to produce a feasible ERD that, when translated to a relational schema, ends up with the existing structure—in other words, to “reverse engineer” the existing database.

Please remember that this part of the project is not  about  designing a better schema, but about describing an existing schema. So do not incorporate entities or attributes into the ERD that are not in the database (even if you think you can make improvements) or you will lose marks. There is one exception to this, because when an ERD is converted to a relational schema, some of the relationships in the ERD are not implemented as a table, but via foreign keys instead.  In this situation, the name of the relationship in the ERD does not appear in the relational schema at all, and so you will have to make up just one name.

You must use ERDPlus .com to prepare your ERD and then use the “Export Image” selection from the  “Menu” button at the top-left of a diagram to save it to a PNG file.  The file will be saved under some generic name like image .png, but you should rename it to ERD .png and submit it via cssubmit.

Do  not submit  anything  that is produced by a different ER diagramming tool, or produced as a figure in Microsoft Word, or drawn in a drawing/painting program, or is hand-drawn and pho- tographed/scanned.

(The reason for this is that there are literally hundreds of diagramming tools / conventions, and it would be impossible for the markers to know them all.)

2.  A new database schema (2 + 2 + 2 = 6 marks as specified below) You should prepare files called

BookEdition .sql

BookCopy .sql

loan .sql

Client .sql

that each contain a DDL statement  (i.e., a CREATE  TABLE statement) that will create the tables BookEdition, BookCopy, loan and Client respectively.  Each of the statements should create a table with exactly the same columns and data types as in the current database, but with additional data integrity features as described below.

Each of your files will be tested individually — for example, when marking loan .sql we will create a database that uses our model solutions to create BookEdition, BookCopy and Client, but your submission to create loan, and then test that it works properly.

Your files should only create the empty tables — we will use our own simulated data to test that your tables behave according to the specification.

(Of course you may want to test your own tables by making up some data of your own, but you should not leave any trace of this in your submitted files.)

Marks will be allocated for the following additional features that you should incorporate into your improved schema.

(a)  Primary key constraints in BookEdition, BookCopy and Client (2 marks)

The description of the table Client indicated that a client should be uniquely identified by the clientId column. However at the moment, there is nothing to prevent two different rows being entered into the table with the same client number.

The sections describing BookEdition and BookCopy also identified columns or combinations of columns that would uniquely identify a row in those tables.

Add key constraints to the DDL statements that you submit to create these three tables, so that SQLite will enforce these constraints on these three tables.

(Do not add any primary key constraints to the table loan.)

(b)  Referential integrity in loan (2 marks)

One problem faced by SCML is that it is quite busy when they visit an aged-care residence and it is very easy to mistype numbers when entering rows into the table loan. This results in rows of the table loan that refer to clients or books that don’t actually exist, and it takes them some time to correct these errors.

SCML realises that the best time to catch these typing errors is at the moment that the row is entered into the table loan by having the system check that any new rows actually refer to genuine clients and actual books.

Your file loan .sql should create the table including referential integrity constraints ensuring that the combination of ISBN and copyNumber refers to an actual book, and that clientId refers to an actual client.

You are given some further information about how SCML’s operations:  SCML wants to be able to change a client’s id  (in the table Client) and to have that change automatically reflected in the table loan.  Due to the nature of their business, they frequently lose clients, in which case they delete the corresponding row from Client. If this happens, then the loans involving that client should remain in the table loan (for the purposes of analysing the data on books loaned out), but the client id should be set to NULL.

You are told to assume that no rows of BookCopy will be updated or deleted, so do not specify any particular action to deal with changes on this table.

(c)  Data entry validation for BookEdition

The 13th digit of a real ISBN is a check digit calculated from the first 12 digits by taking the last digit of the sum

(d1 + d3 + d5 + d7 + d9 + d11 ) + 3 X (d2 + d4 + d6 + d8 + d10 + d12 ).

For example, if we consider the book with ISBN 9780593607695 then the calculation gives

9 + 8 + 5 + 3 + 0 + 6 + 3 X (7 + 0 + 9 + 6 + 7 + 9) = 145

and indeed the 13th digit of the ISBN is 5.

The most common error when typing a number is to get one digit incorrect. If this happens while typing an ISBN, then the  actual  final digit will not match the  required  final digit. Therefore systems dealing with ISBNs should automatically check that it is valid.

We will not use 13-digit ISBN for this project because it is too cumbersome, but instead we will imagine that ISBNs are only 5 digits long, and assume that the check digit of the ISBN is obtained by taking the final digit of the value

3 X (d1 + d3 ) + 7 X (d2 + d4 ).

Your file BookEdition .sql should create the table BookEdition with an additional constraint to prevent the insertion of any rows with an invalid ISBN. More precisely, your constraint should ensure that the ISBN field satisfies the following conditions:

● The ISBN is exactly 5 characters long.

● The 5 characters are all numeric digits (from 0 to 9)

● The 5th digit is determined by the first 4 digits according to the calculation given above.

3.  A trigger to improve data consistency                                                     (2 marks)

When a book is returned, the row for that book in the table loan is updated by an UPDATE statement changing the value of dateBack from NULL to the return date.

At this point, the person updating the database is also meant to calculate the number of days that the book has been out on loan (using dateOut and dateBack) and then add this value to the daysLoaned column in the corresponding row of BookCopy.

However because it is busy, this step is often not done, or the calculation of the days is done incorrectly, so it would be much better if these values could be updated automatically .                  You realise that this is an ideal situation for the use of a trigger .

Your file loanTrigger .sql should  create  a  trigger  on the table loan that fires when a row is updated, calculates the number of days from this loan, and adds the value to the daysLoaned column of the corresponding row in BookCopy.

When calculating the number of days, include both of the days specified in dateOut and dateBack. So if a book is loaned out on 2022-08-25 and returned on 2022-08-29 then it has been out for 5 days. You will find the built-in SQLite function julianday() useful — this takes a string such as '2022-08-25' representing a date and calculates how many days have elapsed from the beginning of the Julian period (a fixed day in the year 4714 BC) to the given date.

Note that it would be easy to simply make your trigger update every row in the table Book, rather than update only the row that is being changed, but this is not allowed because it defeats the purpose of the question.   In particular, we will check that your trigger only affects one row of BookCopy.

4.  A view

(2 marks)

Write the SQLite code to create a view of the data called ReadingHistory that has the following schema:

For each combination of client, year and genre, the view lists the clientId, the year, the genre and the number of loans of books of that genre made to the client in that year. Use the dateOut column when deciding what year a loan belongs to (so a book borrowed on December 25 2021 and returned on January 5 2022 counts towards the 2021 total).

For example, if Jill Borowicz had borrowed 10 adventure novels, 4 fantasy novels and 6 biographies in 2022, then the command

should produce the following output:

112  2022  'fantasy'        4

112  2022  'adventure'  10

112  2022  'biography'    6

(If Jill borrows the same book three times, then this counts as three loans.)

We will be testing your code by creating the database tables with our model solution, and then creating just the view with your code, prior to testing it with simulated data.

So it is absolutely imperative that every column has exactly the correct name. You are free to alter the way a name is capitalised (so numloans and numLoans are the same), but apart from that you cannot add or remove even a single letter.  For example, numLoan instead of numloans will cause the testing to fail.

Make sure that your file starts with