There are a number of publicly funded libraries in the Greater Sydney region. The objective of this assignment is to develop a database system that will be used to centrally store and manage all...

There are a number of publicly funded libraries in the Greater Sydney region. The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for these libraries. We will refer to this asSydney Library(SL). The information to be stored include information on different branches of SL (Ryde, Epping, etc), the collections they hold (books, magazines, movies, etc.), staff they employ (librarians, security personnel, etc.), members of the library, events they host (book reading and signing by author-guests). The basic requirements gathered from the stake holders is presented in the following five points. As typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required, and keep a note of the assumptions you made.

1. 
   

   Branch Information:The SL System shall keep information on each branch including its name and address, number of employees who work there, spaces available for holding events and cost of renting such spaces. The system shall also contain information on which days (Mon-Sun) the branch is open, and during which hours (opening and closing time). It will also keep information on how staff are rostered to work during those hours.

   
   


2. 
   

   Collection Information:The system shall contain information on items it holds, their type, cost and provenance (authorship, publisher, producer, editor, prominent actors, etc. as relevant). It is possible that multiple copies of a particular “title” are held at the same branch. Information should also be kept on whether a particular item is available for borrowing, and if not, who has borrowed it, and by which date it should be returned.

   
   


3. 
   

   Employee Information: The system shall record information on all employees who work at different branches of SL. This will include their roles, type of employment (e.g. permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to.

   
   


4. 
   

   Membership Information: The system shall record information on members at different branches, including when the membership will expire.

   
   


5. 
   

   Events Information.The system shall keep information on the events hosted at different branches. A full-time staff manages each event, supported by one or more other staff. The guest would contribute, as mutually agreed, a fixed percent of the selling price of the books they sold at the event. After the event is over, based on the sales, a single invoice for the whole campaign is sent to the guest. When the guest pays, the date paid is recorded. Furthermore, the actual cost of a event is calculated from a range of information such as: cost of staff time and infrastructure cost.