Maintaining Database Safely

1. Maintaining Database Safely

2. Validation check on data (3-1) • Use Orders form • We want to check the validity of expiration date for credit card • expiration date >= Now (current date) • Modify the expiration date field in the ORDERS form

3. Validation check on data (3-2) • Open ‘Orders’ in design mode, Click input box for ‘Expiration Date’ • Click [데이터] tab in[속성시트] menu in design mode (‘속성 ’button in ACCESS2005) • Use ‘>=now()’ function for today’s date for ‘유효성 검사 규칙’ • Enter also an appropriate warning message for ‘유효성 검사 메시지’for input error • Test • Enter 2000-04-30, • or 2015-12-01 • for expiration date

4. Exercise • Modify the ORDERS form to display, “The card already expired!! Check it” when a user enters an expired date.

5. Adding a combo box control (3-3) • Instead of entering customerID directly in ORDERS form, Use a customer combo box to enter it. • Delete input box of ‘CustomerID’ • Click 기존필드추가’button (‘필드목록’button ( )in the middle of 2nd line menus to see available field list in access 2005) • Use the control wizard컨트롤마법사 in the tool box • Click Combo box button first to make sure you’ll make it • Drag CustomerID field in the field list to the previous customerID field in the form • Continue to work in the control wizard (see the next slides)

6. Work in control wizard(3-4) • At page 1 of Combobox wizard • Make sure we have the default option ‘콤보 상자에서 테이블이나 쿼리에 있는 값을 조회하도록 합니다.’ • Click [다음] button • At page 2 of Combobox wizard • Select ‘쿼리’at ‘보기’region • Choose ‘Customer List’ from ‘쿼리’list • Click [다음] button • At page 3 of Combobox wizard • Double click ‘CustomerID’, ‘LastName’, ‘FirstName’ in ‘사용할 수 있는 필드:’ list • Click [다음] button

7. Work in control wizard(3-5) • At the next page of Combobox wizard • Choose ‘CustomerID’ for ordering • Click [다음] button • At the next page of Combobox wizard • Double click each right corner of 3 fields to adjust the field size properly • Click [다음] button • At the next page of Combobox wizard • Choose ‘CustomerID’ to distinguish each raw • Click [다음] button • At the next page of Combobox wizard • Confirm ‘이 필드에 값을 저장합니다’option is chosen for ‘CustomerID’ field • Click [다음] button • At the last page of Combobox wizard • Enter ‘Customer ID’ for label • Click [마침] button

8. Exercise • Adjust ‘Order Date’ field to display the date appropriately, and modify the field to display today’s date as the default value. Confirm your modification by clicking ‘new record’ input button. • Hint: • See the slide of validation check(3-2) and use ‘기본값’field • Use the function ‘Date()’

9. Changing the TAB orders • You may have to change visiting orders for each field, because CustomerID has been modified finally, it’s visiting order is the last • Select [정렬(보기 in ACCESS2005)]-[탭 순서] at top line menu • Arrange the field order as follows • CustomerID, OrderDate, OrderID, FirstName, LastName, Street, City, StateOrProvince, PostalCode, Country, ShipFirstName, ShipLastName, ShipStreet, ShipCity, ShipStateOrProvince, ShipPostalCode, ShipCountry, CreditCard, AccountNumber, ExpirationDate, Gift, Orders Subform, Subtotal

10. Referential Integrity • We cannot enter a foreign key value that does not exist as a primary key value in the base table • Foreign key외래키: primary key of other table Ex) we cannot enter an order unless the customer has been registered Order Customer * custID in order table is a foreign key

11. Referential Integrity(cont.1) • We cannot delete a primary key value in the primary table if a corresponding foreign key value exists Ex) we cannot delete a customer if orders from the customer still exist Order Customer   * custID in order table is a foreign key

12. Referential Integrity(cont.2) • We cannot modify a primary key value if a corresponding foreign key value exists Ex) we cannot modify the ID of a customer if orders from the customer still exist Order Customer   * custID in order table is a foreign key

13. All update/ All delete • All update • Change in the primary key value will affect all foreign key values in other tables Ex) If we modify the ID of a customer in primary table, then all ID values of the customer in ORDERS table will be changed • All delete • deletion in the primary key value will delete all foreign key values in other related tables Ex) If we delete the ID of a customer in primary table, then all records which have the ID values of the customer in ORDERS table and the records which have the corresponding OrderID values in ORDERS DETAIL table will be deleted • Order(OrderID, CustomerID,…), OrderDetails(OrderID, BoxID, Quantity)

14. Referential integrity(3-6) • Let’s check the referential integrity between ‘Customers’ table and ‘Orders’ table • Customers(CustomerID, Lastname, …) • Orders(OrderID, CustomerID, …) • Confirm it in the ‘relationship window’ • Click ‘관계’button • Click ‘레이아웃지우기’button • Click ‘테이블표시’button • Choose ‘Customers’ in ‘테이블표시’dialog window • Click ‘직접관계표시’button to see related tables • Modify two tables’ relationship as ‘항상 참조 무결성 유지’by clicking the line between two tables

15. Exercise • The Sweetlil company sometimes modify BoxID in table Boxes • But often forget to modify the corresponding BoxID in the table ‘Box Details’ • So we want to modify the database so that the modification occurs automatically. What can we do? • Boxes(BoxID, BoxName, …) • BoxDetails(BoxID, BonbonID, Quantity)

16. Many-to-many relationship Ex) • Basket-bonbon • A basket contains several kinds of bonbons: 1-N • A bonbon can be contained in several baskets: 1-M • So, N-M

17. Many-to-many relationship(cont. 3-7) • Conjunction table • A bridge table that intervenes two tables with N-M relationship • Ex) Basket--BasketDetails– Bonbons Baskets(BasketID, Basketname, …) BasetDetails(BasketID, BobbonID, Quantity) Bonbons(BobbonID, BonbonName, …) • Confirm it in the ‘relationship window’ • Conjunction: 연결