Introduction to Databases

Databases

• A database is an organized collection of structured information or data, typically stored electronically in a computer system.
• File-based systems are used to organize and maintain data files, but they are not very efficient.
• Limitations of file-based systems include storing large amounts of data, redundant data, lack of security, and difficulty sharing data with multiple users.
• Database Management Systems (DBMS) are software systems designed to manage and organize data in a structured manner.
• DBMS allow users to create, modify, and query databases, as well as manage security and access controls.
• RDBMS (Relational Database Management Systems) store data in tables.
• Examples of RDBMS include Oracle, SQL Server, MySQL, MS Access, and DB2.
• Advantages of DBMS include efficient data access, data integrity and security, concurrent access, and crash recovery.
• Disadvantages of DBMS include the large size of the software and potential performance issues.

Difference between File System and DBMS

• File System: Used to manage and organize files stored on a hard disk. Redundant data is present. Less security mechanisms.
• DBMS: A software for storing and retrieving user data. No presence of redundant data. Supports more security mechanisms.

Concepts of DBMS

• Fundamental concepts include data models, query languages, file organization, candidate keys, and keys.

Relational Data Model

• Data and relationships are structured as tables.
• Advantages include a simple format, effective record retrieval, and representation of different relationships.

Relation

• Refers to a table in a relational database and relationships that can be created between tables.

Tuples

• Rows in a relation (table) are called tuples.

Attributes

• Columns in a relation (table) are called attributes.

Schema of a Relation

• Schema refers to the structure of the table. The schema includes the name of the relation and all attributes.

Degree of a Relation

• The number of attributes (columns) in a relation (table) is called the degree.

Cardinality of a Relation

• The number of tuples (rows) in a relation (table) is called the cardinality.

Keys in DBMS

• Keys in DBMS are attributes or sets of attributes that help identify a row (tuple) in a relation (table).

Types of Keys

• Candidate Key: The minimal set of attributes that uniquely identify a tuple. A relation can have more than one candidate key.
• Primary Key: A minimal candidate key chosen to uniquely identify each row in the table.
• Alternate Key: Any candidate key which is not the primary key.
• Super Key: Super key is a group of single or multiple keys which identifies rows in a table.
• Foreign Key: An attribute in a table that is a primary key in another table. It helps to maintain data as well as referential integrity.

Structured Query Language (SQL)

• SQL is a standard language for storing, manipulating, and retrieving data in databases.
• Advantages include faster data retrieval, standardization, ease of use, and multiple data views.

MySQL Installation in Ubuntu

• Steps to install MySQL in Ubuntu using the command line.

Data types in SQL

• CHAR(Size): Used to specify a fixed-length string.
• VARCHAR(Size): Used to specify a variable-length string.
• INT(size): Used for integer values.
• DECIMAL(size,d): Used for fixed-point numbers.
• DATE: Used to specify date format YYYY-MM-DD.

Constraints in SQL

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• PRIMARY KEY: Uniquely identifies each row in a table.
• DEFAULT: Sets a default value for a column if no value is specified.
• CHECK: Ensures that values in a column satisfy a specific condition.

Types of SQL

• DDL: Data Definition Language. Commands for creating, deleting, and altering tables. (CREATE,ALTER, DROP,TRUNCATE)
• DML: Data Manipulation Language. Used to insert, update and delete data in the tables. (INSERT, UPDATE AND DELETE).
• DCL: Data Control Language. Used for user management and authorization.
• TCL: Transaction Control Language. Used for transaction management in the database. (COMMIT, ROLLBACK,SAVEPOINT)
• DQL: Data Query Language. Used to retrieve data from the database using the 'SELECT' command.

Data Manipulation Language(DML)

• INSERT INTO: Used to insert data into tables.
• UPDATE SET: Used to change the existing data in the tables.
• DELETE FROM: Used to delete data from the database.

Data Query Language (DQL)

• SELECT: Used to view data from tables.

Data Control Language (DCL)

• GRANT: Used to grant privileges to users.
• REVOKE: Used to remove privileges from users.

Transaction Control Language (TCL)

• COMMIT: Used to save all database transactions.
• ROLLBACK: Used to undo partially completed transactions.
• SAVEPOINT: Set a savepoint in the database and to undo to a specific point.

Clauses in SQL

• WHERE: Used to filter data retrieved from tables.
• ORDER BY: Used to sort data retrieved from tables.
• GROUP BY: Used to group rows with the same value(s) in one or more columns.
• HAVING: Used to filter grouped rows in a query.

SQL Functions

• Single row functions (e.g., UPPER(), LOWER(), CONCAT(), LENGTH(), SUBSTR())
• Aggregate functions (e.g., COUNT(), SUM(), AVG(), MAX(), MIN())

GUI Programming in Python

• Tkinter is a Python module for creating Graphical User Interfaces (GUIs).

• It uses widgets which include Labels, Buttons, Entries, Canvas, Checkbuttons and Radio buttons.

• GUI applications are used to get user input in a user-friendly way

• The basic steps in GUI programming are:

  • Import Tkinter module.
  • Create the main window using Tk().
  • Add widgets to the window.
  • Run the main event loop (window.mainloop()).

• Data types for widgets include StringVar(), IntVar()...etc.

File Handling in Python

• Opening and closing files in Python
• Python file object handling

Python Errors

• Syntax Errors: Occur when code doesn't follow Python's rules (grammar).
• Exceptions: Runtime errors (occur when the code is running).

Software Engineering

• Definition: Application of engineering principles, methods and procedures in the design and construction of software products.
• Software Products: Systems or software components delivered to customers with documentation for use.
• Need for Software Engineering: Higher rate of change in user requirements and environment.
• Characteristics of Good Software: Operational, Transitional, Maintainable
• Software Development Life Cycle (SDLC): Diagrammatic/descriptive representation of software life cycle.
• Waterfall Model: Describes a sequential process with specific phases (Requirements, Design, Implementation, Testing, Deployment, and Maintenance).
• Agile Model: An iterative and incremental approach emphasizing rapid delivery of working software.
• Different software development life cycle models.

Management Information Systems (MIS)

• Definition: A system that delivers information to managers for decision-making.
• Pillars: Management, Information, System.
• Importance: Supports business operations, decision-making, competitive advantage.
• Types of information.

Program Testing

• Testing involves providing program input and observing its behavior.
• Aim: Identifying defects, and increasing user confidence.
• Verification vs Validation

Types of Software Testing

• Unit Testing : Testing individual units/modules.
• Integration Testing : Testing interactions between modules.
• System Testing : Testing the entire system, covering all components.
• Acceptance Testing : Formal testing that confirms if the system meets user requirements.
• Alpha Testing : Internal testing by the development team.
• Beta Testing : Testing by external users before release.
• Gamma Testing: Final testing before software release.

Software Testing Tools

List of Software testing tools such as Selenium, Appium and others.

Cybercrime

• Definition of Cybercrime, common types of cyber crimes including Child abuse or exploitation, Computer Intrusion/Hacking, Cracking, Credit Card Fraud, E-mail/SMS Spoofing, Phishing, Software Piracy, and Denial-of-service Attack.
• Specific laws related to cybercrime (e.g., the Information Technology Act, 2000).

Blockchain

• Description of Blockchain technology.
• Key elements of a Blockchain (e.g., distributed ledger, immutable records, smart contracts).

Social Engineering

• Definition of Social Engineering.
• Steps involved in a social engineering attack.

Data Analytics

• Definition of Data Analytics.
• Steps involved in Data Analytics.
• Different types of analytical techniques (e.g., descriptive, advanced analytics).

Intellectual Property (IP)

• Copyright, Trademark, Patent, Industrial Property.

Internet of Things (IoT)

• Definition of IoT, its components, and different categories of IoT devices.

Artificial Intelligence (AI)

• Definition of AI, and its various types including supervised, semi-supervised, unsupervised, and reinforcement learning.