1 / 13

Designing a Library Database System: E-R Modeling and SQL Implementation

This document outlines a systematic approach to modeling a small library's database needs. It focuses on designing an Entity-Relationship (E-R) diagram and the necessary SQL for managing library holdings including books, music, and movies. Detailed specifications for each type of holding, including borrowing rules and patron management, are provided. Revisions to the E-R model are discussed to ensure comprehensive tracking of borrowing activities, and SQL definitions are presented to facilitate adding new holdings and patrons while managing borrowing records effectively.

eleanor-dennis
Download Presentation

Designing a Library Database System: E-R Modeling and SQL Implementation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Entity-Relationship ModelingReview Week 6, Day 2 combining E-R and SQL CMPT 355 Sept-Dec 2010 - w6d2

  2. The basic problem • We want to consider the database needs of a small library. It wants a system to act as a catalog for its holdings and to keep track of all borrowings. • The library contains three types of holdings: books, music, and movies. • The library (from time to time) sets the maximum number of each type of item that a patron can borrow. • A code is used to identify all individual holdings. It includes • a code that uniquely identifies the particular title which it obtains separately using an external library reference system) and • (if needed) a copy number to distinguish between duplicate holdings. • Individual holdings may be borrowed for 0, 1, 2, or 3 weeks depending on how popular they are. • Reference holdings cannot be borrowed, thus the 0 weeks category. • The library wants to keep the records of all borrowings so that it can analyze the demand for different specific types of holdings. CMPT 355 Sept-Dec 2010 - w6d2

  3. Entity Relationship modeling • Develop an E-R diagram to model the basic problem. CMPT 355 Sept-Dec 2010 - w6d2

  4. SQL for our initial model • Create the SQL needed for this initial model. CMPT 355 Sept-Dec 2010 - w6d2

  5. Further details about holdings • For books it is important to have info that includes: • author, title, publisher, date of publication, number of pages, key words to identify contents, library classification category, allowable borrowing time • For music it is important to have info that includes: • artist(s), title, publisher, date of recording, recording media (e.g. cd, dvd, lp, cassette), song titles, library classification category, allowable borrowing time • For movies it is important to have info that includes: • title, studio, date of recording, actors, director, review rating, age rating, library classification category, allowable borrowing time CMPT 355 Sept-Dec 2010 - w6d2

  6. Revising our E-R Model • Consider the revisions necessary to our E-R model based on the further details about holdings. CMPT 355 Sept-Dec 2010 - w6d2

  7. SQL for our revised model • Identify where changes are needed to the initial SQL. • Identify additional SQL that is required • To define holdings. • To add new holdings to the system. CMPT 355 Sept-Dec 2010 - w6d2

  8. Further details about borrowings • It is important to know the dates: • when a holding was borrowed • when it is due. • Once a holding is returned, • the borrowing is complete, • but the record of the borrowing should still remain. CMPT 355 Sept-Dec 2010 - w6d2

  9. Revising our E-R Model • Consider the revisions necessary to our E-R model based on the further details about borrowings. CMPT 355 Sept-Dec 2010 - w6d2

  10. SQL for our revised model • Identify where changes are needed to the initial SQL. • To define borrowings. • Identify additional SQL that is required. • To add new borrowings to the system. CMPT 355 Sept-Dec 2010 - w6d2

  11. Further details about patrons • Eligible individuals may apply to become “patrons” who are eligible to borrow items. • Each patron will be given a library card with a unique membership number. • Library membership must be renewed each year. • Patrons with items that are overdue are not allowed to borrow new items, until the overdue items are returned. CMPT 355 Sept-Dec 2010 - w6d2

  12. Revising our E-R Model • Consider the revisions necessary to our E-R model based on the further details about patrons. CMPT 355 Sept-Dec 2010 - w6d2

  13. SQL for our revised model • Identify where changes are needed to the initial SQL. • To define patrons. • To add new patrons to the system. • To renew patron memberships. • To add new borrowings to the system. CMPT 355 Sept-Dec 2010 - w6d2

More Related