Some Topics to Know and Understand
to prepare
for a DBMS related Job Interview
ACID
Database Normalization
-
1NF: No repeating groups possible for a cell, PK identified, dependencies mapped
-
2NF: No Partial Dependence. Non key attributes must have to depend on the full key
-
3NF: No transitive dependency. non key attributes must have to depend on the primary key, not on any other attributes. if there is another candidate key, still that is in 3NF
-
BCNF: 3NF satisfied but multiple candidate key exist then BCNF required
-
4NF: Multivalued Dependency exist. Need to remove that.
-
5NF: Do not have Join dependency
4NF: Normalization
-
Fourth Normal Form
-
Multivalued Dependency exist
-
Attributes independent of each other but depends on the same attribute
-
No row should show multivalued dependency
-
Solution: usually break into multiple tables
-
https://bohr.wlu.ca/cp363/notes/theory/20_4nf.php
-
Definition: Multivalued Dependency:
-
https://www.javatpoint.com/dbms-multivalued-dependency
-
5NF: is already in 4NF. and if contains join dependency.
-
Remove join dependency, break into new tables, but lossless break
-
https://www.javatpoint.com/dbms-fifth-normal-form
-
Table: lecturer, semester, course.
-
We cannot create semester without knowing other attributes.
-
Not in 5NF
-
Solution: usually break into multiple tables
Normalization
-
Normalization: Determinant
-
https://mix.sumdu.edu.ua/textbooks/11961/472103/index.htm
Read Misc.: PL/SQL, T-SQL
-
PL/SQL Tutorial:
-
https://www.tutorialspoint.com/plsql/index.htm
-
Oracle vs SQL Server
-
https://www.cybrary.it/blog/database-differences-microsoft-sql-server-vs-oracle-database
-
Oracle Vs MS SQL Server
-
https://www.guru99.com/oracle-vs-sql-server.html
-
https://www.guru99.com/oracle-vs-sql-server.html
-
T-SQL vs PL/SQL:
-
https://hevodata.com/learn/t-sql-vs-p-l-sql
Cursors, Triggers, Functions, Stored Procedures
-
An INSTEAD OF trigger
-
is an SQL trigger that is processed “instead of” an SQL UPDATE, DELETE or INSERT statement. Unlike SQL BEFORE and AFTER triggers, an INSTEAD OF trigger can be defined only on a view, not a table.
-
https://www.ibm.com/docs/en/i/7.2?topic=triggers-instead-sql
-
Implicit Cursor:
-
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/implicit-cursor-attribute.html [may have wrong info]
-
https://www.mygreatlearning.com/plsql/tutorials/pl-sql-cursors
-
Function vs Stored Procedure:
-
https://www.tutorialspoint.com/what-are-the-differences-between-stored-procedures-and-functions
Oracle Misc.
-
Oracle Three Tier:
-
Application Architecture:
-
https://docs.oracle.com/cd/E18727-01/doc.121/e12841/T120505T120508.htm
-
Oracle Datastore
-
https://www.oracle.com/webfolder/technetwork/data-quality/edqhelp/content/concepts/data_stores.htm
Oracle Admin and Policies
-
Oracle Tutorials:
-
https://www.oracletutorial.com/oracle-administration/
-
Database Admin Policy:
-
https://study.com/academy/lesson/database-security-policies-examples-and-creation.html
-
Establish Security Policies:
-
https://docs.oracle.com/cd/A58617_01/server.804/a58397/ch19.htm
-
The critical elements for creating a database environment are (1) data administration, (2) data-planning and modeling methodology, (3) database technology and management, and (4) users.
-
https://docs.microfocus.com/OpsCx/10.11/Content/policies/database/db_policies.htm
-
Policies in Oracle:
-
https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/policies.htm
-
Common Policies:
-
https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/commonpolicies.htm#top
Misc. Oracle
-
Oracle: SQL Plus
-
https://docs.oracle.com/cd/B14117_01/server.101/b12170/qstart.htm
-
Start Working with Oracle:
-
https://docs.oracle.com/en/database/oracle/oracle-database/21/
MongoDB Misc.
-
Tutorial:
-
https://www.tutorialspoint.com/mongodb/mongodb_interview_questions.htm
-
Read the Q & A, the online Quiz
-
BSON:
-
https://www.mongodb.com/basics/bson
-
Explain:
-
https://www.mongodb.com/docs/manual/reference/command/explain/
Sayed Ahmed
Professor, Software Engineer, Data Scientist, and ML EngineerPart-time Professor and Course Facilitator at Colleges in Ontario, Canada
Extensive experience in Software Development and Engineering
Significant experience in Teaching.
Taught in Universities, Colleges, and Training Institutes
Master of Engineering (MEng.) in Electrical and Computer Engineering (McMaster University) (Changed Ph.D. Studies to MEng)
MSc in Data Science and Analytics (TMU/Ryerson)
MSc in Computer Science (U of Manitoba)
BSc. Engineering in Computer Science and Engineering (BUET).
(Session: 1994-95 to 1996-97 Class: 1996 to 2001)
Linkedin: https://ca.linkedin.com/in/sayedjustetc