-
Notifications
You must be signed in to change notification settings - Fork 516
Week3 Readme
Objective: This class invites students to discuss Entity Relationship Diagram (ERD). Students should be able to explain their choices of entities, relationships, attributes etc. SQL injection should be explained with a demonstration (with a simple JS client). Concepts of database transaction, ACID properties, normal forms should be introduced with examples / live coding (creating a transaction, committing and rollback-ing).
This YouTube video by freeCodeCamp.org explains all the important topics.
Also, please read the following page that explains database foreign keys.
- Associative entities from many-to-many relationships
- Boolean attribute instead of a table
Database Design following normal forms as a convention. These normal forms build incrementally. E.g. The database is in 3NF if it is already in 2NF and satisfied the rules for 3rd normal form. Read [here] (https://www.studytonight.com/dbms/database-normalization.php) for more details.
- Rule 1 : Single valued attributes (each column should have atomic value, no multiple values)
- Rule 2 : Attribute domain should not change
- Rule 3 : Unique names for attributes / columns
- Rule 4 : Order does not matter
No partial dependency. (i.e. no field should depend on part of the primary key) Example
Score table (student_ID, subject_ID, score, teacher)
Subject table (subject_ID, subject Name)
No transitive dependency (i.e. no field should depend on non-key attributes).
for any dependency A → B, A should be a super key.
No multi-value dependency.
- Storing prices (floating point errors)
- Storing dates (datetime vs. timestamp)
- datetime : fixed value (joining date of employee): has a calendar date and a wall clock time
- timestamp : unix timestamp, seconds elapsed from 1 Jan 1970 00:00 in UTC (takes timezone into consideration)
-
A transaction is a set of commands that you want to treat as "one command." It has to either happen in full or not at all.
-
A classical example is transferring money from one bank account to another. To do that you have first to withdraw the amount from the source account, and then deposit it to the destination account. The operation has to succeed in full. If you stop halfway, the money will be lost, and that is Very Bad.
- To start transaction:
mysql> start;
OR
mysql> begin transaction;
- To commit, use
commit;and to abort, userollback; - Note that
autocommitvariable should be set to false for rollback to work.
mysql> set autocommit = 0;
- Atomicity : states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.
- Consistency : states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back, and the database will be restored to a state consistent with those rules.
- Isolation : requires that multiple transactions occurring at the same time not impact each other’s execution.
- Durability : ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.
Some SQL clients accept input from user to fabricate the queries.
A malicious user can tweak the input so as to acquire more information from the database or
to destroy the database (literally!). Demo program sql-injection.js is in the Week3 folder.
Consider the following query SELECT name, salary FROM employees where id = X.
If X is `101 OR 1=1`, then the query returns all records because 1=1 is always true
SELECT name, salary FROM employees where id = 101 OR 1=1;
If X is `101; DROP database mydb`, then the query will delete the entire database
SELECT name, salary FROM employees where id = 101; DROP database mydb;
mysqljs prevents the second injection by not allowing multiple SQL statements to be executed at once.
-
Procedures in SQL (aka Stored procedures) are similar to functions in other programming languages. i.e. You can define them once and call them multiple times. However, it should be noted that MySQL has two different concepts : functions and procedures. This stack overflow post has an excellent answer that describes the difference between MySQL functions vs procedures
-
There are two scenarios in which procedures are particularly useful: (credits to this stack overflow post)
- When we want to entirely encapsulate access to the database by forcing apps to use the stored procedures. This can be good for an organization with a strong/large database group and a small/weak programming team. It's also helpful when you have multiple code bases accessing the database, because they all get one interface, rather than each writing their own queries, etc.
- When you're repetitively doing something that should be done in the database.
- To create a procedure, use the following syntax:
Example:
delimiter //
create procedure countCountries (OUT param1 int)
BEGIN
select count(*) into param1 from country;
END
//
delimiter ;
- To see existing procedures, use the following command:
mysql> show procedure status where db = 'dbname';
- To call the procedure, use the following command:
mysql> call countCountries(@result);
mysql> select @result;
Jim (@remarcmij) wrote these excellent demo programs for better understanding. Do check them out.