BUS611: Data Management

2a. Explain the fundamental properties of a database and a database management system 

• What is meant by the term database?
• How does a database management system allow for the creation and maintenance of databases?
• What kinds of software are available to create and manage databases?
  

A database is a collection of data that is formatted in a formal manner to achieve a particular purpose for an organization. The database is both a historical repository that can support decision-making and a collection of data that can support ongoing operational processes. Multiple users access the database to support multiple roles.
 
A database management system (DBMS) is an integrated set of computer processes and algorithms that allow for a database's creation, maintenance, and operation. The DBMS will also control security and access to the underlying data. The primary objective of a DBMS is to provide an integrated and systematic computer environment. The DBMS should be easy to use, have a high degree of structural and data integrity, efficiently use computing resources, and facilitate users' needs as simply as possible.
 
DBMS Software is a set of designated programs that manages and regulates the database. DBMS Hardware consists of the physical components (computer, hard drive/disk) needed before any data can be successfully stored. DBMS Data is the primary reason for the DBMS design. Remember, the DBMS was created to store and analyze data to support organizational operations and planning. DBMS Procedures are the organization's general instructions on using a DBMS to support a specific organizational process. This includes setup, installation, login, logout, management process, and report generation. Database Access Language is a simple language to write commands to either access, insert, update, or delete data stored in a database. Structured Query Language (SQL) is the most commonly used access language.
 
Several characteristics distinguish the database approach from the file-based system or approach. Among these are:

1. Self-describing: the database contains not only the database itself but also metadata that defines and describes the data and relationships between tables in the database
2. Views: a subset of the database, which is defined and dedicated for particular users of the system
3. Multi-user: allowing many users to access the same database at the same time
4. Integrity constraints: restrictions or rules that dictate what can be entered or edited in a table
5. Data independence: The system data descriptions or metadata are separated from the application programs.
   

To review, see Database Systems Concepts.

2b. Explain the objectives of data and information management and outline the database development process and software tools used to support development

• How would you distinguish a file-based system from a database?
• Why is data independence important, and what are the key features of this independence?
  

A fundamental goal of the management of information is that it be cared for in a way that allows the organization to achieve its mission and operate its systems. The use of a DBMS facilitates this management function. Because the DBMS is integrated, efficient, and robust, data becomes available to any organizational stakeholder needing access. This, in turn, supports achieving the organization's strategic objectives.
 
A database in a database management system represents a centralized repository for all of the data that is to be stored by an organization. In the past, the data associated with a particular IT application was stored with that application. We call that program-data dependence. With a database, we can eliminate that dependence. Each application can be developed independently and use the standardized structure of the database management system. Similarly, users can interact directly with the database management system. This allows for changes to be made to the operational systems without needing to change the database each time a change is made to the operational system.
 
As mentioned previously, several characteristics distinguish the database approach from the file-based system or approach. Perhaps the most significant of these is the elimination of program file independence. Users and applications both interact with the database. Users do not interact with application programs directly. This figure illustrates this idea graphically.

Notice the way that the database is represented with the large canister. This is a standard symbolic representation of a database. Notice how the various applications can represent only portions of the database. Each application will have its associated data stored within the central database. Notice also how the data associated with applications might overlap. This is because some data elements might be used in several different business processes and application programs. An example of this might be data relating to a customer. Such data might be used for product ordering, billing, and other applications.
 
We call this feature of a database management system data independence. One way to think about this is that the data and the way the data is defined are independent of the programs developed to support particular processes. The DBMS makes this possible by storing the data in an integrated database engine and requiring all access to the data from application programs to be routed through this engine.
 
To review, see Introduction to Database Management System (DBMS).

2c. Outline the database development process and software tools used to support development

• What are the critical elements of a database design process?
  

Database design is a process to facilitate the design, development, implementation, and maintenance of an enterprise DBMS. Enterprise systems are a combination of hardware and software for operational use. Organizational operations typically consist of functions and tasks necessary to implement a business process. Therefore, an enterprise DBMS manages reporting and manages more than one function within an organization.
 
The database design and development process will be a multidisciplinary and multi-user process. The ultimate goal is to create a database design that conforms to good design practices and meets the users' needs. The business process owners contribute a deep understanding of the needs of their process and the data elements required to support those needs. Staff from the larger organizational information systems department contribute a perspective on the organization's hardware, software, and network standards. Finally, the database administrator provides an understanding of the type of modeling processes available and how those modeling processes and models will lead to a good design.
 
Most database design efforts follow some standardized methodology. This methodology is often either some variant of the Systems Development Life Cycle (SDLC) or one of the more rapid iterative methodologies like Rapid Application Development (RAD).
 
The development process will also be guided by the standards developed by that organization in data governance. Different data governance policies will influence the design of database systems. For example, if the data governance standard calls for the maintenance of data for some specified period, then the development process relating to the management of data archives and backups must be adjusted to be in conformance with this governance standard.
 
To review, see Combining Data Management with Organizational Change.

2d. Describe DBMS components and administration 

• What are the main components of a database management system (DBMS)?
• What are the properties of a DBMS?
  

In addition to the physical components, such as hardware, a database will have several primary components. These would include the data entry system, the storage system, the security system, and the backup and recovery system. Each of these systems will have unique administrative requirements and require specialized administrators, policies, and procedures. For example, the archiving, backup, and recovery system will require administrative policies describing how frequently backups are to be conducted, where backups are to be stored, what level of security is required on backed-up media, and so forth. There would also likely be an administrator for the backup process with specialized knowledge, skills, and abilities.
 
The various people and roles that have access to the database will have specific privileges. These privileges define what data a particular person will be granted access to. For example, in an HR system, some users might have read-only access, some might have the ability to modify certain data elements, and so on. There may also be multiple levels of approval before a data element, such as an employee's salary, is changed. These privileges will depend on the user's role within the operational or database process. This assignment of specific privileges is also why the DBMS needs to provide robust and granular security capabilities.
 
Before the advent of relational databases, most transaction processing systems were characterized by application-specific data structures. Applications were not integrated, and thus there was no way to share data between applications. With the advent of database processing systems, it was possible to integrate and centralize the data relating to many operational processes in a single location. The management and administration of this centralized database system is the database administrator's responsibility.
 
To review, see Combining Data Management with Organizational Change.
 

2e. Design the logical and physical structure of a relational database for efficient data storage

• What is the purpose of a data model?
• What is a data dictionary, and how is it used?
  

There are many different ways that databases could be organized. These different ways of data organization are referred to as data models. A data model is a logical structure of data items and the relationships between them. Numerous models have been developed over time, but the predominant one since about the 1980s is the relational model. Relational databases are now found in all types of organizations and are the most commonly used organizational scheme for databases.
 
In a relational database, the form of data organization consists of tables (or relations). A table will consist of fields. The format of the fields defines the structure of the data stored in the table. One instance or occurrence of a group of fields is called a record.
 
The following figure illustrates this graphically. The tables (relations) are shown along with the key field (attribute) and the non-key attributes. For example, The STUDENT table (STUDENT relation) indicates that StudentID is the key field. Similarly, the COURSE table is shown with CourseID as the key field. The relationships between the tables are represented by the inclusion of the key field from one (or more) relations occurring as a foreign key in another relation. Notice how the ClassroomID field (attribute), which is the key field in the CLASSROOM relation, is included in the COURSE relation as a non-key field. We call this type of structure a foreign key that establishes the cardinality of the relationship between the two tables.

When we define a collection of data in a database model, we also talk about how the data is organized. One convenient structure to create is a data dictionary containing all the data elements' definitions and format in one convenient location. The reason each element is collected, how it figures into the structure of the database, and how applications use it provides designers with guidance as they create and modify the database over time.
 
When designing the physical structure of the disk for efficient storage and read and write access times, we have to consider the organization of the disk and how the read and write heads of the disk drive travel over the surface of the disk. Partitioning allows for the different sections of the database to be stored in a unique area on a disk drive. This partitioning process improves disk performance and supports faster read and write times for the database.
 
To review, see Combining Data Management with Organizational Change.
 

Unit 2 Vocabulary

This vocabulary list includes the terms that you will need to know to successfully complete the final exam.

• data dictionary
• data independence
• data model
• database
• database access language
• database management system (DBMS)
• enterprise system
• field
• key field
• metadata
• multi-user
• partitioning
• privileges
• record
• relation
• relational database
• self-describing
• Structured Query Language (SQL)
• table
• views