Database Structure and Schema Quiz

Relational Databases: Store data in tables with rows & columns
Attributes (Columns): Represent individual properties of data, like ID, name, salary
Tuples (Rows): Represent individual entities or records, like a specific instructor's details
Domains: Define possible values for each attribute.

Database Schema: Defines the logical structure of a database, including its tables and attributes. Example: instructor(ID, name, dept_name, salary)
Database Instance: Represents a snapshot of actual data at a specific time, filling in the tables defined by the schema.

Superkey: A set of attributes whose values uniquely identify a tuple in any possible relation. Example: {ID} or {ID, name} are superkeys for the instructor relation.
Candidate Key: A minimal superkey, meaning no subset is also a superkey. Example: {ID} is a candidate key for instructor.
Primary Key: One of the candidate keys chosen to uniquely identify tuples in a table.
Foreign Key Constraints: Enforce relationships between tables. A value in one table (referencing relation) must correspond to a value in another table (referenced relation). Example: dept_name in instructor references the department table.

Diagrammatically represent the structure of a database, showing tables and their relationships. Often use symbols to depict relationships like foreign key constraints.

Procedural vs Non-Procedural:
- Procedural languages specify step-by-step instructions.
- Non-procedural (declarative) languages describe what to retrieve, not how.
"Pure" Query Languages:
- Relational Algebra: A procedural query language with set operations and relational operators.
- Tuple Relational Calculus: A non-procedural language that uses logical formulas to define queries.
- Domain Relational Calculus: Similar to tuple calculus, but uses variables representing domain values.
Equivalence: These pure languages are equivalent in expressive power, meaning they can express the same queries.

Purpose: A procedural query language using operations to manipulate relations.
Basic Operations:
- Select (σ): Retrieves tuples meeting a specified condition.
- Project (Π): Extracts specific columns from a relation.
- Union (∪): Combines two relations, removing duplicates.
- Set Difference (-): Returns tuples in one relation, but not in another.
- Cartesian Product (x): Creates all possible combinations of tuples from two relations.
- Rename (ρ): Assigns a new name to a relation or attribute.

SQL (Structured Query Language) is a language for interacting with relational databases.
It's used for querying data, inserting new data, deleting data, and modifying existing data.
SQL has been standardized by ANSI and ISO.
Commercial database systems support most SQL-92 features and offer proprietary extensions beyond standard SQL.

SQL originated as "Sequel" at IBM's San Jose Research Laboratory.
It was developed as part of the System R project.
SQL has undergone multiple revisions as standards have evolved: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003.

char(n): Fixed-length character strings, with user-specified maximum length n.
varchar(n): Variable-length character strings, with user-specified maximum length n.
int: Integers (dependent on machine precision).
smallint: A subset of integers, dependent on machine precision.
numeric(p,d): Fixed-point numbers, with user-specified precision of p digits, with d digits to the right of the decimal point.
real, double precision: Floating-point and double-precision floating-point numbers.
float(n): Floating-point numbers, with user-specified precision of at least n digits.