IDs 200 Summer 2019 Assignment 1 Due Friday 6/28/2019 Points

Ids 200summer 2019assignment 1due Friday 628201924 Pointsinstruction

Design a database file with tables & fields, relationships, and queries based on the following specifications:

Tables:

  • Vendor: Id (AutoNumber), Name (short text, field size 40), StreetAddress (short text, field size 30), ZipCode (Number with 00000 input mask)
  • SalesRep: Id (Number), LastName (short text, field size 20), FirstName (short text, field size 15), Vendor (Number), PrimaryPhone (short text), SecondaryPhone (short text)
  • Buyer: Id (Number), LastName (short text, field size 20), FirstName (short text, field size 15), PrimaryPhone (short text), SecondaryPhone (short text)
  • Item: Code (Number with 000000 input mask), Name (short text, field size 30), Vendor (Number), UnitPrice (Number)
  • Transaction: InvoiceNumber (AutoNumber), Vendor (Number), SalesRep (Number), Buyer (Number), Date (Date/Time, format: short date), Amount (Number), Received (yes/no, default: no), Paid (yes/no, default: no)
  • TransactionElement: Item (Number with 000000 input mask), InvoiceNumber (Number), Quantity (Number), Amount (Number)

Relationships:

  • Link Vendor fields to Vendor.Id:

      - SalesRep.Vendor, Item.Vendor, and Transaction.Vendor

  • Link Transaction.SalesRep to SalesRep.Id
  • Link Transaction.Buyer to Buyer.Id
  • Link TransactionElement.InvoiceNumber to Transaction.InvoiceNumber

Queries:

  • Select all Vendors where ZipCode = 60101
  • Select all TransactionElements where Vendor Id = 2345
  • Select all Transactions from August 2018 that have been paid
  • Select all Items where Code is less than 1000
  • Select all SalesReps where Vendor = 1122 and LastName = "Berry"
  • Select all Buyers where LastName = "Chang" and FirstName = "Robert"
  • Select all Transactions from May 2019 where SalesRep was named Kelly Jackson and Buyer was named Juan Ortiz

Paper For Above instruction

The development of a comprehensive database system is critical for managing complex business transactions efficiently. The task involves designing a relational database model that captures various entities such as Vendors, Sales Representatives, Buyers, Items, Transactions, and Transaction Elements, establishing proper relationships between these entities, and creating queries to extract meaningful data based on specific conditions.

In designing such a database, the first step is defining the tables with appropriate fields and data types. The Vendor table contains vendor details, uniquely identified by an auto-generated ID, with attributes like name, address, and zip code. Similarly, the SalesRep table includes information about sales representatives, linked to respective vendors to indicate their affiliation. The Buyer table records customer details, enabling tracking of individual buyers.

The Item table catalogs available products, each linked to a vendor and characterized by a unique code, name, and unit price. The Transaction table logs sales activities, capturing essential details such as transaction date, total amount, and payment status, along with foreign keys to customer, vendor, and sales representative involved. The TransactionElement table represents line items within each transaction, detailing individual items, quantities, and their specific amounts.

Establishing relationships between tables ensures data integrity and facilitates complex queries. For example, linking Vendor foreign keys to the Vendor table allows for retrieving vendor-specific data. The relationship between Transaction and TransactionElement enables understanding transaction line items in detail, while associating SalesRep and Buyer entities with transactions provides comprehensive transaction tracking.

Querying these tables allows users to extract tailored reports such as all vendors within a specific zip code, transactions within a particular month that are paid, or customers matching certain names. These queries are essential for business analysis, inventory management, and customer relationship management.

The construction of this database model emphasizes normalization principles, ensuring minimal redundancy and optimal data organization. Proper indexing of key fields enhances performance, while default values for fields like Received and Paid streamline data entry procedures.

In conclusion, designing an effective relational database involves careful planning of table structures, establishing precise relationships, and crafting queries tailored to business needs. This approach ensures data consistency, enhances retrieval efficiency, and supports complex decision-making processes vital for business success.

References

  • Harrington, J. L. (2016). Relational Database Design Clearly Explained. Morgan Kaufmann.

Related Assignments