• DUE DATE: Mon. June 25 by 11:59:59pm
  

BiblioBase: A Bibliographic Database

This project expands on the publication example we have been using in class to create a complete bibliographic database system, such as would be found in a library online public access catalog (OPAC), maintained by a publisher, or in used in a digital preservation system.

BiblioBase is designed to catalogue bibliographic information about a variety of pubication types, including periodic publications, books, and book series. It is also extensible to other publications types in the future, such as DVDs, and domain specific databases. It supports general queries about content, as well as queries that are specific to a particular publication type.

The project provides a back-end that could be used for future projects in a web development class to create a front end to the database that allows both end-user and administrative access through a web or mobile interface. It could also be used as a starting point for a course in web-base software architecture to implement web services that can be accessed by web-based applications or integrated with a content management system that contains the catalogued content.

Project details

The project is to develop and document a data model for representing the entities and relationships in a bibliographic system, provide DDL for creating the tables, DML for adding entries in the tables, and DQL for making commonly used queries to retrieve data from the database. The project should also provide sample data that can be loaded into the database.This system holds only bibliographic information and not content.

The data model for the project is based on the concept of a publisher, collections of publications put out by the publisher, and authors who are responsible for contributing to those publications. Here are details of these entities and their relationships.

Publisher is the entity who is responsible for publishing the content. A pubisher may publish multiple types of content. An example of a publisher is National Geographic Society. Unfortunately, there is no unique identifier for a publisher other than the name, and the issue of publisher name is a ridculously complex isse that can safely be ignored for this project.

Publication is is any work that is published by a publisher. A publication has a publicaton type that indicates the type of the publication. with some common and some publication specific attributes. For example, National Geographic Society publishes journals, books, book series, as well as DVDs and maps. Here are some details about kinds of publications.

• Periodical is a serial publication appearing at regular intervals. a periodical contains Articles written by one or more authors. Periodicals include magazines like National Geographic and scholarly journals like the Proceedings of the National Academy of Science. A periodicals is identified by an ISSN (International Standard Serial Number). An article has a title, authors, a range of pages, and often a DOI (Digital Object Identifier).
• Book is a stand-alone volume that can have one or more authors. It can represent a single work, or a collection of works as chapters by one or more authors (e.g. an anthology). An example of a National Geographic Society book is Apollo on the Moon. A book is identified by an ISBN (International Standard Book Number). Books can sometime have their own DOIs. A book has a title and a year of publication, and the number of pages. New editions of a book have their own ISBN. A book may also include records for its individual chapters, especially if it an an anthology.
• Book series is a sequence of books with certain characteristics in common that are formally identified together as a group. Book series can be organized in different ways, such as written by the same author, or marketed as a group by their publisher. For example, Bugs!, is port of a book series, National Geographic Kids Readers. Each book in the series has its own title and ISBN and sometimes a DOI. The series itself is also eligible for an ISBN, and books in a series can also be referred to by a volume number in the series.

Author is the creator of of content in a publication. For a periodical, one or more authors are associated with an individual article. The default role of one or more authors associated with an issue is editor. The default role of one or more authors associated with a book is as its authors. If the book has individual chapters, the default role of associated chapter authors is as authors, and the role of authors associated with the book is as editors. There are no authors associated with a periodical or a book series. An ORCID ID (Open Researcher and Contributor ID) is recogniaed as an author identifier in the academic community. Otherwise, there is no recognized identifier other than the name.

• DUE DATE: Dec 2 by 11:59:59pm
  

ImageOrganizer: An Image Management Database

This project is a portion of a an image management program similar to ones that are available on web-based platforms such as Wordpress or on image management program that run locally on a computer.

ImageOrganizer is a personal image management program that enables users to storeimages in a file-based or cloud-based storage system, create a catalog to organize images in a RDBMS that includes physical and logical metadata about the images, and then queries to retrieve images based on queries to the database that identify which images meet the criteria.

This project provides a back-end that could be used for future projects in a web development class or a course on graphical user interfaces that provides a front-end for users to manage their images. It could also be used as a starting point for a course in web-based software architecture to implement web services that can be accessed by web-based applicions or integrated with a file- or cloud-based content management system that contains the store images.

Project details

The project is to develop and document a data model for representing the entities and relationships in an image management system, provide DDL for creating the tables, DML for adding entries in the tables, and DQL for making commonly used queries to retrieve image location information from the database. This system hold only physical and logical metadta and not content.

The data model for the project is based on the concept of image information that provides basic information, categories that can be used to classify related images, and properties that provide physical and logical metadata about for an image. Here are details of these entities and their r elationships.

ImageInfo is the entity that holds basic information about an image. This includes a "gensym" image identifier, a locator that identifies the image itself in a file or cloud-based content management system, a creation data/time, a display name, a short description of the image, and a long description of the image.

Category is a set of image categories that can be applied to images. Caegories can be used to create taxonomic classifiers for a group of images. Categories can be organized hierarchically as a tree, and images can be associated with any level in the hierarchy. A category has an identifier key and a name. An image can apppear in multiple categories and a cateagory can have multiple images. It may be desirable to ensure that an image does not also appear in a prefix of its location in a category path.

Property is a way to provide physical and logical metadta about an image. An image can have any number of name/value pairs. Both the name and value are stored as strings, but a type parameter allows an application to interpret the value string as another type. To ensure that properties are used consistently, names are always associated with the the same type, and values are associated with the images to which they apply. A stored procedure could be used to check the value for a given property. A property can also be used as a named tags on images with no value.

• DUE DATE: Dec 2 by 11:59:59pm
  

OrderManager: A Database for Managing Products and Orders

This project is a portion of a an application for managing products, product inventory, and customer orders for an online store..

OrderManager is an e-commerce program that enables a business to manage information about products that can be sold to customers, to track current inventories of products, and to process orders for products from customers. The RDBMS maintains informaiton about products that can be ordered by costumers, tracks inventory levels of each product, and handles orders for product by customers. It enables a business to track sales of products, and allows customers to determine product availability and place orders.

The project provides a back-end that could be used for future projects in a web development class to create a front end to the database that allows both businesses and their customers access through a web or mobile interface. It could also be used as a starting point for a course in web-base software architecture to implement web services that can be accessed by web-based applications or integrated with a e-commerce system that contains product and order information.

Project details

The project is to develop and document a data model for representing the entities and relationships in an order management system, provide DDL for creating the tables, DML for adding entries in the tables, and DQL for making commonly used queries to retrieve product, inventory, and order information from the database.

The data model for the project is based on the concept of products that can be purchased, inventories of products available for purchase, customers who purchase products, and orders for products by costumers.

Product represents a product that can be purchased. It includes the name of the product, a product description, a vendor product SKU (Stock Keeping Unit) that identifies the product. For this exercise the SKU is a 12-character value of the form AA-NNNNNN-CC where A is an upper-case letter, N is a digit from 0-9, and C is either a digit or an uppper case letter. For example, "AB-123456-0N".

InventoryRecord is the number of units available for purchase and the price per unit for the current inventory (positive, with 2 digits after the decimal place).

Customer is information about the customer, including name, address, city, state, country, postal code. Make reasonable assumptions about the sizes of the fields, and whether state and country should be enumerated values or strings. The customer also has a customer id that is a numeric gensym. Payment information is not part of this database.

Order is an order for a set of products. It includes a customer ID, an order ID gensym, the order date, and shipment date indicating when the order was shipped. If shipment date is null, the order has not yet shipped. All items must be available in a single transaction to place an order.

OrderRecord is the record for an item in the order. it includes the order ID, the number of units, and the unit price. The item must be available and the inventory is automatically reduced when an order record is cretated for an order.

• DUE DATE: Dec 2 by 11:59:59pm
  

iRate: A Database for Managing Movie Ratings

This project is a portion of a an application that enables registered movie theater customers to rate a movie that they saw at the theater, and for other registered customers to vote for reviews.

iRate is a social media application that encourages theater customers to rate a movie that they saw at the theater in the past week and write a short review. Other costumers can vote one review of a particular movie as "helpful" each day. The writer of the top rated review of a movie written three days earlier receives a free movie ticket, and voting is closed for all reviews of the movie written three days ago. Someone who voted one or more movie reviews as "helpful" on a given day will be chosen to receive a free concession item.

This project provides a back-end that could be used for a future project in a web development class to create web-based front-end to the database that allows a movie theater to operate this promotional application. It can also be used as part of a back end to a mobile application for the theater.

Project details

The project is to develop and document a data model for representing entities and relationships in this promotial social media application, provide DDL for creating the tables, DML for editing entries in the tables, and DQL for making commonly used queries to retrieve information about the status of reviews and votes from the database.

The data model for the project is based on the concept of registered customers, movies seen by a costumer, reviews written by a customer, and votes cast by other costumers for a given movie.

Customer is a registered customer of the theater. The Customer information includes a customer name, email address, the date the customer joined, and a gensym customer ID. The information is entered by the theater when the customer registers. If a customer is deleted, all of his or her reviews and endorsements are deleted

Movie is a record of a movie playing at the theater. It includes a title, and a gensym movie ID. This information is entered by t he theater for each movie it plays.

Attendance is a record of a movie seen by a customer on a given date. It includes a movie ID, the attendance date, and the customer ID. This information is entered when the customer purchases a ticket for a show. If a movie is deleted, all of its attendances are deleted. Attendance info is used to verify attendance when creating a review.

Review is a review of a particular movie attended by a custumer within the last week. The review includes the customer ID, the movie ID, the review date, a rating (0-5 stars), a short (1000 characters or less) review, and a gensym review ID. There can only be one movie review per customer, and the date of the review must be within 7 days of the most recent attendance of the movie. If a movie is deleted, all of its reviews are also deleted.

Endorsement is an endoresement of a movie review by a customer. A customer's current endorsement of a review for a movie must be at least one day after the customer's endorsement of a review for the same movie. The endorsement includes the review ID, the customerID of the endorser, and the endoresemnt date. A customer cannot endorse his or her own review. If a review is deleted, all endorsements are also deleted.