Please help me to complete the database assessment

 

University of CanberraFaculty of Business Government and LawSemester 2, 2016Database Design 5915 and Database Design G 6672Assignment 2This assignment is worth 50 marks constituting 25% of the total marksfor this unit.Due date: FridayWeek 12 of Semester 2, 2016 at 11:00pmYou need to submit all your design documents and your database (MS Access database)with this assignment as described in the assignment specification below. Please contactyour lecturer or tutor if you have any queries about this assignment.1. General InformationThe purpose of this assignment is to provide you with experience in analyzing, designing andimplementing a solution for a university accommodation office. Your solution should beimplemented as a program for a database system using Microsoft Access 2000. This assignmentwill help you to understand the nature and purpose of database analysis, design andimplementation. It offers you experience in managing a technical database project.This assignment is to be attempted by groups of 4 to 5 students. Each group is collectivelyresponsible for both the submission and the outcome.Individual efforts will not be marked.There are no restrictions on the use of word processors or similar tools for the production ofsubmissions for this assignment.Be sure to maintain regular back-ups for any models or material prepared with the aid ofsoftware. Loss of files will not be accepted as an excuse for non-completion of this assignment.Submit your assignment to the Moodle site of this subject. Marked assignments will beavailable from Moodle website of this subject.2. Problem DescriptionA university library requires you to design a database system for the library. The librarydatabase will record and store data about each student including their first and lastname, postal address and home address (street number, street name, suburb, post-codeand city), sex, the course (i.e. Course number and course name) they are enrolled in andany special needs they may have. A student may be enrolled in up to two courses at anygiven time.The name (first and last name), contact telephone number and address (street number,street name, suburb, post-code and city), of student’s next-of-kin is also stored for eachstudent.For each student the system records and stores all books that each student has on loanand any details of any books that are overdue.The university offers undergraduate (UG), and postgraduate (PG) courses in differentdepartments, namely: Art (Dancing, Singing, Painting, Acting, Languages), Science(Biology, Chemistry, Computing, Mathematics, and Physics), History, Business,Commerce, Economics, Education, Law, Medicine and Dentistry.When a student joins the library, he/she is assigned a library number and his/her details1

are recorded. Every student is provided with a sheet of paper showing the rules relatingto borrowing, return procedures, charges for late return of library items (books, DVDs).The library has several staff member. For each staff member the following data is storedin the library system: staff first and last name, staff number, position, section numberand section name, gender, date of birth, internal telephone number and office number.Library consists of six sections. These are: information-desk, loan-desk, return-desk,cataloging, administration and book order section.The information about each section is stored in the library database. The informationabout each section is: section name, location and section telephone number. Eachsection has up to three telephone numbers.To borrow a book a student browses the library catalogue. Each book is assigned acatalogue number. Several copies of some books may be available in the library. Thebooks are grouped on shelves according to the book field of study and contents, namely:Art (Dancing, Singing, Painting, Acting, Languages), Science (Biology, Chemistry,Computing, Mathematics, and Physics), Business, Commerce, Economics, Education,Law, Medicine, and Dentistry etc.Titles fall into 2 categories, each with different borrowing rules, namely, Short term (3hours) and Long term (4 weeks) borrowing.CategoryTypeBorrowing LimitsLate fee Charges1Short term1 book at a time$0.10 cents per hour2Long term10 books$0.05 cents per dayA library member selects one or more books and takes the book/s to library loan-desk.Students are limited to borrowing a maximum of 10 books in total. The student'sidentity is established with the student library card and their student number is used toaccess their records. If there are any late charges noted in a student record, these mustbe paid before additional books can be borrowed. If there are no outstanding latecharges, details of the books being borrowed are entered into the computer thatcalculates the return date of each book.The details of each book (book title, author name/s, publisher name/s and address,catalogue number, type of book (Short, Medium, or Long term), borrowing date, returndate and borrower’s id number is stored in the library database.The books are then handed to the student, along with a receipt that lists details of thebooks borrowed with their return dates.When books are returned they are placed in a return bin at the return-desk. Later thebooks are removed from the return bin and taken back to the display shelves. Details ofthe books that are returned are entered into the library database. If any book is returnedlate then a late charge fee is calculated and it is recorded in the student's account thatborrowed this book. The late charge is not particularly severe - just a pro-rata chargerounded up to the nearest day. (In this way a book returned any time on the dayfollowing its due date would incur a full days extra charge; a day and a half late costs 2days etc.)The library lends DVDs too. The procedure for borrowing DVDs is similar toborrowing books. For each DVD, DVD title, actor names, producer names and2

addresses, catalogue number, type of loan (Short, Medium, or Long term) as well as thereturn date is stored in the library database. Details of student that borrow DVDs arerecorded in the library database. When DVDs are returned, the records are updated withthe return date information and late charges if any are calculated and added to thestudent's balance.The library system provides book and DVD reservation facility where a library membercan browse the library catalogue and put reservation requests for any library items(books and DVDs). For reservation a member enters his/her library number and thecatalogue number and name of the item that needs to be reserved. The item is thenreserved by the library system and all reservation details are stored in the librarydatabase. Each student can reserve a maximum of 10 books and 10 DVDs.University library has several library research assistants. Library research assistantsassist student in finding relevant information for their study.A student can make an appointment with a library research assistant. The detail of eachappointment is stored in the library database. These details are: student number andname, staff number and name, appointment date and appointment time.University library has several seminar rooms and study rooms. Student and staff canbook these rooms. Details of students that book seminar rooms and study rooms arestored in the library database. These details are: student number, student first and lastname, seminar room number or a study roomnumber and date and time for which aseminar room or a study room is booked.Details of staff that book a seminar room or study room for a student is also recorded inthe library database. These details are: staff number, staff first and last name, position ofstaff, date and time for which a seminar room or a study room is booked.Library has contracts with several restaurants that can supply food if required for anyseminar booked in the library. The details stored about all seminar rooms hired and foodprovided is recorded in the library database. For each seminar booking the number ofattendees to the seminar is recorded in the library database and the type and price offood ordered for each seminar is also recorded. These details are: Restaurant number,restaurant name, date and time for which a seminar room is booked, the total cost for thefood supplied, number of people attended the seminar, name and student number ofstudent that hired the seminar room.The details recorded for staff who book a seminar room are: Restaurant number,restaurant name, date and time for which a seminar room is booked, the total cost for thefood supplied, number of people attended the seminar, name and staff number of staffthat hired the seminar room.The cost of hiring a seminar room is $100 per day. Seminar room details are stored inlibrary database. The seminar room details are: seminar room number, room size, roomlocation, and rental price per day.Before a seminar due date an invoice for the seminar room rental and food cost isprovided to the person who has booked the seminar room. Each invoice has a uniqueinvoice number. The data stored on each invoice includes: invoice number, seminarbooking number, payment due date, student full name and student number, seminar3

room number, and amount to be paid. When a student makes a payment for an invoicethen payment details are recorded. The payment details include: payment number,invoice number, payment amount, date of payment and the method of payment (cash orcredit card) and student number and name.Staff can also book seminar rooms. When staff makes a booking an invoice for the costof the seminar room is provided to the staff. The staff member is then required to makea payment for the invoice provided. All staff payment details are recorded. The paymentdetails include: payment number, invoice number, payment amount, date of paymentand the method of payment (cash or credit card) and staff number and name.Staff on a regular basis inspects seminar room. This is to ensure that the seminar roomsare well maintained. The information recorded for each inspection is the name of themember of staff who carried out the inspection, the date of inspection, an indication ofwhether the seminar room was found to be in a satisfactory condition (yes or no), andany additional comments. Three types of computer-generated reports are produced inthe library. The first report shows all overdue books, and DVDs. This report is sorted bystudent number and it is used to contact people with overdue items by e-mail. People arereminded to return the specified items and of the late-return charges that will beincurred.The second report shows the total number of seminar rooms that are booked. The thirdreport shows the details of the payments made for seminar rooms.The library has 12000 members, about half of whom borrow books at least once permonth. The rest are casual users who may come into the library every 2 months or so.The library stocks 200,000 books, and DVDs covering about 100,000 titles. About 200new books and DVDs titles are added to the collection every month. The name andaddress of book publishers, and DVD suppliers are also recorded in the system. Thedetails about the supplier are: supplier ID, supplier name, address, telephone number,and email address.3. RequirementsThe library database program developed by your team should have well designed screens thatare easy to use, understand and follow by staff and students. A novice user should be able to usethe university library database application (program) by reading instruction on the computerscreen of your program. For security reasons the views of students and staff should be different(Hint: use switchboard in MS Access to achieve this task).The university library database system designed and developed by your team should:Support UsabilityThat is it has well designed screens that are easy to use, understood by novice university staffand students. A novice user should be able to use the university library database application(program) by reading instruction on the computer screen of your program.SecurityFor security reasons the views of customer and staff should be different. (Hint: useswitchboard in MS Access to achieve this task).ReportsAll reports and input screens should be easy to read, use and understand. It should be logicallylaid out for novice users.4

The university library staff should be able to use the library database system to:1.Display a report listing the names and student numbers of all students.2.Display the details of the total rent paid for all seminar room bookings.3.Display all details of all students who have not paid their invoices.4.Display all details of all students who have paid their invoices.5.Display all details of all of seminar room inspections where the seminar room was foundto be in an unsatisfactory condition.6.Display all details of all students that have made reservation for books or DVDs(including the details of the books and DVDs reserved by each student).The students should be able to use the library database to:1.Display the details of all books.2.Display the details of all DVDs.3.Display name and telephone number of each staff and their office number.4.Make a book reservation.5.Make a DVD reservation.Additional tasks for students enrolled in Database Design G 6672. The following tasks arerequired only for students enrolled in Database Design G 6672:The university library staff should be able to use the library database system to:1.Display a report listing the names and student numbers of all students that have anoutstanding late fee.2.Display the details of the maximum rent paid for a seminar room bookings.The students should be able to use the library database to:1.Display the details of all books reserved.2.Display the details of all DVDs reserved.4. Assignment submissionFor the scenario in the problem above:a)Identify entity types and their attributes, including the primary keys and any foreignkeys for each entity (in 3NF).b)Compile the E-R diagram of the system (state all assumptions that you have made).c)Implement a database based on your designed database using Ms Access.Include with your assignment a cover page containing the first and last name of all students inyour team as well as their student id, your tutorial day and time, subject name and number aswell as the name of your tutor. You need to include with your program the details of allassumptions that you have made for the University library database. Submit all your designdocuments with your database programs on the due date specified via Moodle website of thissubject.Marking SchemeDOCUMENTATION includingEntity types and their attributes, including the primary keys and anyforeign keys for each entity (Normalization to 3NF)20 MarksE-R diagrams10 Marks============================================================Subtotal:30 MarkIMPLEMENTATION includingTables and data5MarksQueries, forms, reports and switchboards15 Marks============================================================5

Subtotal:20 MarksTOTAL:50 Marks (Documentation (30 Marks) + Implementation (20 Marks)Note:Be sure to maintain regular back-ups for any models or material prepared with the aid ofsoftware. Loss of files will not be accepted as an excuse for non-completion of this assignment.Every group member is advised to retain a full copy of the material handed in for this groupassignment. Each group should make arrangements for notification of all members of the marksand marker’s comments.6

• 1495075897-DAtabase 1.doc

SuperTutor

(15)

Status NEW Posted 18 May 2017 02:05 AM My Price 11.00

-----------

Attachments

file 1495075900-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer