items are added to the inventory, and stock quantities are updated as purchases are transacted. ✓ A database management system (DBMS) is a computerized system that enables users to create and maintain a database. ✓ a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications. ✓ The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary; it is called meta-data ✓ Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS. ✓ Manipulating a database includes functions such as querying the database to retrieve specific data, updating the database to reflect changes in the mini-world, and generating reports from the data. ✓ Sharing a database allows multiple users and programs to access the database simultaneously. ✓ An application program accesses the database by sending queries or requests for data to the DBMS. ✓ 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.
DBMS: Example
Characteristics of the database approach
✓ In traditional file processing, each user defines and implements the files needed for a specific software application as part of programming the application. ✓ In the database approach, a single repository maintains data that is defined once and then accessed by various users repeatedly through queries, transactions, and application programs. ✓ The main characteristics of the database approach: ✓ 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 multi-user transaction processing ✓ Meta-data contains the catalogue. ✓ A complete definition or description of the database structure and constraints is stored in the DBMS catalog, which contains information such as the structure of each file, the type, and storage format of each data item, and various constraints on the data. ✓ NoSQL systems, do not require meta-data: the data is stored as self-describing data ✓ XXXXNNNN is used to define a type with four alphabetic characters followed by four numeric digits.
Insulation between programs and data, and data abstraction
✓ In traditional file processing, the structure of data files is embedded in the application programs ✓ The structure of data files is stored in the DBMS catalogue separately from the access programs: program-data independence. ✓ In object-oriented and object-relational systems, 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): the operation name and the data types of its arguments ✓ The implementation (or method) of the operation
Sharing of data and multi-user transaction processing
✓ A multi-user DBMS, as its name implies, must allow multiple users to access the database at the same time. ✓ The DBMS must include concurrency control software to ensure that when several users are trying to update the same data in a controlled manner, the result of the updates is correct. ✓ A transaction is an executing program or process that includes one or more database accesses, such as reading or updating of database records. ✓ The DBMS must enforce several transaction properties: ✓ The isolation property ensures that each transaction appears to execute in isolation from other transactions ✓ The atomicity property ensures that either all the database operations in a transaction are executed or none are
Actors on scene
Database Administrator
✓ In a database environment: ✓ primary resource → database itself, ✓ Secondary resource → DBMS and related software ✓ Administering these resources is the responsibility of the database administrator (DBA). ✓ The DBA is responsible for ○ authorizing access to the database, ○ coordinating and monitoring its use, and ○ acquiring software and hardware resources as needed
Database Designer
Responsible for ✓ Identifying the data to be stored in the database ✓ Choosing appropriate structures to represent and store this data ✓ Communicating with all prospective database users in order to understand their requirements ✓ Creating a design that meets user requirements. ✓ Developing views of the database: 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.
There are several categories of end-users: ✓ Casual end users occasionally access the database, but they may need different information each time. ✓ Naive or parametric end users constantly querying and updating the database, using standard types of queries and updates— called canned transactions—that have been carefully programmed and tested. Examples are: ✓ Bank customers and tellers check account balances and post withdrawals and deposits. ✓ Social media users post and read items on social media Websites. ✓ 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 (users of a financial software package) maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces.
System Analysts and Application Programmers (Software Engineers)
System analysts determine ✓ The requirements of end-users, especially naive and parametric end users ✓ Develop specifications for standard canned transactions that meet these requirements. ✓ Application programmers (software developers or software engineers) implement these specifications as programs; then they Test, Debug, Document, and Maintain these canned transactions. Data models Data abstraction generally refers to the suppression of details of data organization and storage, and the highlighting of the essential features for an improved understanding of data. A data model - a collection of concepts that can be used to describe the structure of a database ✓ provides the necessary means to achieve this abstraction. Structure of a database ✓ the data types ✓ relationships ✓ constraints that apply to the data
Categories
High-level or conceptual data models provide concepts that are close to the way many users perceive data. Use concepts such as
Degree - The total number of attributes in the relation is called the degree of the relation.
Cardinality - Total number of rows present in the Table.
Column - The column represents the set of values for a specific attribute.
Relation instance - Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.
Relation key - Every row has one, two, or multiple attributes, which is called the relation key.
Attribute domain - Every attribute has some pre-defined value and scope which is known as attribute domain
Hierarchical Model
✓ This database model organizes data into a tree-like structure, with a single root, to which all the other data is linked. ✓ The hierarchy starts from the Root data, and expands like a tree, adding child nodes to the parent nodes. ✓ In this model, a child node will only have a single parent node.
Advantages and Limitations
Advantages
✓ A user can retrieve data very quickly due to the presence of explicit links between the table structures. ✓ The referential integrity is built-in and automatically enforced due to which a record in a child table must be linked to an existing record in a parent table, along with that if a record is deleted in the parent table then that will cause all associated records in the child table to be deleted as well.
Disadvantages
✓ When a user needs to store a record in a child table that is currently unrelated to any record in a parent table, it gets difficult in recording, and the user must record an additional entry in the parent table.
✓ This type of database cannot support complex relationships, and there is also a problem of redundancy, which can result in producing inaccurate information due to the inconsistent recording of data at various sites.
Network
✓ This is an extension of the Hierarchical model. ✓ In this model, data is organized more like a graph, and are allowed to have more than one parent node. ✓ Data is more related as more relationships are established in this database model. ✓ Also, as the data is more related, hence accessing the data is also easier and fast. ✓ This database model was used to map many-to-many data relationships.
Advantages and Limitations
Advantages
✓ Because it has the many-many relationship, the network database model can easily be accessed in any table record in the database ✓ For more complex data, it is easier to use because of the multiple relationships founded among its data ✓ Easier to navigate and search for information because of its flexibility
Disadvantage
✓ Difficult for first-time users ✓ Difficulties with alterations of the database because when the information entered can alter the entire database Schemas, Instances ✓ The description of a database is called the database schema, which is specified during database design and is not expected to change frequently ✓ A displayed schema is called a schema diagram. ✓ The diagram displays the structure of each record type but not the actual instances of records.
The Database System Environment
DBMS Component Modules
✓ The database and the DBMS catalog are usually stored on a disk. ✓ Access to the disk is controlled primarily by the operating system (OS), which schedules disk read/write. ✓ Many DBMSs have their own buffer management module to schedule disk read/write. ✓ A higher-level stored data manager module of the DBMS controls access to DBMS information that is stored on disk, whether it is part of the database or the catalog.
Database System Utilities
Database utilities help the DBA manage the database system. Common utilities have the following types of functions: ✓ Loading : A loading utility is used to load existing data files - such as text files or sequential files - into the database. ✓ Backup : A backup utility creates a backup copy of the database, usually by dumping the entire database onto tape or other mass storage medium. Incremental backups are also often used, where only changes since the previous backup are recorded. ✓ Database storage reorganization : This utility can be used to reorganize a set of database files into different file organizations and create new access paths to improve performance. ✓ Performance monitoring : Such a utility monitors database usage and provides statistics to the DBA. Overall System Structure/Architecture
Basic Client/Server Architectures
✓ The concept of client/server architecture assumes an underlying framework that consists of many PCs/workstations and mobile devices as well as a smaller number of server machines, connected via wireless networks or LANs and other types of computer networks. ✓ A client in this framework is typically a user machine that provides user interface capabilities and local processing. ✓ A server is a system containing both hardware and software that can provide services to the client machines, such as file access, printing, archiving, or database access. ✓ Two main types of basic DBMS architectures were created on this underlying client/server framework: ○ Two-tier & Three-tier.
Two-Tier Client/Server Architectures
✓ Two-tier architecture: the software components are distributed over two systems: client and server ✓ the query and transaction functionality related to SQL processing remained on the server side: query server or transaction server. ✓ In an RDBMS, the server is also often called an SQL server. ✓ The user interface programs and application programs can run on the client-side. ✓ A standard called Open Database Connectivity (ODBC) provides an application programming interface (API), which allows client-side programs to call the DBMS, as long as both client and server machines have the necessary software installed. ✓ The advantages of this architecture are its simplicity and seamless compatibility with existing systems.
Three-Tier Client/Server Architectures
✓ Many Web applications use an architecture called the three-tier architecture, which adds an intermediate layer between the client and the database server, as illustrated in Figure (a). ✓ This intermediate layer or middle tier is called the application server or the Web server, depending on the application. ✓ Figure (b) shows another view of the three-tier architecture used by database and other application package vendors. ✓ The presentation layer displays information to the user and allows data entry. The business logic layer handles intermediate rules and constraints before data is passed up to the user or down to the DBMS. ✓ The bottom layer includes all data management services. ✓ The middle layer can also act as a Web server. Querying ✓ A query is a way of requesting information from the database. ✓ A database query can be ○ A select query: for retrieving data ○ An action query. requests additional actions to be performed on the data, like deletion, insertion, and updating. ✓ For example, a manager can perform a query to select the employees who were hired 5 months ago. The results could be the basis for creating performance evaluations.
FROM table_name; ✓ Select data with a specific condition SELECT column1, column2, ... FROM table_name WHERE condition; ✓ Update query: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; ✓ Delete query: DELETE FROM table_name WHERE condition; Control Data ✓ Transactions: COMMIT ; ROLLBACK ; Relational Algebra ✓ The basic set of operations for the formal relational model. ✓ These operations enable a user to specify basic retrieval requests as relational algebra expressions. ✓ The result of a retrieval query is a new relation. ✓ A sequence of relational algebra operations forms a relational algebra expression, whose result will also be a relation that represents the result of a database query.
Importance
✓ Provides a formal foundation for relational model operations. ✓ Used as a basis for implementing and optimizing queries in the query processing and optimization modules that are integral parts of RDBMS. ✓ Some of its concepts are incorporated into the SQL standard query language for RDBMS
Unary Relational Operations
SELECT
✓ The SELECT operator is unary; that is, it is applied to a single relation. ✓ Used to choose a subset of the tuples from a relation that satisfies a selection condition: select some rows
✓ A filter that keeps only those tuples that satisfy a qualifying condition. ✓ Can also be visualized as a horizontal partition of the relation into two sets of tuples ✓ Denoted by: σ
Rename
✓ We can explicitly show the sequence of operations, giving a name to each intermediate relation, and using the assignment operation, denoted by ← (left arrow), as follows: ○ DEP5_EMPS ← σDno=5(EMPLOYEE) ○ RESULT ← πFname, Lname, Salary(DEP5_EMPS) ✓ To rename the attributes in a relation, we simply list the new attribute names in parentheses, as in the following example: ○ TEMP ← σDno=5(EMPLOYEE) ○ R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP) ✓ We can rename either the relation name or the attribute names, or both—as a unary operator.
✓ The general RENAME operation, when applied to a relation R of degree n, is denoted by any of the following three forms: ○ ρS(B1, B2, ..., Bn)(R): renames both the relation and its attributes ○ ρS(R): renames the relation only ○ ρ(B1, B2, ..., Bn)(R): renames the attributes only ○ where the symbol ρ (rho) is used to denote the RENAME operator, S is the new relation name, and B1, B2, …, Bn are the new attribute names.
UNION
✓ The result of this operation, denoted by R ∪ S, is a relation that includes all tuples that are either in R or in S or in both R and S. ✓ Duplicate tuples are eliminated. ✓ For example, to retrieve the Social Security numbers of all employees who either work in department 5 or directly supervise an employee who works in department 5: ○ DEP5_EMPS ← σDno=5(EMPLOYEE) ○ RESULT1 ← πSsn(DEP5_EMPS) ○ RESULT2(Ssn) ← πSuper_ssn(DEP5_EMPS) ○ RESULT ← RESULT1 ∪ RESULT
INTERSECTION and MINUS
✓ INTERSECTION: The result of this operation, denoted by R ∩ S, is a relation that includes all tuples that are in both R and S. ✓ SET DIFFERENCE (or MINUS): The result of this operation, denoted by R – S, is a relation that includes all tuples that are in R but not in S. ✓ UNION and INTERSECTION are commutative operations: ○ R ∪ S = S ∪ R and ○ R ∩ S = S ∩ R ✓ UNION and INTERSECTION are associative operations: ○ R ∪ (S ∪ T ) = (R ∪ S) ∪ T and ○ (R ∩ S) ∩ T = R ∩ (S ∩ T) ✓ MINUS operation is not commutative: ○ R − S ≠ S − R
