ASSESSMENT BRIEF 3
Scenario for Individual Project
|COURSE:Bachelor of Business/ Bachelor of IT|
|Unit Title:||Database Fundamentals|
|Type of Assessment:||Assessment 3- Individual Project|
Unit Learning Outcomes addressed:
- Design and build relational databases
- Formulate efficient queries using a query language and
- Be knowledgeable about issues relating to data access and retrieval, storage, ethics and privacy
|Assessment Task:||The design, building, and querying of a relational database.|
|Total Mark:||52 Marks|
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 recurring service and maintenance. Many of its customers have multiple building, apartments, and rental properties that require gardening and lawn maintenance 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, Property’s Owner, 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 company, 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:
- Using the table and data in Figure 1, state assumptions about dependencies among the columns of the table. Justify your assumptions on the basis of the sample data and also on the basis of what you know about service business.
- 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.
- Break up the dependency diagram you drew in (b) 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.)
- 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.
- Using 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.
- Create a database using MySQL, with primary keys, foreign keys, and other attributes mentioned for each table developed in (e) using proper constraints.
- 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 list 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 for all properties.
- Write SQL statements to list all owner names and their property owned.
- Write SQL statements to determine how many times of ‘Lawn Mow’ have been done at ‘Earls Courts’?
- Write SQL statements to list name of employees who have provided ‘Garden Service to a
property owned by ‘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.