SIT103 – Data and Information Management T2, 2020
Summative Assessment Task 1 – Database design activity Total Marks =250, Weight= 40%
Due Date:
Monday 24th August 8:00PM (AEST)
Introduction
This assessment is for students to develop an understanding of various data storage and retrieval methods and to develop the capacity to design a database. The assessment requires students to conduct a survey on data storage and retrieval techniques. The assessment also requires students to understand different steps of database design, create a conceptual design of a database (prepare an Entity- Relationship diagram).
This is an individual assessment task that requires students to create a written report using their own words and conceptual design using standard software and explanations of the design.
Unit Learning Outcomes
Of the three Unit Learning Outcomes (ULOs) of this unit, this assessment task focuses on:
ULO1: Students will describe the techniques used in storing and retrieving data.
ULO2: Students will evaluate data models and apply data modelling techniques to
capture the data aspects of a practical information system.
The assessment of this report will indicate whether students can partially attain the above ULOs.
Instructions (READ CAREFULLY)
Submission requirements
• Must be submitted via the SIT103 unit site (CloudDeakin). Label your answers as Task 1A, Task 1B, Task 2A and so on, as this assists a marker to find your answers.
• For report and design document: Only Word (.docx) documents are accepted and any other documents such as PDF files will not be marked. You must copy the ER
diagrams in the design document (MS Word file)
• Design diagram file: Submit the original file with format of the design software.
First part of Assignment-1 (Task 1 to 3)
• A written report of 1000 words, excluding the references section. Task 1B should
be limited to 500 words. For the rest of first part of assignment-1: –500 word limit
is preferable.
• You must place your name and student ID on the first page of your report
• The report filename should be specified as: Assignment_1_1 – John Smith.docx
Second part of Assignment-1 (Task 4)
• Expected: A conceptual design. This part has no word limit, but should include all
components of the conceptual design.
• You must place your name and student ID on the first page of the database design
document. The document filename should be specified as: Assignment_1_2 –
John Smith.docx
Referencing requirements
• Use the IEEE referencing style to correctly cite and create a section containing references. For further information on IEEE referencing style, see Deakin resource site at : https://www.deakin.edu.au/students/studying/study-support/referencing
Extension
Students are encouraged NOT to apply for an extension unless there has been some long- term negative impact on their studies during the trimester. Having multiple assignments due on the same day/week will not be accepted as grounds for an extension. Only a request for extension received at least 2 days before the due date accompanied with appropriate documentary evidences will be considered unless you can demonstrate that it was impractical to apply otherwise. The Unit Chair may require evidence of the work completed so far and/or appropriate supporting documentation for the extension request. Applying for extension does not necessarily mean it will be granted, so you need to assume it won’t until you hear from the unit chair or Dr. Xuequan Lu.
o Policy to apply for extension
Burwood and Cloud students should contact Dr. Shamsul Huda. Geelong students should contact Dr. Xuequan Lu.
Subject of your email must be Extension for Assessment Task 1 –
SIT103.
In your email include your full details (first name, last name, student
ID, campus enrolled) as well as statement and any evidence.
Academic Integrity
Your attention is drawn to the Academic Integrity which is available on the unit site. Anyone using cut-and-paste or copying of other people’s work will be easily identified by Turnitin and the markers. The outcome of such actions will be a disciplinary committee hearing which can have very serious outcomes. Please read the relevant information on the unit site under Resources > Assessment.
Contract cheating? DON’T DO IT, see here https://blogs.deakin.edu.au/deakinlife/2018/09/18/dont-ruin-your-career-dont-contract-cheat/.
Task 1
Find, read, cite and reference several sources for different types of database and commercial database management system (DBMS) products, then complete the following tasks in your own words:
A. Briefly describe different types of databases [5 marks]
B. Prepare a report on the following five commercial database management systems.
1. MS Access, 2. MS SQL Server, 3. IBM DB2, 4. MySQL, 5. Oracle DBMS
Your report should explain: (i) required system components (e.g., hardware, compatible operating system/platform, network technologies for each product); (ii) the types of database that it supports based on data storage location (centralized, distributed, cloud) ; (ii) the types of database that it supports based on data types (e.g., structured, unstructured, semi structured) and the number of users; and (iii) the significant characteristics/features (e.g., price, data security capabilities, availability and recovery). The report should be limited to 500 words. [25 marks]
Task 2
Find, read, cite and reference several sources and then complete the following tasks in your own words:
A. List and explain the stages of Database Development Lifecycle. [10 marks]
B. Describe the three stages of database design (i.e. conceptual, logical and physical). Your description should include the differences of the above stages when designing
the database. [15 marks]
Task 3
Find, read, cite and reference several sources for each of the following three SQL statements. In your own words:
A. Briefly describe an SQL DDL statement for creating a new table. Include integrity rules in your DDL statement and explain how these are implemented in the DDL statement.
[15 marks]
B. Briefly describe an SQL DML statement for retrieving existing data from a table. Consider
different options such filtering, grouping and sorting in the SQL DML statement.
[15 marks]
C. BrieflydescribeanSQLDMLstatementforstoringnewdataintoatable.Writeanddescribe an SQL DML statement that copies data from a selected table and inserts into the table that you created. [15 marks]
Task 4
Conceptual design of a database for a real world application and corresponding entity relationship diagram (ERD):
Recently you have joined a software development team of a real estate company “Burwood real estate” as a database designer. Your first assignment in this role is to design a database for a specific business process of the company-related to “the home maintenance services”.
You and your team have completed an initial study through a comprehensive reading of the company documents/reports, physical observations of the business process and interviews with the staff members. Then you have prepared a report based on your initial study. Your initial study report includes company situation, organizational structure, the operating environment (operational descriptions of the required business process- “maintenance services”), description of the current problem of the business process and the objective of the new software system (application) for the maintenance services. You also prepared a description of data analysis and requirements. Your report and descriptions are presented in the following:
1) Initial Study (company situation and background):
a) Analysis of the company situation and brief organizational structure of Burwood real
estate:
“Burwood real estate” is a renowned real estate company in the eastern suburb of Melbourne which has a highly competitive property market. Recently company achieves a huge growth with a large number of properties to manage. The company has different types of real estate services including property sales, property management, auctions and property development. Burwood real estate also provides home maintenance services for the properties it manages. A senior executive manager leads a service of the company. A number of property managers work under a senior executive manager. However, maintenance service (business process) of the company is not automated; instead, a manual business process is used which is very time-consuming.
b) The operational descriptions of the business process- “maintenance services”
A property owner has at least one property for leasing with Burwood real estate. The company may have more than one property to manage from a single property owner. Each property is managed by a property manager. A property manager may manage more than one property. Some managers may have other official duties such as conducting auctions and sales. Therefore, they are not assigned to manage a property.
Burwood real estate has a large number of contractors for maintenance services. A property manager has a primary/preferable contractor. A contractor can be preferable to more than one property manager because of his reputation. Company wants to keep the record of preference relationships. However, a contractor cannot have more than five preference relationships with the managers.
i) Maintenance Job creation and Quotation invitation sub-process:
A property generally requires more than one maintenance job per year. In the current system, a job is described by the related property and a short note which describes the tasks and requirements of the job. For every job, quotations are invited from the contractors. Quotation invitation date for a job is also recorded. Every job is assigned to a manger. A manager can be assigned to more than one job.
ii) Sub-process for approval of a quotation:
The quotations for a particular job are reviewed and approved by a property manager. A quotation has a list of required products and number of hours to complete a job. The products in the quotations are building and home maintenance materials/supplies. Manager verifies the brand of each product, per unit price of each product, quantity for each product (in terms of units) and completion hours for the job as listed in the quotation. After verification, the manger approves only one quotation per job and marks its status as accepted and other quotations for the job are marked as rejected. Manger also writes a comment of the reason for approval/rejection on the quotation.
c) Descriptions of the Problems:
Managing all maintenance contractors and properties and ensuring the quality of maintenance works are getting very difficult day-by-day for Burrowed real estate. A company staff needs to spend significant amount of time to generate reports for scheduling of the maintenance jobs. It does not have any quick method to reply queries from its property owners and contractors, e.g. whether a quotation has been approved or not, or which contractor has won the quotation. There is too much data duplication and inconsistency due to human error in the existing manual system, which is wasteful and time consuming for staff members. The maintenance services leader takes long time to find how many maintenance jobs need to be completed at a particular time. When the company CEO wants to review the approval process it takes hours to find and review a quotation submitted by a contractor. The tax-time is the worst season for the company when it wants to prepare the financial statements for its property owners such as yearly maintenance expenses. Therefore, the staff-cost burden is increasing. The company wants to reduce staff cost, provide faster maintenance services for its clients and generate more revenue.
d) Objectiveoftheproposedsoftwaresystemformaintenanceservices:
The main objective of the project is to develop a database and an application program that will automate its home maintenance business process. Several sub-objectives are identified by you and your team members for the database design and implementation phase of the project which are listed in the following.
(1) To facilitate a faster report generation on a regular basis for the maintenance business process,
(2) to provide appropriate responses to the queries from its stakeholders as quickly as possible,
(3) to avoid redundancy and inconsistency in the data, and
(4) to facilitate an efficient administrative review of the business process.
At present, the scope of the project is only limited to the home maintenance services. It is informed to the team that some other processes are not fully automated. Therefore, the new software system for home maintenance services will work independently and its interactions with other processes remains unchanged at this stage as mentioned in the operational description.
2) Data analysis and other operational requirements:
Data of all jobs and quotations should be captured by the system. Every quotation is uniquely identified such that its related job and contractor can be recognised. Same product may appear in different quotations with the same or a varying quantity as the submitted contractor thinks. Company wants to preserve the information of submitted quotations regardless of its approval status including date submitted, date approved/rejected, product details and quantity of each product and other information as mentioned in the above description.
For contractors: their names, addresses, qualifications, mobile phone numbers and e-mail addresses are captured from the initial appointment forms.
Property owner’s name, residential address, phone number, e-mail address are captured from the leasing agreement forms.
For the leasing property: corresponding property owner, related property manager, address of the property, type of the property (house, unit, flat, commercial), and age of the property are recorded. Burwood real estate needs to keep a record of its property managers, which should preserve the name of staff, joining date, residential address, qualification and phone number. These data are captured from the employees’ contracts.
Data sources
Data for maintenance process
Sources of Data
Users
Interface/ external processes at the boundaries
Maintenance job
Maintenance forms
CEO, staff members, home owner
Manual process (e.g. e- mail, phone order ) in Burwood real estate)
Quotations and products
Quotations documents
CEO, staff members, home owner, contractors
Manual process (e.g. e- mail, phone order) in Burwood real estate
Employee
Employee contracts forms
CEO, staff members,
Human resources, Manual process
Home owner and property
Leasing agreements forms
CEO, staff members, Home owner
Property management , Manual process
Contractors
Appointment letter/ forms
CEO, staff members, Home owner
Human resources , Manual process
The tasks to be completed for the second part of this assignment
(Task 4):
Complete the rest part of conceptual design for the required database of “Maintenance services” as specified in the following tasks based on the above report and requirements:
A. Identify the business rules from the above descriptions. For identification of business rules:
You need to focus more on (the operational descriptions of the business process- “maintenance services” (sections 1.a and 1.b) and data analysis and other operational requirements (section 2) as described in the aforementioned initial study report. [30 marks]
B. Prepare an entity relationship diagram (ERD) for the conceptual design of the required database of Burwood real estate based on the description above. [110 marks]
C. Provide a description for all entities and attributes and relationships used in your ERD and
why you have considered those. This should relate to your business rules. [10 marks ]
Your ERD should include all of the following:
1) All required entities and attributes.
2) All relationships among the entities and their strength including strong and weak
relationships.
3) All entity and referential integrity rules for each entity.
4) All connectivities for each relationship.
5) All maximum and minimum cardinalities for each relationship.
6) Use professional database design and modeling software (e.g., MS Office Visio,
LucidChart) to draw the ER diagram. Crow’s Foot notation is preferable.