Explore BrainMass

Explore BrainMass

    Relational Database concepts explained with example

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    A1. Use the following partial database tables to answer the required questions below:

    Cash Table___________________________________________________________

    Cash #________________ Type of Account_______________Bank Name____

    110146758 Regular Checking North First

    1203948102 Payroll Checking Account Credit Grantors

    Inventory Table______________________

    Inventory Item # Description__

    001 XL T-shirt
    224 XL Sweatshirt
    302 XXL T-shirt
    451 Felt pennant
    513 Ping-pong ball
    674 Golf ball
    736 XL Polo shirt
    876 Bumper sticker
    887 Foam football

    Sales Event Table
    ________________________________________________________________________

    Sales Event# Date Terms Saleperson ID Customer ID

    1 11/5 2, 10 net 30 2 2543

    2. 11/5 2, 10 net 30 4 635

    3 11/5 COD 6 1845

    Sales Inventory Table

    Sales Event# Inventory Item # Inventory Quantity Price

    1 876 10 1.25
    1 674 8 0.875
    1 451 30 0.995
    2 887 54 1.475
    2 513 188 0.525
    3 736 36 24.995
    3 001 58 7.875
    3 302 16 8.00
    3 224 114 8.75

    Salesperson Table

    Salesperson ID Last Name First Name

    2 Cleaves Mateen
    4 Warrick Peter
    6 Peterson Morris
    8 Janakowski Sebastian

    Cashier Table

    Cashier ID Last Name First Name

    1 Weinke Chris
    2 Outzen Marcus
    Cash Receipts Event Table

    Cash Recepit # Date Chk# Cashier ID Sales Event # Customer ID
    1001 11/6 11097 1 2 635

    Cash Account # Amount Received
    110146758 $178.35

    Customer Table
    Customer ID Last Name First Name Address City State Zip
    101 Conrad Chris 5629 Longfellow Dr Paragould AK 65323
    183 Anderson Paul 674 Sunderland Lane Sioux City IA 63126
    635 Padgham Donna 1264 Algonquin Road Mason MI 48854
    1845 Oliver Andrew 8512 Bonita Drive Clearwater FL 33051
    2543 Cook Carol 536 Secondary Ave. Fremont CA 75518

    Questions to Answer Based on the Above Tables:

    a. What events, resources, and agents must have been included in the underlying conceptual model from which these relational tables were designed?

    b. Identify the primary key of each table.

    c. Identify each foreign key in the database

    d. List the resources and agents involved in Sale event 2.

    e. List the resources and agents involved in Cash Receipt 1001.

    f. Suppose you wanted to generate an invoice (bill) for customer 2543 that lists the customer name and address, the salesperson name, and all other information about the sale, including the items sold. Which tables contain the data you will need to generate the invoice?

    g. Suppose you want to generate a report listing each customer name and the amount due from each customer. Which tables contain the data you need to generate the report?

    h. Explain why "total sales amount" did not need to be included as an attribute in the sales table What are the pros and cons associated with leaving this attribute out of the database tables?

    i. If you need to record the following sale:

    Sale event 4; on 11/10; COD terms; Salesperson 2; Customer 101; 30 unites of item 887, for a total of $44.25.

    What tables would you use? How many records would you add or modify in the tables.

    j. If you need to record the following cash receipt:

    Cash receipt 1002; on 11/10; from customer 2543 to pay off sale event 1; in the amount of $49.35 deposited into cash account 110146758

    What tables would you use? How many records would you add or modify in the tables?

    © BrainMass Inc. brainmass.com November 30, 2021, 12:56 am ad1c9bdddf
    https://brainmass.com/computer-science/databases/relational-database-concepts-explained-with-example-71966

    Attachments

    Solution Summary

    Solution clearly explains Relational Database concepts like Primary Key identication, Foreign Key identification, etc with example.

    $2.49

    ADVERTISEMENT