SIT772-Assignment 1 Solved

 

Unit Information: SIT772 Database and Information Retrieval Trimester: 2021 T3 Assessment 1 Part A: Database and Information Retrieval Problem Solving Task 

 

This document supplies the detailed information on assessment tasks for this unit.
 

Learning Outcomes
This assessment assesses the following Unit Learning Outcomes (ULO) and related Graduate Learning Outcomes (GLO):

 

Unit Learning Outcome (ULO) 
Graduate Learning Outcome (GLO) 
ULO 5: Demonstrate data retrieval skills in the context of a data processing system.
 GLO   1:   Discipline-specific   knowledge and capabilities
ULO 2: Explain the concept of data modelling and use Entity-Relationship (ER) models to represent data.
GLO   1:   Discipline-specific   knowledge and capabilities
 

Purpose
This task evaluates the student's technical skills in the management of unstructured data, with potential usage in real applications. This assessment supports student understandings of the techniques related to unstructured data management and data processing
 

Question 1:                

Application Description: Suppose there is one bank company – Commonwealth that requires you to develop a relational database. The daily duty is to serve their customers managing their saving bank accounts, e.g., withdraw, deposit, etc. Additionally, the company Commonwealth also makes business for the home loan marketing, e.g., a customer may have a home loan account. To set up a home loan account for a customer, there are several information to be collected and recorded: (1) the customer needs to specify a property address; (2) a bank staff needs to be assigned to process the home loan application; (3) the property value should be assessed by using the average of the sold price of the properties located in the same suburb. Note: suppose there are sold properties in every suburbs; (4) Each customer has their own home loan max limit that is calculated by his/her 10 years annual salary plus 0.65*property value plus customer savings (amount in his accounts): Note: property value is based on avg sold price of that suburb. Note: In general, the bank company used 10 years’ salary to estimate, e.g., if the customer Kevin has annual income $60,000, then his home loan limit cannot exceed $600,000. For the customers having multiple home loans, their total home load amount cannot exceed the customers’ home loan limit; (5) a customer may have more than one home loan because multiple properties can be bought by one customer. Last but not least, your design should reflect the practical requirement as much as possible, i.e., meeting the maximum business rules. For example, some customers may have home loan accounts, but some ones may only have saving accounts. For a customer who need to set up a home loan account, it must create a saving account first. The average sold price for a suburb should be calculated on demand, not saved directly in the database. (6) A customer may have more than one back account. (7). A loan may be associated with more than one customer as joint loan (8) In case of join loan, the database records the loan/joint customer to compute borrowing capacity and total loan of the property.

 
Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints.

Question 2: 
 
Aladin Distributor Pt. (ADp) is an international supplier company located in Melbourne, Australia. The company purchase products from markets majorly from China and India, then sells these products to different stores/companies such as Coles, Woolworth as well as other small superstores such as Indian Mart, China-SuperStore, Indian Groceries etc.

 

Currently, the company has a paper-based system to manage its product acquisition and sales records. Obviously, the current system is prone to errors and slow to respond to inquiries from each regional department store. ADp is hiring your company to develop a database management system to improve the process. Your company asked you to design an ERD that reflect the business need of Asp.

 

ADp has contract with several local companies (suppliers) in India and China. To shorten your requirement analysis, relevant departments at ADp have provided you the following information:

 

Product Supplier: The product supplier information includes a unique supplier ID, supplier name (e.g., a company name), address, phone and email. While a supplier may have several phones and email, at present only two phone and two email are recorded on the company information. The required information about a supplier includes supplier ID, supplier name, supplier address, supplier phone and supplier email.

 

Product: A product ordered by Aladin Distributor Pt. from potential suppliers in China, India etc. The information about a product includes product identifier, product name, product description, and quantity-on-hand. The required information for a product includes product ID and name.

 

Purchase: A purchase order between Aladin Distributor Pt. and a particular supplier. The purchase order information includes the unique order number, order date, and arrival date. Certainly, it also includes relevant information about the supplier, product, and the purchasing manager who handles the purchase order. Note that some of this data is to be included via relationships between the related entities. Once a purchase order is made, the following information must be provided: order number, order date, and order quantity.

 

Purchase Item: A purchase item represents a particular product ordered in a given purchase order. The basic information about a line item includes an item number, quantity ordered, and unit cost. A purchase item must be related to a purchase order and a particular product. The required information for a purchase item includes item number and quantity ordered.

 

Customer Company: A customer company (Desi Mart, China-SuperStore, Indian etc) is a potential department store to which Aladin sells the products. The information about a customer includes company name (e.g., Desi Mart), address, and phone.

 

Sales List: This list includes all potential buyer companies (Desi Mart, China-SuperStore, Indian etc) for a particular product. The list contains information about the customer company, product, and the last transaction date on which the customer made a purchase of that product. If there is no transaction between the customer and product, then the date is left empty. The list is updated regularly.

 

Note that the above description has addressed only the "entities" involved in the data model. Additional information about the relationships between entities is summarized below:

➔ A purchase order is issued to a particular manager.

➔ A manager may be involved in one or several purchase orders.

➔ A purchase order may order one or several products.

➔ A product may or may not appear on a particular purchase order.

➔ A product could be purchased by several purchase orders.

➔ A purchase order is handled by only one manager. A manager may process one or several purchase orders.

➔ A product could be sold to one or several customers, and a customer might be interested in buying one or several products.

 

Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints. 


Question A-3: [HD Task only] 

 

The Task 3 is designed for HD tasks. The marking guide will base on your professional DB knowledge and professional presentation. For each task, we only give the perfect answer with the full 5 marks, reasonable in most criteria with 3 marks. If the solution didn’t show well engagement in the practical application, it could be given 0 or 1 mark depending on your exact work. 

 
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, 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, 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 a dormitory number, dormitory bed number (1,2,3), address, and the number of dormitories available in each apartment. Building number uniquely identifies each building. Each building has multiple dormitories/rooms. Each bed in Dormitory has a monthly rent rate. The place number uniquely identifies each room and bed number available in a building 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 ID 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.

 

Draw the Entity Relationship Diagram (ERD) of the database designed for the above application scenario. In the ERD, you need to specify the main components including Entities, Attributes, Relationships, Primary Keys, and Constraints.

Assessment feedback
General feedback to the class will be provided via CloudDeakin-Discussion Forum. The formal assessment feedback will be released with the marks in CloudDeakin altogether.

 

Extension requests
Requests for extensions should be made to Unit/Campus Chairs 3 days early before the assessment due date.

 

Special consideration
You may be eligible for special consideration if circumstances beyond your control prevent you from undertaking or completing an assessment task at the scheduled time.

 

See     the       following         link      for       advice  on        the       application      process: http://www.deakin.edu.au/students/studying/assessment-and-results/special-consideration 

 

Assessment feedback 

Detailed written feedback and results will be provided within two weeks of submission.