MIS303 Database 1 PDF

# DATABASE - 1 ## Dr. Ahmed Mounir ## Contents ### Chapter 1: Introduction to Database - 1.1 Introduction - 1.2 Traditional File Systems (TFS) - Traditional File Systems (TFS) - Problems with TFS - Data redundancy and inconsistency - Program-data dependence - Lack of flexibility - Po...

# DATABASE - 1 ## Dr. Ahmed Mounir ## Contents ### Chapter 1: Introduction to Database - 1.1 Introduction - 1.2 Traditional File Systems (TFS) - Traditional File Systems (TFS) - Problems with TFS - Data redundancy and inconsistency - Program-data dependence - Lack of flexibility - Poor security - Lack of data sharing and availability - 1.3 Database Management System (DBMS) - What is a DBMS? - Benefits of DBMS over TFS - Features of DBMS - 1.4 Types of Databases - 1.4.1 Relational Database - What is a relational database? - Examples of RDBMS - 1.4.2 Object-Oriented Databases - What are object-oriented databases? - Advantage of object-oriented databases - 1.4.3 Hierarchical Database - What are hierarchical databases? - Examples of hierarchical databases - 1.4.4 Network Database - What are network databases? - Examples of network databases - 1.5 Data Warehouses and Data Mining - 1.5.1 Data Warehouse - What is a data warehouse? - Benefits of data warehouses - 1.5.2 Data Mining - What is data mining? - Use cases of data mining - 1.6 NoSQL Database - 1.6.1 Document-Based Database - What are document-based databases? - 1.6.2 Key-Value Stores - What are key-value stores? - 1.6.3 Column Oriented Databases - What are column-oriented databases? - 1.6.4 Graph-Based databases - What are graph-based databases? - 1.7 NewSQL Database - What are NewSQL databases? - Features of NewSQL databases - Review Questions ### Chapter 2: Relational Database - 2.1 Introduction - 2.2 Relational Database Management Systems (RDBMS) - What is a RDBMS? - Benefits of RDBMS over other types of database - Features of RDBMS - 2.3 Properties of Relational Database - What are transactions? - What are ACID properties? - Atomicity - Consistency - Isolation - Durability - 2.4 Entity Relationship Diagram (ERD) - What is an ERD? - 2.4.1 Entity - What are entities? - What are entity sets? - How to represent entities in an ERD? - 2.4.2 Attributes - What are attributes? - What are the different types of attributes? - How to represent attributes in an ERD? - Simple attributes - Composite attributes - Derived attributes - Multi-value attributes - 2.4.3 Relationship - What are relationships? - How to represent relationships in an ERD? - Cardinality - One-to-one relationship. - One-to-many relationship. - Many-to-many relationship. - Participation - Total participation - Partial participation - 2.5 ERD Example - What is an ERD? - Entities and Attributes - Users - Articles - Comments - Relationships - Writes - Posts - Has - 2.6 Types of Keys in Relational Model - What are keys? - Primary key - Foreign key - Composite key - Alternate key - Candidate key - Super key - 2.7 Normalization - What is normalization? - 2.7.1 First Normal Form (1NF) - What is the definition of 1NF? - Objective of 1NF - Convert a table into 1NF - 2.7.2 Second Normal Form (2NF) - What is the definition of 2NF? - Objective of 2NF - Convert a table into 2NF - 2.7.3 Third Normal Form (3NF) - What is the definition of 3NF? - Objective of 3NF - Convert a table into 3NF - 2.8 Structured Query Language (SQL) - What is SQL? - SQL commands - Data Definition Language (DDL) - Data Manipulation Language (DML) - Data Control Language (DCL) - Transaction Control Language (TCL) - Review Questions ### Chapter 3: MySQL - 3.1 Introduction - 3.2 XAMPP - What is XAMPP? - Benefits of using XAMPP - Components of XAMPP - Apache - MySQL - PHP - Perl - 3.3 Installing MySQL - 3.3.1 Downloading XAMPP - How to download XAMPP? - 3.3.2 Running the XAMPP Installer - How to run the installer? - How to select components to install? - How to choose the installation folder? - How to complete the installation? - 3.4 Runing MySQL - How to open the XAMPP Control panel? - How to start MySQL and Apache? - 3.5 Create a simple MYSQL Database - How to create a database in phpMyAdmin? - 3.6 Creating and Managing Tables - How to create tables? - How to manage tables? - How to add data into your tables using the graphical interface in phpMyAdmin? - How to insert data into your tables using SQL queries? - 3.7 Securing MySQL - How to set a root password? - 3.8 Import & export database - How to import a .sql file using phpMyAdmin? - How to export a database using phpMyAdmin? - 3.9 Run SQL Query in phpMyAdmin - How to run a SQL query in phpMyAdmin? - Review Questions ### Chapter 4 : Data Definition Language (DDL) - 4.1 Introduction - 4.2 Data Types - How to define data types in SQL? - 4.2.1 String Data Types - CHAR(size) - VARCHAR(size) - BINARY(size) - VARBINARY(size) - TINYBLOB - TINYTEXT - TEXT(size) - BLOB(size) - MEDIUMTEXT - MEDIUMBLOB - LONGTEXT - LONGBLOB - ENUM(vall, val2, val3, ...) - SET(vall, val2, val3, ...) - 4.2.2 Numeric Data Types - BIT(size) - TINYINT(size) - BOOL - BOOLEAN - SMALLINT(size) - MEDIUMINT(size) - INT(size) - INTEGER(size) - BIGINT(size) - FLOAT(size, d) - FLOAT(p) - DOUBLE(size, d) - DOUBLE - PRECISION(size, d) - DECIMAL(size, d) - DEC(size,d) - 4.2.3 Date and Time Data Types - DATE - DATETIME(fsp) - TIMESTAMP(fsp) - TIME(fsp) - YEAR - 4.3 Create Database - How to create a databas using SQL? - 4.4 Drop Database - How to drop an existing database using SQL? - 4.5 Create Table - How to create a new table in a database using SQL? - 4.6 Drop Table - How to drop an existing table in a database using SQL? - How to delete data inside a table without deleting the table itself? - 4.7 Alter Table - How to modify the structure of existing tables in a database: - 4.7.1 Add Column - How to add columns to a table using SQL? - 4.7.2 Drop Column - How to delete columns from a table using SQL? - 4.7.3 Modify Column - How to modify the data types of columns in a table using SQL? - 4.8 Constraints - What are constraints? - NOT NULL - UNIQUE - PRIMARY KEY - FOREIGN KEY - CHECK - DEFAULT - CREATE INDEX - 4.9 AUTO_INCREMENT - What is AUTO_INCREMENT? - Define a column as AUTO_INCREMENT? - Change the starting value of an AUTO_INCREMENT? - Review Questions ### Chapter 5 : Data Manipulation Language (DML) - 5.1 Introduction - What is DML? - DML Commands - 5.2 SELECT - What is SELECT? - Syntax of SELECT - 5.3 DISTINCT - What is DISTINCT? - Syntax of DISTINCT - 5.4 WHERE - What is WHERE? - Syntax of WHERE - Text Fields vs. Numeric Fields - How to use quotes with text fields? - How to use quotes with numeric fields? - Operators - AND - OR - NOT - Combining AND, OR and NOT - Example of Combining AND, OR and NOT - NULL - IS NULL - IS NOT NULL - 5.5 ORDER BY - What is ORDER BY? - Syntax of ORDER BY - Example of using ORDER BY - ASC - 5.6 INSERT INTO - What is INSERT INTO? - Syntax of INSERT INTO - Example of using INSERT INTO - 5.7 UPDATE - What is UPDATE? - Syntax of UPDATE - Example of using UPDATE - 5.8 DELETE - What is DELETE? - Syntax of DELETE - Example of using DELETE - 5.9 LIMIT - What is LIMIT? - Syntax of LIMIT - Example of using LIMIT - 5.10 MIN and MAX - What are MIN and MAX? - Syntax of MIN and MAX - Example of using MIN and MAX - 5.11 COUNT, AVG and SUM - What are COUNT, AVG and SUM? - Syntax of COUNT, AVG and SUM - Example of using COUNT, AVG and SUM - 5.12 LIKE - What is LIKE? - Syntax of LIKE - Example of using LIKE - 5.13 IN - What is IN? - Syntax of IN - Example of using IN - 5.14 BETWEEN - What is BETWEEN? - Syntax of BETWEEN - Example of using BETWEEN - 5.15 Aliases - What are aliases? - When to use aliases? - Syntax of alias - Example of using aliases - 5.16 GROUP BY - What is GROUP BY? - Syntax of GROUP BY - Example of using GROUP BY - 5.17 HAVING - What is HAVING? - Syntax of HAVING - Example of using HAVING - 5.18 EXISTS - What is EXISTS? - Syntax of EXISTS - Example of using EXISTS - 5.19 ANY and ALL - What are ANY and ALL? - Syntax of ANY and ALL - Example of using ANY and ALL - 5.20 CASE - What is CASE? - Syntax of CASE - Example of using CASE - 5.21 INSERT INTO SELECT - What is INSERT INTO SELECT? - Syntax of INSERT INTO SELECT - Example of using INSERT INTO SELECT - 5.22 Comments - What are comments? - Types of comments - Single line comments - Multi-line comments - 5.23 UNION - What is UNION? - Syntax of UNION - Example of using UNION - 5.24 JOIN - What is JOIN? - INNER JOIN - Syntax of INNER JOIN - Example of inner join - LEFT JOIN - Syntax of LEFT JOIN - Example of LEFT JOIN - RIGHT JOIN - Syntax of RIGHT JOIN - Example of RIGHT JOIN - CROSS JOIN - Syntax of CROSS JOIN - Example of CROSS JOIN - 5.24.5 Self Join - What is Self JOIN? - Syntax of Self JOIN - Example of Self JOIN - Review Questions ## References - [Chan, J. (2024)](https://www.w3schools.com/MySQL/default.asp). Learn SQL Using MySQL in One Day and Learn It Well: SQL for Beginners with Hands-On Project. Packt Publishing Ltd. - Coronel, C., & Morris, S. (2018). Database Systems: design, implementation, & management. Cengage Learning. - Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems, Global Edition. - Gillenson, M. L. (2023). Fundamentals of database management systems. John Wiley & Sons. - Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern Database Management, Global Edition. - W3Schools.com: MySQL Tutorial. MySQL. Retrieved September 15, 2024, from https://www.w3schools.com/MySQL/default.asp