CASE: NUNSTA ONLINE BOOK EXCHANGE
Students at the National University of Ngendipura (NUN) buy books for their studies. They also lend and borrow books from other students. Your company, Apasaja Pt. Ltd., is commissioned by NUN Students Association (NUNStA) to design and implement an online book exchange system for its students.
Apasaja Pt. Ltd. designs and implements a database application that records information about students, books they own and books they borrow from other students.
The database records the name, faculty, and department and student number of each student. Each student is identified in the system by email. The database also records the date at which the student joined the university.
The database records the title, authors, publisher, year and edition and the ISBN-10 and ISBN-13 for each book. The International Standard Book Number, ISBN-10 or -13, is an industry standard for the unique identification of books. It is possible that the database records books that are not owned by any students (because the owners of a copy graduated or because the book was advised by a lecturer for a course but not yet purchased by any student.)
The database records the date at which a book copy is borrowed and the date at which it is returned. We will refer to this information as a loan record.
For historical purposes the database records information about the copies and the owners of copies as long as the owners are students or there are loan records concerning the copies.
For historical purposes the database records information about graduated students as long as there are loan records concerning books that they owned.
For historical purposes (in order to keep the loan records for this book) the database records the case of a book that was owned and subsequently sold unless the copy was never borrowed.
Lab 1: Starting up
Without creating the schema using the code in the file NUNStASchema.sql, without populating the database with the code in the files NUMNStACopy.sql, NUMNStALoan.sql and NUMNStAStudent.sql, and without looking at these files, write the following queries in SQL.
Question 1. Create a table book that contains information about books (title, format, and number of pages, authors, publisher, year, edition, ISBN-10 and 13). Find one book on the Web, for instance on amazon.com, to see some examples of the values of these attributes. Search the Web for available SQL domains (types).
Question 2. Delete the table book.
Question 3. Create a table book that contains information about books (title, format (paperback or hardcover), number of pages, authors, publisher, year, edition, ISBN-10 and ISBN-13). Choose a primary key. Forbid NULL values for the title and ISBN-10 and 13, make sure the format is either "paperback" or "hardcover".
Question 4. Insert one book called "Introduction to Database Systems". Go to the Web to find actual details.
Question 5. Insert half a dozen books with title containing "Introduction to Database Systems" or authored by C.J. Date. Go to the Web, for instance amazon.com, to find the details of such books.
Question 6. Find all the information about all books.
Question 7. Modify all books authored by C.J. Date to mention the author's first name (find the author's first name from the Web.)
Question 8. Delete all books authored by C.J. Date.
Question 9. Find the title, format, number of pages, authors, publisher, year, edition, ISBN-10 and ISBN-13 of the books.
Question 10. Find the titles of the books.
Question 11. Find the authors of the books called "Introduction to Database Systems". Question 12. Add a language attribute to all books. Set the default language to English. Question 13. Delete the table book.
Create the tables for the remainder of this tutorial and populate them using the SQL code provided to you for the SQL tutorials. Create the tables using the file NUNStASchema.sql. Populate the tables using the files NUMNStABook.sql, NUMNStACopy.sql, NUMNStALoan.sql and NUMNStAStudent.sql. You can clean up data and tables anytime using the file NUNStAClean.sql. The files are available in IVLE Workbin.
Lab 2: Queries, duplicates, referential integrity, multiple relations and conditions and complex conditions, UNION.
Write the following queries in SQL. Always ask yourself the question about duplicates potentially created by the query with or without DISTINCT. Only use DISTINCT when strictly necessary.
Question 1. Find the emails of students.
Question 2. Find the different emails of students.
Question 3. Print the names of students in descending alphabetical order.
Question 4. Are there students with the same name?
Question 5. Find the different names of students. Is the result sorted? Look at the execution plan.
Question 6. Find the names of students who owned a copy of the book ‘978-0262033848'.
Question 7. Find the names of students who owned a copy of a book with more than 100 pages whose title contains the word ‘Computer'.
Question 8. Find the number of A4 pages needed to photocopy the two books with ISBN-13 ‘978-0262033848' and ‘978-0321295354' (2 pages of a book can be copied on one A4 page).
Question 9. Find the different names of students who owned a copy of a book other than ‘978-0262033848'.
Question 10. Find the names of students who borrowed a copy of the book ‘978- 0262033848'.
Question 11. Find the names of students who owned or borrowed a copy of the book ‘978- 0262033848'. Use UNION.
Question 12. Find the names of students who owned or borrowed a copy of the book ‘978- 0262033848'. USE OR.
Question 13. Delete all the data in table loan.
Question 14. Try again the last two queries.
Lab 3: Aggregate functions, nested queries, views and triggers.
Write the following queries in SQL.
Question 1. Find the total number of copies.
Question 2. Find, for each book, the corresponding number of copies. [Print the primary key of the book and the number of copies.]
Question 3. Find the available books with the largest number of copies.
Question 4. Find the names of the students who have borrowed some book by ‘Charles Dickens.
Question 5. Find the number of different books by ‘Charles Dickens.
Question 6. Find the names of the different students who have borrowed all the books by ‘Charles Dickens'. Use aggregate functions.
Question 7. Find the names of students who owned a copy of a book with more than 100 pages whose title contains the word ‘Computer'. Use nested queries. (Note: This is not the preferred answer.)
Question 8. Find the different names of students who never owned a copy of a book other than the book ‘978-0262033848'.
Question 9. Find the names of the different students who have borrowed all the books by ‘Charles Dickens'. Use NOT EXISTS. (Note: You may also try with NOT IN and EXCEPT.)
Question 10. Find the names of the different students who have borrowed all the books by ‘Amelie Nothomb'.
Question 11. Create and query views for the copies and loans for which the owner is a Computer Science student.
Question 12. We could not cascade the update and deletion of book borrowers to the loan table. Create a trigger that propagates the update of a student's email to the loan.
MODELING WITH THE ENTITY- RELATIONSHIP MODEL AND THE RELATIONAL MODEL
PROBLEM I - CASE
The Varsity International Network of Oenology (VINO) wishes to computerize the management of the information about its members as well as to record the information they gather about various wines. The organization is big enough so that there are several members with the same name. A card with a unique number is issued to identify each drinker. The contact address of each member is also recorded for the mailing of announcements and calls for meetings.
At most once a week, VINO organizes a tasting session. At each session, the attending members taste several bottles. Each member records for each bottle his or her evaluation of the quality (very good, good, average, mediocre, bad, very bad) of each wine that she or he tastes. The evaluation may differ for the same wine from one drinker to another. Actual quality and therefore evaluation also varies from one to another bottle of a given wine. Every bottle that is opened during the tasting session is finished during that session.
Each wine is identified by its name ("Parade D'Amour"), appellation ("Bordeaux") and vintage (1990). Other information of interest about the wine is the degree of alcohol (11.5), where and by whom it has been bottled ("Mis en Bouteille par Amblard-Larolphie Negociant- Eleveur a Saint Andrede Cubzac (Gironde) - France"), the certification of its appellation if available ("Appellation Bordeaux Controlee"), and the country it comes from (Produce of "France").
Generally, there are or have been several bottles of the same wine in the cellar. For each wine, the bottles in the wine cellar of VINO are numbered. For instance the cellar has 20 bottles numbered 1 to 20 of a Semillon from 1996 named Rumbalara. For documentation purposes VINO may also want to record wines for which it does not own bottles.
The bottles are either available in the cellar, or they have been tasted (and drunk...).
1. Design an entity-relationship schema that most correctly and most completely captures the constraints expressed in the above description of the VINO application. Draw the corresponding entity-relationship diagram. Do not omit to declare the necessary integrity constraints. Indicate in English the constraints that cannot be captured, if any. Follow the following steps. At each step, justify your choice by quoting the sentences in the text that support it.
a. Identify entity sets.
b. Identify relationship sets and link them to the entity sets they relate.
c. Indicate attributes of entity and relationship sets.
d. Indicate the combination of attributes that form keys.
e. Indicate the participation constraints.
2. Translate your entity-relationship diagram into a relational schema. Give the SQL DDL statements to create this schema. Do not omit to declare the necessary integrity constraints. Indicate in English the constraints that cannot be captured, if any.
CASE
Let us consider a database application for a pizza company. The company manages several stores in different parts of Singapore.
Pizzas have a code identifying them, a name, and a size in inches. A store has a name. The database records the location of the store (area) and the telephone number of the store. Different stores may sell the same pizzas at different prices.
The schema of the database is as follow: PIZZA (code, name, size)
STORE (name, area, phone)
SELLS (store_name, code , price)
PROBLEM II - TUTORIAL QUESTIONS
Write the following queries in SQL, relational algebra, t-uple relational calculus and domain relational calculus. For each of the following queries find as many equivalent queries in in SQL, relational algebra, t-uple relational calculus and domain relational calculus. In particular find all the possible SQL queries for question 7.
1. Find the names of pizzas that come in a 10 inch size.
2. Find the names of pizzas that come in a 10 inch or a 12 inch size.
3. Find the names of pizzas that come in both a 10 inch and a 12 inch size.
4. Find the pairs of different codes of pizzas with the same name and same size (is there any?)
5. Find the names and phone numbers of the stores in "College Park" or "Greenbelt" that sell a 10 inch pizza named "pepperoni" for less than $8.
6. Find the codes of the most expensive pizzas - assume that the schema of the database is reduced to a relation pizza(code, price) for the sake of simplicity.
7. Find the names of the stores that sell all pizzas.
PROBLEM III
1. Is the following rule correct?
∀ X ∈ R ∀ Y∈ R (if X → Y then Y ⊆ X)
2. The following rule is called Pseudo-transitivity.
∀X ∈ R ∀ Y ∈ R ∀ Z ∈ R ∀ V ∈ R (if X → Y and Z → V and Z ⊆ Y, then X → V)
a. Prove it using the Armstrong axioms.
b. Argue that if we replace transitivity with pseudo-transitivity in the Armstrong's axioms we still have a set of axioms that is complete.
3. Consider the set of functional dependencies
F= { {A} →{B},{C} →{D}, {B,D} →{E}, {D} →{A,D}, {A,C} →{E,B} } on the relation scheme R= {A, B, C, D, E}.
a. Give an example instance of R that complies with the functional dependencies.
b. Give an example instance of R that violates the functional dependencies.
c. Compute F+ the closure of F.
d. Give an example of a trivial functional dependency in F+
e. Give an example of a completely non trivial functional dependency in F+
f. Give an example of a non completely non trivial and non trivial functional dependency in F+
g. Compute {C}+ the closure of the set of attributes {C}.
h. Compute a minimal cover of F
4. Invent three examples consisting of a relation scheme R and a set of integrity constraints on R such that, respectively,
a. R is not in 2NF;
b. R is not in 3NF but in 2NF;
c. R is not in BCNF but in 3NF.
5. Using the algorithms we have learned, decompose your examples in the previous question
a. into BCNF;
b. into dependency preserving BCNF (if possible);
c. into 3NF.
PROJECT - WEB DATABASE APPLICATION
OBJECTIVES
The objective of this project is for you to familiarize yourself with database technologies, to to use the school's available software and infrastructure, and to apply the concepts and techniques learned in class for the design and programming of a database application.
The evaluation of the project will consider the scope of concepts and techniques used and their relevance. For instance, you should try and appropriately use the simple and advanced SQL constructs that you have learned: simple queries, aggregate queries, integrity constraints, views, etc. Feel free to extend the application requirements and add features in order to demonstrate interesting use of the technology learned. Consequently, the amount of data in the database should be sufficient for a complete and realistic demonstration of the system.
TECHNOLOGY
Figure 1 is an overview of the architecture of your application. The architecture consists of a Web server, a server page language and, of course, a relational database management system.
The DBMS and possibly the Web Server can be located on the Cloud.
TOPICS
The manager of your company, ThinkCan Pte Ltd, a Singaporean software house, asked your group to design and implement the prototype of an online (Web based) system. The prototype should be realistic in order to convince a major customer to commission your company to develop the system, but should also illustrate the use of relational database technology in order to serve as an in-house showcase application for engineers in your company.
It is left to your creativity to design a realistic model for the description of the elements and ancillary information in the system. The design can be kept simple but should be sufficiently rich to allow the meaningful demonstration of SQL and DBMS features. Similarly, you should also populate the database with sufficiently enough data to both make the demonstration realistic and to illustrate the use of interesting SQL and DBMS features.
The five candidate topics are:
Topic A, Airline Ticketing: the system is a catalog of flights. Users can search for flights, check for seat availability and make reservations. They can update their particulars and make changes to their reservations. Administrators can create, modify and delete all entries. Please refer to https://www.singaporeair.com/ for examples and data.
Topic B, Digital Content Store: the system is a catalogue of digital contents, such as apps, games, tv shows and movies. Users can search the catalog by title, genre, device, release dates, ratings, etc. Registered users can buy/rent digital contents and rate them. Administrators can create, modify and delete all entries. Please refer to iTunes Store for examples and data.
Topic C, Flashcards for Language Learning: the system is a catalogue of language learning flash cards. Users can create sets of flashcards with words on one side of a card and the corresponding translations on the other side. Users may also search sets by title, description, languages, etc. and add sets created by others to their collections. Administrators can create, modify and delete all entries. Please refer to https://quizlet.com/ for examples and data.
Topic D, Social News Platform: the system is a catalogue of contents (e.g., text posts and links) organized by topics. Registered users can view, post and comment on the contents. Registered users may also upvote/downvote comments and contents. This affects not only the visibility of
the comments and contents, but also the reputation of their owners. Administrators can create, modify and delete all entries. Please refer to https://www.reddit.com/ for examples and data.
Topic E, Module Bidding System: the system is a catalogue of modules for bidding. In each bidding phase, students can use their points to bid for modules as long as some conditions are met (e.g., no clashes in timetable). After each phase, the vacancies in a module are filled by students in the order of the highest bidder to the lowest. (Note: The allocation algorithm should be kept simple.) The bidding process ends after a certain number of phases have been completed. Administrators can create, modify and delete all entries. Please refer to the NUS CORS system for examples and data.
In order to meet the minimum requirements, the system should allow 1) browsing and searching of entries (basic and advanced search features) and ancillary data, and 2) creation, deletion, and modification of entries and ancillary data. Simple login and sessions may be required by the topic but advanced access control or shopping basket is not required. Extra credit will be gained by appropriate usage and demonstration of advanced SQL and DBMS features (advanced SQL queries such as aggregates, nested queries, views, non standard integrity constraints, triggers, and stored procedures).