10/27/2014 Blackboard Learnhttps://elearning.utdallas.edu/webapps/portal/frameset.jsp?tab_tab_group_id=_105_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3D…1/5SQL Library Project New RequirementsThe following new requirements are now added to the SQLLibrary Project. These new requirements:1. simplifiy book_authors table2. enable the tracking and payment of library fines.BOOK AUTHORSThe book_authors table schema has changed. An author is not simply a single attribute which containsthe author's full name, regardless if it is a person of organization. fname, minit, lname are no longer partof the schema.A new attribute called "Type" is now required. It shoudl be an integer value: If the author is a personthen Type = 1, and if the author is a group or organization then Type = 2FINESCreate a new table FINES(loan_id, fine_amt, paid)The primary key loan_id is also a foreign key that references BOOK_LOANS(loan_id)fine_amt attribute is a dollar amount that should have two decimal places.paid attribute is a boolean value (or integer 0/1) that idicates whether a fine has been paid.Fines are assessed at a rate of $0.25/day (twenty-five cents per day).You should provide a button, menu item, etc. that updates/refreshes entries in the FINES table. Inreality, this would occur as a cron/batch script that executed daily.There are two scenarios for late books(1) Late books that have been returned — the fine will be [(the difference in days between thedue_date and date_in) * $0.25].(2) Late book that are still out — the estimated fine will be [(the difference between thedue_date and TODAY) * $0.25].If a row already exists in FINES for a particular late BOOK_LOANS record, thenIf paid == FALSE, do not create a new row, only update the fine_amt if different than currentvalue.If paid == TRUE, do nothing.Provide a mechanism for librarians to enter payment of fines (i.e. to update a FINES record where paid== TRUE)Do not allow payment of a fine for books that are not yet returned.Display of Fines should be grouped by card_no. i.e. SUM the fine_amt for each Borrower.Display of Fines should provide a mechanism to filter out previously paid fines (either by defaultor choice).Book Loans DataAttached Files: SQL_library_project_data.zip (14.555 KB)SQL ProjectThis is the link to submit yourSQL Library Project.This SQL programming project involves the creation of a database host application that interfaces with a SQLdatabase that implements a Library Management System. Assume that the users of the system are librarians(not book borrowers).This is an individual (not group) project. All work, design, and coding must be done individually by you.Due Date: Oct 27 9:00amDue Date: Nov 3 9:00am10/27/2014 Blackboard Learnhttps://elearning.utdallas.edu/webapps/portal/frameset.jsp?tab_tab_group_id=_105_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3D…2/5Programming Language(s) and FrameworksJava + MySQL is the recommended language and framework for this project. This combination will havethe most resources and support for the project. You may alternatively C# to build your host application and/or Microsoft SQL Server as your database.This combination will have fewer resources and support for the project.If you would like to use any other languge for your host application and/or database and framework youmust obtain prior approval from the TA that they are able to evaluate the language/framework used inyour submission.SchemaThe schema for the library database is derived from (but NOT the same as) the library schema in thetextbook (p.113 Figure 4.6). The actual schema used for this project is provided in this folder. You arepermitted to modify or augment this schema provided that your system adheres to the writtenrequirements below. As long as it supports the documented functionality, you may add any features youdeem useful.DataInitial baseline data to initialize your database will be provided in this folder. There is no initialdata in the BOOK_LOANS table.The use cases executed for grading will be based upon this data.Data is provided in plain text (ascii) files. It is your task to map these onto the schema andtables, i.e. there is not one "load file" per table.All book id's are 10-character ISBN numbers (i.e. some contain alpha characters). If a book hasa 9-digit id, you must prepend a 0 (zero) to make it 10-digits.BOOK_AUTHORS should contain 3 columns for Fname, Minit, Lname. If an author has nomiddle name or middle initial, it should be NULL.Replace primary key of BOOK_LOANS with a new synthetic key that is INT type named"loan_id". This will allow a BORROWER to check out a book more than once from the samebranch.SubmissionYou will be required to submit the following files:All application source code, including any build files (e.g. make, ant, maven, etc.)An SQL schema creation file that includes appropriate all appropriate CREATE TABLEcommands and (potentially) ALTER TABLE commands and/or CREATE TRIGGER commands toimplement any integrity constraints.Table data import file(s) that include includes either (a) a sequence of INSERT INTOcommands, or (b) A file will load directives and the .dat on which it operatesA 1-2 page written description of your system that includes (a) user manual for librarians, (b)your design decisions and justifications, (c) technical dependencies (software libraries, softwareversions, etc.).All files must be zipped together into a single file. This file should be named <net-id>_cs6360.zip,where <net-id> is your Net ID. Example: cid021000_cs6360.zipGradingYou will be required to demonstrate your application for the TA. If you are unable to bring alaptop computer to demonstrate your app, please let me know as soon as possible so you canmake alternate arrangements.Each student will have 10-15 minutes to demonstrate their system and execute the test casesprovided at grading time.A sign-up mechanism will be made available to reserve a specific time for evaluation. As acourtesy to the TAs and those waiting behind you, please be on time for your scheduled slot.Notice: Requirements will change before the due date. One objective of this project is the ability toaccomodate requirement changes after beginning a database project. There will be at least one schemachange, one functional requirement change, and one data change.The follow are a summary of your functional requirements:10/27/2014 Blackboard
View Full Document
Unlocking...