1 / 30

Chapter 4

Chapter 4. Database Processing. 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. Purpose of Database.

axl
Download Presentation

Chapter 4

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

More Related