Database Design Essentials Course

1. CS U430: Database Design Spring 2006 Panfeng (Tony) Zhou

2. Who am I? • PhD student in Database research area • 10 years experiences in DB application development • 12 industry world projects in Finance, Education, Health Insurance, Manufacture, etc

3. What could you learn from this course? • You will understand the structure of DBMS and DB language (SQL). • You will learn the index methods of database. • You will learn how to implement a web application from scratch.

4. Main content of the course • Basic structure of DBMS • Queries the database • Create/Update table content/structure • Design the database (ER model) • Web interface for database • A project that covers all contents in the course and you can implement a DBMS from scratch

5. Main content of today • 1. Introduction to DBMS • 2. Relational rules • 3. Sample database

6. 1.1 Database is everywhere • Shop at supermarket (e.g., Stop&Shop, etc). • Shop online (e.g., www.amazon.com). • Borrow book from library. • ………..

7. Structured QueryLanguage (SQL) 1.2 Structure of web DBMS Web browser Web server Databases and DBMS

8. 1.3 Products for each components

9. Field Header Record 1.4 What inside the database? • A set of tables. • Each table “is” a spread sheet.

10. 2. Relational rules for relational database • Rule 1: First Normal Form Rule • Rule 2: Access Row by Content Only Rule • Rule 3: The Unique Row Rule • Rule 4: Entity integrity Rule

11. 2.1 First Normal Form Rule • Fields cannot have multi-values attributes or have any internal structures. Staff_ID Name Position Clients 001 Tony Manager Amanda Smith 002 Robbie Agent Andrew

12. 2.1 First Normal Form Rule (cont) Staff_ID Name Position Clients1 Clients2 001 Tony Manager Amanda 002 Robbie Agent Smith Andrew • Problems: • Waste space • Not flexible

13. 2.1 First Normal Form Rule (cont) Employees Clients Staff_ID Name Position Staff_ID Client 001 Tony Manager 001 Amanda 002 Robbie Agent 002 Andrew 002 Smith

14. 2.2 Access Row by Content Only Rule • Records can only be retrieved by their content (the attribute values in each record). • Implications: • There is no order on the records (e.g., We cannot ask for the • 3rd record in the employee table) • 2. We cannot retrieve a “pointer” to a record to retrieve it later.

15. 2.3 The Unique Row Rule • Two records in a table cannot be identical in all column values at once. Staff_ID Name Position 001 Tony Manager 001 Tony Manager 002 Robbie Agent Note: Keep this row in your mind. You might spend a lot of time and effort to enforce this rule.

16. 2.3 The Unique Row Rule (cont) Table Key: a set of attributes K with two properties: • K is unique for each record. • The subset of K is not unique for each record. Primary Key: is a table key chosen by the database designer to uniquely identify specific records in the table. Note: one table has more than one key, but only one primary key.

17. 2.3 The Unique Row Rule (cont) Employee Staff_ID Name Position 001 Tony Manager 002 Scott Agent 003 Robbie Agent

18. null Scott Agent 2.4 Entity integrity Rule • The primary key cannot be empty Staff_ID Name Position 001 Tony Manager null Scott Agent 003 Robbie Agent • Note: • Cannot retrieve the record by primary key. • Cannot distinguish duplicated records.

19. Client Staff Registration Branch Viewing Private Owner Properties For Rent 3. Sample database

20. 3. Sample database (cont) • Client table • PrivateOwner table • Staff table • Branch table • PropertyForRent table • Registration table • Viewing table

21. 3. Sample database (cont) • Load table structure • Load data in table one by one • Client table • PrivateOwner table • Branch table • Staff table • PropertyForRent table • Registration table • Viewing table