1TS INSTRUCTIONS :
Use Lucidchart.com (preferred) or Visio to create an ERD to model the situation described below.
• Create entities, attributes and relationships that are appropriate to represent the business rules, using Crow’s
Foot notation to indicate the cardinalities for each of the relationships. Indicate which attributes are PK/FK as
appropriate. (SEE MUCH MORE DETAILED GUIDANCE IN THE GRADING RUBRIC.)
• Be sure each relationship includes a verb/action phrase per the Crow’s Foot notation standard.
• All words on the diagram should be capitalized.
• Each entity should have Key field(s) indicated in the left column, and the names of all attributes in the right.
• See the example ERD attached to this document that is mostly based on Chapter 2, pg 60, Figure 2.8 from the
12th Edition textbook “External Models for Tiny College”.
• Copy the finished Lucidchart (preferred) or Visio diagram to a single Microsoft Word document. It must fit on a
single page in Word and be clearly legible at 100 percent zoom. Upload it to Canvas.
• Your team must also submit your original Lucidchart (.PNG) or Visio (VSD or .VSDX) file.
• To assist in receiving the maximum possible grade, it is recommended that the team include at least
one paragraph of explanation for the design choices made in creating this ERD. (E.g. “Given the word
must for the — entity, we decided to…”) If part of the assignment was unclear, even after consulting the
assignment’s online discussion board, describe the problem in this explanation section.
• Carefully review the grading rubric so that your submission will meet the requirements.
TASK: Create an ERD for this attorney’s office, also known as “a firm”. Here is a list of the business rules:
• An attorney must provide billable meetings to many clients and a client will attend billable meetings with one or more
attorneys. (Sometimes an attorney with broad skills passes a client along to a specialist attorney for subsequent
meetings.)
• An attorney is known in the system by their ID number, first name, last name, partner status, number of years with the
firm, number of years of experience practicing law, and the year in which they passed the bar exam.
• Each billable meeting is between one client and one attorney.
• Some unplanned billable meetings involve walk-in clients. However, most billable meetings are planned.
• In addition to the attendees, a billable meeting also has a unique meeting number and whether it was a planned or
unplanned meeting.
• Each time a client meets with an attorney, the client’s records are verified or updated with their billing status, current
street number, street name, city, state, zip code, email address and phone number.
• Each attorney may issue invoices and a client may receive multiple invoices.
• Some attorneys provide aid to clients with the pro bono billing status (pro bono – Latin “for the public good” – no
payment required.) A pro bono invoice has a zero balance due ($0.00). All clients receive invoices for billing and/or
tax purposes.
• An invoice may be redeemed by one or more payments and a payment will be used to redeem one or more invoices.
• Each client may provide one or more payments and each payment is provided by only a single client. Some pro bono
clients provide payments, but this is not required.
2
• A payment is recorded using a payment identification number, the client’s identification number, the amount of the
payment (cannot be zero), and the date of the payment.
• This law office relies on a central billing office to handle invoicing. Information from the attorney and from the central
billing office appears on each invoice. This information includes a unique invoice number that cannot be left empty,
the issuing attorney, the client, the date of service, the number of hours billed, the billing rate per hour (rate is zero for
pro bono meetings), tax rate per hour, the tax amount, and the balance due.
• Each invoice should also include the ID number of the customer service representative from the central billing office
who is the client’s point of contact regarding invoice and payment issues. Each customer service representative is
known by their first name, last name, office phone number, and email address, in addition to their ID number. If no
customer service representative was assigned to the client prior to the invoice issuance date, their ID number can be
added to the invoice later.
2ND INSTRUCTIONS: AFTER THE PREVIOUS INSTRUCTIONS ARE COMPLETED:
1.Turn in a complete Entity Relationship Diagram (ERD) that illustrates the actual structure of the database that is also submitted. The ERD submitted must perfectly match the actual database submitted. This diagram will allow for testing and grading of the Project Sample Database that is submitted. Paste the Lucidchart (preferred) or Visio ER Diagram into a Microsoft Word document. Make sure the text in the ERD is legible at 100 percent zoom.
2.Create a sample database in Microsoft Access with the tables and relationships required to support your team’s design.
3.Each table of the Access database must contain at least all of the attributes that were submitted as part of the original Entity Relationship Diagram project deliverable.
4.Populate the tables in your database with at least 30 rows of TEST data (no actual student, employer, or other security-sensitive data). Make sure that the data is of the proper data type and is appropriate to the subject matter – a law firm. While the data is “dummy data,” make sure it is appropriate for the associated SQL Queries assignment. For example, a field like FIRST_NAME should have entries with fictional people’s first names, not colors, shapes, or something else. In the SQL assignment, the team will create SQL queries to retrieve and/or modify specific sets of records in the database.
-A great resource is GenerateData.com LINK (Links to an external site.), a website that creates 100 records as directed for a wide variety of needs. (Thanks to Chris Gilham, a former IS301 online student.)
5.Note that records must be entered in the table on the 1 side of 1-to-Many relationships. When entering records in the table on the Many side of a relationship, Access normally provides a drop-down list in that column, allowing you to choose just one of the entries that was made on the 1 side of the relationship. (E.g. You must first create attorney records in the ATTORNEY table and then as you create meeting records, you will be able to choose the ATTORNEY_ID in that column of the MEETING table. If MEETING is existence-dependent on ATTORNEY, you will not be allowed to enter a new ATTORNEY_ID in the MEETING table.)
6.Err on the side of “too much” documentation rather than too little. Describe the ERD and database as if they are being turned over to the customer. At least one paragraph is appreciated, but keep it to less than two pages. The documentation allows the team to explain design choices that may be subject to differing interpretations. However, if SQL queries don’t work with he final product, excuses in the documentation won’t help with the grade.
7.It is recommended you use Access menu “Database Tools > Relationships” and compare that visual to the ERD to verify a close match.
8.Upload the Microsoft Access database file AND the Word document through the link in Canvas. The database file submitted must be the one that is used in the SQL Queries assignment. Part of the grade will be based on the grader getting the same SQL query result sets turned in by the team using the Access database turned in by the team.