Download Relational Database Concepts and SQL and more Exams Service Management in PDF only on Docsity!
Data management applications D
database model components data structure, operations, and rules relational model database model based on tabular data structure; used for banks, airline reservations big data characterized by unprecedented data volumes and rapidly changing data structures Set unordered collection of elements enclosed in braces {a,b,c} = {c,b,a} tuple ordered collection of elements closed in parenthesis (a,b,c) is different from (c,b,a) table has a name, fixed tuple of columns, and a varying set of rows
column has a name and a data type row unnamed tuple of values, each value corresponds to a column and belongs to the columns data type Since a table is a set of ___: the ___ have no inherent order row data type named set of values, from which column values are drawn table, column, row, data type Databases relation, attribute, tuple, domain Mathematics
select rows that appear in one table but not another relational operation: rename changes a table name relational operation: aggregate computes functions over multiple table rows, such as sum and count relational rules logical constraints that ensure data is valid which database is relational? MySQL what was the initial impediment to commercial adoption of relational databases in the early 1980s processing speed which terms are commonly used in database processing
row, table, column in relational data structure, which components are named data type, table, column T/F in relational databases, rows do not have names true can a query select one specific row from a table Yes, by specifying one or more row values T/F since the result of relational operations is always a table, the result of an SQL query is also a table True what is the result of a relational operation? a table T/F a SQL statement can implement only one relational operation false
SQL Sublanguage: data query language retrieves data from the database SQL Sublanguage: data manipulation language manipulates data stored in a database SQL Sublanguage: data control language controls database user access SQL Sublanguage: data transaction language manages database transactions SQL Sublanguage insert a data into table product DML SQL Sublanguage rollback database changes DTL
SQL Sublanguage select all rows from table Product DQL SQL Sublanguage grant all permissions to user 'tester' DCL SQL Sublanguage create table Product DDL " INSERT INTO Student VALUES (888, 'Smith', 'Jim', 3.0); " How many clauses are in this INSERT statement? 2 CREATE DATABASE DatabaseName creates a new database named 'DatabaseName' DROP DATABASE DatabaseName deletes a database named 'DatabaseName', including all tables in the database
Assume database already contains a database called university: The statement DROP DATABASE nonprofit; deletes the nonprofit database False SHOW DATABASES lists databases available in the database system T/F to use SHOW tables for a certain database, you must first USE DatabaseName in order to make that the default database to pull from True Statement will show all columns in the Country table (if already inside the database with the country table) SHOW COLUMNS from Country Exactly one value per cell, no duplicate column names, no duplicate rows, no row order relational rules for tables statement creates a new table by specifying the table name, column names, and column data types CREATE TABLE
statement will deletes a table along with the table rows, from a database DROP TABLE statement can add, delete, or modify columns on an existing table ALTER TABLE ALTER TABLE clause to add column ADD ALTER TABLE clause to modify a column CHANGE ALTER TABLE clause to delete a column DROP T/F a table must have at least one row false the term to refer to no row order in tables
data type: character CHAR (fixed string) and VARCHAR (string of var. length up to specified max size) data type: date and time DATE, TIME, DATETIME, TIMESTAMP data type: binary BLOB, BINARY, VARBINARY, IMAGE data type: spatial POLYGON, POINT, GEOMETRY data type: document XML or JSON data type: BOOLEAN true/false values
INT or DECIMAL signed vs unsigned allows/disallows number to be negative symbol that compares a value from one or more other values called operands operators Operator compares two values for inequality != or <> logical operator returns TRUE only when both values are TRUE AND logical operator returns FALSE only when both values are FALSE OR logical operator reverses a logical value NOT
data type that will store the price of an item that ranges from a few dollars to a few hundred dollars with the minimum storage requirements DECIMAL(5,2) data type that will store a students assigned letter grade with the minimum storage requirements CHAR(1) special value that represents either unknown or inapplicable data NULL prevents column from having NULL value (will reject statements) NOT NULL IS NULL or IS NOT NULL values can be used in the ___ clause when selecting rows WHERE MySQL and query results: FALSE represents 0 while TRUE represents 1
INSERT clauses INTO and VALUES DEFAULT values keyword and ___ value follow the column name and data type in a CREATE TABLE statement; this value is assigned rather than NULL when omitted from an INSERT statement the SET and WHERE clause are used with which statement UPDATE This keyword follows DELETE and prior to TableName from which rows are to be deleted FROM Omitting this clause in a DELETE FROM statement results in all rows being deleted WHERE (condition here) Statement deletes all rows from a table TRUNCATE TABLE TableName
foreign key T/f data types of foreign key and primary key must be the same, but names may be different true T/f primary key and foreign key rules are the same: no repeated values, no NULL values false foreign key rule foreign key value must either be NULL or match some value of the referenced primary key REFERENCE keyword follows the FOREIGN KEY Rejects an insert, update, or delete that violates referential integrity. RESTRICT sets invalid foreign keys to NULL
SET NULL
sets invalid foreign keys to the foreign key default value SET DEFAULT apply to primary key update and delete, and foreign key insert and update RESTRICT, SET NULL, and SET DEFAULT propagates primary key changes to foreign key CASCADE T/F on foreign key inserts and updates: MySQL supports only restrict true T/F for primary key updates and deletes: MySQL supports 4 actions True ____ and ____ are followed by either RESTRICT, SET NULL, SET DEFAULT, or CASCADE ON UPDATE, ON DELETE
