IDS 200 UIC lab 1 Week 4
IDS 200 UIC lab 1 Week 4
You must design a database file with tables & fields, relationships, and queries as given below. Please submit your Access file without any data – the TAs will import a standard batch of data to evaluate your work. However, you may find it useful to temporarily add some data to your tables, especially to test your queries and reports.
1) Tables: Fields (keys in yellow, foreign keys in blue, both key & foreign key in green)
-
Supplier: SupId (Number), LastName (short text, field size 30), FirstName (short text, field size 30), Email (Short Text, field size 30), PrimaryPhone (Short Text with 000-000-0000 input mask)
-
Distributor: DistId (Number), LastName (short text, field size 30), FirstName (short text, field size 30), Supplier (Number), Email (Short Text, field size 30), PrimaryPhone (short text with 000-000-0000 input mask)
-
Customer: CustId (Number), Name (short text, field size 30), UserId (short text), Password(short text, field size 30), PrimaryPhone (short text with 000-000-0000 input mask), StreetAddress (short text), ZipCode (Number with 00000 input mask)
-
Item: UPC (AutoNumber), Name (short text, field size 50), Supplier (Number), UnitPrice (Number)
-
Transaction: InvoiceNumber (AutoNumber), Supplier (Number), Distributor (Number), Customer (Number), Date (Date/Time with Short Date format), Amount (Number)
-
TransactionElement (note that this table’s key is two fields): Item (Number), InvoiceNumber (Number), Quantity (Number), Amount (Number), Sent (yes/no, default value: no), Paid (yes/no, default value: no).
2) Relationships: Link all foreign key fields to their original tables
-
Supplier, Item.Supplier, and Transaction.Supplier to Supplier.SupId
-
Distributor to Distributor.DistId
-
Customer to Customer.CustId
-
InvoiceNumber to Transaction.InvoiceNumber
-
Item to Item.UPC
3) Queries:
-
Select all Transactions where Supplier.SupId = 509
-
Select all TransactionElements where TransactionElement.Amount <= 1000
-
Select all Items where Supplier.SupId = 771
-
Select all Items ordered by customer with the Transaction.Customer = 1112 that are not yet sent (e, TransactionElement.Sent = “no”)
-
Select all Distributors where Supplier.SupId = 101 and LastName = “Smith”
-
Select all Customers where Name = “Joe” and UserId = “xyz123”
-
Select all Transactions from March 2020 where the Distributor was named Jake Adams and the Customer’s UserId was “abc098”
DETAILED ASSIGNMENT
