January 7 2000 Database Application Design Handout 1

Course information • Instructor: Dragomir R. Radev (radev@si. umich. edu) • Office: 305 A, WH, Phone: 615 -5225 • Office hours: TBD • Course page: http: //www. si. umich. edu/~radev/654 w 00/ • Meeting time: Fridays, 3: 00 - 6: 00 PM, 311 WH (C) 2000, The University of Michigan 2

Books • Required: Database Processing by David Kroenke (7 th Edition, Prentice Hall, ISBN 0 -13 -084816 -6) • Recommended: My. SQL and m. SQL by Yarger, Reese, and King (O'Reilly, ISBN 1 -56 -592434 -7) (C) 2000, The University of Michigan 3

Structure of the course • • Lectures Assignments Final project Final exam (C) 2000,

Assignments • Assignment 1: SQL problem set • Assignment 2: TBD (C) 2000, The

Final project • • • Proposal Database design Progress report Project Final presentation (C)

Grading • Two assignments: 30% (15% each) • Project: 40% • Final exam: 30%

Policies • Attendance counts as 10% of the grade • Timely submission of assignments

Notes on programming • All students will do some programming as part of the assignments. • For the final project, teams will be formed in ways to include students with diverse backgrounds. (C) 2000, The University of Michigan 9

Syllabus • 1. Relational Model – Relations, Domains, Attributes, Keys, Representation, Manipulation – Integrity – Engines: Oracle, MS Access, Sybase, m. SQL, My. SQL • 2. SQL – Syntax and semantics of SQL – Queries, subqueries, joins (C) 2000, The University of Michigan 10

Syllabus • 3. Programming in SQL – Use of views – Queries using EXISTS, set comparison queries – Outer joins • 4. Database design – Data modeling – Conventions – Design review (C) 2000, The University of Michigan 11

Syllabus • 5. Application design – Application structure – Prototyping • 6. Web interfaces

Syllabus • 7. m. SQL, My. SQL, and Oracle – Availability – Functionality – Similarities and Differences • 8. System administration – Database administration – Security (C) 2000, The University of Michigan 13

Readings for next time • Kroenke – Chapter 1: Introduction to Database Processing – Chapter 2: Introduction to Database Development • YRK (optional) – Chapter 1: Introduction to Relational Databases – Chapter 2: Database design (C) 2000, The University of Michigan 14

Introduction to database processing (C) 2000, The University of Michigan 15

Databases • Move WWW beyond simple publishing • Examples: – personal : Mary Richards House Painting – workgroup: Treble Clef Music – organizational: DMV licensing and registration – Internet technology: Calvert Island Reservations (C) 2000, The University of Michigan 16

More examples • Give examples of databases which you have been using for research

Issues • Databases vs. database applications? • Forms, reports, queries (C) 2000, The University

File processing systems (FPS) Customer processing application Customer file user Rental processing application Rental

Problems with FPS • Data are separated and isolated • Data are often duplicated • Application programs are dependent on file formats • Files are often incompatible with one another • It is difficult to represent data in the users’ perspectives (C) 2000, The University of Michigan 21

Separated and isolated data • Merging files • Which parts • Complexity (C) 2000,

Data duplication • Data integrity • Updates • Credibility (C) 2000, The University of

Application program dependency • Format changes (C) 2000, The University of Michigan 24

Incompatible files • Cobol vs. Visual Basic vs. C (C) 2000, The University of

Users’ perspectives • Relationships among records are not easily represented or processed • E.

Database processing systems • • Integrated data Reduced data duplication Program/data independence Easier representation

Definition of a database • Self-describing: user data + structure data (metadata or data directory) • Collection of integrated records, usually indexed • DB is a model of the users’ model • Transactions (C) 2000, The University of Michigan 28

Structure of FPS and DBS • • • Bytes Fields Records Files DBS only:

The relational Model • E. F. Codd, “A Relational Model of Data for Large Shared Databanks” in CACM, June 1970, pp. 377 -387 • Data is stored in tables • Normalization • Ashton-Tate, then Borland (d. Base II) (C) 2000, The University of Michigan 30

Client-server database applications • Shared files • Multiple CPUs (cf. mainframe) • Internet technology

Questions • Why is database processing an important subject? • What are the main differences between a single-user and a multi-user database? • What are the limitations of file processing systems as described in the lecture? • What is a database? Metadata? • What is the relational model? • Check out the Web sites of Dell and Amazon. What databases are involved? Which is the most recent book by Günter Grass? (C) 2000, The University of Michigan 32

Introduction to database development (C) 2000, The University of Michigan 33

Example: registrar’s data • Relations and tuples • Example: Student. Name, Student. Phone, Adviser.

Questions • What happens if Adviser Parks changes his phone number? • What is

Use two relations: R 1 and R 2 R 1 R 2 (C) 2000,

Sys. Tables table (C) 2000, The University of Michigan 38

Sys. Columns table (C) 2000, The University of Michigan 39

CREATE TABLE acllink ( link_id CHAR(10), url CHAR(190), title CHAR(110), author CHAR(60), cat 1 CHAR(30), cat 2 CHAR(20), cat 3 CHAR(20), cat 4 CHAR(20), email CHAR(40), annotation TEXT(50), date_added TEXT(30), date_indexed TEXT(30) ) (C) 2000, The University of Michigan 40