SIT772 - Database and Information Retrieval - IT/Computer Science Assignment Help

Assignment Task

Part A:  
In the first part of this assignment, you are required to design and implement database from given  user story by applying the concept that you have learnt in this unit. Use LucidChart to sketch  detail ERD, normalize the relations and create physical database using SQL queries, followed by  insertion of small data (at least 6 records in each table). You are required to provide complete  documentation such as Requirements to ERD (entity selection), ERD, Step by step Normalization to 3rd NF, database.sql and mydata.sql. 
Deakin Student Accommodation Office currently using a Database to maintain their record, however,  in the last meeting, the Dean of Student Accommodation decided to replace the presently running  outdated database with a new database. The Dean of Accommodation wants to have a new database  designed and developed to assist with the administration of the multi-campus office. The initial  requirement collection and analysis phase of the database design process has provided the following  data requirements specification for the University Accommodation. The office database, followed by  examples of query transactions that should be supported by the database. 
Data Requirements: 
Students: Only full-time students can avail the university housing. The data stored for each student  include student ID (badge no), student name (first and last name), home address (street, city, postcode),  Nationality, age, email, mobile phone number, date of birth, gender, Study level of student  (undergraduate, postgraduate), special needs, any additional comments, current status (placed/waiting),  school (i.e. SIT). 
The student information stored relates to those currently renting a room as well as those on the waiting  list. Students may rent a room in a hall of residence or student apartment. When a student joins the  university housing, he/she is assigned a staff member who acts as his/her Adviser. The Adviser is  responsible for monitoring the student's welfare and academic progression throughout his or her time  at the university. The data held on a student's Adviser includes full name, staff id (badge no), position,  name of the department, internal telephone number, email, and office number. In addition to this, a new  student is also assigned a mentor as a guide. The student-mentor is also a student. 
Victoria Hall: Each hall of residence has a name, address, telephone number, and a hall manager,  who supervises the operation of the hall. The halls provide only single luxury rooms, which have a  room number, place number and monthly rent rate. The place number uniquely identifies each room in  all halls controlled by the Residence Office and is used when renting a room to a student. 
Deakin Unit: The University residence also offers single room for student. These are fully furnished  and provide single-room accommodation for groups of three, four, or five students. The information  held on student apartments includes an apartment number, address, and the number of single bedrooms  available in each unit. The unit number uniquely identifies each unit. Each bedroom in unit has a  monthly rent rate, room number, and a place number. The place number uniquely identifies each room  available in all student Units and is used when renting a room to a student.  
Dormitory: The Residence Office also offers dormitory. These are fully furnished and provide  dormitory accommodation for groups of max three undergraduate students. The information held on  student dormitory includes an dormitory number, dormitory bed number (1,2,3), address, and the  number of dormitories available in each apartment. The flat number uniquely identifies each apartment.  Each bed in Dormitory has a monthly rent rate, Dormitory and bed number, and a place number. The  place number uniquely identifies each room and bed number available in all student apartments and is  used when renting a room to a student. 
Leases: A student may rent a room in a hall or student apartment or dormitory for various periods.  New lease agreements are negotiated at the start of each academic year, with a minimum rental period 
of one semester and a maximum rental period of one year, which includes semesters 1 and 2 and the  summer semester. Each individual lease agreement between a student and the Residence Office is  uniquely identified using a lease number. The data stored on each lease includes the lease number,  duration of the lease (given as semesters), student's name and Bdg number, place number, room number,  address details of the hall or student apartment, and the date the student wishes to enter the room, and  the date the student wishes to leave the room (if known). 
Payment and Inspection: At the start of each semester, each student is sent a payment invoice for the  rental period. Each payment invoice has a unique number. The data stored on each invoice consist of  invoice number, lease number, term (T1,T2, T3), due payment, student's full name and badge number (studentID), place number, room number, and the address of the hall or apartment. Additional data is  also held regarding the payment of the invoice and includes the date the invoice was paid, the method  of payment (check, cash, Visa, and so on), the date the first and second reminder was sent (if necessary).  Staff inspect the accommodation on a regular basis to ensure that the accommodation is well maintained  inspect student apartment inspections Student apartments. The information recorded for each inspection  is the name of the member of staff who carried out the inspection, the date of inspection, an indication  of whether the property was found to be in a satisfactory condition (yes or no), and any additional  comments.  
Accommodation Staff: Some information is also held on members of staff of the Accommodation Office and includes the staff number, name (first and last name), email, home address (street, city,  postcode), date of birth, gender, position (for example, Hostel Manager, Administrative Assistant,  Cleaner) and location (for example, Residence Office or Hall). 
Program: The Residence Office also stores a limited amount of information on the Program offered  by the university, including the course number, Program title (including year), Program director,  campus telephone number, director email, room number, and department name. Each student is also  associated with a single program of studies.  
Guardian: Whenever possible, information on a student's guardian is stored, which includes the name,  relationship, address (street, city, postcode), and contact telephone number.  
Student and staff can login into the system and system can keep record of login history. 
Question A-1: Define the entities, attributes and draw ERD. (3)  
Question A-2: List the Functional and Transitive dependencies in each relation (3) Question A-3 Normalize each relation to 3NF. (3)  
Question A-4: Create the database using SQL queries and submit database.sql. (3) Question A-5: Insert the data (6 records in each table) into “database” and submit mydata.sql. (3) *You can add dummy data.  
Question A-6: Complete the following queries and provide query and its output. (8) Query Transactions Listed here are some examples of query transactions that should be supported by  the University Accommodation Office database system 
a) Present a report listing the Manager's name and telephone number for each hall of residence  b) Present a report listing the names and student id with the details of their lease agreements.  c) Display the details of lease agreements that include the summer semester 
d) Display the details of the total rent paid by a given student.  
e) Present a report on students who have not paid their invoices by a given date.  f) Display the details of apartment inspections where the property was found to be in an  unsatisfactory condition  
g) Present a report of the names and ID of students with their room number and place number in  a particular hall of residence  
h) Present a report listing the details of all students currently on the waiting list for 
accommodation; that is; who were not placed.  
i) Display the total number of students in each Study level 
j) Present a report of the names and ID numbers for all students who have not supplied details of  their Guardian.  
k) Display the name and internal telephone number of the Adviser for a particular student.  l) Display the minimum, maximum, and average monthly rent for rooms in residence halls.  m) Display the total number of places in each residence hall.  
n) Display the staff number, name, age, and current location of all members of the residence staff  who are over 60 years old today. 
o) List each student and his mentor who lives in either Victoria Hall or DeakinUnit. 
Part B:  
Question B-1: 
Try and find a Query of the form [Query-term-1 Query-term-2] (without quotes) that, when run on  Google, produces at least one result that either does not contain query-term-1 or does not contain query term-2. That is, try to find an example where Google does not interpret a two-term query as a  conjunction. (If you have difficulty with finding an appropriate query, try one that produces very few  hits, say, fewer than 20.)  
(i) Take screenshot of the first page of Google results (or more if you want to) and mark each  result with 2 (both terms occur on the page), 1 (one term occurs on the page) or 0 (neither  term occurs on the page)  
(ii) Based on this evidence, does Google interpret all queries as a Boolean conjunction? 
Question B-2:   
Recall and Precision are two important evaluation metrics that we use to analyze a set of unranked  results. Precision and Recall metrics consider the differences between set of documents retrieved for  given query and the set of documents that are relevant to the user’s need.  
Academic integrity, plagiarism and collusion 
Plagiarism and collusion constitute extremely serious breaches of academic integrity. They are  forms of cheating, and severe penalties are associated with them, including cancellation of  marks for a specific assignment, for a specific unit or even exclusion from the course. If you  are ever in doubt about how to properly use and cite a source of information refer to the  referencing site above. 
Plagiarism occurs when a student passes off as the student’s own work, or copies without  acknowledgement as to its authorship, the work of any other person or resubmits their own  work from a previous assessment task.  

This SIT772 IT/Computer Science Assignment has been solved by our IT/Computer Science Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.