Extra Practice Data Modeling Problems UML REA Problems and Solutions

docx

School

Florida State University *

*We aren’t endorsed by this school

Course

4401

Subject

Information Systems

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by gialinh2097

Document contents: 4 Modeling Problems (PhoneSource, J&B Enterprises, FarmFresh Produce, University Slum-Lord) and Solutions PhoneSource Prepare a UML class diagram (with classes, associations, attributes, and multiplicities) and a relational database (tables) for the following: PhoneSource is a company that sells several kinds of telephones to customers. Product offerings include traditional corded phones, cordless phones, cellular phones, etc. Each type of phone comes in various models; each model has a different stock number. Although cellular phones must be programmed with unique information for each customer, PhoneSource does not offer cellular service or the corresponding programming. Therefore, PhoneSource does not need to keep track of each individual phone; it is sufficient to track them by model number. PhoneSource has several salespeople who are paid on commission. Although each customer is assigned to one particular salesperson, if a customer's assigned salesperson is not available when the customer comes in, another salesperson may assist that customer. The resulting commission is split between the two salespeople. A customer’s assigned salesperson may be unavailable because of a sick day or vacation day or because he or she is helping another customer to whom the same salesperson is assigned. No sale is allowed without the customer paying at least 25% down at the time of the sale; the cash receipts are processed by the salesperson that is assisting the customer. Most customers pay the 25%, and then make 3 more equal monthly installments. Some customers pay in full at the time of the sale. A customer’s payment may include payments for multiple sales. PhoneSource has only one cash account into which all cash receipts are deposited (including those from sources other than sales, such as bank loans, which are not processed by any salesperson). Salespeople, customers, and inventory items are often input to the system before any transactions involving them occur; however, a customer cannot be entered without assigning a salesperson to him or her. Below, PhoneSource has identified attributes that need to be included in its information system (don't add or subtract any attributes). Beside each attribute is an abbreviation (in bold), which you may use in your conceptual model and in your relational database tables: -phone-stock-number (phone#) -salesperson-name (sp-name) -salesperson-address (sp-add) -customer-ship-to-address (c-ship) -phone-description (p-desc) -sale-date (s-date) -salesperson-SSN (sp#) - salesperson-telephone (sp-ph#) -customer-number (cus#) -customer-name (c-name) -sale-invoice-number (sale#) -customer-billing-address (c-bill) -total-quantity-on-hand of each phone type (qoh) -phone-item-actual-selling-price (act-price) -phone-item-standard-cost (std-cost) -phone-item-list-price (list-price) -total-dollar-amount-of-sale (s-amt) -remittance-advice-number (ra#) -cash-account-number (c-acct#) -cash-receipt-date (cr-date) -cash-account-balance (c-bal) -remittance-advice-total-amount (ra-amt) -cash-receipt-amount-applied-to-a-sale (cr-app) -location-of-cash-account (c-loc) -quantity-sold of a phone (on a particular sale) (qty-sold) 1

PhoneSource Solution PhoneSource Relational Database Tables Phone Inventory (Phone# , description, standard cost, list price, QOH) Sale (Sale# , date, amount, customer#*) Stockflow1 (Sale#, Phone# , quantity sold, actual price) Customer (Customer# , name, billing address, shipping address, salesperson#*) Salesperson (Salesperson# , name, address, phone number) Participation1 (Salesperson#, Sale# ) Participation3 (RA#, Customer# ) Participation4 (RA#, Salesperson# ) Cash (Cash account# , location, balance) Cash Receipt (RA#, date, amount, cash account#*) Duality (RA#, Sale# , cash receipt amount applied) Note: Primary keys are underlined; foreign keys are indicated with an asterisk* 2 1..1 0..* Assignment <<Event>>Cash Receipt -RA# PK -date -RAAmount <<Resource>>Cash -accountNumber PK -acctLocation -acctBalance 1..1 1..* Stockflow2 <<InternalAgent>> Salesperson -SP# PK -spName -spAddress -spPhone# Stockflow1 - qtySold - actPrice Participation4 1..* Participation1 <<Resource>> Phone Inventory -Phone# PK -description -listPrice -qoh -stdCost 0..* <<Event>> Sale -saleNumber PK -saleDate -saleAmount 0..* 0..* 0..1 1..* Participation2 <<ExternalAgent>> Customer -cus# PK -custName -shipAddress -billAddress 1..1 Duality - amountApplied 0..* 1..* Participation3 0..* 0..1 0..*

J&B Enterprises Give a UML class diagram (classes, associations, attributes, and multiplicities) and a relational database for the following object system. J&B Enterprises offers each of its clients a full range of outsourcing service options which allow those companies to concentrate almost exclusively on their main value-added activities. J&B will do your bookkeeping, computer services, taxes, cleaning, advertising, legal work, etc. for you by dispatching its own well-trained employees to your place of business. Each service engagement involves exactly one type of service, and it must include at least one engagement employee. Each type of service has a standard hourly billing rate. Employees can engage in multiple service engagements, but they have just one hourly wage rate. When a service engagement is completed during a particular month, that client is required to pay in full for it by the 20th of the next month (if a client has had more than one service engagement, one payment can be submitted for all of the engagements). Clients NEVER make partial payments. Although the vast majority of cash receipts come from clients for service engagements, some cash receipts come from other sources (e.g., bank loans). J&B wants foreign keys to be created, where appropriate, for associations with high load. Every cash receipt is processed by exactly one of J&B’s several cashiers and is deposited into one of J&B’s bank accounts. Information about service types, cashiers, engagement employees, and clients will often need to be entered into the database before any transactions involving them have occurred. Information about cash accounts may not be entered into J&B’s system without assigning at least one cash receipt to that account. The following attributes are of interest to potential users of this data model. -name-of-service -client-name -invoice#-for-service-rendered -cash-receipt-amount -remittance-advice# -bank-account-location -cash-account# -individual-accounts-receivable -client# -cashier-phone# -cashier-SSN -employee-SSN -engagement-employee-highest-degree -service-billing-rate -total-charge-for-service-engagement -employee-wage-rate (engagement & cashier) -bank-account-balance (SSN = social security number) 3

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

J&B Enterprises Solution J&B Enterprises Relational Database Tables Service Type (Service Name , Standard Hourly Billing Rate) Service Engagement (Invoice# , Total Charge, Service Name*, Client#*) Engagement Employee (Employee SSN , Hourly Wage Rate, Highest Degree) Participation1 (Employee SSN, Invoice#) Cash Receipt (Remittance Advice# , Amount, Cash Acct# * ,Cashier SSN*) Client (Client# , Name, A/R Balance) Cash (Cash Acct# , Cash Acct Location, Cash Acct Balance) Cashier (Cashier SSN , wage rate, cashier phone#) Duality (Remittance Advice#, Invoice# ) Participation3 (Remittance Advice#, Client#) Note: Primary keys are underlined; foreign keys are indicated with an asterisk* 4 <<Event>>Cash Receipt -RA# PK -Amount Participation3 0..1 0..* <<InternalAgent>>Engage mentEmployee -empSSN PK -hourlyWageRate -highestDegree <<ExternalAgent>>Client -client# PK -Name -A/RBalance <<InternalAgent>>Cashie r -cashierSSN PK -wageRate -phoneNumber <<Resource>>ServiceTy pe -serviceName PK -stdHourlyBillRate <<Resource>>Cash -accountNumber PK -acctLocation -acctBalance 1..* Participation1 Stockflow1 <<Event>> ServiceEngagement -invoiceNumber PK -totalCharge 0..* 1..1 0..* 0..* Participation2 1..1 0..* Duality 0..1 Stockflow2 1..* 1..1 0..* 1..1 Participation4

FarmFresh Produce Company FarmFresh Produce Company is a produce wholesaler that buys large quantities of produce and sells this produce to grocery stores and restaurants. Four purchasing agents arrange the acquisitions of produce; each agent is assigned to specific produce types. Only one agent is assigned to each item of produce; however, if an assigned agent is on vacation or is ill, an alternative agent may acquire the absent agent’s produce types. Only one agent is involved with any acquisition. Agents can acquire several types of produce on any single purchase. A produce item may be available from more than one vendor, and FarmFresh wants to be able to track which produce types are available from which vendors. One of FarmFresh’s three disbursement clerks prepares a check drawn on one of the company’s checking accounts to pay vendors for purchases. One check is prepared for all purchases made from a vendor during the previous week. Partial payments are never made. The company has checking accounts at several different banks, and the same check numbers could be used by the different checking accounts. Besides checks written to vendors (which constitute only approximately 20% of all checks written), FarmFresh’s disbursement clerks also issue checks to employees, lenders, stockholders, etc. FarmFresh does not have any petty cash or on-hand cash accounts. FarmFresh does have some savings accounts and certificates of deposit, held at some of the same banks at which FarmFresh has checking accounts. Employees, vendors, inventory items, cash accounts, and banks can be added to the database before any transactions involving them occur. Prepare a UML class diagram. Also convert your class diagram into a set of relational database tables. The owner of FarmFresh has identified the following attributes that she wants included in the database. You may use the abbreviations in the database design. Disbursement clerk ID (DC-ID) VendorID (VenID) Check number (Ck#) Cash account type (Ctype) Check amount (Ck-amt) Purchase date (Date-pur) Vendor name (Vname) Bank’s phone number (B-ph) Vendor address (Vadd) Cash account number (Acct#) Produce type ID (PT-ID) Date of check (Date-ck) Purchase number (Pur#) Vendor’s phone number (V-ph) Purchasing agent name (PA-name) Purchasing agent ID (PA-ID) Bank ID (Bank-ID) Produce type description (Desc) Disbursement clerk name (DC-name) Name of bank (B-name) Cash disbursement ID (CD-ID) Actual unit cost of a produce type on a purchase (act-cost) Quantity of each produce type received on a purchase (act-qty) 5

FarmFresh Produce Company Solution FarmFresh Produce Relational Tables Acquisition (Pur# , Date-pur, PA-ID * , VenID * ) AvailableFrom (PT-ID, VenID ) Bank (Bank-ID , B-name, B-ph) Cash (Acct# , Ctype, Bank-ID * ) Cash Disbursement (CD-ID , Ck#, Ck-amt, Date-ck, DC-ID * , Acct# * ) Disbursement Clerk (DC-ID , DC-name) Duality (CD-ID, Pur# ) Participation3 (CD-ID, VenID ) Produce Type (PT-ID , Desc, PA-ID * ) Purchasing Agent (PA-ID , PA-name) Stockflow1 (Pur#, PT-ID , Act-cost, Act-qty) Vendor (VenID , Vname, Vadd, Vph) Note: Primary keys are underlined; foreign keys are indicated with an asterisk* 6 <<InternalAgent>>Disbur sementClerk -DisClerkID PK -Name <<InternalAgent>>Purcha singAgent -PurchAgentID PK -PurchAgentName <<Resource>>ProduceT ype -ProdTypeID PK -description <<Resource>>Cash -accountNumber PK -acctType Bank -bankID PK -bankName -bankPhone AvailableFrom 1..1 Assigned Stockflow1 - actQty -actCost 0..* 0..* 1..1 Participation1 0..* <<Event>> Acquisition -purchaseNumber PK -date 1..* 0..* 0..* 0..* Participation2 1..1 <<ExternalAgent>> Vendor -VendorID PK -vendorName -vendorAddress -vendorPhone 0..* 0..1 Duality 0..1 Participation3 <<Event>> Cash Disbursement -CashDisbID PK -check# -checkDate -checkAmount Stockflow2 0..* 0..* 1..1 0..* 1..1 Participation4 0..* 1..1

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

University Slum-Lord University Slum-lord (USL) does business by leasing single-family homes, minimizing the upkeep on them, and then renting them to students who attend college in the same city. The paragraphs below describe part of the acquisition cycle for USL. USL only deals in single-family houses in its home city, never in multiple unit dwellings. All of its “homes away from home” are houses leased from their present owners by USL employees who are called SL-agents. Lease contracts negotiated by these agents always deal with just one house, and the lease may last 3-5 years. All houses are in the same city and have unique street addresses. Every house is also given a single neighborhood designation by USL depending upon where it is located. Sample neighborhoods are “Walden,” “Wuthering Heights,” “Pembroke,” “The Heights” (few USL houses) and “The Depths” (many USL houses). Those houses not associated with a traditional neighborhood are given a designation of either “downtown” or the “outskirts,” depending upon their location distance from the center of the city. Information on houses is not entered into the database until a lease for them has been contracted; however, information on neighborhoods is entered into the database as soon as USL determines that a new one exists (such as would happen when a new subdivision is being built). USL determines its ultimate rental rates to its student customers by considering such matters as lot-size and number-of-bedrooms for each house, so it tracks data like that carefully. Additionally, each house is given a monthly rental surcharge that depends solely on its neighborhood designation. These monthly surcharges range from very high (the Heights) to very low (The Depths), but every house has one associated with it, and all houses in a particular neighborhood have the same surcharge. Many USL lessors own multiple houses that they lease to the company, but each house has only one owner. Payments to owners are not handled by USL-agents, but by cashiers who are actually a much duller class of employees than the flamboyant agents who revel in their ability to wheel and deal real estate at the expense of both owners and students. Cashiers are bonded for security purposes, and cashiers never become agents (or vice-versa). Payments to lessors are done by checks that are drawn from company cash accounts. The check numbers used from each account are unique. There are some USL cash accounts from which checks are never written. Many checks are also written to discharge other obligations of the company, such as advertising and taxes. Checks are sent to lessors on a schedule determined by one of three lease payment options that are negotiated by the agents (1) each month (2) every six months and (3) every year. Most leases do not require any kind of payment at contract signing time. When multiple lease payments are due to the same lessor during the same month (because USL is leasing more than one property from that person), cashiers simply disburse one check. Information concerning both employees and lessors is put into the database as soon as it is known without waiting for any transactions to take place. Required: Using the information above and the list of attributes below (no additions/deletions), draw a UML class diagram. Also derive the relational database tables. - neighborhood name -number-of-houses-leased-in-this-neighborhood -lessor-ID# -lessor-name -cashier-employee# -agent-employee# -check# -disbursement-$-amount -cashier-bonding-status -cash-account# -agent-real-estate-license-status -rental-surcharge-amount -house-street-address -lease-contract# -yearly-lease-fee -lease-payment-option -lot-size -number-of-bedrooms-in-a-house -cash-account-balance 7

University Slum-Lord Solution University Slum-Lord Relational Database Tables House (Street address , lot size, # of bedrooms, neighborhood name * , lessorID# * ) Neighborhood (Neighborhood name , # of houses leased, rental surcharge amount) Cash (Account # , Balance) Lease (Contract# , yearly fee, payment option, street address *, SL Agent Emp# *, LessorID# *) Cash Disbursement (Check# , amount, cashier emp# *, cash account# *) SL Agent (Employee# , real estate license status) Lessor (ID# , name) Cashier (Employee# , bonding status) Duality (Contract#, Check# ) Participation3 (LessorID#, Check# ) Note: Primary keys are underlined; foreign keys are indicated with an asterisk* 8 <<Resource>>House -streetAddress PK -lotSize -#Bedrooms <<Resource>>Cash -accountNumber PK -acctBalance Participation3 0..1 0..* <<InternalAgent>>Cashie r -Employee# PK -bondingStatus <<ExternalAgent>>Lessor -ID# PK -Name <<InternalAgent>>SLAge nt -Employee# PK -RElicenseStatus Neighborhood -neighborName PK -#housesLeased -rentalSurchargeAmt 0..* 1..1 OwnedBy <<Event>> Lease -contractNumber PK -yearlyLeaseFee -paymentOption 1..* Participation2 Stockflow2 0..* 1..1 0..* 1..1 Stockflow1 0..* 0..* <<Event>> CashDisbursement -Check# PK -Amount 0..* 1..1 1..1 Participation4 0..* 1..1 1..1 location 0..* Participation1 Duality

Extra Practice Data Modeling Problems UML REA Problems and Solutions

Related Documents