IS 361 Database Management Systems — Assignment 7
Fall 2015
2015
Purpose
The purpose of this exercise is to practice database design (0. Initial requirements, 1. Analysis, 2. Design). Review week 8 lectures on initial requirements and analysis if needed. Review ch4 on design details.
Assignment (20 points)
Perform the first three steps of database design procedure (0. Initial requirements, 1. Analysis, 2. Design) on a given database problem. Read the problem description carefully to understand the scope of the problem and what needs to be included in the database. Since you don’t have a real client to talk to verify some possible ambiguous statements, you may make reasonable assumptions about the design problem based on common sense when necessary and if it affects your design result. When in doubt, check with the instructor. If you make assumptions, statement clearly in your submission each assumption you make and why it is necessary to make that assumption for your design. While it’s possible that you may revise your design going from one step to the next, your results from each step should mostly match each other. A minimum penalty of -20% will be applied if there is much discrepancy between results from different steps. There are two database design problems listed in this assignment. Each student is assigned to one problem based on their park ID. Be sure to work on the right problem. If you worked on and/or submitted the wrong problem, you may receive at most 40% of the credit. If the last digit of your park ID is odd (1, 3, 5, 7, 9), you are assigned problem 1.
Problem 1. A small firm that performs lawn care needs to track customer jobs and employees. Currently, the owner keeps daily records on a pad of paper. Similar to the form shown here, it lists the jobs by time. Employees can do several tasks for each customer, including lawn mowing, edging, and tree trimming. Design a database system for this organization.
If the last digit of your park ID is even (0, 2, 4, 6, 8), you are assigned problem 2.
Problem 2: Design a database system for a friend of yours, who has a business that translates documents for other companies. She knows several languages and started out doing most of the translations herself. As her business grew, she found more people to work as part-time translators. Most translators specialize in translating from one language into their native tongue. A few can translate from several languages. Several companies send documents for translation on a regular basis. Others are one-time translations. With more business and several translators, your friend needs to track which people are working on which documents. The translators set the price for each translation, but pay a commission to your friend. The commission is generally 25 percent of the price, with a minimum of $5 per contract. The main data will be tracked through a Web site form that is filled in by the translators.
Requirements: include the following in a single document (doc, docx, rtf, or pdf) 0. Initial Requirements: initial problem statements Submit: (2 pts) A list of tasks by all the people who will use the system need to carry out. Mark clearly that who needs to do what. For example:
(library users) search for an item, either book or DVD (librarian) Manage user accounts: create and/or update account
1. Analysis: based on the requirements, what data we will need to store? What data is involved? What is the objective of the system? (any report/statistics) First data model: entity types and attributes Submit: (2 pts) Task and data analysis table as shown below:
Task # Task Description Data that could be recorded

(2 pts) A list of reports/statistics that this database system should support. Be realistic about what kinds of reports/statistics such an organization may need AND that you will be able to develop by writing queries later. You may have 4~6 items here. (6 pts) ER diagram: Include all attributes of each entity; Mark and state clearly the relationship constrains between entities (0:1, 1:1, 0:M, 1:M). Use statements like: one customer may place zero or multiple orders. 2. Design Submit: (6 pts) A list of relations as the result of ERD  relations. Mark clearly PK, FK, referential dependency, and functional dependency. (2 pts) Verify that your relations are in 3NF. At least statement that whether (yes or no) and why (explain) each relation of your design satisfies 3NF. For example,
Relation xxxx is in 3NF (answered whether: yes), because (here comes why) each attribute is single-valued/1NF, the primary key of this relation contains only one column thus automatically in 2NF, and each non-key column of this relation is dependent on only the primary key i.e. there is no transitive dependency in this relation so it’s in 3NF.

Notes
You may draw diagrams in any way that is convenient. Visio or Word are options, as is drawing on paper and scanning in the result. Either way, please submit your work as .doc (.docx) or .pdf so I can be sure to open your work.
Rubric

Task Points
(Optional) Assumptions and explanations –
0. Initial Requirements 2
1. Analysis
Data analysis
List of reports and statistics
ER diagram (with relationship statements)
2
2
6
2. Design
A list of relations
Verifications on each relation being in 3NF
6
2
Penalty
Wrong problem
Discrepancy between results from different steps
Minimum -60%
Minimum -20%

For a custom paper on the above topic, place your order now!

What We Offer:

• On-time delivery guarantee

• PhD-level writers

• Automatic plagiarism check

• 100% money-back guarantee

• 100% Privacy and Confidentiality

• High Quality custom-written paper