70 likes | 151 Views
Building the Database. Late Books!. So people are returning our books late and we now need to add a way to track our fines. Define relationships and make tables. Resources. Agents. Events. Patrons. Books. Borrows Add Fines Add Fine was Paid. Cash AccountNumber (PK)
E N D
Late Books! • So people are returning our books late and we now need to add a way to track our fines. • Define relationships and make tables
Resources Agents Events Patrons Books • Borrows • Add Fines • Add Fine was Paid • Cash • AccountNumber (PK) • Beginning Balance • BankName • BankAddress • Account_Type Employees • Fines_Paid • Fines_Paid_ID (PK) • BorrowID (FK) • AccountNumber (FK) • Paid_Amount • Date_Paid
Hey, new books are nice… • We want to be able make several payments on one purchase if it happens to be very expensive. • We also want to be able to pay for many inexpensive purchases with one payment. How could we do this? • (Hint—we need this to be Many to Many)
Resources Agents Events • Money_Out • MoneyOutID (PK) • MoneyOutAmount • AccountNumber Many to Many relationships required an intermediate table to match the primary keys from each table. • Purchases_MoneyOut • PurchaseID(FK) • MoneyOutID(FK) • Purchases • Purchase ID (PK) • Merchant ID (FK) • Purchase_Date • Purchase Amount • Merchants • MerchantID (PK) • MerchantName • Address • Books • Add Purchase ID (FK)
Resources Agents Events Money_Out Purchases_MoneyOut Merchants Purchases Patrons Borrows Books Cash Fines_Paid Employees
Empty tables...how about some data? • We’ve worked so hard to make our tables and relate them, so the best way to celebrate is to import data!