Please enable JavaScript.
Coggle requires JavaScript to display documents.
Zybooks 2 - Coggle Diagram
Zybooks 2
Relational Model
-
-
-
Initially designed for transactional data, fell out of favor for big data, but have come back
-
Relational Rules
-
-
There are business rules, which are like business rules in 340. Stuff which can't change in the real world
-
Remember, there is a difference between relational rules and business rules. Business rules are more specific where relational rules are more broad rules for the whole database
Selecting Rows
Operators
Symbol that computes a value from one or more other values, called operands.
-
Comparison operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types.
-
Operators may return NULL when either operand is NULL. NULL-valued operations are discussed elsewhere in this material
-
SELECT statement
-
- means select everything, all columns
after SELECT columnyouwant1, columnyouwant2 ...
LIMIT clause
-
after FROM, do something like LIMIT 100 to limit the number of return entrees
-
Inserting, updating, and deleting rows
-
DEFAULT
-
means when you put in data, there is default value that gets inserted if not specified.
otherwise, the entrees are NULL
UPDATE and SET
if you dont specify WHERE, it does everything :smiley:
-
-
Primary Keys
-
Composite Primary Keys
-
-
denoted with parentheses (cola, colb)
must be...
unique - when grouped together, must be unique. cant be repeated
-
-
-
composit can be specified like this - PRIMARY KEY (ID, NUMBER)
-
Foreign Keys
What is it?
a column, or group of cols that refer to a primary key
data types of foreign and primary keys must be the same, but names must be different
-
may be repeated, may be null
-
-
-
-
Tables
Tables, Columns, and Rows
-
-
-
-
Table rules
-
-
-
-
data independence means administrators can improve query performance by changing the organization of storage devices with affecting query results
-
most Databases allow you to break the rules and have duplicate rows, but this is usually temporary
-
ALTER TABLE
adds, deletes, or modifies columns of existing table.
the exact syntax is in the book, chapter 2, section 4
-
Referential Integrity
-
basically, foreign keys must reference something
Actions
-
RESTRICT - rejects an insert, update or delete that violates referential integrity
-
-
-
-
Constraints
db auto rejects insert, update, and delete statements that violate a constraint
Some constraints
-
-
UNIQUE constraint
ensure that values in a column, or group of cols are unique
When applied to single column, UNIQUE may appear either in column declaration or separate clause
When applied to group of columns, UNIQUE is a table constraint and must appear in a separate clause
-
-
Managing databases
Database system instance
-
-
Shared computers, like on a cloud, usually run multiple instances of a database system
-
-
USE and SHOW statements
-
Several SHOW statements provide info about databases, tables and columns
-
-
-
-
Datatypes
Categories
Integer
positive, negative integers
Different types, depending on data size
-
-
-
-
Date and time
-
TIME, DATE, DATETIME, TIMESTAMP
Binary
exact data on memory or computer files, bit for bit
BLOB, BINARY, VARBINARY, IMAGE
Spacial
geometric information, lines, polygons, map coordinates.
-
new, tent to vary across systems
-
There are also some specialized ones, like MONEY, BOOLEAN, BIT, ENUM
-
-
