Database Design and Management

1. Database DesignandManagement  2000 by Prentice Hall.

2. Learning Objectives After studying this chapter, you will be able to: • Explain how data are stored and managed in a database • Describe a database management system (DBMS) and its components • Outline how structured query languages affect decision making • Describe data models • Discuss data warehousing and data mining • Explain how distributed databases help organizations  2000 by Prentice Hall.

3. Three Key Principles Guide Database Creation and Use • The main purpose of databases is to help a company become so fast, responsive, and useful to customers that it becomes the “company of choice.” • Databases should help decision makers assess how their decisions influence the overall health of the business. The typical byproduct is more committed involvement to the business and the decision-making process. • Databases should deliver relevant, timely information in a way that meets users’ needs. Information that is too much, too little, too soon, or too late will doom the communication process.  2000 by Prentice Hall.

4. Last Name First Name Address Phone Number SSN Adams Jefferson George Mary 123 Lancelot Dr. 1779 Washington Ave. 704-555-1234 704-555-6789 987-76-5432 123-45-6789 Jefferson Mary 1779 Washington Ave. 704-555-6789 123-45-6789 The Data Hierarchy Database A collection of interrelated data Personal Data File Credit History File Transaction Data File File A group of interrelated records Record Jefferson (Last Name) Mary (First Name) 1779 Washington Ave. (Address) 704-555-6789 (Phone Number) 1234-56-789 (Social Security No.) Field Byte R Y M A Bit 0 (or 1)  2000 by Prentice Hall.

5. Database Management System (DBMS) • A group of programs that helps to create, process, store, retrieve, control, maintain, and manage data.  2000 by Prentice Hall.

6. The Four Main DBMS Components DBMS Data Manipulation Language Data Definition Language Data Dictionary Reports and Utilities Language to process and update data Language to create and modify data An electronic document that provides detailed information about each and every piece of data in the database Software that generates reports and makes the database user- friendly  2000 by Prentice Hall.

7. Data Manipulation Language (DML) • A language that processes and updates data.  2000 by Prentice Hall.

8. Structured Query Language (SQL) • A language that deals exclusively with data, namely, data integrity, data manipulation, data access, data retrieval, data query, and data security.  2000 by Prentice Hall.

9. Data Languages • Data definition language • A DBMS language used to create and modify the data • Data manipulation language (DML) • A language that processes and updates data • Structured query language (SQL) • A language that deals exclusively with data, namely, data integrity, data manipulation, data access, data retrieval, data query, and data security  2000 by Prentice Hall.

10. Data Dictionary • Location of the data (in what file the data are located) • Size of the data (how many bytes) • Range of acceptable values for each field • Type of data (number, character, audio, etc.) • Source of the data (where the data originated) • Usage (who uses the data) • Ownership (who has the right to view or modify the data) • Methods for accessing and securing data A data dictionary describes each piece of data in a business and describes in detail the characteristics of the data  2000 by Prentice Hall.

11. Data Views • Logical view of data • A view that shows the logical relationship(s) between different pieces of data in a database • Physical view of data • A view that shows how and where data are physically stored in a storage medium  2000 by Prentice Hall.

12. The Three Types of Relationships among Entities Priya Priya’s Mother 1:1 Peter Priya Paula Pam 1:many MasterCard Customer A Customer B Visa Customer C Customer D Many:Many American Express Customer E  2000 by Prentice Hall.

13. Data Models • Hierarchical Data Model • Looks similar to an organizational chart • Each record in a hierarchical model can have only one parent • Ideally suited to represent one-to-many (1-M) relationships • Network Model • Represents many-to-many relationships • A record can have multiple parents in a network model  2000 by Prentice Hall.

14. Data Models (cont.) • Relational Model • Most popular type of data model • A relational model is based on relations • A relation is a table that satisfies three criteria • Each cell in the table has one and only one value • Each row in a table is unique • All entries in a column must be of the same kind  2000 by Prentice Hall.

15. Data Warehouse • A large database that is a collection of smaller databases containing useful data designed to support decision making.  2000 by Prentice Hall.

16. Similarities and Differences between a Database and a Data Warehouse • In many, though not all cases, data warehouses are significantly larger than databases because the warehouses are often a collection of interrelated databases. • Databases are often updated frequently, some even instantaneously. Data warehouses are not. • Like databases, data warehouses support fast on-line queries and quick summaries for managers. • Data warehouses are ideal for large volumes of data because the software that supports them is designed to hold sizable amounts of data. • Databases are usually organized around a department, say public safety, or around a function, say marketing. Data warehouses, in contrast, are often designed to gain a view of the entire organization.  2000 by Prentice Hall.

17. Data Mining • The automated analysis of large data sets to find patterns and trends that might otherwise go undiscovered.  2000 by Prentice Hall.

18. Distributed Databases • A database distributed over computer hardware located in different geographical areas.  2000 by Prentice Hall.

19. CUSTOMER DATABASE New York Chicago Raleigh Portland Different Ways to Distribute Databases Dividing a customer database and locating portions in relevant locations Raleigh New York CUSTOMER DATABASE Portland Chicago Duplicate copies of the customer database in different locations  2000 by Prentice Hall.

20. Guidelines for Database Management Success • Use the Database to Improve Decision Making • Many companies collect mammoth amounts of data, but few put them to good use • By carefully analyzing data and taking appropriate action a company can win and keep customers • Recognize That Databases Are Competitive Weapons for All Businesses • Many companies are delving into public databases with a fine-toothed comb to find valuable competitive information  2000 by Prentice Hall.

21. Guidelines for Database Management Success (cont.) • Design the Database to Meet Users’ Communication Needs • The information in the database must be simple and accessible to everyone involved in making decisions • Show Decision Makers How Their Choices Affect the Business • Databases enable decision makers at all levels of the business to see how their decisions affect the entire business  2000 by Prentice Hall.

22. Guidelines for Database Management Success (cont.) • Use the Database to Become Consumers’ Company of Choice • Aligning IS and corporate goals is an important business challenge • A company must develop and integrate its databases to support its business strategy in order to provide outstanding customer service • Plan for Appropriate Security • A database with valuable data must be guarded • Businesspeople must plan and budget for appropriate security and expect to upgrade security as technology improves  2000 by Prentice Hall.

23. Guidelines for Database Management Success (cont.) • Plan for Database Maintenance • Maintaining the database so that it continues to meet the needs of end users is an important aspect of database development • Managers must allocate resources for database management and maintenance at the beginning of the development project  2000 by Prentice Hall.