Section 1
Preview this deck
ON DELETE RESTRICT
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
7 years ago
Date created
Mar 1, 2020
Cards (59)
Section 1
(50 cards)
ON DELETE RESTRICT
When someone tries to delete the row from the other table (master table), prevent deletion from that other table (and of course, also don't delete from me, but that's not the main point here).
candidate keys
a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
what term is synonymous with relation?
table
sql domain
new data type, not supported in mysql
relational table
A set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows, the cell being the unit where a row and column intersect.
Product list of tables
SHOW TABLES
cross join
each row from one table is combined with each row from another table
composite key
A primary key that consists of two or more fields.
foreign keys
A column or group of columns used to represent relationships. Values of the foreign key match values of the primary key in a different (foreign) table.
default date format for mysql
yyyy-mm-dd
can you code USING with ON
no
command to change value in record of a table
UPDATE
The order of precedence for the logical operators in a WHERE clause is
NOT, AND, OR
LIMIT 4, 7
return a maximum of 7 rows, starting with the 5th row
individual character wildcard
_
conceptual schema
high-level description of the whole database
internal schema
describes the physical storage structure of the database
the date data type can store
dates only, not times
null value
A special field value, distinct from 0, blank or any other value, that indicates that the value for the field is missing or otherwise unknown
data
facts that are recorded and can be accessed. In addition to text and numbers, data can also appear in other formats, such as figures, graphics, images, and audio/video recordings.
database management system
a computerized system that enables users to create and maintain a database. The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications.
database
a collection of related data
data manipulation language
A language associated with a database management system that end users and programmers use to manipulate data in the database.
relation instance
current set of rows for a relation schema
A full outer join returns
unmatched rows from both the left and right tables
atomic domain
If elements of the domain are considered to be indivisable units
multi-valued attributes
A single entity may have multiple values for that attribute. For example, Color of a Car or Previous Degrees of a STUDENT
advantages of dbms
1)Reductions of data redundancy and inconsistency 2) Potential for data sharing 3) data independence 4) data standardization 5) improved data security 6) Expanded data fields 7) Enhanced information timeliness, effectiveness and availability
super key
a combination of columns that uniquely identifies any row within a relational database management system (RDBMS) table
logical data independence
The capacity to change the conceptual schema w/o having to change the external schemas and their associated application programs
information
refers to the data that is accessed by a user for some particular purpose.
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
CASCADE DELETE
When a record in a parent table is deleted, deletes all the related records from the child table where the value from the primary key matches the value in the foreign key.
relation schema
relation name and its attributes
data redundancy
unnecessarily duplicated data are found in the database
surrogate key
A system-assigned primary key, generally numeric and auto-incremented.
DROP TABLE STUDENT;
remove both the data and the table structure of the table named student
data definition language
builds the data dictionary, creates the database, describes logical views, and specifies record or field security constraints.
display the results of the combined SQL statements without suppressing duplicate rows
UNION ALL
external schema
describe the views of different user groups
three tier architecture
users, servers, databases
SET NULL
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.
intersect
all rows in both tables
two tier architecture
client & server
cartesian product
The combination of all rows in the first table and all rows in the second table
physical data independence
Applications do not access data directly but, rather through an abstract data model provided by the DBMS. Applications immune to changes in storage structures
primary key
A field (or group of fields) that uniquely identifies a given entity in a table
derived attributes
Attributes Calculated
how do you simulate a full outer join
union
remove only the data from a table named STUDENT while leaving the table structure intact
DELETE FROM STUDENT;
Section 2
(9 cards)