Our friend Wally Los Gatos (see Problem and Exercise 28), realizing that his wallcovering business had a few wrinkles in it, decided to pursue a law degree at night. After graduating, he has teamed up with Lyla El Pàjaro to form Peck and Paw, Attorneys at Law. Wally and Lyla have hired you to design a database system based upon the following set of business rules. It is in your best interest to perform a thorough analysis, to avoid needless litigation. Create an ERD based upon the following set of rules:
• An ATTORNEY is retained by one or more CLIENTS for each CASE.
• Attributes of ATTORNEY are Attorney ID, Name, Address, City, State, Zip Code, Specialty (may be more than one), and Bar (may be more than one).
• A CLIENT may have more than one ATTORNEY for each CASE.
• Attributes of CLIENT are Client ID, Name, Address, City, State, Zip Code, Telephone, and Date of Birth.
• A CLIENT may have more than one CASE.
• Attributes of CASE are Case ID, Case Description, and Case Type.
• An ATTORNEY may have more than one CASE.
• Each CASE is assigned to one and only one COURT.
• Attributes of COURT are Court ID, Court Name, City, State, and Zip Code.
• Each COURT has one or more JUDGES assigned to it.
• Attributes of JUDGE are Judge ID, Name, and Years In Practice.
• Each JUDGE is assigned to exactly one court.
State any assumptions that you have made. Also, draw a data model for this situation using the tool you have been told to use in your course.
Reference Problem and Exercise 28
Wally Los Gatos, owner of Wally’s Wonderful World of Wallcoverings, has hired you as a consultant to design a database management system for his chain of three stores that sells wallpaper and accessories. He would like to track sales, customers, and employees. After an initial meeting with Wally, you have developed a list of business rules and specifications to begin the design of an E-R model:
• Customers place orders through a branch.
• Wally would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language.
• A customer may place many orders.
• A customer does not always have to order through the same branch all the time.
• Customers may have one or more accounts, although they may also have no accounts.
• The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount, and Type.
• A branch may have many customers.
• The following information about each branch needs to be recorded: Branch Number, Location (Address, City, State, Zip Code), and Square Footage.
• A branch may sell all items or may only sell certain items.
• Orders are composed of one or more items.
• The following information about each order needs to be recorded: Order Date and Credit Authorization Status.
• Items may be sold by one or more branches.
• We wish to record the following about each item: Description, Color, Size, Pattern, and Type.
• An item can be composed of multiple items; for example, a dining room wallcovering set (item 20) may consist of wallpaper (item 22) and borders (item 23).
• Wally employs 56 employees.
• He would like to track the following information about employees: Name, Address (Street, City, State, Zip Code), Telephone, Date of Hire, Title, Salary, Skill, and Age.
• Each employee works in one and only one branch.
• Each employee may have one or more dependents. We wish to record the name of the dependent as well as the age and relationship.
• Employees can have one or more skills.
Based upon this information, draw an E-R model. Indicate any assumptions that you have made. Also, draw a data model for this situation using the tool you have been told to use in your course.
13 have requested this problem solution
The more requests, the faster the answer.