CSE 305: Database Systems Midterm I Material
Semantic Constraints
Express the logic of the application at hand: e.g. number of registered students <= maximum enrollment
Example of semantic constraint
Limit attribute Values:: CREATE TABLE Transcript( StudId INTEGER, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1), CHECK(Grade IN ('A','B','C','D', 'F')), CHECK(StudID > 0 AND StudID < 1000000000) ) -- Each row in table must satisfy condition
Relation
Mathematical entity corresponding to a table: i) row ---> Tuple ii) column ---> Attribute Relation R can be thought of as a predicate R(x,y,z) is true IFF tuple (x,y,z) is in R. Why relations? They are a very simple model.
System Admin
maintains transaction processing system: monitors interconnection of HW and SW modules, deals with failures and congestion.
Cardinality of Relation
number of tuples
Domain
possible values of an attribute
Superkey
set of attributes containing key {ID, name} is a superkey of Student
Serial Execution
synonym for sequentially: A set of transactions is executed sequentially if one transaction in the set is executed to completion before another is started. Serializable execution has better preformance than serial, but performance might still be inadequate.
Data Model
Mathematical representation of data ex: relational model = tables semistructured model = trees / graphs * Determines the operations on the data * Determines the constraints on the language
Key
Minimum set of attributes that uniquely identifies an entity (candidate key)
Minimality property
NO SUBSET OF A1,..., An is a key constraint
Airity of a Relation
Number of Attributes
Data Warehouse
Offline: Repository of historical data generated from OLTP or other sources
Data Manipulation Language
Operations on Data
Physical Data Independence
Physical schema can be changed without changing application: * DBMS would change mapping from conceptual to physical transparently * This property is referred to as physical data independece
User-Defined Domains
Possible attribute values can be specified: Creating a new domain CHECK clauss
Semantic Constraints in SQL
Primary key and foreign key are examples of ________________ constraints
Inclusion Dependency
Referential integrity contraint that is not a foreign key constraint: Foreign key constraint is just a special kind of inclusion dependency where referrenced attribute set is a key.
Relation Schema
Relation name + attribute list (optionally: types of attributes) Ex: Student(ID, name, address, status) ---> CAPS (actually would be underline: is a KEY) * Tuples cannot have the same value in all key attributes, excellent example of a constraint.
Examples of SELECT FROM WHERE
SELECT Id, Name FROM Student SELECT Id, name, FROM Student WHERE Status = 'senior'
SQL Data Definition Sublanguage
SQL is a language for describing database schema and operations on tables Data Definition language DDL sublanguage of SQL for describing schema.
Structured Query Language
SQL: Language for manipulating tables Declarative: Statement specifies what needs to be obtained, not how it is to be achieved (e.g., how to access data, the order of operation=)
CREATE SCHEMA <name>
SQL: Create a new schema
Database Schema
Set of all Relation Schemas in the Database
Declarative
Statement specifies what needs to be obtained, not how it is to be achieved (e.g., how to access data, the order of operation=) SQL determines evaluation strategy but this does not mean that the DBMS is not incapable of making mistakes or being wrong.
Rollback
TP MONITOR: Responsibility of ensuring that whatever partial changes the transaction has made to the database are undone. An example of this is known as __________________.
TPS
TP Monitor: Transactions get passed to the DBMS and the DBMS updates the database This whole system is reffered to as the ________________________________.
Unary
Takes a single argument, usually a relation itself: (e.g. delete certain rows)
Binary
Takes two arguments: Operation on two relations. (e.g. union, Cartesian product)
External Data Level
The _________________________________ customizes the conceptual schema to the needs of various classes of users (it also plays a role in database security). In the relational model, the external schema also presents data as a set of relations. The external schema looks and feels like a conceptual schema, and both are definied in essentially the same way in modern DBMSs There might be several external schemas (i.e. views on the conceptual schema), usually one per user category.
Database System
The collection of database applications that interact with the database along with the DBMS and the database itself.
Data Mining
Use of warehouse data to discover relationships (discovers hidden patterns in data) that might influence enterprise strategy.
Simplified Database System Environment
Users / Programmers v DBS: Application Programs / Queries v DBMS Software: Software to Process Queries / Programs v Software to Access Stored Data v v Stored Database Definition (Metadata) + stored database
Reactive Constraints
When a constraint is violated, the corresponding transaction is typically aborted. However in some cases other remedianl actions are more appropriate. Foreign key constraints are one example of this situation. It would be nice to have a mechanism that allows a user to specify how to react to a violation of a constraint. A ______________________ constraint is a static constraint coupled with a specification of what to do if a certain event happens. Triggers attached to a foregin key constraints.
Transaction
When an event in the real world changes the state of an enterprise, a "transaction" is executed to cause the corresponding change in the database state. ---> Transaction is an application: ACID properties
Consistency
[ACID Properties] Each transaction maintains database ___________________ . __________________ means that a transaction must access and update the database in such a way that it preserves all database integrity constraints. The transaction designer must ensure that: IF the database is in a state that satisfies all integrity constraints when execution of a transaction is started THEN the same should be true when the transaction completes. (constraints can be violated in intermediate states).
Isolation
[ACID Properties] The concurrent execution of a set of transactions has the same effect as some serial execution of that set. A set of transactions is executed sequentially, or serially, if one transaction in the set is executed to completion before another is started. If all transaction are coinsistent and the database is initially in a consistent state, serial execution maintains consistency. But serial execution maintains consistency. But serial execution is inadequate from a performance perspective.
Durability
[ACID Properties] The effects of committed transactions are permanently recorded in the database _____________ means that the system must ensure that once the transaction commits, its effects remain in the database even if the computer or the medium on which the database is stored subsequently crashes.
Atomicity
[ACID properties] Each transaction is executed completely or not at all ____________ means that the system must ensure that the transaction either runs to completion (i.e commits) or if it does not complete has no effect at all as if it has never been started (i.e aborts) TP MONITOR: Responsibility of ensuring that whatever partial changes the transaction has made to the database are undone (i.e. rolled back)
conceptual, physical
[Conceptual Data Level] DBMS maps _____________________ schema to __________________ schema This is known as physical data independence
Long Lifetime
[Database Systems Requirements] Complex systems are not easily replaced, must be designed so they can be easily extended as the needs of the enterprise change
High Reliability
[Database Systems Requirements] Correctly tracks state, does not lose data, controlled concurrency
High Availability
[Database Systems Requirements] Online: Must be operational while enterprise is functioning
Low Response Time
[Database Systems Requirements] Online: users are waiting
Database Security
[Database Systems Requirements] Sensitive information must be carefully protected since system is accessible to many users, Authentication, authorization, encryption.
High Throughput
[Database Systems Requirements] many users: many transactions per second
Static
[Integrity Constraints] Restricts legal states of data base
Semantic
[Integrity Constraints] Sub-category of static: Involves the meaning of attributes cannot register for more than 18 credits
Syntactic
[Integrity Constraints] Sub-category of static: Structural constraints: e.g. all values in a column must be unique
WHERE a2 = b2 (filter)
[Join] B 17 rst 3.2 17 B 17 rst 4.8 17
FROM T1,T2
[Join] Yields: Cartesian Product a1 a1 a3 b1 b2 A 1 xxy 3.2 17 A 1 xxy 4.8 17 B 17 rst 3.2 17 B 17 rst 4.8 17
Application Programmer
[Roles in Design, Implementation, and Maintenance of a TPS] Implements application programs (transactions) that access data and support enterprise rules.
Database Admin
[Roles in Design, Implementation, and Maintenance of a TPS] Maintains the database once system is operational, space allocation, performance optimization, database security
Database Designer
[Roles in Design, Implementation, and Maintenance of a TPS] Specifies structure of data that will be stored in database.
System Analyst
[Roles in Design, Implementation, and Maintenance of a TPS] Specifies system using input from customer; provides complete description of functionality from customer's and user's point of view
Trigger Keyword
___________ is a schema element (like table assertion, etc)
Triggers
____________ are a more general mechanism for handling events CREATE __________________ CrsChange AFTER UPDATE OF CrsCode, Semester ON Transcript WHEN (Grade IS NOT NULL) ROLLBACK
Dynamic
______________ constraints are a limitation on sequences of database states for e.g. cannot raise the salary by more than 5% in one transaction.
Integrity Constraints
_____________________ also known as consistency constraints are rules of the enterprise generally limiting the occurrence of certain real world events. EX: A Student cannot register for a course if current number of registrants = maximum allowed.
INSERT INTO
________________________ Student(Id, Name, Address, Status) VALUES (12345, 'John Smith', 123 Main St, NYC', NULL)
Concurrent Execution
________________________ is when multiple transactions are executed simultaneously Different transactions are effectively interleaved in time.
Integrity Constraint (IC)
_______________________________ is a statement about all legal instances of a database: Restriction on a state or of sequence of states of a database it is a part of the schema
Referential Integrity
An item named in one relation must refer to tuples that describe that item in another.
Entity
An object that is involved in the enterprise. (think of as an instance) Ex: John, CSE 305
Database
* Collection of data central to some enterprise (Ex of an enterprise: SBU Solar, Amazon, eBay, etc.)
Table
* Set of rows, no duplicates * Each row describes a different entity * Each column states a particular fact about each entity * Each column has an associated domain
Transaction Processing System (TPS)
* Transaction execution is controlled by a Transaction Processing (TP) monitor * TP monitor and DBMS together guarantee the special properties of transactions. TPS = TP Monitor + (possibly) Multiple Databases & transactions
Handling Foreign Key Violations
1) Reject if no row exists in B containing foreign key of inserted row 2)Delete B 3) Set NULL in A CASCADE: Delete referencing rows in A as well
Entity Type
A Set of similar objects: Ex: Students, courses
Transaction
A ___________________ is a program that accesses the database in response to real world events Additional Information: Many enterprises use databases to store information about their state: E.g. Balances of all depositors. * The occurrence of a real world event that changes the enterprise state requires the execution of a program that changes the database state in a corresponding way (e.g. balance must be updated when you withdraw)
Database Application
A computer program that interacts with the database by issuing an appropriate request (typically using SQL statements to the DBMS).
Relational Database
A database schema = the set of a relation schemas and constraints among relations (inter-relation constraints) * A relational database (or database instance) = a set of corresponding relation instances.
Key Constraint
A key constraint or candidate key is a sequence of attributes A1,...An (n = 1 is possible) of a relation schema, S with the following property: A relation instance s of S satisfies the key constraint IFF at most one row in s can contain a particular set of calues, a1, ... an for the attributes A1, ... An
Relation Schema
A relation schema is the heading of that table and the appicable constraints (integrity constraints) COMPOSED OF: Relation name Attribute names & domains Integrity constraints such as the values of a particular attribute in all tuples are greater than 0 (although the domain can be bigger) Default values for some attributes. A relation consists of a relation schema and a relation instance.
Relationships among entities
A relationship captures how entities are related to one another: can be thought of as verb
Relation Instance
A set of tuples
Online Analytical Processing (OLAP)
Analysis of information in a database for the purpose of making management decisions. * Analyzes historical data (terabytes) using complex queries * Summarizes the data and makes forecasts "What are the avg sales of cars by region and by year?"
Assertion
Applies to entire database (not just the individual rows of a single table) Unlike CHECK, conditions that appear inside a table definition, those in the CREATE ASSERTION statement must be satisfied by the
SELECT a1, b1 (projection)
B 3.2 B 4.8
Primary / Candidate Keys
CREATE TABLE Course( CrsCode CHAR(6), CrsName CHAR(20), DeptID CHAR(4), Descr CHAR(100), PRIMARY KEY(CrsCode), UNIQUE(DeptID, CrsName) -- candidate key) )
Table Declaration
CREATE TABLE Student(Id INTEGER, Name CHAR(20), Address CHAR(50), Status CHAR(10) )
Entities
Can be thought of as nouns: Examples: a computer, an employee, a song
Database
Collection of relations
Data Definition Language
Conceptual and external schema, * Integrity constraints and comains
Database Management System (DBMS)
DBMS is a program that manages a database. ---> Supports high-level access language (SQl) ---> DBMS interprets statements of language to perform rquested database access.
Data Model
Data and it's Structure
Online Transaction Processing (OLTP)
Day to day handeling of transactionsthat result from enterprise operation, maintains correspondence between database state and enterprise state
Attribute
Describes one aspect of an entity type * Every attribute of an entity specifies a particular property of that entity. Example: an employee entity might have a social security number
Schema
Description of data at some abstraction level
SELECT Name, CrsCode, Grade FROM Student, Transcript WHERE Transcript.StudId = Student.Id AND Status = 'senior'
GOAL: extract a table in which each row names a senior student and gives a course taken and grade It combines information from two tables: Student: Id, Name, Address, Status Transcript: StudID, CrsCode, Semester, Grade
Storage Definition Language
The directives that influence the physical schema (affects performance, not semantics)
Every relation has a super key
The set of all attributes in a schema S is always a superkey because if a legal instance of S has a pair of tuples that agree on all attributes in S then these must be identical tuples since relations are sets they cannot have identical elements
Operations
They take relations as an argument, produce new relation as a result. * Using mathematical properties, equivalence can be decided, important for query optimization: opt(T1, op2(T2)) =? op3(op4(T1), T2)
Modifying Tables
UPDATE Student SET Status = 'Soph' WHERE Id = 111111111 INSERT INTO Student(Id, name, Address, Status) VALUES (99999999, 'Bill', '432 Pine', 'senior') DELETE FROM Student WHERE Id = 111111111
WHERE
indicated which rows to retain (acts like a filter)
FROM
indicates source tables
SELECT
indicates which columns / attribute to extract from the retained rows.
Inter-relational
involve several relations e.g. the value of the attribute ID of each professor shown as Teaching a course must appear as the value of the ID attribute of some row of the table Professor (foreign Key)
Intra-relational
involves only one relation e.g. all Student IDs are unique (key)
DROP TABLE <name>;
to delete a relation
