1 / 29

Database Processing

Database Processing. Kyu Y. Lee, Ph.D. (253)848-5267 (evenings & wkends) (253)678-5562 (cell) qsystems@comcast.net. Textbook. Database Processing: Fundamentals, Design, and Implementation, 10th Ed.  10 th Edition By David Kroenke Prentice Hall, 2005 ISBN: 0131672673. Database Design.

seth
Download Presentation

Database Processing

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. 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

More Related