csc340 Information Systems Analysis and Design page 1/12University of TorontoFaculty of Arts and ScienceDepartment of Computer ScienceCSC340S - Information Systems Analysis and DesignSpring 2002 John MylopoulosApril-May ExaminationNo Aids AllowedDuration: 2 hoursMake sure that your examination booklet has 12 pages(including this one). Write your answers in the space provided.This examination counts for 35% of your final mark.Name: (Please underline your last name)Student Number: Question Marks1. ____________ /152. ____________ /153. ____________ /154. ____________ /205. ____________ /106. ____________ /25 Total ____________ /100csc340 Information Systems Analysis and Design page 2/12The Toronto Public Libraries (hereafter TPL) is a public organization managing the 99 publiclibraries that operate in Metro Toronto. TPL has decided to redesign its collection material database.Currently, the database holds information on:• Books, videos and CDs available for borrowing;• Every item (book, video, CD) has a unique collection #, a title, and an ISBN# which is unique forevery publication; every item may be in good order or damaged (see below); note that if there are10 copies of a single book, they have distinct collection #s, but identical ISBN#s;• Books can be in English, or another language; in either case, the database stores the language thebook is written in;• Books have a publisher and one or more authors; CDs and videos have a unique producer, and oneor more artists.In addition, the database maintains data on library users and their borrowed material:• Each user has a unique userID, address, phone#; users can be adults, children or seniors; seniors canborrow material for longer periods, while children pay a nominal fine of $0.05/day for un-returnedmaterial;• When a user selects an item to be borrowed, the library clerk updates the database, recordinguserID, collection#, and date of borrowing;• When a user returns an item, the library clerk updates the database, recording the date when theitem was returned; if the item is overdue, the clerk also collects a fine calculated as DaysLate *fine/day; the fine per day amount is $0.50, $0.05, $1.00 for seniors, children and adult usersrespectively; the clerk also records (in the database) the fine collected, if any; in addition, the clerkchecks if the returned item is damaged, and if so, records this information (in the database, again).To add new material to the collection, TPL orders it from XYZ Co., a wholesale supplier. When it arrives,it is catalogued (i.e., an entry is added to the database) and it is made available in some library. Damagedand unused material is removed from the collection. This is done once every six months. The processconsists of selecting all items that are damaged, or haven't been borrowed in the last year, and physicallyremoving them from the collection.Note: The above description is complex, may leave details out and supply unnecessary information. Youwill need to make some assumptions about the problem for the parts below. MAKE SURE you writeyour assumptions down!csc340 Information Systems Analysis and Design page 3/121. [Entity-Relationship Diagrams; 15 marks]Draw an Entity-Relationship diagram that represents the information about collection materials, users andborrowings handled by the TPL database. Make sure to specify clearly the attributes of every class andrelationship, as well as cardinalities and keys. Your solution should be an Entity-Relationship diagram,NOT a UML class diagram!Sample solutionItemBookBorrowuserIDphoneborrowDatecollection#titlelanguage(0,N)VideoCDpublisherauthorUserReturnreturnDate(0,N)addressAdultChildSeniorISBN(0,N)(0,N)(1,N)producerartistproducerartist(1,N)(1,N)csc340 Information Systems Analysis and Design page 4/122. [Sequence Diagrams; 15 marks]Draw a sequence diagram that describes how returned material is handled by TPL. The sequence diagramshould show interactions between a user, a clerk and the TPL database during the process of having a userreturn a borrowed item. Make sure to show explicitly conditional interactions.Sample solutionTPLDBUserClerkreceiveItem[damaged?]calculateCharge(item)[undamaged?]getPurchasePrice(item)[late?]calculateFine(user)amountOwed[amount>0]pleasePay[amount>0]receive(payment)thanksupdateRecord(item,payment)csc340 Information Systems Analysis and Design page 5/123. [State Diagrams; 15 marks]Draw up a state diagram that describes the lifetime of a collection item from the moment it is ordered byTPL until it is removed from the TPL collection. For each state, specify entry, exit, and do actions. Foreach transition, specify relevant events, conditions and actions.Sample solution Waitingfor itemtoarrivePreparingDo/catalogueExit/place inlibrary/orderarrivedAvailable/makeAvailableOn loanborrowed/updateRecord()returned[undamaged?&onTime?]/updateRecord()returned[undamaged?&late?]/requestPayment(fine)&updateRecord()returned[damaged?]/requestPayment(itemPrice)&updateRecord()&removeItem()when(beginningOf6Months)[toBeRemoved?]/updateRecord()&removeItem()csc340 Information Systems Analysis and Design page 6/124. [Database Design; 20 marks]Suppose you are given the Entity-Relationship (ER) schema shown below. It describes the contents of astudent database for Ontario universities. All attributes are assumed to be single-valued. In addition, youmay assume that there are students who are neither undergraduate nor graduate (special students, allowed totake courses but not studying towards a degree).[Part a; 10 marks] Draw an ER schema that captures the same information as the diagram above, butdoes not have any generalization relationships.StudentUndergradUniversityAttendsProfessorSupervisorGraduatenamecitynamedeptstartYearstartDatest#nameaddraveragethesisTitle(1,2) (0,N)(1,1)(1,N)StudentUndergradUniversityAttendsProfessorSupervisorGraduatenamecitynamedeptstartYearstartDatest#nameaddraveragethesisTitle(1,2)(0,N)(1,1)(1,N)hasParthasPart(0,1)(0,1)(1,1)(1,1)csc340 Information Systems Analysis and Design page 7/12[Part b; 10 marks] Give a relational schema that captures the same information as your ER schema ofpart (a). Show clearly keys for each relation in your
View Full Document
Unlocking...