Download
chapter 4 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 4 PowerPoint Presentation

Chapter 4

180 Views Download Presentation
Download Presentation

Chapter 4

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Chapter 4 Database Processing

  2. Agenda • Purpose of Database • Terminology • Components of Database System • Multi-user Processing • Database Design • Entity-relationship Model • Database Administration • Database Security • Discussion, Design, and Case Study

  3. Purpose of Database • to keep track of things that involve more than one theme

  4. Terminology - I • Database • A collection of self-describing and integrated records • The hierarchy of data elements • Bytes – columns – fields – rows (records) – tables (files or relations) – database • Database • A collection of tables plus relationships (in terms of) among rows (records) in these tables • Relationships • Primary key: a column or group of columns to identify a unique row in a table • Foreign key: a non-key column or field in one table that links to a primary key in another table

  5. Hierarchy of Data Elements

  6. Examples of Relationships Among Rows

  7. Terminology - II • Relational database • Tables • Relationship using primary key and foreign key • Metadata • The structure of the database • The data describe the data • Field name, data type, description, property

  8. Metadata Example (in Access)

  9. Components of a Database

  10. Components of Database System • Database management system (DBMS): software • To create tables, relationships, and other structure • To process (read, insert, modify, or delete) data • To produce forms, reports, and queries through database application • To administer (security, back up, recover, clean up, and enhance performance) a database • Structured Query Language (SQL) • An international standard language for processing a database • Product types • Enterprise DBMS: IBM (DB2), Microsoft (SQL Server), Oracle Corporation (Oracle), Open-source (MySQL) • Personnel DBMS: Microsoft (Access) for 15 or less users

  11. Components of a Database Application System

  12. Use of Multiple Database Applications

  13. Four Application Programs on Web Server Computer

  14. Multi-user Processing • More than one user accessing a particular database table at same time • Lost-update problem • Lock

  15. Database Design • Create data model • Logical representation of database based on users view on the business environment (user’s requirements) • Data and relationships that users want to track • Create database design • The process of converting a data model into tables, relationships, and data constraints • Normalization: the process of converting poorly structured tables into two or more well-structured tables to avoid data integrity problems • Users’ reveiw • Create database

  16. Entity Relationship Model - I • Components • Entity: physical object (rectangular) • Attributes: characteristics of the entity • Primary key: identifier • Relationship: primary key and foreign key (line) • Normalization • The process of converting poorly structured tables into two or more well-structured tables • Every table describe a single topic or theme • To avoid data integrity problems • To avoid generating incorrect and inconsistent information • Trade off between performance • Normal form

  17. Entity Relationship Model - II • Entity-relationship diagram • Entity: rectangular • Relationship: line • One-to-one: 1:1 • One-to-many (crow’s foot): 1:N • Many-to-many: N:M • Many-to-many relationship has to be changed to two 1:N relationship via an intersection table • Maximum cardinality • Minimum cardinality

  18. Database Development Process

  19. Example Relationships Showing Minimum Cardinalities

  20. Transforming a Data Model into a Database Design

  21. Representing a 1:N Relationship - I

  22. Representing a 1:N Relationship - II

  23. Representing a N:M Relationship

  24. Database Administration - I • Functions • Manage the development, operation, and maintenance of a database to achieve the organization’s objectives • Balance conflicting goals to protect the database and maximize its availability for authorized use • DBA • Database administrator or office of database administration • an auditor, a consultant, sometimes a policeman, and a diplomat working as a liaison between the users and professional developers

  25. Database Administration - II • DBA responsibilities • Database development • Database operation • Backup and recovery • Adopt new database change requirements • Create a steering committee consisting of key users • Steering committee • Community-wide decisions regarding the development, use, and maintenance of the database • Community-wide policies for the processing of the database • Processing rights (minimum)

  26. Database Security • Firewall • OS and DBMS patches • Physical access security • User accounts, roles, and minimum permission • Processing and error logs • Security emergency plan

  27. Discussion • Ethics (81a-b) • What should the company do to avoid the situation at first place? • What should the company do to properly handle Kelly’s case • What should Kelly do to avoid the stated consequence after querying the database? • What could Kelly do to save his career? • Opposing forces (97a-b) • What should the DBA of car dealer do to convince every salesperson to build and use a solid sales information database? • Reflections (101a-b) • What should the DBA of any organization to handle the changing requirements for a new database? • What should the DBA of any organization to handle the new requirements for a existing database?

  28. Relational Database Design • Given the following entities and their attributes • Buyer (BuyerID, BuyerName) • Owner (OwnerID, OwnerName) • Property (PropertyID, PropertyAddres, OwnerID) • Agent (AgentID, AgentName) • Sales (SalesID, BuyerID, OwnerID, PropertyID, AgentID, Price, Date) • Design a relational database • Create primary key for every entity and the necessary foreign key • Create an entity-relationship diagram

  29. Case Study • Assignment 22 (105) • Case 4-1 (107-110): questions 1, 4, and 7 (add aircraft for 7) • Case 4-2 (110-113): questions 1 and 2

  30. Points to Remember • Purpose of Database • Terminology • Components of Database System • Multi-user Processing • Database Design • Entity-relationship Model • Database Administration • Database Security • Discussion, Design, and Case Study