1 / 21

CS U430: Database Design

CS U430: Database Design. Spring 2006 Panfeng (Tony) Zhou. 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. What could you learn from this course?.

buffy-floyd
Download Presentation

CS U430: Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related