Test 1 Flashcards
What is a Database (informal Definition)
A database is a collection of related data. For example, consider the names, telephone numbers, and addresses of the people you know. Nowadays, this data is typically stored in mobile phones, which have their own simple database software. This data can also be recorded in an indexed address book or stored on a hard drive, using a personal computer and software such as Microsoft Access or Excel. This collection of related data with an implicit meaning is a database.
Data
known facts that can be recorded and that have implicit meaning.
What is a Database (Formal Definition)
A model of structures of reality
- A database represents some aspect of the real world, sometimes called the miniworld or the universe of discourse (UoD). Changes to the miniworld are reflected in the database.
- A database is a logically coherent collection of data with some inherent meaning. A random assortment of data cannot correctly be referred to as a database.
- A database is designed, built, and populated with data for a specific purpose. It has an intended group of users and some preconceived applications in which these users are interested.
In other words, a database has some source from which data is derived, some degree of interaction with events in the real world, and an audience that is actively interested in its contents.
Database Management System (DBMS)
A database management system (DBMS) is a computerized system that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications
Database System
To complete our initial definitions, we will call the database and DBMS software together a database system.
Defining a Database
Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database.
Designing a Database
Design of a new application for an existing database or design of a brand new database starts off with a phase called requirements specification and analysis. These requirements are documented in detail and transformed into a conceptual design that can be represented and manipulated using some computerized tools so that it can be easily maintained, modified, and transformed into a database implementation. (We will introduce a model called the Entity-Relationship model in Chapter 3 that is used for this purpose.)
The design is then translated to a logical design that can be expressed in a data model implemented in a commercial DBMS. (Various types of DBMSs are discussed throughout the text, with an emphasis on relational DBMSs in Chapters 5 through 9.)
The final stage is physical design, during which further specifications are provided for storing and accessing the database. The database design is implemented, populated with actual data, and continuously maintained to reflect the state of the miniworld.
meta-data
The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary
Query VS. Transaction
A query typically causes some data to be retrieved; a transaction may cause some data to be read and some data to be written into the database.
Main Characteristics of Database
- Self-describing nature of a database system
- Insulation between programs and data, and data abstraction
- Support of multiple views of the data
- Sharing of data and multiuser transaction processing
Main Characteristics of Database: Self-describing nature of a database system
A database system is considered self-describing because it not only contains the data but also metadata, which is data about data. This metadata describes the structure of the database, the types and formats of stored data, and the relationships between parts of the data. This self-describing nature allows the database system to be more flexible and adaptable to changes than traditional file-processing systems, where such metadata might be embedded in the application programs themselves.
Main Characteristics of Database: Insulation between programs and data, and data abstraction
- In a database system, there is a level of insulation (also known as program-data independence) between the data and the programs that access the data. This means that changes in the structure of the database do not necessarily require changes in the programs that access that data.
- Data abstraction refers to the process of hiding certain details of how data is stored and maintained while presenting a simple interface to the user. For example, a user can interact with the data through high-level queries without needing to know how the data is physically stored.
The characteristic that allows program-data independence and program-operation independence is called data abstraction. A DBMS provides users with a conceptual representation of data that does not include many of the details of how the data is stored or how the operations are implemented. Informally, a data model is a type of data abstraction that is used to provide this conceptual representation. The data model uses logical concepts, such as objects, their properties, and their interrelationships, that may be easier for most users to understand than computer storage concepts. Hence, the data model hides storage and implementation details that are not of interest to most database users.
Main Characteristics of Database: Support of multiple views of the data
A database system can provide multiple views of the data to different users. A view is a subset of the database or a particular presentation of the data designed for a specific purpose or user. Different users or user groups can have different views depending on their needs and privileges, allowing for a personalized interaction with the database.
Main Characteristics of Database: Sharing of data and multiuser transaction processing
- One of the key advantages of a database system over a file-processing system is the ability to allow multiple users to access and modify the data concurrently. This is essential in many environments where different users need to access the same data at the same time.
- Multiuser transaction processing refers to the system’s ability to handle and manage transactions from multiple users without letting the transactions interfere with each other. This includes maintaining data integrity and consistency even when multiple transactions are occurring simultaneously.
The DBMS must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct. For example, when several reservation agents try to assign a seat on an airline flight, the DBMS should ensure that each seat can be accessed by only one agent at a time for assignment to a passenger. These types of applications are generally called online transaction processing (OLTP) applications. A fundamental role of multiuser DBMS software is to ensure that concurrent transactions operate correctly and efficiently.
Database Operation
In some types of database systems, such as object-oriented and object-relational systems (see Chapter 12), users can define operations on data as part of the database definitions. An operation (also called a function or method) is specified in two parts.
The interface (or signature) of an operation includes the operation name and the data types of its arguments (or parameters). The implementation (or method) of the operation is specified separately and can be changed without affecting the interface. User application programs can operate on the data by invoking these operations through their names and arguments, regardless of how the operations are implemented. This may be termed program-operation independence.
Database Administrators
The DBA is responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed. The DBA is accountable for problems such as security breaches and poor system response time. In large organizations, the DBA is assisted by a staff that carries out these functions.
Database Designers
**Database designers are responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data. **These tasks are mostly undertaken before the database is actually implemented and populated with data. It is the responsibility of database designers to communicate with all prospective database users in order to understand their requirements and to create a design that meets these requirements. In many cases, the designers are on the staff of the DBA and may be assigned other staff responsibilities after the database design is completed. Database designers typically interact with each potential group of users and develop views of the database that meet the data and processing requirements of these groups. Each view is then analyzed and integrated with the views of other user groups. The final database design must be capable of supporting the requirements of all user groups.
End Users
End users are the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use. There are several categories of end users:
- Casual end users occasionally access the database, but they may need different information each time. They use a sophisticated database query interface to specify their requests and are typically middle- or high-level managers or other occasional browsers.
-
Naive or parametric end users make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database, using standard types of queries and updates—called canned transactions—that have been carefully programmed and tested. Many of these tasks are now available as mobile apps for use with mobile devices. The tasks that such users perform are varied. A few examples are:
– Bank customers and tellers check account balances and post withdrawals and deposits.
– Reservation agents or customers for airlines, hotels, and car rental companies check availability for a given request and make reservations.
– Employees at receiving stations for shipping companies enter package identifications via bar codes and descriptive information through buttons to update a central database of received and in-transit packages.
– Social media users post and read items on social media Web sites. - Sophisticated end users include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the facilities of the DBMS in order to implement their own applications to meet their complex requirements.
- Standalone users maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces. An example is the user of a financial software package that stores a variety of personal financial data.
System Analysts and Application Programmers (Software Engineers)
System analysts determine the requirements of end users, especially naive and parametric end users, and develop specifications for standard canned transactions that meet these requirements.
Application programmers implement these specifications as programs; then they test, debug, document, and maintain these canned transactions. Such analysts and programmers—commonly referred to as software developers or software engineers—should be familiar with the full range of capabilities provided by the DBMS to accomplish their tasks.
Workers behind the Scene
In addition to those who design, use, and administer a database, others are associated with the design, development, and operation of the DBMS software and system environment. These persons are typically not interested in the database content itself. We call them the workers behind the scene, and they include the following categories:
- DBMS system designers and implementers design and implement the DBMS modules and interfaces as a software package. A DBMS is a very complex software system that consists of many components, or modules, including modules for implementing the catalog, query language processing, interface processing, accessing and buffering data, controlling concurrency, and handling data recovery and security. The DBMS must interface with other system software, such as the operating system and compilers for various programming languages.
- Tool developers design and implement tools—the software packages that facilitate database modeling and design, database system design, and improved performance. Tools are optional packages that are often purchased separately. They include packages for database design, performance monitoring, natural language or graphical interfaces, prototyping, simulation, and test data generation. In many cases, independent software vendors develop and market these tools.
- Operators and maintenance personnel (system administration personnel) are responsible for the actual running and maintenance of the hardware and software environment for the database system.
When not to use DBMS
Relational Model- Data Structures
Schema:
- The table name, column names, and data types represent the schema.
- Represents the structure of the data
- The schema represents aspects of the data that will stay stable over time (i.e. will not change)
- We call each object in the schema—such as STUDENT or COURSE—a schema construct.
State/Snapshot
- Represented by the rows of the table reflects aspects that are dynamic and change over time.
- Represents the current state of reality that is modelled by the table
The distinction between database schema and database state is very important. When we define a new database, we specify its database schema only to the DBMS. At this point, the corresponding database state is the empty state with no data. We get the initial state of the database when the database is first populated or loaded with the initial data. From then on, every time an update operation is applied to the database, we get another database state. At any point in time, the database has a current state. The DBMS is partly responsible for ensuring that every state of the database is a valid state—that is, a state that satisfies the structure and constraints specified in the schema. Hence, specifying a correct schema to the DBMS is extremely important and the schema must be designed with utmost care. The DBMS stores the descriptions of the schema constructs and constraints—also called the meta-data—in the DBMS catalog so that DBMS software can refer to the schema whenever it needs to. valid state—that is, a state that satisfies the structure and constraints specified in the schema.
The schema is sometimes called the intension, and a database state is called an extension of the schema.
Relational Model- Constraints
Constraints are not expressed by the table structure or data types of the column
Data Model-Operations
Operations support change and retrieval of data
Data Models: Keys and Identifiers
Keys are uniqueness constraints
Integrity and Consistency
Integrity: For row with Louise Mark, it is assumed the person is a boy. This is a poor reflection of reality
Consistency: For 1st row, notice the cities are different when comparing city and address between the two tables. This is bad consistency.
Named-Based Vs Surrogate-Based Representation
A surrogate:
- System-generated artificial identifier for an entity.
- Represents an entity of the real world inside the database
- is immutable by the application programs
Surrogate-Based Representation is preferred. Think of a lady who changes her last name or someone who moves. In both instances, we have no way of telling if that is a different person or the same person.
Controlled Redundancy & Denormalization
However, in practice, it is sometimes necessary to use** controlled redundancy** to improve the performance of queries. For example, we may store Student_name and Course_number redundantly in a GRADE_REPORT file (Figure 1.6(a)) because whenever we retrieve a GRADE_REPORT record, we want to retrieve the student name and course number along with the grade, student number, and section identifier. By placing all the data together, we do not have to search multiple files to collect this data. This is known as denormalization.
In such cases, the DBMS should have the capability to control this redundancy in order to prohibit inconsistencies among the files. This may be done by automatically checking that the Student_name–Student_number values in any GRADE_REPORT record in Figure 1.6
(a) match one of the Name–Student_number values of a STUDENT record (Figure 1.2). Similarly, the Section_identifier–Course_number values in GRADE_REPORT can be checked against SECTION records. Such checks can be specified to the DBMS during database design and automatically enforced by the DBMS whenever the GRADE_REPORT file is updated. Figure 1.6(b) shows a GRADE_REPORT record that is inconsistent with the STUDENT file in Figure 1.2; this kind of error may be entered if the redundancy is not controlled. Can you tell which part is inconsistent?
ANSI/SPARC 3-Level DBB Architecture
This architecture divides the database system into three levels:
External Level (or View Level):
- The highest level of the ANSI/SPARC database architecture.
- It consists of several external schemas or user views.
- Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group.
- This level provides a customized view of the data based on the specific needs and rights of different users. For example, a sales manager and a human resources manager would have different views of the same database.
- It helps in achieving data security, since users can be limited to accessing only certain parts of the database.
Conceptual Level (or Logical Level):
- The middle level of the architecture.
- This level contains the conceptual schema which describes the structure and constraints for the whole database. This schema is a complete view of the data requirements of the organization that the database serves.
- It defines what data is stored in the database and the relationships among those data.
- The conceptual level provides a level of abstraction that hides the details of how the data is stored and managed (physical level details).
- It ensures that the database’s structure can be modified without affecting the users or user applications, as changes are managed through the external level.
Internal Level (or Physical Level):
- The lowest level of the architecture.
- It includes the internal schema, which describes the physical storage structures and access paths (indexes, for example). Essentially, it specifies how the data is actually stored in the database.
- This level deals with the efficiency of the database, like how quickly and efficiently data can be retrieved.
- It allows the database administrator to optimize database performance without affecting the higher-level schemas.
Overall, the ANSI/SPARC 3-level architecture provides a framework that separates the user’s view of the database (external level), the way the database is logically organized (conceptual level), and how the data is physically stored (internal level). This separation enhances the flexibility, security, and data independence of the database system.
External Schema
Definition and Purpose:
- The external schema, also known as the view level, consists of various user views of the database.
- Each external schema presents a tailored view of the database to meet the specific needs and permissions of individual users or user groups. It filters and formats the database information so that users can focus on the most relevant data for their tasks.
Customization and Security:
- The external schemas are highly customizable. For instance, a database may have one external schema for a sales department, showing sales-related data, and another for the human resources department, showing employee data.
- This customization helps in implementing security measures, as users are restricted to accessing only the data they are authorized to see. It helps in enforcing data privacy and confidentiality policies.
Data Independence:
- External schemas contribute to data independence by isolating the user applications from changes in the conceptual schema. If the structure of the database (conceptual schema) changes, these changes do not necessarily reflect in the external schemas.
- This means that users can continue to interact with the database through their familiar views even if the underlying database structure is altered.
Interface with Applications:
- Applications interact with the database through these external schemas. They serve as the interface between the users and the database.
- This level translates requests and responses between the database’s conceptual schema and the user’s view, ensuring that users can access and manage data as per their specific context and requirements.
Use in Database Design:
- When designing a database, defining external schemas is a critical step to ensure that the database serves its diverse user base effectively.
- It involves understanding the different requirements of various user groups and creating views that cater to these requirements efficiently.
In summary, external schemas in a database system provide customized views for different user groups, ensuring security, data independence, and ease of use. They allow users to interact with the database in a way that is most relevant to their specific needs, without exposing the entire database structure or unnecessary data.
Conceptual Schema
At the conceptual level, the only thing that is visible to the query language is the table and the columns in the table
Conceptual Schema Overview:
- The conceptual schema is a representation of the entire content of a database at a high level of abstraction, typically the logical level.
- It includes all the entities, attributes, relationships, constraints, and other elements that are relevant to the organization’s data model.
What Applications See at the Conceptual Schema Level:
- At this level, applications are exposed to the structures of the database, such as the names of tables and their columns (attributes).
- It provides a map of entities and their relationships, which is crucial for understanding how data is organized and interconnected within the database.
- However, it abstracts away the physical storage details. It doesn’t reveal how the data is stored, indexed, or the specific physical data paths.
Importance for Applications:
- The conceptual schema is important for application development and interaction because it provides a clear and consistent view of the data structure without burdening the application with underlying physical storage details.
- This allows applications to be developed and function independently of the physical database design, promoting database longevity and flexibility in the face of changes to the physical layout.
Physical Data Independence
The three levels of the ANSI-SPARC Architecture provides two types of independence. The first one is Physical Data Independence. Database technology allows you to make that measurement almost perfect.
Generally, physical data independence exists in most databases and file environments where physical details, such as the exact location of data on disk, and hardware details of storage encoding, placement, compression, splitting, merging of records, and so on are hidden from the user. Applications remain unaware of these details.
Logical Data Independence
The second part of independence provided by ANSI Sparc Architecture. It is more difficult than Physical Data Independence. The reason why is because the external schema is logically derived from the conceptual schema
logical data independence is harder to achieve because it allows structural and constraint changes without affecting application programs—a much stricter requirement.
