ASSESSMENT BRIEF 3 Individual Project COURSE: Bachelor of Business/ Bachelor of IT Unit Code: DBFN212 Unit Title: Database Fundamentals Type of Assessment: Assessment 3- Individual Project ...

ASSESSMENT BRIEF 3 Individual Project

ASSESSMENT DESCRIPTION:

This assessment is an individual Project. You will use the following scenario to work on and complete all of the required tasks.

SCENARIO: Sparkling Co

Sparkling Co provides home/office cleaning services. The company manages information about customers in an excel sheet that contains customer number, customer number and address. For every customer, they store information about service provided, that includes yearly clean, monthly clean, or weekly clean. The customers can be one-time customers or can be regular customers that requires services repeatedly. One time customer is charged instantly but for permanent customers, the billing is carried out once in a month. The charges for yearly clean, monthly clean and weekly clean are different. The number of hours that an employee spent on clean also varies from property to property.

The data that manger stores is as follows

Sample data about properties and services collected by the Lawn Mowing Pro is shown in Figure 1 below.

Figure 1 shows data about Sparkling Co customers, employees and services.

Rather than using a spreadsheet, the manager has asked you to design and develop a database for the company, using the sample data to get started with and the following basic business rules to be maintained. The rules are limited to given below. Use your knowledge of businesses in general to develop the system.

• A customer can receive many services.

• Each property can have more than one type of services.

• One type of service can be provided to many properties.  An employee can do zero or many services  Each service is done by one employee.

Tasks to be completed:

a. Create a dependency diagram using Figure 1. The dependency diagram must have proper labels for all functional, partial and/or transitive dependencies, if there are any.

b. Break up the dependency diagram you drew in (a) to produce dependency diagrams that are in 3 NF and also write the relational schemas for the table in 3NF. Make sure the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.

c. Develop an E-R diagram based on the task done in (b). Use crow-foot style and specify entities, attributes, relationships, and multiplicity. Justify the decisions you make regarding minimum and maximum cardinality.

d. Identify different type of entities and relationships, for instance, strong and weak entities/relationships. Specify how you have represented them in ER diagram created in c.

e. Create a database using MySQL, with primary keys, foreign keys, and other attributes mentioned for each table developed in (d) using proper constraints. In your report mention any specific constraints and implementation details.

f. Create SQL statements to satisfy the following:

1. Write SQL statements to insert at least 7 rows of data into each of the table created in task (e).

You may use the sample data provided in Figure 1 for this task.

2. Write SQL statements to list all columns of all tables.

3. Write SQL statements to list the name and mobile phone for all employees.

4. Write SQL statements to list the name and address for all owners.

5. Write SQL statements to list properties in NSW and services they received.

6. Write SQL statements to determine how many times a service was received at post code 2762.

7. Write SQL statements to list name of employees who have provided weekly service to any property.

8. Write SQL statement to list total service charge amounts for Brian Cooper. Use some mathematical functions.

ASSESSMENT SUBMISSION:

You need to submit three things: a) the report, b) The ERD, the normalisation and the dependency diagram in word document format, c) Database file (Using export option in My SQL workbench or Mysqldump command, d) Queries (execute your queries and take the screen shot).

Put all three files (Word, screenshot, SQL file) in a folder named ‘assm3_studentid’ (e.g. assm3_k123456), zip it and submit it on Moodle.

MARKING GUIDE:

Your work will be assessed as per the following marking criteria. Please read carefully each section/level and marks weightage.

GENERAL NOTES FOR ASSESSMENT TASKS

Content for Assessment Task papers should incorporate a formal introduction, main points and conclusion.

Appropriate academic writing and referencing are inevitable academic skills that you must develop and demonstrate in work being presented for assessment. The content of high quality work presented by a student must be fully referenced within-text citations and a Reference List at the end. Kent strongly recommends you refer to the Academic Learning Support Workshop materials available on the Kent Learning Management System (Moodle). For details please click the link http://moodle.kent.edu.au/kentmoodle/mod/folder/view.php?id=3606 and download the file titled “Harvard Referencing Workbook”. This Moodle Site is the location for Workbooks and information that are presented to Kent Students in the ALS Workshops conducted at the beginning of each Trimester.

Kent recommends a minimum of FIVE (5) references in work being presented for assessment. Unless otherwise specifically instructed by your Lecturer or as detailed in the Unit Outline for the specific Assessment Task, any paper with less than five (5) references may be deemed not meeting a satisfactory standard and possibly be failed.

Content in Assessment tasks that includes sources that are not properly referenced according to the “Harvard Referencing Workbook” will be penalised.

Marks will be deducted for failure to adhere to the word count if this is specifically stated for the Assessment Task in the Unit Outline. As a general rule there is an allowable discretionary variance to the word count in that it is generally accepted that a student may go over or under by 10% than the stated length.

GENERAL NOTES FOR REFERENCING

References are assessed for their quality. Students should draw on quality academic sources, such as books, chapters from edited books, journals etc. The textbook for the Unit of study can be used as a reference, but not the Lecturer Notes. The Assessor will want to see evidence that a student is capable of conducting their own research. Also, in order to help Assessors determine a student’s understanding of the work they cite, all in-text references (not just direct quotes) must include the specific page number(s) if shown in the original. Before preparing your Assessment Task or own contribution, please review this ‘YouTube’ video (Avoiding Plagiarism through Referencing) by clicking on the following link: link:

http://moodle.kent.edu.au/kentmoodle/mod/folder/view.php?id=3606

A search for peer-reviewed journal articles may also assist students. These type of journal articles can be located in the online journal databases and can be accessed from the Kent Library homepage. Wikipedia, online dictionaries and online encyclopaedias are acceptable as a starting point to gain knowledge about a topic, but should not be over-used – these should constitute no more than 10% of your total list of references/sources. Additional information and literature can be used where these are produced by legitimate sources, such as government departments, research institutes such as the National Health and Medical Research Council (NHMRC), or international organisations such as the World Health Organisation (WHO). Legitimate organisations and government departments produce peer reviewed reports and articles and are therefore very useful and mostly very current. The content of the following link explains why it is not acceptable to use non-peer reviewed websites (Why can't I just Google?): https://www.youtube.com/watch?v=N39mnu1Pkgw (thank you to La Trobe University for access to this video).