CPSC 203 Tutorial

1. CPSC 203 Tutorial Xin 20 October, 2010

2. Data base design • There are a lot of theories • The most important: • Avoid Redundancy!!! • Avoid inconsistencies • Easy to maintain • Change data at one place

3. Relationship • Relate records in different tables • Must have before working on multi-tables • Build relationship based on the same thing • Department ID  Department ID • In Access • Drag from the primary key to foreign key • Enforce Referential Integrity

4. Query • Query is used for retrieving data • Showing selected data • Filter • Calculation • Summary • Design View • Sort: ascending/descending • Show: if visible • Criteria: can have many rows • (Row1_Col1 AND Row1_Col2 AND …) OR (Row2_Col1 AND Row2_Col2 AND …) OR (Row3_Col1 AND …) OR …

5. Practices • Create a query to show all info in bktblTitles • Create a query to show title_name, price and sales in bktblTitles • Create a query to show all info in bktblAuthors and bktblTitles tables • Create a query to show book title name, publisher’s name, and author’s first and last name.

6. Practices • Create a query to show title_name, price and sales in bktblTitles, satisfying price >10 and/or sales <=10000 • Create a query to show title_name, price and type in bktblTitles, showing only biography books, sorting according to title_name • Create a query to show book title name, publisher’s name, and author’s first and last name. Only show Abatis Publishers records with royalty rates < 0.08 or with advances < 30,000.