DBFN212 Database Fundamentals Assignment

Assignment Help Database Management System

Reference no: EM132988316

DBFN212 Database Fundamentals - Kent Institute

Assessment - Individual Project

Learning Outcome 1: Design and build relational databases
Learning Outcome 2: Formulate efficient queries using a query language and
Learning Outcome 3: 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.

ASSESSMENT DESCRIPTION:
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.

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:

a. 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.

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 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.)
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. 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.
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 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.

Attachment:- Database Fundamentals.rar

Reference no: EM132988316

Questions Cloud

Which is an example of a proper internal controls strategy : Which is an example of a proper internal controls strategy? Segregating the responsibilities of the Preschool Principal and Preschool Teacher

Rule on breach of contract claim : What is the court likely to rule on the breach of contract claim? And why?

What farm to table eatery is an example of a : The two co-owners also share in the management responsibilities of the restaurant. What Farm to Table Eatery is an example of a

When would remittances due for time warp costumes : Routers-R-Us is a Quarterly remitter. Their last pay period ended on April 13th and the employees were paid on April 20th. When would their remittances due?

DBFN212 Database Fundamentals Assignment : DBFN212 Database Fundamentals Assignment Help and Solution, Kent Institute - Assessment Writing Service - Formulate efficient queries using a query language

Product and service offerings : Provide an overall summary of the company and the product/service offerings. What are the short term and long-term goals?

When would the remittance to revenue quebec be due : Employees were paid on August 15th for the pay period ended August 10th. When would their remittance to Revenue Quebec be due?

When would the remittance be due : Verdigris Antiques is an annual remitter to Revenue Quebec. For the pay period ended and paid November 30, prior year, when would their remittance be due?

What is the consolidated entity retained earnings : During the year, Company B earns $35,000 in net income and pays no dividend. What is the consolidated entity's retained earnings as of the end of the year

User Account

All Pages