Design and Build Relational System

Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 2 : 11 th October, 2019 Page 1 of 5 TEQSA Provider Number: PRV12051 ASSESSMENT BRIEF 3 Scenario for Individual Project COURSE: Bachelor of Business/ Bachelor of IT Unit Code : DBFN212 Unit Title : Database Fundamentals Type of Assessment : Assessment 3 - Individual Project Length/D uration : N/A Unit Learning O utcome s addressed : a) Design and build relational databases b) F ormulate efficient queries using a query language and c) Be knowledgeable about issues relating to data access and retrieval, storage, ethics and privacy Submission Date : To be submitted in week 11 Assessment Task : The design, building, and querying of a relational database. Total Mark : 5 2 Marks Weighting : 3 0% Students are advised that submission of an Assessment T ask past the due date without a formally signed approved Assignment E xtension Form ( K ent Website MyKent Student Link > FO R M Assignment E x tension Application Form Student L o g in R e qu i r ed ) or previously approved application for other extenuating circumstances impacting course of study, incur s a 5 % penalty per calendar day , c alculated by deduction from the total mark . For example . A n Assessment T ask marked out of 40 will incur a 2 mark penalty for each calendar day . More information, please refer to ( Kent Website MyKent Student Link > POLICY Assessment Policy & Procedures Student Login Required ) Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 2 : 11 th October, 2019 Page 2 of 5 TEQSA Provider Number: PRV12051 A SSESSMENT D ESCRIPTION : This assessment is an individual Project. Lawn Mowing Pro provides gardening and yard maintenance services to individuals and organisations. While the company will provide one - time garden services, it specialises in rec urring service and maintenance. Many of its customers have multiple building, apartments, and rental properties that require gardening and lawn mai ntenance services. The owner type of these properties is either Individual or Corporation. Currently the manager uses a spreadsheet to keep records of everything in one table - like. This table mainly stores data about Property , , Service , and Employee . Sample data about properties and services collected by the Lawn Mowing Pro is shown in Figure 1 below. Figure 1 shows data the Lawn Moving Pro collects about properties and services. Rather than using a spreadsheet, the manager has asked you to design and develop a database for the comp any, using the sample data to get started with and the following basic business rules to be maintained. Each property is owned by one owner An owner can own many properties . Each property can have more than one services. Each service is provided to one property. An employee can do many services Each service is done by one employee. Tasks to be completed : a. Using the table and data in Figure 1 , state assumptions about depend encies among the columns of the table . Justify your assumptions on the basis of th e sample data and also on the basis of what you know about service business. Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 2 : 11 th October, 2019 Page 3 of 5 TEQSA Provider Number: PRV12051 b. Employing the dependencies stated in in ( a ), write a relational schema and draw a dependency diagram for the table in 1NF. The dependency diagram must have proper labels for all functional, partial and/or transitive dependencies , if there are any. c. Break up the dependency diagram you drew in ( b ) to p roduce dependency diagrams that are in 3 NF and also write the relational schema s 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.) d. Develop an E - R diagram based on the task done in ( c ) . Use crow - foot style and specify entities , attributes , relationships , and multiplicity . Justify the decisions you make regarding minimum and maximum cardinality. Describe how you would go about validating this model. e. Us ing the E - R diagram you developed in ( d ) , convert it to a relational design. Document your design as follows: Specify tables, primary keys, and foreign keys. Describe how you have represented weak entities, if there are any. Document relationship enforcement. f. Create a database using MySQL, with primary keys, foreign keys, and other attributes mentioned for each table developed in ( e ) using proper constraints. g. Create SQL statements to satisfy the following: Write SQL statements to insert at least 7 rows of data into each of the table created in task ( f ). You may use the sample data provided in Figure 1 for this task. Write SQL statements to l ist all columns of all tables. Write SQL statements to list the name and mobile phone for all employees . Write SQL statements to list the name and email address for all owners . Write SQL statements to list the property name, address, state, and post code f or all properties. Write SQL statements to list all owner names and their property owned. s Gard en Service to a Individual Write SQL statements to list total service charge amounts for each property . Note that a service charge is calculated by multiplying the service hour by the service charged per hour . A SSESSMENT S UBMISSION : The assignment must be submitted online in Moodle. You need to submit three things: a) The ERD, the normalisation and the dependency diagram in word document format, b) Database file (Using export op tion in My SQL workbench or Mysqldump command, c) Queries (execute your queries and take the screen shot). See also the ASSESSMENT 3 - Submission Guide for more details. Put all three files (Word, screenshot, SQL file) in a folder named (e .g. assm3_k123456), z ip it and submit it on Moodle. You will lose marks if you do not follow these submission instructions. M ARKING G UIDE (R UBRIC ): Marking Criteria Lecturer Expectation Marks Comments Justifying and identifying Assumptions about dependencies among the columns of the table are clearly made, justifications are based 2 Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 2 : 11 th October, 2019 Page 4 of 5 TEQSA Provider Number: PRV12051 dependencies of the table on the sample data in the table and also on the basis of knowing about service business Relational schema & Dependency diagram in 1NF Relational schema and dependency diagram are correct. Dependency diagram has proper labels for all dependencies. 4 Dependency diagrams in 3NF Dependency diagrams and relational schemas are correct and both are in 3 NF. Dependency diagrams contain no multi - valued attributes , naming conventions are met. 5 E - R diagram ER - D is created correctly using crow - foot style, correctly specified entities, attributes, relationships, and multiplicity. Minimum and maximum cardinality are specified and shown on the diagram 5 Relational design Tables are created with correct primary keys and foreign keys, represented weak entities are described, relationship enforcement is documented. 10 Creating a database using SQL SQL statement is correct. A database was created successfully with primary keys, foreign keys, and other attributes mentioned for all tables. 4 SQL statements to insert at least rows of data into each of the table All SQL statements are correct. All rows of data are inserted correctly into each of the tables. 3 SQL statements to list all columns of all tables All SQL statements are correctly create d. All columns of all tables are displayed correctly. 2 SQL statements to list the name and mobile phone for all employees All SQL statements are correct. List of name and mobile phone for all employees are displayed correctly. 2 SQL statements to list the name and email address for all owners All SQL statements are correct. Name and email address for all owners are displayed correctly. 2 SQL statements to list the property name, address, state, and post code for all properties All SQL statements are correct. Property name, address, stated, and post code for all properties are listed correctly. 2 SQL statements to list all owner names an d their property owned All SQL statements are correct. All owner names and their properties own are listed correctly. 2 SQL statements to determine the number All SQL statements are correct. All owner names and their properties own are listed correctly. 3 Kent Institute Australia Pty. Ltd. Assessment Brief ABN 49 003 577 302 CRICOS Code: 00161E RTO Code: 90458 Version 2 : 11 th October, 2019 Page 5 of 5 TEQSA Provider Number: PRV12051 s SQL statements to list name of employees who have provided property owned by All SQL statements are correct. Name of employees provided the service are listed correctly. 3 SQL statements to list total service charge amounts for each property All SQL statements are correct. Total service charged amounts for each property are correctly determined and listed. 3 Total 5 2