Database Processing

1. Database Processing Kyu Y. Lee, Ph.D. (253)848-5267 (evenings & wkends) (253)678-5562 (cell) qsystems@comcast.net

2. Textbook • Database Processing: Fundamentals, Design, and Implementation, 10th Ed.  10th Edition • By David Kroenke • Prentice Hall, 2005 • ISBN: 0131672673

3. Database Design • What is • Database? • File? • Record? • Field? • Data? • Relational Database? • Object-oriented database? • Hierarchical database? • Network-oriented database?

4. Data Hierarchy • Database/File • Records • Fields • Data Items • A set of words, characters, pictures, … • Word • A set of bytes ( 4 bytes for IBM PC) • Byte • A set of bits (8 bits for IBM PC)

5. Exercise • Suppose you are building a “Friends” database to keep up with your friends. The use of such can be: • Sending invitation to a party • Sending Xmas cards • Record and check to see who has been in contact socially • Record and check to see who has been in contact in need • What interest set each of your friends have • What skill set your friends have • Who is married and what is his/her spouse’s name

6. Application Programs Customer-Processing Application DBMS Rental-Processing Application Database Other Application

7. File Processing Customer-Processing Application Customer File Customer File User Rental-Processing Application Rental File Rental File User

8. Limitations of files • Data are separated and isolated • Much data are duplicated ⇒ Data Integrity problem • Application programs are dependent on file formats • Files are often incompatible with one another • It is difficult to represent data in the user’s perspectives.

9. Problems with Data Dependency • Each application programmer must maintain their own data • Each application program needs to include code for the metadata of each file • Each application program must have its own processing routines for reading, inserting, updating and deleting data • Lack of coordination and central control • Non-standard file formats

10. Problems with Data Redundancy • Waste of space to have duplicate data • Causes more maintenance headaches • The biggest problem: • When data changes in one file, could cause inconsistencies • Compromises data integrity

11. SOLUTION: The DATABASE Approach • Central repository of shared data • Data is managed by a controlling agent • Stored in a standardized, convenient form Requires a Database Management System (DBMS)

12. Database Processing System • Integrated Data • Reduced Data Duplication • Program/Data Independence • Easier Representation of Users’ Perspectives

13. Database isa self-describing collection of integrated records • Self-describing • In addition to the users’ source data, it contains a description of its own structure (data dictionary) • Collection of integrated records • Data • Data dictionary • Indexes

14. Client-Server Database Applications • Peer-to-Peer • Client-Server • File-sharing

15. Developer • Design Tools • Table Creation Tool • Form Creation Tool • Query Creation Tool • Report Generator • Language Compiler • Run Time • Form Processor • Query Processor • Report Writer • Language Runtime • Processor D B M S E n g i n e Database App. Progs User • User Data • Metadata • Indexes • Application • Metadata App. Progs DBMS

16. User Data Relation/Table Row/ Record/ Tuple Contains 2 different topics: students and advisors If Lee changes his phone#, 3 rows of data must be changed => Unnormalized • Column/Field/Attribute

17. Student Relation Advisor Relation Now, if an advisor changes his/her phone, only one row of Advisor Relation needs to be changed.

18. Metadata Database contains a description of its structure => Metadata • Stored in system tables

19. SysTable SysColumn

20. Indexes • To speed up the access

21. Example: Student Table Student Relation • Suppose there are 64K records and we wish to create a report in alphabetical order of last name.  • Requires sorting the table. • Selection Sort requires ½n2 + O(n) or ½n(n-1) comparisons. • For n=65,000  ½4 billion  2 billion • Quick Sort requires n*log2(n) • for n=65,000  16 * 65,000  1 million

22. Introduces Indexes for frequently used keys

23. Application Metadata • Used to store the structure and format of user forms, reports, queries and other application components.

24. Schema • Defines a database’s structure • Example • Student Activities Department sponsors intramural athletic leagues. Provide ways of keeping track of the equipment that has been checked out to various team captains. • What do we need? • Captain • Captain’s attributes (address, Phone#, ...) • Items rented out • Item name • How many? • Date Rented Out • Date Turned In • Who rented it • ..... • Two tables ⇒ CAPTAIN & ITEM

25. Tables Needed CAPTAIN ITEM CaptainName is not unique ⇒ Add an unique number, CAPTAIN_ID is a better choice ITEM also requires a Key ⇒ ITEM_ID

26. Tables CAPTAIN ITEM In Schema definition, CAPTAIN (CAPTAIN_ID, CaptainName, Phone, Street, City, State, Zip) ITEM (ITEM_ID, Description, Quantity, DateOut, DateIn, CAPTAIN_ID)

27. Relationship • The above relations are connected through CAPTAIN_ID • One Captain may rent many items but one item is rented by only one Captain. ⇒ 1:N relationship • 1:1 relationship • M:N relationship

28. DOMAIN • A set of values that a column may have. • Domains • ID: CAPTAIN_ID, ITEM_ID • Names: CaptainName, Description • Date: DateIn, DateOut • etc. • Domain is defined in the schema. • Attributes are drawn from a domain. • An attribute represents the use of a domain within a relation. (similar to class and object) • Associations between tuples are represented solely by data values in columns drawn from a common domain. • Consider domain as type

29. Domain • System-defined (built-in) • Integer, char • User-defined • Phone#, SSN, ID