Please enable JavaScript.
Coggle requires JavaScript to display documents.
Introductory Databases (SQL Syntax (Delete (RESTRICT - Rows cannot be…
Introductory Databases
Intro
Database Management System (DBMS) - Provides an interface for the DB, has a bunch of tools for design, build and maintenance. Allows retrieval of data and queries. Eg MS Access.
ACID
-
-
-
-
These are properties all DB's must have. Maintained even if loss of power. Does this by saving after each step what has been completed
SQL Syntax
CREATE TABLE <table> (<attribute> <domain> [constraints], ...)
Constraints - Eg. "NOT NULL", "UNIQUE"
-
ALTER TABLE <table> [ADD COLUMN <attribute> <domain>] - Allows the definition of a table to be modified
Types - Eg. "BOOLEAN", "CHAR(30)", "INTEGER", "REAL", "DECIMAL", "DATE", "TIME"
-
-
-
-
-
-
-
-
-
SELECT <aggregate> (<attribute>) - Modifies the select. Eg. DISTINCT (removes duplicates), COUNT (counts rows), SUM (sums rows), MAX/MIN (gets max/min value), AVG (gets average)
SELECT <attribute> FROM <table> ORDER BY <attribute> ASC/DESC, ...
LIKE "_ob%" - Pattern matching. matches any char, % matches 0 or more chars
INNER JOIN - SELECT * FROM <table1>, <table2> WHERE <att1> = <att2>. This is the default join
SELECT * FROM <table1> LEFT/RIGHT/FULL JOIN <table2> ON <att1> = <att2>. Left join includes rows in the left table that aren't matched. Right join does right. Full join does both
-
-
Nested Queries - SELECT ... WHERE <att> > ALL(SELECT ...) - Uses the result of one query in another one. ALL (true if condition holds for all values). ANY (true if for any value), IN (IF in selection)
SELECT SUM(<attribute1>), <attribute2> FROM <table> GROUP BY <attribute2>. Basically groups all of the rows with the same attribute into a single row. Works well with aggregates. HAVING <condition> just adds a condition to the group by
-
SQL in Java
-
SQL constraints and queries are stronger so should be used over java. All java should do is create algorithms and data flow for the SQL.
Statement object - An object that handles sql statements. Multiple different statements can be run from the same object. SQL statement is run as soon as specified
Prepared Statement object - The SQL statement is prepared when initialised, so can only run the same statement. Uses ? for parameters. Safe against SQL injection
SQLExceptions are often useful as they contain information the program may need to decide what course of action to take
Design
-
Components - Attribute (oval), Table (rectangle), Diamond (relation set), Lines for cardinality relationships. Primary key underlined
-
Converting ERD to tables
1:1 Relationship - Create two tables with each of the rectangles, pick a primary one and add any relationship attributes plus a foreign key which is primary from the second table
-
M:N Relationship - Create a new table with both primary keys as foreign keys and the relationship attributes
-
Normalisation
Insertion Anomalies - Sometimes you want to insert a record with missing data. Eg. If "Bob" doesn't have a role yet
Update Anomalies - If information is needed to be updated, such as a Manager. Would need to be changed in all instances
Deletion Anomalies - What if a Manager leaves? The employees records would need to be updated manually
Normal Forms - Stages a DB goes through during normalisation, where after each dependency being removed, it moves up a level. 3NF is usually sufficient.