# 資料庫管理筆記
## Chapter 1. Databases and Database Users
### Outline
- Types of Databases and Databases Appplications
- Basic Definitions
- Typical DBMS Functionality
- Example of a Database (UNIVERSITY)
- Main Characteristics of the Database Approach
- Types of Database Users
- Advantages of Using the Database Approach
- Historical Development of Database Technology
- Extending Database Capabilities
- When Not to Use Databases
### Types of Databases and Databases Appplications
- Traditional Applications:
- Numeric and Textual Databases
- More Recent Applications:
- Multimedia Databases
- Geographic Infomation Systems (GIS)
- Biological and Genome Databases
- Data Warehouses
- Mobile databases
- Real-time and Active Databases
- Recent Developments
- Social Networks started capturing a lof of information about people adn about communications among people-posts, tweets, photos, videos, in systems such as:
- Facebook
- Twitter
- Linked-In
- All of the above constitutes data
- Search Engines - Google, Bing, Yahoo: collect their own repository of web pages for searching purposes
- New Technologies are emerging from the so-called non-database software vendors to manage vast amoutns of data generated on the web.
- Big Data storage systems involving large clusters of distributed computers (Chapter 25).
- NoSQL (Not Only SQL) systems (Chapter 24)
- A large amount of data now resides on the "cloud" which means it is in huge data centers using thousands of machines.
### Basic Definitions
- Database:
- A collection of related data.
- Data:
- Known facts that can be recorded and have an implicit meaning.
- Mini-world:
- Some part of the real world about which data is stored in a database. For example, student grades and transcripts at a university.
- Database Management System (DBMS):
- A software package/system to facilitate the creation and maintainance of a computerized database.
- Database System:
- The DBMS software together with the data itself. Sometimes, the applications are also included.
#### Impact of Databases and Database Technology
- Businesses: Banking, Insurance, Retail, Transportation, Healthcare, Manufacturing
- Service Industries: Financial, Real-estate, Legal, Electronic Commerce, Small businesses
- Education: Resources for content and Delivery
- More recently: Social Networks, Environmental and Scientific Applications, Medicine and Genetics
- Personalized Applications: based on smart mobile devices
#### Simplified database system environment

### Typical DBMS Functionality
- *Define* a particular database in terms of its data types, structures, and constrains.
- *Construct* or Load the initial database contents on a secondary storage medium.
- *manipulating* the database:
- Retrieval: Querying, generating reports
- Modification: Insertions, deletions and updates to its content
- Accessing the database through Web applications
- *Processing* and *Sharing* by a set of concurrent users and application programs - yet, keeping all data valid and consistent.
#### Application Activities Against a Database
- Applications interact with a database by generating:
- Queries: that access different parts of data and formulate the result of arequest.
- Transactions: that may read some data and "update" certain values or generate new data and store that in the database.
- Applications must not allow unautuorized users to access data.
- Applications must keep up with changing user requirements against the database.
#### Additional DBMS Functionality
- DBMS may additionally provide:
- Protection or Security measures to prevent unauthorized access
- "Active" processing to take internal actions on data
- Presentation and Visualization of data
- Maintenance of the database and associated programs over the lifetime of the database application
### Example of a Database (with a Comceptual Data Model)
- Mini-world for the example:
- part of a UNIVERSITY environment.
- Some mini-world *entities*:
- STUDNETs
- COURSEs
- SECTIONs (of COURSEs)
- (academic) DEPARTMENTs
- INSTRUCTORs
- Some mini-world *relationships*:
- SECTIONs *are of specific* COURSEs
- STUDENTs *take* SECTIONs
- COURSEs *have prerequisite* COURSEs
- INSTRUCTORs *teach* SECTIONs
- COURSEs *are offered by* DEPARTMENTs
- STUDENTs *major in* DEPARTMENTs
- Note: The above entities and relationships are typically expressed in a conceptual data model, e.g., ENTITY-RELATIONSHIP data model (Chapters 3, 4).

### Main Characteristics of the Database Approach
- Self-describing nature of a database system:
- A DBMS **catalog** stores the description of a particular database (e.g., data structures, types, and constraints).
- The description is called **meta-data**.
- This allows the DBMS software to work with different database applications.
- Insulation between programs and data:
- Called **program-data independence**.
- Allows changing data structures and storage organization without having to chagne the DBMS access programs.
- Data Abstraction:
- A **data model** is used to hide storage details and present the users with a conceptual view of the database.
- Programs refer to the data model constructs rather than data storage details.
- Support of multiple view of the data:
- Each user may see a different view of the database, which describes **only** the data of interest to that user.
- Sharing of data and multi-user transaction processing:
- Allowing a set of **concurrent users** to retrieve from and to update the database.
- *Concurrency control* within the DBMS guarantees that each **transaction** is correctly executed or aborted.
- *Recovery* subsystem ensures each completed transaction hash its effect permanently recorded in the database.
- **OLTP** (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second.
### Types of Database Users
- Users may be divided into
- Those who actually use and control the database cent, and those who design, develop and maintain database appplications ("Actors on the Scene")
- Those who design and develop the DBMS software and related tools, and the computer systems operators ("Workers Behind the Scene")
#### Actors on the Scene
- **Database administrators**
- Responsible for authorizing access to the database, for:
- coordinating and monitoring its use
- acquiring software and hardware resources
- controlling its use and monitoring efficiency of operations.
- **Database Designers**
- Responsible to define the content, the structure, the constraints, and functions or transactions against the database.
- They must communicate with the end-users and understand their needs.
- **End-users**
- They use the data for queries, reports and some of then update the database content. End-users can be categorized into:
- **Casual**: access database occasionally when needed
- **Naive** or Parametric: they make up a large section of the end-user population.
- **Sophisticated**: These include business analysts, scientists, engineers, others thoroughly familiar with the system capabilities.
- **Stand-alone**: Mostly maintain personal databases using ready-to-use packaged applications.
- **System Analysts and Application Developers**
- This category currently accounts for a very large proportion of the IT work force.
- **System Analysts**: They understand the user requirements of naive and sophisticated users and design applications including canned transactions to meet those requirments.
- **Application Programmers**: Implement the specifications developed by analysts and test and debug them before deployment.
- **Business Analysts**: There is an increasing need for such people who can analyze vast amounts of business data and real-time data ("Big Data") for better decision making related to planning, advertising, marketing, etc.

#### Workers behind the Scene
- **System Designers and Implementors**
- Design and implement DBMS packages in the form of modules and interfaces and test and debug them.
- The DBMS must interface with applications, language compilers, operating system components, etc.
- **Tool Developers**
- Design adn implement software systems called tools for modeling adn designing databases, performance monitoring, prototyping, test data generation, user interface creation, simulation, etc., that facilitate building of applications and allow using database effectively.
- **Operators and Maintenance Personnel**
- They manage the actual running and maintenance of the database system hardware and software environment.
### Advantages of Using the Database Approach
- Controlling redundancy in data storage and in development and maintenance efforts.
- Restricting unauthorized access to data. Only the DBA staff uses privileged command sand facilities
- Providing persistant storage for program Objects
- E.g., Object-oriented DBMSs make program objects persistent - see Chapter 12.
- Providing Sorage Structures (e.g., indexes) for efficient Query Processing - see Chapter 17.
- Providing optimizaiton of queries ofr efficient processing.
- Providing backup and recovery services.
- Provding multiple interfaces to different classes of users.
- Representing complex relationships among data.
- Enforcing integrity constraints on the database.
- Drawing inferences and actions from the stored data using deductive and active rules and triggers.
#### Additional Implications of Using the Database Approach
- Potential for enforcing standards
- Reduced application development time
- Flexibility to change data structures
- Availability of current information
- Economies of scale
### Historical Development of Database Technology
- Early Database Applications
- The Hierarchical and Network Models were introduced in mid-1960s and dominated during the seventies.
- Relational Model based Systems
- Relational model was originally introduced in 1970, was heavily researched and experimented within IBM Research and several universities.
- Relation DBMS Products emerged in the early 1980s.
- Object-oriented and emerging applications
- Object-Oriented Database Management Systems (OODBMSs) were introduced in late 1980s and early 1990s to cater to the need of complex data processing in CAD and otehr applications.
- Their use has not take off much.
- Many relational DBMSs have incorporated object database concepts, leading to a new category callled *object-relationl* DBMSs (ORDBMSs).
- *Extended relational* systems add further capabilities (e.g., for multimedia data, text, XML, and other data types).
- Data on the Web and E-commerce Applications
- Web contains data in HTML with links among pages.
- This has given rise to a new set of applications and E-commerce is using new standards like XML.
- Script programming languages such as PHP ans JavaScript allow generation of dynamic Web pages that are partially generated from a database (see Ch. 11).

### Extending Database Capabilities
- New functionality is being added to DBMSs in the following areas:
- Scientific Applications
- Earth and Atmospheric Sciences and Astronomy
- XML
- Image Storage and Management
- Audio and Vide Data Management
- Data Warehousing and Data Mining (Chaper 28, 29)
- Spatial Data Management and Location Based Services
- Time Series and Hsitorical Data Management
- Background since the advent of the 21st Century:
- First decade of the 21st centry has seen temendous growth in user generated data and automatically collected data from appilcations and search engines.
- Social Media platforms such as Facebook and Twitter are generating millions fo transactions a day and businesses are interested to tap into this data to "understand" the users.
- Cloud Storage and Backup is making unlimited amount of storage available to users and applications.
- Emergence of Big Data Technologies and NOSQL databases
- New data storage, management and analysis technology was necessary to deal with the onslaught of data in petabytes a day in some applications -- "Big Data".
- Hadoop and MapReduce Programming approach to distributed data processing as well as the Google file system have gvien rise to Big Data techonologies (Chapter 25). Further enhancements are taking place in the form of Spark based technology.
- NOSQL systems have been desinged for rapid search and retrival from documents, processing of huge graphs occurring on socila networks, and other forms of unstructured data with flexible models of transaction processing (Chapter 24).
### When no to use a DBMS
- Main intibitors (costs) of using a DBMS
- High initial investment and possible need for additional hardware.
- Overhead for providing generality, security, concurrency control, recovery, and integrity fuctions.
- When a DBMS may be unnecessary:
- If the database and applications are simple, well defined, and not expected to chagne.
- If access to data by multiple users is not required.
- When a DBMS may be infeasible:
- In embedded systems where a general purpose DBMS may not fit in available storage.
- When DBMS may not suffice:
- If there are stringent real-time requriements that may not be met because of DBMS overhead (e.g., telephone swtiching systems).
- If the database system is not able to handle the complexity of data because of modeling limitations (e.g., in complex genome and protein databases).
- If the database users need special operations not supported by the DBMS (e.g., GIS and lication based services).

## Chapter 2. DataBase System to Concepts and Architecture
### Outline
- Data Models and Their Categories
- History of Data Models
- Schemas, Instances, and States
- Three-Schema Architecture
- Data Independence
- DBMS Languages and Inferfaces
- Database System Utilities and Tools
- Centralized and Client-Server Architectures
- Classification of DBMSs

### Data Models and Their Categories
- Data Model
- A set of concepts to describe the ***structure*** of a database.
- The ***operations*** of manipulating these structures.
- Certain ***constraints*** that the database should obey.
- Data Model Structure and Constraints
- Constructs are used to define the database struct.
- Constructs typically include
- ***elements*** (and their ***data types***)
- group of elements (e.g. ***entity, record, table***)
- ***relationships*** among such group
- Constraints specify some restrictions on vaild data; these constraints must be enforced at all times.
- Data Model Operations
- These operations are used for specifying database *retrivals* and *updates* by referring to the constructs of the data model.
- Operations on the data model may include ***basic model operations*** (e.g. generic insert, delete, update) and ***user-defined operations*** (e.g. compute_student_gpa, update_inventory).
#### Categories of Data Models
- Conceptual (high-level, semantic) data models
- Provide concepts that are close to the way many user perceive data.
- Also called ***entity-based*** or ***object-based*** data models.
- Physical (low-level, internal) data models
- Provide concepts that describe details of how data is stored in the computer.
- These are usually specified in an ad-hoc manner through DBMS design and administration manuals.
- Implementation (repesentational) data models
- Provide concepts that fall between the above two, used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems).
- Self-Describing Data Models
- Combine the description of data with the data values.
- Examples include XML, key-value stores and some NOSQL systems.
### History of Data Models (addition material)
#### Network Model
- Advantages
- Able to model complex relationships and represents semantic of add/delete on the relationships.
- Can handle most situations for modelling using record types and relationship types.
- **Language is navigational**: uses constructs like FIND, FIND member, FIND owner, FIND NEXT within set, GET, etc.
- Programmer can do do optimal navigation through the database.
- Disadvantages
- Navigational and procedual nature of processing.
- Database contains a complex array of pointers that thread through a set of records.
- Little scope for automated "query optimization".
#### Hierarchical Data Model
- Advantages
- Simple to construct and operate.
- Corresponds to a number of natural hierarchically organized domains, e.g., organization ("org") chart
- Language is simple:
- Uses constructs like GET, GET UNIQUE, GET NEXT, GET NEXT WITHIN PARENT, etc.
- Disadvantages
- Navigational and procedual nature of processing.
- Database is visualized as a linear arrangement of records.
- Little scope for "query optimization"
#### Relantional Model
- Now in several commerical products (e.g. DB2, ORACLE, MS SQL Server, SYBASE, INFORMIX).
- Several free open source implementations, e.g., MySQL, PostgreSQL.
- Currently most dominant for developing database applications.
#### Object-oriented Model
- Several models have been proposed for implementing in a database system.
- One set comprises models of persistant OO Programming Lauguages such as C++, and Smalltalk.
#### Object-related Model
- The trend to mix object models with relational was started with Informix Universal Server.
- Relational systems incorporated concepts from object databases leading to object-relational.
- Exemplified in the versions of Oracle, DB2, and SQL Server and other DBMSs.
- Current trend by Relational DBMS vendors is to extend relational DBMSs with capability to process XML, Text and other data types.
- The term "Object-relational" is recording in the marketplace.

### Schemas, Instances, and States
#### Schemas v.s. Instances
- Database Schema
- The ***description*** of a database.
- Includes descriptions of the database structure, data types, and constraints on the database.
- Schema Diagram
- An ***illustrative*** display of (most aspect of) a database schema.
- Schema Construct
- A ***component*** of the schema or an object within the schema, e.g., STUDENT, COURSE.
- Database state
- The actual data stored in a database at a ***particular moment in time***.
- This includes the collection of all the data in the database.
- Also called database instance
#### Database Schema v.s. Database State
- Database State
- Refers to the ***content*** of a database at a moment in time.
- Initial Database State
- Refers to the database state when it is initially loaded into the system.
- Valid State
- A state that statisfies the structure and constraints of the database.
- Distinction
- The ***database schema*** changes very infrequently.
- The ***database state*** changes every time the database is updated.
- **Schema** is also called **intension**.
- **State** is also called **extension**.

### Three-Schema Architecture
- Proposed to support DBMS characteristic of:
- **Program-data independence**.
- Support of **multiple view** of the data.
- Not explicity used in commercial DBMS products, but has been useful in explaining database system organization.
- Defines DBMS schemas at ***three*** levels:
- **Internal schema**: at the internal level to describe physical storage structures and access paths.
- **Conceptual schema**: at the conceptual level to describe the structure and constraints for the whole database for a community of users.
- **External schema**: at the external level to describe the various user views.

- Mapping among schema levels are needed to transform requests and data.
- Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution.
- Data extracted from the internal DBMS level is reformatted to match the user's external view.

### Data Independence
- Logical Data Independence
- The capacity to change the conceptual schema without having to change the external schemas and their associated application programs.
- Physical Data Independence
- The capcity to change the internal schema without having to change the conceptual schema.
- When a schema at a lower level is changed, only the **mapping** between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence.
- The higher-level schemas themselves are **unchaged**.
- Hence, the application programs need not not be changed since they refer to the external shcemas.

### DBMS Languages and Inferfaces
#### DBMS Languages
- Data Definition Language (DDL)
- Used by the DBA and database designers to specify the conceptual schema of a database.
- In many DBMSs, the DDL is also used to define internal and external schemas (views).
- In some DBMSs, separate **storage definition language (SDL)**, and **view definition language (VDL)** are used to define internal and external schemas.
- SDL is realized via DBMS commands provided to the DBA and database designers.
- Data Manupulation Language (DML)
- Used to specify database retrivals and updates.
- DML commands (data sublanguage) can be *embedded* in a general-purpose programming language (host language), such as COBOL, C, C++, or Java.
- A library of functions can be also be provided to access the DBMS from a programming language.
- Alternatively, stand-alone DML commands can be applied directly (called a *query language*).
- Data Control Language (DCL)
#### DBMS Interfaces
- Stand-alone query language interfaces
- Programmer interfaces for embedding DML in programming languages
- User-friendly interfaces
- Mobile Interfaces: interfaces allowing users to perform transactions using mobile apps

### Database System Utilities and Tools
#### Database System Utilities
- Loading data stored in files into a database.
- Backing up the database periodically on tape.
- Reorganizing database file structures.
- Performance monitoring utilities.
- Report generation utilities.
- Other functions, such as sorting, user monitoring, data compression, etc.
#### Dtatabase System Tools
- Data dictionay/repository:
- Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc.
- **Active data dictionary** is accessed by DBMS software and users/DBA.
- **Passive data dictionary** is accessed by users/DBA only.
- Application Development Environments and CASE (computer-aided software engineering) tools
#### Typical DBMS Component Modules

### Centralized and Client-Server DBMS Architectures
#### Centralized DBMS
- Combines everything into single system including:
- DBMS software
- DBMS hardware
- application programs
- user interface processing software
- User can still connect through a remote terminal--however, all processing is done at centralized site.

#### Client-Server DBMS Architecture
- Clients
- Provide appropriate interfaces through a client software module to access and utilize the various server resources.
- Clients may be disless machines or PCs or Workstations with disks with only the client software installed.
- Connected to the servers via some form of a network.
- DBMS Server
- Provide database query and transaction services to the clients.
- Relational DBMS servers are often called SQL servers, query servers, or transaction servers.
- Applications running on clients utilize an Application Program Interface (**API**)
##### (Basic) 2-tier Client-Server Architecture
- Specialized Servers with Specialized functions
- Print Server
- File Server
- DBMS Server
- Web Server
- Email Server
- Clients can access the specialized servers as needed

- Client and server must install appropriate client module and server module software for ODBC or JDBC.
- A client program may connect to several DBMSs, sometimes called the data sources.
- In general, data sources can be files or other non-DBMS software that manages data.
##### Three-tier Client-Serve Architecture
- Common for Web applications.
- Intermediate Layer called Application Server or Web Server:
- Stores the web connectivity software and the business logic part of the application used to access the corresponding data from the database server
- Acts like a conduit for sending partially processed data between the database server and the client.
- Three-tier Architecture Can Enhance Security:
- Database server only accessible via middle tier.
- Client cannot directly access database server.
- Clients contain user interfaces and Web browsers.
- The client is typically a PC or a mobile device connected to the Web.


### Classification of DBMSs
- Based on the data model used
- Legacy: Network, Hierarchical.
- Currently Used: Relational, Object-oriented, Object-relational.
- Recent Technologies: Key-value storage systems, NOSQL systems: document based, column-based, graph-based and key-value based. Native XML DBMSs.
- Other classifications
- Single-user (typically used with personal computers) v.s. multi-user (most DBMSs).
- Centralized (uses a single computer with one database) v.s. distributed (multiple computers, multiple DBs).
#### Variations of Distributed DBMSs (DDBMSs)
- Homogeneous DDBMS
- Heterogeneous DDMBS
- Federated or Multidatabase Systems
- Participating Database are loosely coupled with high degree of autonomy.
- Distributed Database Systems have now come to be known as client-server based database systems because:
- They do not support a totally distributed environment, but rather a set of database servers supporting a set of clients.
#### Cost considerations for DBMSs
- Cost Range: from free open-source systems to configurations costing millions of dollars.
#### Other considerations
- Type of access paths within database system
- General purpose v.s. special purpose

## Chapter 3. Data Modeling Using the Entity-Relationship (ER) Model
### Outline
- Overview of Database Design Process
- Example Database Application (COMPANY)
- ER Model Concepts
- Entities and Attributes
- Entity Types, Value Sets, and Key Attributes
- Weak Entity Types
- Roles and Attributes in Relationship Types
- ER Diagrams - Notation
- ER Diagram for COMPANY Schema
- Alternative Noatations - UML class diagrams, others
- Relationships of Higher Degree

### overview of Data Design Process
- Two main activities
- Database design
- Applications design
- Focus in this chapter on __conceptual database design__
- To design the conceptual schema for a database application
- Applications design focuses on the programs and interfaces that access the database
- Generally considered part of software engineering


#### Methodologies for Conceptual Design
- Entity Relationship (ER) Diagrams (This Chaper)
- Enhanced Entity Relationship (EER) Diagrams (Chapter 4)
- Use of Design Tools in industry for designing and documenting large scale designs.
- The UML (Unified Modeling Languages) Class Diagrams are popular in industry to document conceptual database designs.

### Example Database Application (COMPANY)
- We need to create a database schema design based on the following (simplified) **requirements** of the COMPANY Database:
- The company is organized into DEPARTMENTs.
- Each department has a name, number and an employee who *manages* the department.
- We keep track of the start date of the department manager. A department may have several locations.
- Each department *controls* a number of PROJECTs.
- Each project has a unique name, unique number and is located at a single location.
- The database will store each EMPLOYEE's social security number, address, salary, sex, and birthdate.
- Each employee *works* for one department but may *work on* several projects.
- The DB will keep track of the number of hours per week that an employee currently works on each project.
- It is required to keep track of the *direct supervisor* of each employee.
- Each employee may *have* a number of DEPENDENTs.
- For each dependent, the DB keeps a record of name, sex, birthdate, and relationship to the employee.

### ER Model Concepts
- Entities and Attributes
- Entity is a basic concept of the ER model. Entities are specific things or objects in the mini-world that are represented in the database.
- For example the EMPLOYEE John Smith, the Research DEPARTMENT, the ProectX PROJECT
- Attributes are properties used to describe an entity.
- For example an EMPLOYEE entity may have the attributes Name, SSN, Address, Sex, BirthDate.
- A specific entity will have a value for each of its attributes.
- For example a specific employee entity may have Naoe='John Smith', SSN='123456789', Address='731, Fondren, Houston, TX', Sex='M', BirthDate='09-JAN-55'.
- Each attribute has a *value set* (or data type) associated with it - e.g. integer, string, date, enumerated type, ...
#### Types of Attributes
- Simple
- Each entity has a single atomic value of the attribute. For example, SSN, or Sex.
- Composite
- The attribute may be composed of several components. For example:
- Address(Apt#, House#, Street, City, State, ZipCode, Country)
- Name(FirstName, MiddleName, LastName)
- Composition may form a hierarchy where some components are themselves composite.
- Multi-valued
- An entity may have multiple values for that attribute. For example, Color of a CAR or PreviousDegress of a STUDENT.
- Denoted as {Color}, or {PreviousDegrees}.
- In general, composite and multi-valued attributes may be nexted arbitrarily to any number of levels, although this is rare.
#### Example of a composite attribute

#### Entity Types and Key Attributes
- Entities with the same basic attributes are grouped or typed into an entity type.
- For example, the entity type EMPLOYEE and PROJECT.
- An attribute of an entity type ofr which each entity must have a unique value is called a key attribute of the entity type.
- For example, SSN of EMPLOYEE.
- A key attribute may be composite.
- VehicleTagNumber is a key of the CAR entity type wiht components (Number, State).
- An entity type may have more than on key.
- The CAR entity type may have two keys:
- VehicleIdentificationNumber (popularly called VIN).
- VehicleTagNumber (Number, State), aka license plate number.
- __Each key__ is __underlined__ (Note: this is different frm the relational schema where only one "primary key is underlined").
#### Entity Set
- Each entity type will have a collection of entites stored in the databased.
- Called the **entity set** or sometimes **entity collection**.
- previous slide shows three CAR entity instances in the entity set for CAR.
- Same name (CAR) used to refer to both the entity type and the entity set.
- However, entity type and entity set may be given different names.
- Entity set is the current *state* of th entites of that type that are stored in the database.
#### Value Sets (Domains) of Attributes
- Each simple attribute is associated with a value set
- E.g., Lastname has a value which is a character string of upto 15 characters.
- Date has a value consisting of MM-DD-YYYY where each letter is an integer.
- A **value set** specifies the set of values associated with an attribute.
#### Attributes and Value Sets
- Value sets are similar to data types in most programming languages.
- Mathematically, an attribute $A$ for an entity type $E$ whose value set is $V$ is defined as a function
$$
A: E \ -> P(V)
$$

Where $P(V)$ indicates a power set (which means all possible subsets) of V. THe above definition covers simple and multivalued attributes.

- We refer to the value of attribute $A$ for entity $e$ as $A(e)$.

### ER Diagrams - Notation
- In ER ddiagrams, an entity type is displayed in a rectangular box.
- Attributes are displayed in ovals.
- Each attribute is connected to its entity type.
- Components of a composite attribute are connected to the oval representing the composite attribute.
- Each key attribute is underlined.
- Multivalued attributes displayed in double ovals.


### ER Diagram for COMPANY Schema
- Based on the requirements, we can identify four initial entity types in the COMPANY database:
- DEPARTMENT
- PROJECT
- EMPLOYEE
- DEPENDENT
- Their initial conceptual design is shown on the following slide:
- The initial attributes shown are derived from the requirements description.

#### Refining the initial desing by introducint **relationships**
- The initial desing is typically not complete.
- Some aspects in the requirements will be represented as **relationships*.
- ER mode has three main concepts:
- Entities
- Attributes
- Relationships

#### Relationships and Relationship Types
- A **relationship** relates two or more distinct entities with a specific meaning.
- For example, EMPLOYEE John Smith *works on* the ProductX PROJECT, or EMPLOYEE Franklin Wong *manages* the Research DEPARTMENT.
- Relationships of the same type are grouped or typed into a **relationship type**.
- From example, the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate.
- The degree of a relationship type is the number of participating entity types.
#### Relationship instances of the WORKS_FOR N:1 relationship between EMPLOYEE and DEPARTMENT
#### Relationship instances of the M:N WORKS_ON relationshp between EMPLOYEE and PROJECT
#### Relationship type v.s. relationship set
- Relationship Type:
- Is the schema description of a relationship
- Identifies the relationship name and the partcipating entity types
- Also identifiies certain relationship constraints
- Relationship Set:
- The current set of relationship instances represented in the database
- The current *state* of a relationship type
- Previous figures displayed the relatipnship sets.
- Each instance in the set relates individual participating entities -- one from each participating entity type
- In ER diagrams, we represent the *relationship type* as follows:
- Diamond-shaped box is used to display a relationship type
- Connected to the participating eneity types via straight lines
- Note that the relationship type is not shown with an arrow. The name should be typically be readable from left to right and top to bottom.
#### Refining the COMPANY database schema by introducing relationships
#### Discussion on Relatipnship Types
- In the refined design, some attributes from the initial entity types are refined into relationships:
- Manager of DEPARTMENT -> MANAGES
- Works_on of EMPLOYEE -> WORKS_ON
- Department of EMPLOYEE -> WORKS_FOR
- etc
- In general, more than one relationship type can exist between the same participating entity types
- MANAGES and WORKS_FOR are distinct relationship types between EMPLOYEE and DEPARTMENT.
- Different meanings and different relatipnship instances.
#### Constraints on Relationships
- Constrains on Relationship Types
- (Also known as ratio constraints)
- Cardinality Ratio (specifies *maximum* participation)
- One-to-one (1:1)

- One-to-many (1:N) or Many-to-one (N:1)

- Many-to-many (M:N)

- Existence Dependency Constraint (specifies *minimum* participation) (also called participation constraint)
- zero (optional participation, not existence-depenent)
- one or more (mandatory participation, existence-dependent)
#### Recursive Relationship Type
- A relationship type between the same participating entity type in **distinct roles**.
- Also called a **self-referencing** relationship type.
- Example: the SUPERVISION relationship
- EMPLOYEE participates twice in two distinct roles:
- supervisor (or boss) role
- supervisee (or subordinate) role
- Each relationship instance relates two distinct EMPLOYEE entities:
- One employee in *supervisor* role
- One employee in *supervisee* role
#### Displaying a recursive relationship
- In a recursive relationship type.
- Both participations are same entity type in different roles.
- For example, SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker).
- In following figure, first role participation labeled with 1 and second role participation labeled with 2.

- In ER diagram, need to display role names to distinguish participations.

#### Weak Entity Types
- An eieity that does not have a key attribute and that is identification-dependent on another entity type.
- A weak entity must participate in an identifying relationship type with an owner or identifying entity type.
- Entities are identified by the combination of:
- A partial key of the weak entity type.
- The particular entity they are related to in the identifying relationship type.
- Example:
- A DEPENDENT entity is identified by the dependent's first name, *and* the specific EMPLOYEE with whom the dependent is related
- Name of DEPENDENT is the *partial key*.
- DEPDNDENT is a *weak entity type*.
- EMPLOYEE is its identifying entity type via the identifying relationship type DEPENDENT_OF.
#### Attributes of Relationship types
- A relationship type can have attributes:
- For example, HoursPerWeek of WORKS_ON.
- Its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT.
- A value of HoursPerWeek depends on a particular (employee, project) combination.
- Most relationship attributes are used wiht M:N relationships
- In 1:N relationships, they can be transferred to the entity type on the N-side of the relationship.
#### Notation for Constraints on Relationships
- Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N.
- Shown by placing appropriate number s on the relationship edges.
- Participation constraint (on each participating entity type): total (called existence dependency) or partial.
- Total shown by double line, partial by single line.
- NOTE: These are easy to specify for Binary Relationship Types.
#### Alternative (min, max) motation for relationship structural constraints
- Specified on each participation of an entity type E in a relationship type R.
- Specifies that each entity e in E participates in at least *min* and at most *max* relationship instances in R.
- Default (no constraint): min=0, max=n (signifying no limit)
- Must have min<=max, min>=0, max>=1.
- Derived from the knowledge of mini-world constraints.
- Examples:
- A department has exactly on manager and an employee can manage at most one department.
- Specify (0,1) for participation of EMPLOYEE in MANAGES
- Specify (1,1) for participation of DEPARTMENT in MANAGES
- An employee can work for exactly one department but a department can have any number of employees.
- Specify (1,1) for participation of EMPLOYEE in WORKS_FOR
- Specify (0,n) for participation of DEPARTMENT in WORKS_FOR

##### CONPANY ER Schema Diagram using (min, max) notation

### Alternative diagrammatic notation
- ER diagrams is one popular example for displaying database schemas
- Many other notations exist in the literature and in various database design and modeling tools.
- UML class diagrams is representative of another way of displaying ER concepts that is used in several commerical design tools.

#### UML class diagrams
- Represent classes (similar to entity types) as large rounded boxex with three sections:
- Top section includes entity type (class) name
- Second section includes attributes
- Thried section includes class oprations (operations are not in basic ER model)
- Relationships (called associations) represented a slines connecting the classes
- Other UML terminology also differs from ER terminology.
- Used in database desing and object-oriented software design.
- UML has many other types of diagrams for software design

##### UML Class diagram for COMPANY database schema

#### Other alternative diagrammatic notations

### Relationships of Higher Degree
- Relationship types of degree 2 are called binary.
- Relationship types of degree 3 are called ternary and of degree n are called n-ary.
- In general, an n-ary relationship is not equivalent to n binary relationships.
- Constraints are harder to specify for higher-degree relationships (n > 2) than for binary relationships
#### Discussion of n-ary relationships (n > 2)
- In general, 3 binary relationships can represent different information than a single ternary relationship
- If needed, the binary and n-ary relationships can all be included in the schema design.
- In some cases, a ternary relationship can be represented as a weak entity if the data model allow s a weak entity type to have multiple identifying relationships.
- If a particular binary relationship can be derived from a higher-degree relationship at all times, then it is redundant.
- For example, the TAUGHT_DURING binary relationship in this figure cna be derived from the ternary relationship OFFERS

#### Example of a ternary relationship

#### Displaying constraints on higher-degree relationships
- The (min, max) constraints can be displayed on the edges -- however, they do not fully describe the constraints.
- Displaying a 1, M, or N indicates additional constraints
- An M or N indicates no constraint
- A 1 indicates that an entity can participate in at most one relationship instance *that has a particular combination of the other participating entities*.
- In general, both (min, max) and 1, M, or N are neede to describe fully the constraints.
- Overall, the constraint specifiation is difficult and possibly ambiguous when we consider relationships of a degree higher than two.

## Chapter 5. The Relational Data Model and Relational Database Constraints
### Outline
- Relational Model Concepts
- Definitions
- Characteristics of relations
- Relational Model Constraints and Relational Database Schemas
- Domain constraints
- Key constraints
- Entity integrity
- Referential integrity
- Update Operations and Dealing with Constraint Violations

### Relational Model Concepts
- The relational Model of Data is based on the concept of a *Relation*.
- In *practice*, there is a *standard model* based on SQL.
- A relation is a mathematical concept based on the ideas of sets.
- First proposed by Dr. E.F. Codd of IBM Research in 1970, and this caused a major revolution in the field of database management and earned Dr. Codd the coveted ACM Turing Award.
#### Informal Definitions
- Informally, a **relation** ooks like a **table** of values.
- A relation typically contains a **set of rows**.
- The data elements in each **row** represent certain facts that correspond to a real-world **entity** or **relationship**.
- In the formal model, rows are called **tuples**.
- Each **column** has a column header that gives an indication of the meaning of the data items in that column.
- In the formal model, the column header is called an **attribute name** (or just **attribute**).
- Key of a Relation
- Each row hash a vlue of a data item (or set of items) that uniquely identifies that row in the table -- called the ***key***.
- E.g. In the STUDENT table, SSN is the key.
- Sometimes row-ides or sequential numbers are assigned as keys to identify the rows in a table. -- called ***artificial key*** or ***surrogate key***.
#### Example of a Relation
#### Comparison Table

##### Schema
- The **Schema** (or description) of a Relation:
- Denoted by R(A1, A2, ..., An).
- R is the **name** of the relation.
- The **attributes** of the relation are A1, A2, ..., An.
- Example:
CUSTOMER (Cust-id, Cust-name, Address, Phone#)
- CUSTOMER is the relation name.
- Defined over the four attributes: Cust-id, Cust-name, Address, Phone#.
- Each attribute has a **domain** or a set of valid values.
- For example, the domain of Cust-id is 6 digit numbers.
##### Tuple
- A **tuple** is a ordered set of values (enclosed in angled brackets '<...>').
- Each value is derived from an appropriate *domain*.
- A row in the CUSTOMER relation is a 4-tuple and would consist of four values.
- A relation is a **set* of such tuples (rows).
##### Domain
- A **domain** has a logical definition.
- A domain also hash a data-type or a format defined for it.
- The attribute name designates the role played by a domain in a relation:
- Used to interpret the meaning of the data elements corresponding to that attribute.
##### State
- The **relation state** is a subset of the Cartesian product of the domains of its attributes.
- Example: attribute Cust-name is defined over the domain of character strings of maximum length 25.
- The role thses strings play in the CUSTOMER relation is that of the *name of a customer*.
#### Summary
- Formally,
- Given $R(A1, A2, ..., An)$
- $r(R) \subset dom(A1) \times dom(A2) \times ... \times dom(An)$
- $R(A1, A2, ..., An)$ is the **schema** of the relation.
- $R$ is the **name** of the relation.
- $A1, A2, ..., An$ are the **attributes** of the relation.
- $r(R)$: a specific **state** (or "value" or "population") of relation $R$ - this is a *set of tuples* (rows)
- $r(R) = {t1, t2, ..., tn}$ where each $ti$ is an n-tuple.
- $ti = <v1, v2, ..., vn>$ where each $vj$ *element-of* $dom(Aj)$.

### Relational Model Constraints and Relational Database Schemas
Constraints determine which values are permissible and which are not in the database.
They are of three main types:
1. **Inherent ofImplicit Constraints**: These are based on the data model itself.
2. **Schema-based or Explicit Constraints**: They are expressed in the schema by using the facilities provided by the model.
3. **Application based or semantic constraints**: These are beyond the expressive power of the model and must be specified and enforced by the application programs.
#### Relation Integrity Constraints
- Constraints are **conditions** that must hold on **all** valid relation states.
- There are three *main types* of (explicit schema-based) constraints that can be expressed in the relational model:
- **Key** constraints
- **Entity integrity** constraints
- **Referential integrity** constraints
- Another schema-based constraint is the **domain** constraint
##### Key Constraints
- **Superkey** of R:
- is a set of attributes SK of R with the following condition:
- No two putles in any valid relation state r\(R\) will haveh the same value for SK.
- That is, for any distinct typles t1 and t2 in r\(R\), t1[SK] =/= t2[SK]
- This condition must hold in *any valid state* r\(R\).
- **Key** of R:
- A "minimal" superkey
- That is, a key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property).
- In general
- Any *key* is a Superkey but **not vice verse**.
- Any set of attributes that *includes a key* is a *superkey*.
- A *minimal* sperkey is also a key.
- If a relation hash several **candidate keys**, one is chosen arbitrarily to be the **primary key**.
- The primary key attributes are ++underlined++.
- The primary key value is used to *uniquely identify* each tuple in a relation.
- Also used to *reference* the tuple from another tuple.
- General rule: Choose as primary key the smallest of the candidate keys (in terms of size).
- Not always applicable - choice is sometimes subjective.
##### Entity Integrity
- The *primary key attributes* PK of each relation schema R in S cannot have null values in any typle of r\(R\).
##### Referential Integrity
- A constraint involving **two** relations
- Tuples in the **referencing relation** R1 have attributes FK (**foreign key** attributes) that reference the primary key attributes PK of the **references relation** R2.
- The value in the foreign key column (or columns) FK of the **referencing relation** R1 can be **either**:
1. a value of an existing primary key value of a corresponding primary key PK in the **referenced relation** R2
2. a **null**
- In case 2., the FK in R1 **should not** be a part of its own primary key.
#### Relational Database Schema
- A set S of relation schemas that belong to the same database.
- S is the name of the whole **database schema**.
- S = {R1, R2, ..., Rn} and a set IC of integrity constraints.
- R1, R2, ..., Rn are the names of the individual **relation schemas** within the database S.
- relation schemas example
##### Relational Database State
- A **relational database state** 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.
- sometimes called a relational database *snapshot* or *instance*.
- A database state that does not meet the constraints is an invalid state.
##### Populated database state
- Each *relation* will have many tuples in its current relation state.
- The *relational database state* is a union of all the individual relation states.
- Whenever the database is changed, a new state arises.
- Basic operations ofr changing the database:
- INSERT
- DELETE
- MODIFY
##### Displaying a relational database shcema and its constraints
- Each relation schema can be displayed as a row of attributes names.
- THe name of the relations is written above the attribute names.
- The primary key attribute(s) will be underlined.
- A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table.
- Can also point the primary key of the referenced relation for clarity.
- Example
##### Other Types of Constraints
- Semantic Integrity Constraints:
- based on application semantics and cannot be expressed by the model per se.
- A **constraint specification** language may have to be used to express these

### Update Operations and Dealing with Constraint Violations
#### Update Operations on Relations
- INSERT
- DELETE
- MODIFY
- Integrity constraints should not be violated by the update operations.
- Several update operations may have to be grouped together.
- Updates may **propagate** to cause other updates automatically. This may be necessary to maintain integrity constraints.
- In case of integrity violation, several actions can be takes:
- Canel the operation that causes the violation (RESTRICT or REJECT)
- Perform the operation but inform the user of the violation
- Trigger additional updates so the violation is corrected (CASCAED, SET NULL)
- Execute a user-specified error-correction routine
#### Dealing with Constraint Violations
- INSERT
- Domain constraint
- Key constraint
- Referential integrity
- Entity integrity
- DELETE
- If the primary key value of the tuple being deleted is referenced from other tuples in the database.
- UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified.

## Chapter 6. Basic SQL
*ommitted because it is too trivial, but remember to practice every day!*

## Chapter 7. More SQL: Complex Queries, Triggers, Views, and Schema Modification
*no need to take a note of this chap., but remember to read this frequently!*
### Outline
- More Complex SQL Retrieval Queries
- Sepcifying Semantic Constraints as Assertions and Actions as Triggers
- Views (Virtual Table) in SQL
- Schema Modification in SQL

### More Complex SQL Retrieval Queries
- Addition features allow users to specify more complex retrievals from DB:
- Nested queries, joined tables, and outer joins (in the FROM clause), aggregate functions, and grouping.
#### Comparisons Involving NULL and Three-Valued Logic
- Meanings of `NULL`
- Unknown value
- Unavailable or withheld value
- Not applicable attribute
- Each invididual `NULL` value considered to be different from every other NULL value
- SQL uses a three-valued logic:
- `TRUE`, `FALSE`, and `UNKNOWN` (like Maybe)
- NULL = NULL comparison is avoided
- SQL allows queries that check whether an attribute value is `NULL`.

#### Nested Queries, Tuples, and Set/Multiset Comparisons
- Nested queries
- Complete select-from-where blocks within WHERE clause of another query
- **Outer query and nested subqueries**.
- Comparison operator `IN`
- Compares value *v* with a set (or multiset) of values *V*.
- Evaluates to `TRUE` if *v* is one of the elements in *V*.
- Use tuples of values in comparisons.
- Place them within parentheses.
- Use other comparison perators to compare a single value *v*
- `= ANY` (or `= SOME`) operator
- Returns `TRUE` if the value *v* is equal to some value in the set *V* and is hence equivalent to `IN`.
- Other operators that can be combined with `ANY`.
- `ALL`: value must exceed all values from nested query.
- Avoid potential errors and ambiguities
- Create tuple variables (aliases) for all tables referenced in SQL query.
#### Correlated Nested Queries
- **Queries that are nested using the = or IN comparison operator** can be collapsed into one single block.
- **Correlated** nested query
- Evaluated once for each tuple in the outer query.
- `EXISTS` function
- Check whether the result of a correlated nested query is empty or not.
- They are Boolean functions that return a TRUE or FALSE result.
- `EXISTS` and `NOT EXISTS`
- Typically used in conjunction with a correlated nested query.
- SQL function `UNIQUE(Q)`
- Returns `TRUE` if there are no duplicate tuples in the result of query Q.
#### Specifying Joined Tables in the FROM Clause of SQL
- **Joined table**
- Permits users to specify a table resulting from a join operation in the FROM clause of a query.
#### Different Types of JOINed Tables in SQL
- Specify different types of join
- NATURAL JOIN
- Verious types of OUTER JOIN (LEFT, RIGHT, FULL)
- NATURAL JOIN on two relations R and S
- No join condition specified
- Is equivalent to an implicit EQUIJOIN condition for each pair of attributes with same name from R and S.
#### NATURAL JOIN
- Rename attributes of on relation so it can be joined with another using NATURAL JOIN.
#### INNTER and OUTER Joins
- INNET JOIN (**versus** OUTER JOIN)
- Default type of join in a joined table.
- Tuple is included in the result only if a matchiing tuple exists in the other relation.
- LEFT OUTER JOIN
- Every tuple in left table must appear in result.
- If no matching tuple
- Padded with NULL values for attributes of right table.
- RIGHT OUTER JOIN
- Every tuple in right table must appear in result.
- If no matching tuple
- Padded with NULL values for attributes of left table.
#### Multiway JOIN in the FROM clause
- FULL OUTER JOIN -- combines result if LEFT and RIGHT OUTER JOIN
- Can nest JOIN specifications for a multiway join.
#### Aggregate Functions in SQL
- Used to summarize information from multiple tuples into a single-tuple summary.
- Buit-in aggregate functions
- `COUNT, SUM, MAX, MIN`, and AVG
- **Grouping**
- Create subgroupsof tuples before summarizing
- To select entire groups, `HAVING` clause is used.
- Aggregate functions can be used in the `SELECT` clause or in a `HAVING` clause.
#### Aggregate Functions on Booleans
- SOME and ALL may be applied as functions on Blloean Values.
- SOME returns true if at least one element in the collection is TURE (similar to OR).
- ALL returns true if all of the elements in the collection are TRUE (similar to AND).

#### Grouping: The GROUP BY and HAVING Clauses
- **Partition** relation into subsets of tuples.
- Based on **grouping attribute(s)**.
- Apply function to each such group independently.
- `GROUP BY` clause
- Specifies grouping attributes
- COUNT(\*) counts the number of rows in the group.
- The grouping attribute must appear in the SELECT clause.
- If the groupgin attribute has NULL as a possible value, then a separate group is created fro the null value.
- GROUP BY may be applied to the result of a JOIN.
- `HAVING` clause
- Provides a condition to select or reject an entire group.
- Note: the WHERE clause applies tuple by tuple wheras HAVING applies to entire group of tuples.
#### Use of WITH
- The WITH clause allows a user to define a table that will only be used in a particular query (not available in all SQL implementations).
- Used for convenience to create a temporary "View" and use that immediately in a query.
- Allows a more straightforward way of looking a step-by-step query.

#### Use of CASE
- SQL also has a CASE construct
- Used when a value can be different based on certain conditions.
- Can be used in any part of an SQL query where a value is expected.
- Applicable when querying, inserting or updating tuples.

#### Recursive Queries in SQL
- An example of a **recursive relationship** between tuples of the same type is the relationship between an employee and a supervisor.
- This relationship is described by the foreign key Super_ssn of the EMPLOYEE relation.

### Specifying Constraints as Assertions and Actions as Triggers
- Semantic Constratins: The following are beyond the scope of the EER and relational model.
- `CREATE ASSERTION`
- Specify additional types of constraints outside scope of built-in relational model constraints.
- `CREATE TRIGGER`
- Specify automatic actions that database system will perform when certain events and conditions occur
#### Specifying Constraints as Assertions in SQL
- `CREATE ASSERTION`
- Specify a query that selects any tuples that violate the desired condition.
- Use only in cases where it goes beyond a simple `CHECK` which applies to individual attributes and domains.
#### Introdution to Triggers in SQL
- `CREATE TRIGGER` statement
- Used to monitor the database.
- Typical trigger has three components which make it a rule for an "active database":
- **Event(s)**
- **Condition**
- **Action**

### Views (Virtual Table) in SQL
- Concept of a view in SQL
- Single table derived from other tables called the **defining tables**.
- Considered to be a virtual table that is not necessarily populated.
#### Specification of Views in SQL
- `CREATE VIEW` command
- Give table name, list of attribute names, and a query to specify the contents of the view.
- Once the view is defined, SQL queries can use the View relation in the FROM clause.
- Views is always up-to-date.
- `DROP VIEW`
- Dispose of a view.
#### View Implementation, View Update, and Inline Views
- Complex problem of efficiently implementing a view of for querying.
- **Strategy 1: Query modification**
- Compute the view as and when needed. Do not store permanently.
- Modify view query into a query on underlying base tables.
- Flaw: inefficient for views defined via complex queries that are time-consuming to execute.
- **Strategy 2: View materialization**
- Physically create a temporary view table when the view is first queried.
- Keep that table on the assumption that other queries on the view will follow.
- Requires efficient strategy for automatically updating the view table when the base tables are updated.
- **Incremental update strategy for materialized views**
- DBMS determines what new tuples must be inserted, deleted, or modified in a materialized view table.
- Multiple ways to handle materialization:
- **immediate update**: updates a view as soon as base tables are changed.
- **lazy update**: updates the view when needed by a view query.
- **periodic update**: updates the view periodically. Commonly used in bank, retail stores, etc.
- Update on a view defined on a single table without any aggregate functions.
- Can be mapped to an update on underlying base table--possible if the primary key is preserved in the view.
- Update not permitted on aggregated view.
- View involving joins
- Often not possible for DBMS to determine which of the updates is intended.
- Clause `WITH CHECK OPTION`
- Must be added at the end of the view definition if a view is to be updated to make sure that tuples being updated stay in the view.
- In-line view
- Defined in the `FROM` clause of an SQL query.
#### Views as authorization mechanism
- Views can be used to hide certain attributes or tuples from unauthorized users.
### Schema Changes Statements in SQL
- **commands**
- DBA may want to change the schema while the database is operational.
- Does not require recompilation of the database schema.
- `DROP`
- Used to drop named schema elements, such as tables, domains, or constraint.
- behaviour options: `CASCADE` and `RESTRICT`
- `ALTER TABLE`
- **`ALTER TABLE` actions** include:
- Adding or dropping a column (attribute)
- Changing a column definition
- Adding or dropping table constraints.
- Adding and Dropping Constraints
- Change constraints specified on a table.
- Add or drop a named constraint.
- Dropping Columns, Default Values
- To drop a column
- Choose either `CASCADE` and `RESTRICT`.
- `CASCADE` would drop the column from view etc.
- `RESTRICT` is possible if no view refer to it.
- Default values can be dropped and altered.

### Applications
SQLite as a document database
https://dgl.cx/2020/06/sqlite-json-support

### Optimization
- What is the Postgres' SQL GAME plan (I)?
- https://blog.douenergy.com/what-is-the-postgres-sql-game-plan-i/