Untitled Flashcards Set

Lecture Notes
- Jan 21, 2025
	- Reviewed Syllabus
	- Introduction
	
- Jan 23, 2025
	- Chapter 1 Databases and Database Users
		- traditional database
			Oracle, Sybase, Informix, Postgresql
		- traditional database applications, in which most of the information that is stored and accessed is either textual or numeric.
		
		- New types of database systems, often referred to as big data
		storage systems, or NOSQL systems
		
	1.1 Introduction
		A database is a collection of related data.1 By data, we mean known facts that can be recorded and that have implicit meaning. For example, consider the names, telephone numbers, and addresses of the people you know. 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.

		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.

	1.2 An Example
		Figure 1.2: A database that stores student and course information.
		
- Jan 28, 2025
	- Chapter 1 Databases and Database Users <Continued>
	
		- Figure 1.1 A simplified database system environment.
		- Figure 1.2 A database that stores student and course information.
		- Figure 1.3 An example of a database catalog for the database in Figure 1.2
		- Figure 1.5 Two views derived from the database in Figure 1.2. (a) The TRANSCRIPT view. (b) The COURSE_PREREQUISITES view.
		- Figure 1.6 Redundant storage of Student_name and Course_name in GRADE_REPORT. (a) Consistent data. (b) Inconsistent record.
		
		
		Databases and database systems are an essential component of life in modern society: most of us encounter several activities every day that involve some interaction with a database. For example, if we go to the bank to deposit or withdraw funds, if we make a hotel or airline reservation, if we access
		computerized library catalog to search for a bibliographic item, or if we purchase something online—such as a book, toy, or computer—chances are that our activities will involve someone or some computer program accessing a database. Even purchasing items at a supermarket often automatically updates the database that holds the inventory of grocery items.
		
		These interactions are examples of what we may call traditional database applications, in which most of the information that is stored and accessed is either textual or numeric. In the past few years, advances in technology have led to exciting new applications of database systems. The proliferation of social media Web sites, such as Facebook, Twitter, and Flickr, among many others, has required the creation of huge databases that store nontraditional data, such as posts, tweets, images, and video clips. New types of database systems, often referred to as big data storage systems, or NOSQL systems, have been created to manage data for social media applications. These types of systems are also used by companies such as Google, Amazon, and Yahoo, to manage the data required in their Web search engines, as well as to provide cloud storage, whereby users are provided with storage capabilities on the Web for managing all types of data including documents, programs, images, videos and emails.
		
		To complete our initial definitions, we will call the database and DBMS software together a database system. Figure 1.1 illustrates some of the concepts we have discussed so far.
		
		1.1 Introduction
			A database is a collection of related data. By data, we mean known facts that can be recorded and that have implicit meaning. For example, consider the names, telephone numbers, and addresses of the people you know.
			
			The common use of the term database is usually more restricted. A database has the following implicit properties:
				â–  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.
				
			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. 
			
			- Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database. 
			- 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 miniworld, 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.
			- Other important functions provided by the DBMS include protecting the database and maintaining it over a long period of time. Protection includes system protection against hardware or software malfunction (or crashes) and security protection against unauthorized or malicious access.
			
		1.2 An Example
			Let us consider a simple example that most readers may be familiar with: a UNIVERSITY database for maintaining information concerning students, courses, and grades in a university environment. Figure 1.2 shows the database structure and a few sample data records.
			The database is organized as five files, each of which stores data records of the same type.
			
			To define this database, we must specify the structure of the records of each file by specifying the different types of data elements to be stored in each record. 
			
			In Figure 1.2, each STUDENT record includes data to represent the student’s Name, Student_number, Class (such as freshman or ‘1’, sophomore or ‘2’, and so forth), and Major (such as mathematics or ‘MATH’ and computer science or ‘CS’);
		
			Database manipulation involves querying and updating. Examples of queries are as follows:
				■ Retrieve the transcript—a list of all courses and grades—of ‘Smith’
				■ List the names of students who took the section of the ‘Database’ course offered in fall 2008 and their grades in that section
				■ List the prerequisites of the ‘Database’ course
			
			Examples of updates include the following:
				■ Change the class of ‘Smith’ to sophomore
				■ Create a new section for the ‘Database’ course for this semester
				■ Enter a grade of ‘A’ for ‘Smith’ in the ‘Database’ section of last semester
				
			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. 
			
			- Conceptual design
				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.) 
				
			- Logical design
				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.)
			
			- Physical design
				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.
				
		1.3 Characteristics of the Database Approach
			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 versus the file-processing approach are the following:
				â–  Self-describing nature of a database system
					A fundamental characteristic of the database approach is that the database system contains not only the database itself but also a complete definition or description of the database structure and constraints. 
					This definition 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. 
					The information stored in the catalog is called meta-data, and it describes the structure of the primary database (Figure 1.1). 
					
					It is important to note that some newer types of database systems, known as NOSQL systems, do not require meta-data. Rather the data is stored as self-describing data that includes the data item names and data values together in one structure (see Chapter 24).
					
					For the example shown in Figure 1.2, the DBMS catalog will store the definitions of all the files shown. 
					Figure 1.3 shows some entries in a database catalog. Whenever a request is made to access, say, the Name of a STUDENT record, the DBMS software refers to the catalog to determine the structure of the STUDENT file and the position  and size of the Name data item within a STUDENT record.
					
				â–  Insulation between programs and data, and data abstraction,
					A file access program may be written in such a way that it can access only STUDENT records of the structure shown in Figure 1.4. If we want to add another piece of data to each STUDENT record, say the Birth_date, such a program will no longer work and must be changed. By contrast, in a DBMS environment, we only need to change the description of STUDENT records in the catalog (Figure 1.3) to reflect the inclusion of the new data item Birth_date; no programs are changed. The next time a DBMS program refers to the catalog, the new structure of STUDENT records will be accessed and used.
					
				â–  Support of multiple views of the data
					A multiuser DBMS whose users have a variety of distinct applications must provide facilities for defining multiple views. For example, one user of the database of Figure 1.2 may be interested only in accessing and printing the transcript of each student; the view for this user is shown in Figure 1.5(a). A second user, who is interested only in checking that students have taken all the prerequisites of each course for which the student registers, may require the view shown in Figure 1.5(b).
					
				â–  Sharing of data and multiuser transaction processing
					A multiuser DBMS, as its name implies, must allow multiple users to access the database at the same time. This is essential if data for multiple applications is to be integrated and maintained in a single database. 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.
					
					The concept of a transaction has become central to many database applications. A transaction is an executing program or process that includes one or more database accesses, such as reading or updating of database records. 
					
					Each transaction is supposed to execute a logically correct database access if executed in its entirety without interference from other transactions. The DBMS must enforce several transaction properties. The isolation property ensures that each transaction appears to execute in isolation from other transactions, even though hundreds of transactions may be executing concurrently. The atomicity property ensures that either all the database operations in a transaction are executed or none are. 
					
		1.4 Actors on the Scene
			1.4.1 Database Administrators
				In a database environment, the primary resource is the database itself, and the secondary resource is the 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. The DBA is accountable for problems such as security breaches and poor system response time.
				
			1.4.2 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
				
			1.4.3 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
					
				■ 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.
					 - 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.
				
			1.4.4 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.
				
		1.6 Advantages of Using the DBMS Approach
		
			1.6.1 Controlling Redundancy
				In traditional software development utilizing file processing, every user group maintains its own files for handling its data-processing applications. For example, consider the UNIVERSITY database example of Section 1.2; here, two groups of users might be the course registration personnel and the accounting office. In the traditional approach, each group independently keeps files on students. The accounting office keeps data on registration and related billing information, whereas the registration office keeps track of student courses and grades. Other groups may further duplicate some or all of the same data in their own files. This redundancy in storing the same data multiple times leads to several problems.
				
				Normalization
					In the database approach, the views of different user groups are integrated during database design. Ideally, we should have a database design that stores each logical data item—such as a student’s name or birth date—in only one place in the database. This is known as data normalization, and it ensures consistency and saves storage space.	
				
				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.
					
			1.6.2 Restricting Unauthorized Access
				When multiple users share a large database, it is likely that most users will not be authorized to access all information in the database. For example, financial data such as salaries and bonuses is often considered confidential, and only authorized persons are allowed to access such data. In addition, some users may only be permitted to retrieve data, whereas others are allowed to retrieve and update. Hence, the type of access operation—retrieval or update—must also be controlled.
				
				A DBMS should provide a security and authorization subsystem, which the DBA uses to create accounts and to specify account restrictions. Then, the DBMS should enforce these restrictions automatically.
					
			1.6.4 Providing Storage Structures and Search Techniques for Efficient Query Processing
				Database systems must provide capabilities for efficiently executing queries and updates. Because the database is typically stored on disk, the DBMS must provide specialized data structures and search techniques to speed up disk search for the desired records. Auxiliary files called indexes are often used for this purpose. Indexes are typically based on tree data structures or hash data structures that are suitably modified for disk search. In order to process the database records needed by a particular query, those records must be copied from disk to main memory. Therefore, the DBMS often has a buffering or caching module that maintains parts of the database in main memory buffers. In general, the operating system is responsible for disk-to-memory buffering. However, because data buffering is crucial to the DBMS performance, most DBMSs do their own data buffering.
				
				The query processing and optimization module of the DBMS is responsible for choosing an efficient query execution plan for each query based on the existing storage structures. The choice of which indexes to create and maintain is part of physical database design and tuning, which is one of the responsibilities of the DBA staff.
				
			1.6.5 Providing Backup and Recovery
				A DBMS must provide facilities for recovering from hardware or software failures. The backup and recovery subsystem of the DBMS is responsible for recovery. For example, if the computer system fails in the middle of a complex update transaction, the recovery subsystem is responsible for making sure that the database is restored to the state it was in before the transaction started executing. Disk backup is also necessary in case of a catastrophic disk failure.
				
			1.6.8 Enforcing Integrity Constraints
				Most database applications have certain integrity constraints that must hold for the data. A DBMS should provide capabilities for defining and enforcing these constraints. The simplest type of integrity constraint involves specifying a data type for each data item. 
				For example, in Figure 1.3, we specified that the value of the Class data item within each STUDENT record must be a one-digit integer and that the value of Name must be a string of no more than 30 alphabetic characters. To restrict the value of Class between 1 and 5 would be an additional constraint that is not shown in the current catalog. 
				
				Referential integrity constraint
					A more complex type of constraint that frequently occurs involves specifying that a record in one file must be related to records in other files. For example, in Figure 1.2, we can specify that every section record must be related to a course record. This is known as a referential integrity constraint. 
					
				Key or uniqueness constraint
					Another type of constraint specifies uniqueness on data item values, such as every course record must have a unique value for Course_number. This is known as a key or uniqueness constraint. 
					
				These constraints are derived from the meaning or semantics of the data and of the miniworld it represents. It is the responsibility of the database designers to identify integrity constraints during database design. 
				
				Some constraints can be specified to the DBMS and automatically enforced. Other constraints may have to be checked by update programs or at the time of data entry. For typical large applications, it is customary to call such constraints business rules.
				
			1.7 A Brief History of Database Applications
				- Read for reference ONLY; will not be in the exam
				
	CRUD: Create, Retrieve, Update, Delete 
	SQL:  insert, select, update, delete 
	
- Jan 30, 2025
	- Continued & completed Chapter 1
	
- Feb 04, 2025
	- Chapter 2 Database System Concepts and Architecture
	- 2.1 Data Models, Schemas, and Instances
		- 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.
		By structure of a database we mean the data types, relationships, and constraints that apply to the data. Most data models also include a set of basic operations for specifying retrievals and updates on the database.
		
		- 2.1.1 Categories of Data Models
			High-level or conceptual data models provide concepts that are close to the way many users perceive data, whereas low-level or physical data models provide concepts that describe the details of how data is stored on the computer storage media, typically magnetic disks.
			
			Conceptual data models use concepts such as entities, attributes, and relationships. An entity represents a real-world object or concept, such as an employee or a project from the miniworld that is described in the database. An attribute represents some property of interest that further describes an entity, such as the employee’s name or salary. A relationship among two or more entities represents an association among the entities, for example, a works-on relationship between an employee and a project. 
			
			Physical data models describe how data is stored as files in the computer by representing information such as record formats, record orderings, and access paths. An access path is a search structure that makes the search for particular database records efficient, such as indexing or hashing.
			
			Another class of data models is known as self-describing data models. The data storage in systems based on these models combines the description of the data with the data values themselves. In traditional DBMSs, the description (schema) is separated from the data. These models include XML (see Chapter 12) as well as many of the key-value stores and NOSQL systems (see Chapter 24) that were recently created for managing big data.
		
		2.1.2 Schemas, Instances, and Database State
			In a data model, it is important to distinguish between the description of the database and the database itself. The description of a database is called the database schema, which is specified during database design and is not expected to change frequently.
			Most data models have certain conventions for displaying schemas as diagrams. A displayed schema is called a schema diagram. 
			
			Figure 2.1 shows a schema diagram for the database shown in Figure 1.2; the diagram displays the structure of each record type but not the actual instances of records. We call each object in the schema—such as STUDENT or COURSE—a schema construct.
			
			The data in the database at a particular moment in time is called a database state or snapshot. Every time an update operation is applied to the database, we get another database 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.
			
			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. The schema is sometimes called the intension, and a database state is called an extension of the schema.
			
	2.2 Three-Schema Architecture and Data Independence
		2.2.1 The Three-Schema Architecture
			Figure 2.2
			
	2.3 Database Languages and Interfaces
		2.3.1 DBMS Languages
			- Data definition language (DDL), is used by the DBA and by database designers to define both conceptual and internal schemas. The DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog
			
			- Once the database schemas are compiled and the database is populated with data, users must have some means to manipulate the database. Typical manipulations include retrieval, insertion, deletion, and modification of the data. The DBMS provides a set of operations or a language called the data manipulation language (DML) for these purposes.
			
		2.3.2 DBMS Interfaces
			- Menu-based Interfaces for Web Clients or Browsing
			- Apps for Mobile Devices
			- Forms-based Interfaces
			- Graphical User Interfaces
			- Natural Language Interfaces
			- Keyword-based Database Search
			- Speech Input and Output
			- Interfaces for Parametric Users
			- Interfaces for the DBA
				
	2.4 The Database System Environment
		A DBMS is a complex software system. In this section we discuss the types of software components that constitute a DBMS and the types of computer system software with which the DBMS interacts.
		
		2.4.1 DBMS Component Modules Figure 2.3 illustrates, in a simplified form, the typical DBMS components. The figure is divided into two parts. The top part of the figure refers to the various users of the database environment and their interfaces. The lower part shows the internal modules of the DBMS responsible for storage of data and processing of transactions.
		
		Figure 2.3 Component modules of a DBMS and their interactions.
			- The DDL compiler processes schema definitions, specified in the DDL, and stores descriptions of the schemas (meta-data) in the DBMS catalog. The catalog includes information such as the names and sizes of files, names and data types of data items, storage details of each file, mapping information among schemas, and constraints.
			
			- The queries are parsed and validated for correctness of the query syntax, the names of files and data elements, and so on by a query compiler that compiles. them into an internal form. This internal query is subjected to query optimization. 
			Among other things, the query optimizer is concerned with the rearrangement and possible reordering of operations, elimination of redundancies, and use of efficient search algorithms during execution. It consults the system catalog for statistical and other physical information about the stored data and generates executable code that performs the necessary operations for the query and makes calls on the runtime processor.
			
			- Application programmers write programs in host languages such as Java, C, or C++ that are submitted to a precompiler. The precompiler extracts DML commands from an application program written in a host programming language. These commands are sent to the DML compiler for compilation into object code for database access. The rest of the program is sent to the host language compiler. The object codes for the DML commands and the rest of the program are linked, forming a canned transaction whose executable code includes calls to the runtime database processor. It is also becoming increasingly common to use scripting languages such as PHP and Python to write database programs.
			
			- In the lower part of Figure 2.3, the runtime database processor executes (1) the privileged commands, (2) the executable query plans, and (3) the canned transactions with runtime parameters. It works with the system catalog and may update it with statistics. It also works with the stored data manager, which in turn uses basic operating system services for carrying out low-level input/output (read/write) operations between the disk and main memory. The runtime database processor handles other aspects of data transfer, such as management of buffers in the main memory. Some DBMSs have their own buffer management module whereas others depend on the OS for buffer management. We have shown concurrency control and backup and recovery systems separately as a module in this figure. They are integrated into the working of the runtime database processor for purposes of transaction management.
			
		2.4.2 Database System Utilities
			In addition to possessing the software modules just described, most DBMSs have database utilities that 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. The	backup copy can be used to restore the database in case of catastrophic disk failure. Incremental backups are also often used, where only changes since the previous backup are recorded. Incremental backup is more complex, but saves storage space.

			â–  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. The DBA uses the statistics in making decisions
			such as whether or not to reorganize files or whether to add or drop indexes to improve performance.

			Other utilities may be available for sorting files, handling data compression, monitoring access by users, interfacing with the network, and performing other functions.
			
	2.5 Centralized and Client/Server Architectures for DBMSs
		2.5.1 Centralized DBMSs Architecture Architectures for DBMSs have followed trends similar to those for general computer system architectures. Older architectures used mainframe computers to provide the main processing for all system functions, including user application programs and user interface programs, as well as all the DBMS functionality. The reason was that in older systems, most users accessed the DBMS via computer terminals that did not have processing power and only provided display capabilities. Therefore, all processing was performed remotely on the computer system housing the DBMS, and only display information and controls were sent from the computer to the display terminals, which were connected to the central computer via various types of communications networks.
		
		2.5.2 Basic Client/Server Architectures First, we discuss client/server architecture in general; then we discuss how it is applied to DBMSs. The client/server architecture was developed to deal with computing environments in which a large number of PCs, workstations, file servers, printers, database servers, Web servers, e-mail servers, and other software and equipment are connected via a network. The idea is to define specialized servers with specific functionalities. For example, it is possible to connect a number of PCs or small workstations as clients to a file server that maintains the files of the client machines. Another machine can be designated as a printer server by being connected to various printers; all print requests by the clients are forwarded to this machine. Web servers or e-mail servers also fall into the specialized server category.
		
		client in this framework is typically a user machine that provides user interface capabilities and local processing. When a client requires access to additional functionality—such as database access—that does not exist at the client, it connects to a server that provides the needed functionality. 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. In general, some machines install only client software, others only server software, and still others may include both client and server software, as illustrated in Figure 2.6.
		
		2.5.3 Two-Tier Client/Server Architectures for DBMSs 
		In relational database management systems (RDBMSs), many of which started as centralized systems, the system components that were first moved to the client side were the user interface and application programs. Because SQL (see Chapters 6 and 7) provided a standard language for RDBMSs, this created a logical dividing point between client and server. Hence, the query and transaction functionality related to SQL processing remained on the server side. In such an architecture, the server is often called a query server or transaction server because it provides these two functionalities. 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. When DBMS access is required, the program establishes a connection to the DBMS (which is on the server side); once the connection is created, the client program can communicate with the DBMS. 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. Most DBMS vendors provide ODBC drivers for their systems. A client program can actually connect to several RDBMSs and send query and transaction requests using the ODBC API, which are then processed at the server sites. Any query results are sent back to the client program, which can process and display the results as needed. A related standard for the Java programming language, called JDBC, has also been defined. This allows Java client programs to access one or more DBMSs through a standard interface.

		The architectures described here are called two-tier architectures because the software components are distributed over two systems: client and server. The advantages of this architecture are its simplicity and seamless compatibility with existing systems. The emergence of the Web changed the roles of clients and servers, leading to the three-tier architecture.
		
		2.5.4 Three-Tier and n-Tier Architectures for Web Applications
		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 2.7(a).
		
		Figure 2.7 Logical three-tier client/server architecture, with a couple of commonly used nomenclatures.
		
		This intermediate layer or middle tier is called the application server or the Web server, depending on the application. This server plays an intermediary role by running application programs and storing business rules (procedures or constraints) that are used to access data from the database server. It can also improve database security by checking a client’s credentials before forwarding a request to the database server. Clients contain user interfaces and Web browsers. The intermediate server accepts requests from the client, processes the request and sends database queries and commands to the database server, and then acts as a conduit for passing (partially) processed data from the database server to the clients, where it may be processed further and filtered to be presented to the users.
		
		Other architectures have also been proposed. It is possible to divide the layers between the user and the stored data further into finer components, thereby giving rise to n-tier architectures, where n may be four or five tiers.
		
	2.6 Classification of Database Management Systems
	Several criteria can be used to classify DBMSs. The first is the data model on which the DBMS is based. The main data model used in many current commercial DBMSs is the relational data model, and the systems based on this model are known as SQL systems. The object data model has been implemented in some commercial systems but has not had widespread use. Recently, so-called big data systems, also known as key-value storage systems and NOSQL systems, use various data models: document-based, graph-based, column-based, and key-value data models. Many legacy applications still run on database systems based on the hierarchical and network data models.
	
	2.7 Summary
	In this chapter we introduced the main concepts used in database systems. We defined a data model and we distinguished three main categories:
		â–  High-level or conceptual data models (based on entities and relationships)
		â–  Low-level or physical data models
		â–  Representational or implementation data models (record-based, objectoriented)

	We distinguished the schema, or description of a database, from the database itself. The schema does not change very often, whereas the database state changes every time data is inserted, deleted, or modified. Then we described the three-schema DBMS architecture, which allows three schema levels:
	
		â–  An internal schema describes the physical storage structure of the database.
		â–  A conceptual schema is a high-level description of the whole database.
		â–  External schemas describe the views of different user groups.

	A DBMS that cleanly separates the three levels must have mappings among the schemas to transform requests and query results from one level to the next. Most DBMSs do not separate the three levels completely. We used the three-schema architecture to define the concepts of logical and physical data independence.

	Then we discussed the main types of languages and interfaces that DBMSs support. A data definition language (DDL) is used to define the database conceptual schema. In most DBMSs, the DDL also defines user views and, sometimes, storage structures; in other DBMSs, separate languages or functions exist for specifying storage structures. This distinction is fading away in today’s relational implementations, with SQL serving as a catchall language to perform multiple roles, including view definition. The storage definition part (SDL) was included in SQL’s early versions, but is now typically implemented as special commands for the DBA in relational DBMSs. The DBMS compiles all schema definitions and stores their descriptions in the DBMS catalog.

	A data manipulation language (DML) is used for specifying database retrievals and updates. DMLs can be high level (set-oriented, nonprocedural) or low level (recordoriented, procedural). A high-level DML can be embedded in a host programming language, or it can be used as a standalone language; in the latter case it is often called a query language.

	We discussed different types of interfaces provided by DBMSs and the types of DBMS users with which each interface is associated. Then we discussed the database system environment, typical DBMS software modules, and DBMS utilities for helping users and the DBA staff perform their tasks. We continued with an overview of the two-tier and three-tier architectures for database applications.
	
- Feb 06, 2025
	- Reviewed Chapter 2
	- Provided scripts to create company database 

- Feb 11, 2025
	- Chapter 3 Data Modeling Using the Entity Relationship (ER) Model
		Conceptual modeling is a very important phase in designing a successful database application. Generally, the term database application refers to a particular database and the associated programs that implement the database queries and updates. For example, a BANK database application that keeps track of customer accounts would include programs that implement database updates corresponding to customer deposits and withdrawals. These programs would provide user-friendly graphical user interfaces (GUIs) utilizing forms and menus for the end users of the application—the bank customers or bank tellers in this example. In addition, it is now common to provide interfaces to these programs to BANK customers via mobile devices using mobile apps. Hence, a major part of the database application will require the design, implementation, and testing of these application programs. Traditionally, the design and testing of application programs has been considered to be part of software engineering rather than database design. In many software design tools, the database design methodologies and software engineering methodologies are intertwined since these activities are strongly related.
		
		The design of application programs is typically covered in software engineering courses. We present the modeling concepts of the entity–relationship (ER) model, which is a popular high-level conceptual data model. This model and its variations are frequently used for the conceptual design of database applications, and many database design tools employ its concepts. We describe the basic data-structuring concepts and constraints of the ER model and discuss their use in the design of conceptual schemas for database applications. We also present the diagrammatic notation associated with the ER model, known as ER diagrams.
		
		3.1 Using High-Level Conceptual Data Models for Database Design
			- Figure 3.1
				- conceptual design
					- The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints; these are expressed using the concepts provided by the high-level data model. Because these concepts do not include implementation details, they are usually easier to understand and can be used to communicate with nontechnical users.
				- logical design 
					- the conceptual schema is transformed from the high-level data model into the implementation data model. This step is called logical design or data model mapping; its result is a database schema in the implementation data model of the DBMS.
				- physical design 
					- The last step is the physical design phase, during which the internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files are specified.
					
		3.2 A Sample Database Application
			â–  The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.

			â–  A department controls a number of projects, each of which has a unique name, a unique number, and a single location.

			■ The database will store each employee’s name, Social Security number,2 address, salary, sex (gender), and birth date. An employee is assigned to one	department, but may work on several projects, which are not necessarily controlled by the same department. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each employee (who is another employee).
			
			■ The database will keep track of the dependents of each employee for insurance purposes, including each dependent’s first name, sex, birth date, and relationship to the employee.
			
			Figure 3.2 shows how the schema for this database application can be displayed by means of the graphical notation known as ER diagrams
			
		3.3 Entity Types, Entity Sets, Attributes, and Keys
			The ER model describes data as entities, relationships, and attributes.
			
			3.3.1 Entities and Attributes Entities and Their Attributes. 
				- The basic concept that the ER model represents is an entity, which is a thing or object in the real world with an independent existence.	An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) or it may be an object with a conceptual existence (for instance, a company, a job, or a university course). Each entity has attributes—the particular properties that describe it.
				
				■ Composite versus Simple (Atomic) Attributes. Composite attributes can be divided into smaller subparts, which represent more basic attributes with independent meanings. For example, the Address attribute of the EMPLOYEE entity shown in Figure 3.3 can be subdivided into Street_address, City, State, and Zip,3 with the values ‘2311 Kirby’, ‘Houston’, ‘Texas’, and ‘77001’. Attributes that are not divisible are called simple or atomic attributes.
				
				■ Single-Valued versus Multivalued Attributes. Most attributes have a single value for a particular entity; such attributes are called single-valued. For example, Age is a single-valued attribute of a person. In some cases an attribute can have a set of values for the same entity—for instance, a Colors attribute for a car, or a College_degrees attribute for a person.
				
				■ Stored versus Derived Attributes. In some cases, two (or more) attribute values are related—for example, the Age and Birth_date attributes of a person
				
				â–  NULL Values. In some cases, a particular entity may not have an applicable value for an attribute. For example, the Apartment_number attribute of an address applies only to addresses that are in apartment buildings and not to other types of residences, such as single-family homes.
				
				â–  Complex Attributes. Notice that, in general, composite and multivalued attributes can be nested arbitrarily. We can represent arbitrary nesting by grouping components of a composite attribute between parentheses ( ) and separating the components with commas, and by displaying multivalued attributes between braces { }.
				
			3.3.2 Entity Types, Entity Sets, Keys, and Value Sets 
				
				Entity Types and Entity Sets. 
				A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribute. An entity type defines a collection (or set) of entities that have the same attributes. Each entity type in the database is described by its name and attributes. Figure 3.6 shows two entity types: EMPLOYEE and COMPANY, and a list of some of the attributes for each.
				
				An entity type is represented in ER diagrams5 (see Figure 3.2) as a rectangular box enclosing the entity type name. Attribute names are enclosed in ovals and are attached to their entity type by straight lines. Composite attributes are attached to their component attributes by straight lines. Multivalued attributes are displayed in double ovals. Figure 3.7(a) shows a CAR entity type in this notation.

				An entity type describes the schema or intension for a set of entities that share the same structure. The collection of entities of a particular entity type is grouped into an entity set, which is also called the extension of the entity type.
				
				Key Attributes of an Entity Type. 
				An important constraint on the entities of an entity type is the key or uniqueness constraint on attributes. An entity type usually has one or more attributes whose values are distinct for each individual entity in the entity set. Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely.
				
- Feb 13, 2025

				For example, the Name attribute is a key of the COMPANY entity type in Figure 3.6 because no two companies are allowed to have the same name. For the PERSON entity type, a typical key attribute is Ssn (Social Security number). Sometimes several attributes together form a key, meaning that the combination of the attribute values must be distinct for each entity.
				
				Superfluous attributes must not be included in a key. In ER diagrammatic notation, each key attribute has its name underlined inside the oval, as illustrated in Figure 3.7(a).
				
				Some entity types have more than one key attribute. For example, each of the Vehicle_id and Registration attributes of the entity type CAR (Figure 3.7) is a key in its own right. The Registration attribute is an example of a composite key formed from two simple component attributes, State and Number, neither of which is a key on its own. An entity type may also have no key, in which case it is called a weak entity type (see Section 3.5).
				
				In our diagrammatic notation, if two attributes are underlined separately, then each is a key on its own. Unlike the relational model (see Section 5.2.2), there is no concept of primary key in the ER model that we present here; the primary key will be chosen during mapping to a relational schema (see Chapter 9).
				
				Value Sets (Domains) of Attributes. Each simple attribute of an entity type is associated with a value set (or domain of values), which specifies the set of values that may be assigned to that attribute for each individual entity.
				
				3.3.3 Initial Conceptual Design of the COMPANY Database We can now define the entity types for the COMPANY database, based on the requirements described in Section 3.2. After defining several entity types and their attributes here, we refine our design in Section 3.4 after we introduce the concept of a relationship. According to the requirements listed in Section 3.2, we can identify four entity types—one corresponding to each of the four items in the specification (see Figure 3.8):
				
				An entity type EMPLOYEE with attributes Name, Ssn, Sex, Address, Salary, Birth_date, Department, and Supervisor. Both Name and Address may be composite attributes; however, this was not specified in the requirements. We must go back to the users to see if any of them will refer to the individual components of Name—First_name, Middle_initial, Last_name—or of Address. 
				
		3.4 Relationship Types, Relationship Sets, Roles, and Structural Constraints
			In Figure 3.8 there are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute Controlling_department of PROJECT refers to the department that controls the project; the attribute Supervisor of EMPLOYEE refers to another employee (the one who supervises this employee); the attribute Department of EMPLOYEE refers to the department for which the employee works; and so on. In the ER model, these references should not be represented as attributes but as relationships. The initial COMPANY database schema from Figure 3.8 will be refined in Section 3.6 to represent relationships explicitly. In the initial design of entity types, relationships are typically captured in the form of attributes. As the design is refined, these attributes get converted into relationships between entity types.
			
			3.4.1 Relationship Types, Sets, and Instances
			3.4.2 Relationship Degree, Role Names, and Recursive Relationships
			3.4.3 Constraints on Binary Relationship Types
				Cardinality Ratios for Binary Relationships. 
					The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in. For
					example, in the WORKS_FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N, meaning that each department can be related to (that is, employs) any number of employees (N),9 but an employee can be related to (work for) at most one department (1). This means that for this particular relationship type WORKS_FOR, a particular department entity can be related to any number of employees (N indicates there is no maximum number). On the other hand, an employee can be related to a maximum of one department. The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N.
			
				Participation Constraints and Existence Dependencies. The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This constraint specifies the minimum number of relationship instances that each entity can participate in and is sometimes called the minimum cardinality constraint. There are two types of participation constraints—total and partial—that we illustrate by example. If a company policy states that every employee must work for a department, then an employee entity can exist only if it participates in at least one WORKS_FOR relationship instance (Figure 3.9). Thus, the participation of EMPLOYEE in WORKS_FOR is called total participation, meaning that every entity in the total set of employee entities must be related to a department entity via WORKS_FOR. Total participation is also called existence dependency. In Figure 3.12 we do not expect every employee to manage a department, so the participation of EMPLOYEE in the MANAGES relationship type is partial, meaning that some or part of the set of employee entities are related to some department entity via MANAGES, but not necessarily all. We will refer to the cardinality ratio and participation constraints, taken together, as the structural constraints of a relationship type.

				In ER diagrams, total participation (or existence dependency) is displayed as a double line connecting the participating entity type to the relationship, whereas partial participation is represented by a single line (see Figure 3.2). Notice that in this notation, we can either specify no minimum (partial participation) or a minimum of one (total participation). An alternative notation (see Section 3.7.4) allows the designer to specify a specific minimum number on participation in the relationship, such as 4 or 5.
				
				3.4.4 Attributes of Relationship Types Relationship types can also have attributes, similar to those of entity types. For example, to record the number of hours per week that a particular employee works on a particular project, we can include an attribute Hours for the WORKS_ON relationship type in Figure 3.13. Another example is to include the date on which a manager started managing a department via an attribute Start_date for the MANAGES relationship type in Figure 3.12.

				Notice that attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types. For example, the Start_date attribute for the MANAGES relationship can be an attribute of either EMPLOYEE (manager) or DEPARTMENT, although conceptually it belongs to MANAGES. This is because MANAGES is a 1:1 relationship, so every department or employee entity participates in at most one relationship instance. Hence, the value of the Start_date attribute can be determined separately, either by the participating department entity or by the participating employee (manager) entity.
				For a 1:N relationship type, a relationship attribute can be migrated only to theentity type on the N-side of the relationship. For example, in  Figure 3.9, if the WORKS_FOR relationship also has an attribute Start_date that indicates when an employee started working for a department, this attribute can be included as an attribute of EMPLOYEE. This is because each employee works for at most one department, and hence participates in at most one relationship instance in WORKS_FOR, but a department can have many employees, each with a different start date. In both 1:1 and 1:N relationship types, the decision where to place a relationship attribute—as a relationship type attribute or as an attribute of a participating entity type—is determined subjectively by the schema designer.

				For M:N (many-to-many) relationship types, some attributes may be determined by the combination of participating entities in a relationship instance, not by any single entity. Such attributes must be specified as relationship attributes. An example is the Hours attribute of the M:N relationship WORKS_ON (Figure 3.13); the number of hours per week an employee currently works on a project is determined by an employee-project combination and not separately by either entity.

		3.6 Refining the ER Design for the COMPANY Database We can now refine the database design in Figure 3.8 by changing the attributes that represent relationships into relationship types. The cardinality ratio and participation constraint of each relationship type are determined from the requirements listed in Section 3.2. If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints.
			In our example, we specify the following relationship types:
			â–  MANAGES, which is a 1:1(one-to-one) relationship type between EMPLOYEE and DEPARTMENT. EMPLOYEE participation is partial. DEPARTMENT participation is not clear from the requirements. We question the users, who say that a department must have a manager at all times, which implies total participation.13 The attribute Start_date is assigned to this relationship type.
			â–  WORKS_FOR, a 1:N (one-to-many) relationship type between DEPARTMENT and EMPLOYEE. Both participations are total.
			â–  CONTROLS, a 1:N relationship type between DEPARTMENT and PROJECT. The participation of PROJECT is total, whereas that of DEPARTMENT is determined to be partial, after consultation with the users indicates that some departments may control no projects.
			â–  SUPERVISION, a 1:N relationship type between EMPLOYEE (in the supervisor role) and EMPLOYEE (in the supervisee role). Both participations are determined to be partial, after the users indicate that not every employee is a supervisor and not every employee has a supervisor. â–  WORKS_ON, determined to be an M:N (many-to-many) relationship type with attribute Hours, after the users indicate that a project can have several employees working on it. Both participations are determined to be total.
			â–  DEPENDENTS_OF, a 1:N relationship type between EMPLOYEE and DEPENDENT, which is also the identifying relationship for the weak entity type DEPENDENT. The participation of EMPLOYEE is partial, whereas that of DEPENDENT is total.

		3.7 ER Diagrams, Naming Conventions, and Design Issues
			3.7.1 Summary of Notation for ER Diagrams
			3.7.2 Proper Naming of Schema Constructs
			Figure 3.14

			X 3.8 Example of Other Notation: UML Class Diagrams
			3.9 Relationship Types of DegreecHigher than Two

			3.10 Another Example: A UNIVERSITY Database

- Feb 18, 2025
	Chapter 4 - The Enhanced Entity–Relationship (EER) Model
		- Figure 4.1 - Figure 4.9
		- 4.1 Subclasses, Superclasses, and Inheritance
		- 4.2 Specialization and Generalization
			- 4.2.1 Specialization
			- 4.2.2 Generalization
		- 4.3 Constraints and Characteristics of Specialization and Generalization Hierarchies
			- 4.3.1 Constraints on Specialization and Generalization
			- 4.3.2 Specialization and Generalization Hierarchies and Lattices
			- 4.3.3 Utilizing Specialization and Generalization in Refining Conceptual Schemas
		- 4.4 Modeling of UNION Types Using Categories
		- 4.5 A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
			- 4.5.1 A Different UNIVERSITY Database Example
			- 4.5.2 Design Choices for Specialization/Generalization
			
		- University Database creation
		
- Feb 20, 2025
	Chapter 5 - The Relational Data Model and Relational Database Constraints
		- Figure 5.1, ..., Figure 5.7
		- 5.1 Relational Model Concepts
			The relational model represents the database as a collection of relations. Informally, each relation resembles a table of values or, to some extent, a flat file of records. It is called a flat file because each record has a simple linear or flat structure.
			
			When a relation is thought of as a table of values, each row in the table represents a collection of related data values. A row represents a fact that typically corresponds to a real-world entity or relationship. The table name and column names are used to help to interpret the meaning of the values in each row.
			
			In the formal relational model terminology, a row is called a tuple, a column header is called an attribute, and the table is called a relation. The data type describing the types of values that can appear in each column is represented by a domain of possible values.
		
			- 5.1.1 Domains, Attributes, Tuples, and Relations
			- 5.1.2 Characteristics of Relations
			- 5.1.3 Relational Model Notation
		
		5.2 Relational Model Constraints and Relational Database Schemas
			In a relational database, there will typically be many relations, and the tuples in those relations are usually related in various ways. The state of the whole database will correspond to the states of all its relations at a particular point in time. There are generally many restrictions or constraints on the actual values in a database state. These constraints are derived from the rules in the miniworld that the database represents, as we discussed in Section 1.6.8.
			Constraints on databases can generally be divided into three main categories:
				1. Constraints that are inherent in the data model. We call these inherent model-based constraints or implicit constraints.
				2. Constraints that can be directly expressed in the schemas of the data model, typically by specifying them in the DDL (data definition language, see Section 2.3.1). We call these schema-based constraints or explicit constraints.
				3. Constraints that cannot be directly expressed in the schemas of the data or in some other way. We call these application-based or semantic constraints or business rules.
				
				The characteristics of relations that we discussed in Section 5.1.2 are the inherent constraints of the relational model and belong to the first category. For example, the constraint that a relation cannot have duplicate tuples is an inherent constraint. The constraints we discuss in this section are of the second category, namely, constraints that can be expressed in the schema of the relational model via the DDL. Constraints in the third category are more general, relate to the meaning as well as behavior of attributes, and are difficult to express and enforce within the data model, so they are usually checked within the application programs that perform database updates. In some cases, these constraints can be specified as assertions in SQL (see Chapter 7).

				Another important category of constraints is data dependencies, which include functional dependencies and multivalued dependencies. They are used mainly for testing the “goodness” of the design of a relational database and are utilized in a process called normalization, which is discussed in Chapters 14 and 15. The schema-based constraints include domain constraints, key constraints, constraints on NULLs, entity integrity constraints, and referential integrity constraints.
				
			5.2.1 Domain Constraints
			5.2.2 Key Constraints and Constraints on NULL Values
				1. Two distinct tuples in any state of the relation cannot have identical values for (all) the attributes in the key. This uniqueness property also applies to a superkey.
				2. It is a minimal superkey—that is, a superkey from which we cannot remove any attributes and still have the uniqueness constraint hold. This minimality property is required for a key but is optional for a superkey.
				
				In general, a relation schema may have more than one key. In this case, each of the keys is called a candidate key. For example, the CAR relation in Figure 5.4 has two candidate keys: License_number and Engine_serial_number. It is common to designate one of the candidate keys as the primary key of the relation. This is the candidate key whose values are used to identify tuples in the relation.
				
			5.2.3 Relational Databases and Relational Database Schemas
				A relational database schema S is a set of relation schemas S = {R1, R2, … , Rm} and a set of integrity constraints IC. A relational database state10 DB of S is a set of relation states DB = {r1, r2, … , rm} such that each ri is a state of Ri and such that the ri relation states satisfy the integrity constraints specified in IC. Figure 5.5 shows a relational database schema that we call COMPANY = {EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON, DEPENDENT}. In each relation schema, the underlined attribute represents the primary key. Figure 5.6 shows a relational database state corresponding to the COMPANY schema. 
				
				A database state that does not obey all the integrity constraints is called not valid, and a state that satisfies all the constraints in the defined set of integrity constraints IC is called a valid state.
				
			5.2.4 Entity Integrity, Referential Integrity, and Foreign Keys
				
		5.3 Update Operations, Transactions, and Dealing with Constraint Violations
			5.3.1 The Insert Operation
			5.3.2 The Delete Operation
			5.3.3 The Update Operation
			
			5.3.4 The Transaction Concept
				A database application program running against a relational database typically executes one or more transactions. A transaction is an executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database. At the end of the transaction, it must leave the database in a valid or consistent state that satisfies all the constraints specified on the database schema. A single transaction may involve any number of retrieval operations (to be discussed as part of relational algebra and calculus in Chapter 8, and as a part of the language SQL in Chapters 6 and 7) and any number of update operations. These retrievals and updates will together form an atomic unit of work against the database. For example, a transaction to apply a bank withdrawal will typically read the user account record, check if there is a sufficient balance, and then update the record by the withdrawal amount.

				A large number of commercial applications running against relational databases in online transaction processing (OLTP) systems are executing transactions at rates that reach several hundred per second. Transaction processing concepts, concurrent execution of transactions, and recovery from failures will be discussed in Chapters 20 to 22.
				
		5.4 Summary
		
- University 
	create table person (
		int person_id primary_key,
		...
	)
	-- create a sequence person_id 
	alter table person 
	add category_type ENUM('faculty', 'student') not null;
	
	create table faculty (
		int faculty_id primary_key,
		...
		foreign key(faculty_id) 
	)
	alter table faculty 
	add constraint chk_faculty_person
	check(person_id in (select person_id 
	                      from person 
						 where categorytype = 'faculty')) 
						 
	create table student (
		int student_id primary_key,
		...
		foreign key(person_id) 
	)
	alter table student 
	add constraint chk_student_person
	check(person_id in (select person_id 
	                      from person 
						 where categorytype = 'student')) 
						 
- Mar 04, 2025	
	- Chapter 6 
	SELECT Pnumber, Dnum, Lname, Address, Bdate
	  FROM PROJECT, DEPARTMENT, EMPLOYEE
	 WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND
		   Plocation = ‘Stafford’

	select Pnumber, Dnum, Lname, Address, Bdate
	  from project p, department d, employee e
	 where p.dnum = d.dnumber
	   and d.mgr_ssn = e.ssn
	 
	 desc department;
	 desc project;
	 
	 select dnum, count(*)
	   from project 
	  group by dnum
	  
- Mar 6, 2025
	Chapter 7 - More SQL: Complex Queries, Triggers, Views, and Schema Modification
	- Exam on Tuesday, March 11 in class 
		- Locked down browser 
			- MCQ, MSQ, T/F, FB