3.1.3. Common Database Objects

💡 First Principle: Relational databases use specific object types to organize data, optimize access, and provide abstraction layers for users and applications. Each object type serves a distinct purpose in the database ecosystem.

• Tables: The primary storage structures containing rows and columns. Each row is a unique record, and each column is a specific attribute with a data type.
• Views: Virtual tables based on the result set of a SQL query. They don't store data themselves but provide a way to look at data from one or more tables, often used for security or simplifying complex queries.
• Indexes: Structures used to speed up retrieval of data. Think of it like the index at the back of a book; it allows the database engine to find rows without scanning the entire table.
• Stored Procedures: Precompiled collections of SQL statements stored in the database. They enable code reuse, security (granting access to logic without raw data), and reduced network traffic.
• Triggers: Procedural code the database runs automatically in response to an event on a table — an INSERT, UPDATE or DELETE. Unlike a stored procedure, nobody calls a trigger; it fires whether the change came from an application, a script or a person. The classic use is an audit trail: a trigger on UPDATE writes the old values into a history table before the new ones land.
• Schemas: A named container that groups related objects inside one database — sales.Orders and hr.Employees can coexist without collision. Schemas organise a large database and give you something to grant permissions on as a unit, rather than table by table.
• Constraints: Rules the engine enforces on every write — PRIMARY KEY (unique row identity), FOREIGN KEY (referential integrity), NOT NULL, UNIQUE and CHECK. Constraints are what make the database, rather than the application, the last line of defence for data quality.

⚠️ Exam Trap: views, stored procedures and triggers are easy to conflate because all three hold SQL. Separate them by how they are invoked: a view is queried like a table, a stored procedure is called by name, and a trigger fires by itself when data changes.