DBMS that manages data as collection of tables in which all data relationships are represented by common values in related tables
Set of schemas that, when put together, constitute a description of a database
Structure that contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a DB
Data Definition Language
Commands used to define a DB, including those for creating, altering, and dropping tables and establishing constraints
Data Manipulation Language
Commands used to maintain and query a DB, including those for updating, inserting, modifying, and querying data
Data Control Language
Commands used to control a DB, including those for administering priviliges and committing (saving) data
Character (CHAR): stores string values containing any characters in a character set; fixed length. Character Varying (VARCHAR or VARCHAR2): stores string values containing any characters in a character set but of definable variable length. Binary Large Object (BLOB): stores binary string values in a hexadecimal format; variable length.
Numeric: stores exact numbers with a defined precision and scale. Integer (INT): stores exact numbers with a predefined precision and scale of zero.
Time Stamp: stores a moment an event occurs, using a definable fraction-of-a-second precision; adjusted for user's time zone.
Boolean: stores truth values (true, false, unknown).
Basic Syntax for Creating DB
CREATE SCHEMA database_name; AUTHORIZATION owner_user id
Used to define the portion of the DB that a particular user owns.
Defines a new table and its columns.
Defines a logical table from one or more tables or views.
CREATE TABLE Customer_T (CustomerID NUMBER (11,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30)...
ALTER TABLE table_name alter_table_action
ADD [COLUMN] column_definition ALTER [COLUMN] column_name SET DEFAULT default_value ALTER [COLUMN] column_name DROP DEFAULT DROP [COLUMN] column_name [RESTRICT] [CASCADE] ADD table_constraint
Example of ALTER TABLE command
ALTER TABLE Customer_T ADD COLUMN CustomerType VARCHAR2(2) DEFAULT "Commercial"
DROP TABLE command example
DROP TABLE Customer_T
INSERT command example
INSERT INTO Customer_T VALUES (001, 'Contemporary Casuals', '1355 S. Himes Blvd.', 'Gainesville', 'FL', 32601)
Entering Null Value
When inserting data into a DB and specifying only a few selected attributes to enter data into, while excluding the remainder of attribute fields
DELETE command example
DELETE FROM Customer_T WHERE CustomerState='HI'
UPDATE command example
UPDATE Product_T SET ProductStandardPrice = 775 WHERE ProductID = 7
CREATE INDEX command example
CREATE INDEX Name_IDX ON Customer_t (CustomerName)
DROP INDEX command
DROP INDEX Name_IDX
Lists the columns from based tables, derived tables, or views to be projected into the table that will be the results of the command (lists the data you want to display); necessary
Identifies that tables, derived tables, or views from which columns will be chosen to appear in the result table and includes the tables, derived tables, or views needed to join tables to process the query; necessary
Includes the conditions for row selection within the items in the FROM clause and the conditions between tables, derived tables, or views for joining; use the AND, OR , and NOT operators to customize conditions; optional
SELECT command example
SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice < 275
Data Manipulation Commands
UPDATE, INSERT, DELETE, SELECT; UPDATE, INSERT, DELETE allow you to modify tables, while SELECT allows you to query the data contained in the tables.
Indicate categorization of results
Indicate the conditions under which a category (group) will be included
Sorts the result according to specified criteria
COUNT command example
COUNT (*) FROM Orderline_t WHERE OrderID= 1004
Used in SELECT commands to select everything in that table; example= SELECT * Customer_T
Table in the relational data model containing the inserted raw data
Table constructed automatically as needed by a DBMS; not maintained as real data
Virtual table that is created dynamically upon request by a user; not temporary, but its definition is stored in the system catalog and the contents of the view are materialized as a result of the SQL query that uses the view
Copies or replicas of data, based on SQL queries created in the same manner as dynamic views