JMN ACADEMY

Posts

Practical 1: Getting Started with MYSQL

Getting Started with MySQL Introduction to MySQL Definition: MySQL is an open-source relational database management system (RDBMS) Uses: Web applications, data warehousing, e-commerce, logging applications Key features: Speed, reliability, scalability, and ease of use Installing MySQL Download MySQL Community Server from official website Follow installation wizard for your operating system Set root password during installation Verify installation: mysql --version MySQL Command-line Client Accessing MySQL: mysql -u root -p Basic commands: SHOW DATABASES ; CREATE DATABASE mydb ; USE mydb ; SHOW TABLES ; MySQL Workbench Introduction: Visual tool for database design and management Key features: SQL development Data modeling Server administration Example: Creating a new connection New Connection > Enter details (hostname, username, password) PHPMyAdmin Web-based MySQL administration tool Often comes pre-installed with web hosting packages

MYSQL Constraints

PK - Primary Key: Uniquely identifies each record in a table. NN - Not Null: Ensures a column cannot have a NULL value. UQ - Unique: Ensures all values in a column are different. B - Binary: Stores binary byte strings. UN - Unsigned: For numeric types, allows only non-negative values. ZF - Zero Fill: Pads numeric values with zeros to the left. AI - Auto Increment: Automatically generates a unique number for new records. G - Generated Column: Value is computed from an expression. PK - Primary Key A primary key uniquely identifies each record in a table. It must contain unique values and cannot have NULL values. Example: CREATE TABLE Students ( StudentID INT PRIMARY KEY , Name VARCHAR ( 50 ) , Age INT ) ; Here, StudentID is the primary key. NN - Not Null This constraint ensures that a column cannot have NULL values. Example: CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY , Name VARCHAR ( 50 ) NOT NULL , Email VARCHAR ( 100 ) ) ;

Key Terminologies used in Relational Database

1. Database Definition: A database is a structured collection of data stored and managed electronically. It allows for efficient data retrieval, manipulation, and management. Characteristics: - Structured : Data is organized in tables with rows and columns. - Persistent: Data remains available over time. - Accessible : Allows querying and manipulation through a Database Management System (DBMS). 2. Database Management System (DBMS) Definition: A DBMS is software that facilitates the creation, manipulation, and management of databases. It provides an interface for users and applications to interact with the data. Characteristics : - Data Definition: Allows creation and modification of database schemas. - Data Manipulation : Supports querying, updating, and deleting data. - Data Security: Manages user access and data integrity. 3. Table (Relation) Definition: A table, also known as a relation, is a collection of rows and columns used to store data in a relational database. Characteri

Keys in Relational Database

Database keys are fundamental to relational database design, ensuring data integrity, uniqueness, and efficient data retrieval. They also play a crucial role in establishing relationships between tables. Let's explore each type of key in detail using an expanded student records example. Student Table 1. Super Key Definition : A super key is a set of one or more attributes (columns) that uniquely identifies a record in a table. Characteristics: - Uniqueness : A super key can have multiple attributes and still uniquely identify each record. - Can contain extra attributes not necessary for unique identification - Any superset of a key is also a super key - May include redundant attributes Examples in Student Table: - {StudentID} - {StudentID, FirstName} - {StudentID, Email} - {StudentID, FirstName, LastName, Email} - {Email, PhoneNumber} Importance: Super keys form the basis for understanding and identifying more specific types of keys. They help in recognizing all possible combinatio

Brief History of Databases

Flat File Systems (1950s-1960s) Earliest form of data storage Characteristics: Data stored in plain text files Each line represents a record Fields separated by delimiters (e.g., commas, tabs) Advantages: Simple and easy to understand Suitable for small amounts of data Disadvantages: Data redundancy Lack of data independence Difficult to manage relationships between data Limited data integrity and security Example use: Early payroll systems Hierarchical Model (1960s) Introduced by IBM with Information Management System (IMS) Structure: Tree-like structure with parent-child relationships One parent can have multiple children, but each child has only one parent Characteristics: Based on parent-child relationships Efficient for one-to-many relationships Advantages: Fast data retrieval for hierarchical queries Good for applications with natural hierarchies (e.g., organizational structures) Disadvantages: Inflexible structure Difficulty in represe

Types of DBMS

DBMS Types Hierarchical DBMS: Structure: Data organized in a tree-like structure with parent-child relationships. Example: IBM's Information Management System (IMS) Use case: Organizational charts, file systems Imagine a family tree: Grandparent Parent 1 Child 1 Child 2 Parent 2 Child 3 Network DBMS: Structure: Data organized in a graph-like structure allowing many-to-many relationships. Example: Integrated Data Store (IDS) Use case: Complex relationship modeling Think of a social network: Person A is friends with Person B and Person C Person B is friends with Person A and Person D Person C is friends with Person A and Person D Relational DBMS: Structure: Data organized in tables with rows and columns, using relationships between tables. Examples: MySQL, Oracle, PostgreSQL Use case: Most common type, used in various applications Imagine a library system: Books table: (ID, Title, Author, ISBN) Members table: (ID, Name, Address, Phone) Loans table