LAB-PRACTICE-HOME WORKS & SOFTWARE

Lab Works

I. DB Conceptual Design.

LW-01EN. Creation ER Diagrams with Online CASE Tools.

• LW-01EN. Report Blank Form.


• 0. Read. ERD Introduction. [pdf]
• 1. Soft. Draw.io [online]
• 2. Look. Draw.io - Creating ERD. [video 11 min]
• 3. Look. Draw.io - Creating ERD from SQL. [animation]
• 4. Look. Draw.io - ERD Example
• 5. Soft. LucidChart [online]
• 6. Look. LucidChart - ER diagram Tool [topic]
• 7. Look. LucidChart - ERD Example
• 8. Soft. VisualParadigm Online ERD Tool [online]
• 6. Soft. Creately.comt [online]

II. DB Logical Design.

LW-02EN. Creation Relation Data Model with Online CASE Tools.

• LW-02EN. Report Blank Form.


• 1. Look. Convert ERD to RDM on draw.io. [video 11 min]
• 2. Soft. DbDesigner.net - design RDM and generate SQL script for SQLite, MySQL, MSSql, Oracle.
• 3. Read. DBDesigner Support
• 4. Soft. dbDiffo - really free online data modeler RDM and generate SQL-code for PostgreSQL, MySQL, MSSql, Oracle, IBM DB2.

III. DB Physical Design. SQL DDL.

LW-03EN. Creation MySQL Database on a XAMPP Stack or DBMS Hosting.
LW-03ENa. MySQL Command Line Client Exercises.

• LW-03EN. Report Blank Form.


• 1. Soft. XAMPP Stack - Apache+MariaDB+PHP+Perl+phpMyAdmin.
• 2. Read. MySQL Client - Some Basic Operations with Command Line Client.
• 3. Read. MySQL Tutorial.
• 4. Soft. Db4free.net - free database hosting MySQL+phpMyAdmin.
• 5. Soft. Avardspace.com - free web hosting (MySQL+phpMyAdmin+CPanel+domain+LAMP+email+ZackyAppInstaller).
• 6. Soft. Paizer.io - online MySQL (Free).
• 7. Soft. RexTester.com - online MySQL (Free).Register
• 8. Soft. Adminer.org - online demo (root).
• 9. Soft. phpMyAdmin.net - online demo.
• 10. Soft. OneCompiler.com
• 11. Soft. FreeSQLdatabase.com
• 12. Soft. InfinityFree.com - Full Stack Free Web Hosting with PHP and MySQL.
• 13. Soft. Endor.dev - XAMPP for Browsers. (Any changes will be lost if you close this browser tab!)

IV. DB Data Modification.

LW-04EN. SQL: Data Modification Exercises (INSERT, UPDATE, DELETE, MERGE).

• LW-04EN. Report Blank Form.


• SQL Exercises. DML: INSERT, UPDATE, DELETE. Make ANY 11 from 57 tasks.
• Интерактивный учебник по SQL Full (EN=RU) (link)
• Упражнения и Сертификация к Интерактивному учебнику по SQL (EN=RU) (link)
• Краткий Интерактивный учебник по SQL (EN=RU) (link)
• Схемы БД для Интерактивного учебника по SQL (EN=RU) (link)

V. DB Data Retrieving.

LW-05EN. SQL: Data Retrieving Exercises (Select)

• LW-05EN. Report Blank Form.


• SQL Exercises. DML: SELECT (learning stage). Make ANY 31 from 190 tasks.
• Интерактивный учебник по SQL Full (EN=RU) (link)
• Упражнения и Сертификация к Интерактивному учебнику по SQL (EN=RU) (link)
• Краткий Интерактивный учебник по SQL (EN=RU) (link)
• Схемы БД для Интерактивного учебника по SQL (EN=RU) (link)

Top

Practice Works

*PW-01EN. SQL: Interactive Tutorial on SQL Bolt Site. (online)

• Without any Report.


• Complete 18 Lessons and 18 Exercises on SQL Bolt Site (https://sqlbolt.com/).

*PW-02EN. SQL: Training Quizzes on SQL Zoo Site.

• Without any Report.


• Complete next 8 Tutorials and 6 Quizzes on SQL Zoo Site (https://sqlzoo.net/):
0. SELECT basics Tutorial. (3 tasks + 7 quiz questions)
1. SELECT names Tutorial. (15 tasks + 0 quiz questions)
2. SELECT from WORLD Tutorial. (13 tasks + 7 quiz questions)
3. SELECT from Nobel Tutorial. (14 tasks + 7 quiz questions)
4. SELECT within SELECT Tutorial. (10 tasks + 7 quiz questions)
5. SUM and COUNT Tutorial. (8 tasks + 8 quiz questions)
6. The JOIN operation Tutorial. (13 tasks + 7 quiz questions)
8+. Numeric Examples Tutorial. (8 tasks + 0 quiz questions)

• Register on SQL Zoo. (link)
• Video-instruction. Analysis for Wrong Answer. [video 3 min]
• Video-instruction. Solution Example for Select in Select Nr 5 Solution. [video 5 min]

*PW-03EN (Optional). Free Querys Training on SQL Tutorial Site. (online)

• Without any Report.

*PW-04EN (Optional). SQL Quizzes and Exercises on w3schools Site (online).

• Without any Report.


• W3schools registration.
• 1. W3schools SQL Quiz. (25q/20min)
• 2. W3schools MySQL Quiz. (25q/20min)
• 3. W3schools SQL Exercises. (52 exercises in 16 groups)
• Make Your score ScreenShot.

Top

Home Works

HW-01,02,03,04,05EN. Relational Database Design. Guideline.

• HW-01,02,03,04,05EN. Task Variants.
• HW-01-05EN. Report Blank Form.

Names homework parties (you consistently solve one problem for all parts):

• HW-01EN. Conceptual Database Design. Subject Area Analyze, ERD.
• HW-02EN. Logical Database Design. Data Dictionary, RDM, Normalization.
• HW-03EN. Physical Database Design. SQL DDL/DML: Create, Insert Design.
• HW-04EN. SQL DML: Select Design.
• HW-05EN (Optional). SQL DDL/DML: View, Update, Delete Design.
• HW-06EN (Optional). Web Application Design for Databases.

Top

Softwares

• WWW SQL Designer [online] - similar to DbDesigner, but worse.
• phpMyAdmin [online] - press "Try demo" button.
• Adminer [online] - online demo (as root create new DB, select utf8_unicode_ci).
• Adminer - Home Page. [link]
• MySQL Workbench - unified visual tool for database architects, developers, and DBAs.
• MySQL Workbench for Mac.
• Visual Studio Code Download.
• MySQL Shell for VS Code.
• Visual Studio Code Download.
Server connection
• PuTTY 0.70 Portable [exe, 1.4 MiB]
• WinSCP 5.11.3 Portable [exe, 7.7 MiB]

Top