130 likes | 233 Views
Entity-Relationship Modeling Review. Week 6, Day 2 combining E-R and SQL. 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.
E N D
Entity-Relationship ModelingReview Week 6, Day 2 combining E-R and SQL CMPT 355 Sept-Dec 2010 - w6d2
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
Entity Relationship modeling • Develop an E-R diagram to model the basic problem. CMPT 355 Sept-Dec 2010 - w6d2
SQL for our initial model • Create the SQL needed for this initial model. CMPT 355 Sept-Dec 2010 - w6d2
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
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
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
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
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
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
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
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
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