INFORMATION-MANAGEMENT_-REVIEWER.pdf

DATABASE BASICS 1. Database vs. Table o A database is a collection of related data organized for efficient retrieval. o A table is an organized structure within a database that stores data in rows and columns. 2. Key Concepts:...

DATABASE BASICS 1. Database vs. Table o A database is a collection of related data organized for efficient retrieval. o A table is an organized structure within a database that stores data in rows and columns. 2. Key Concepts: o A table must be created after a database, and tables hold the actual data. o A table can only belong to one database, but a database can contain multiple tables. 3. Data Types in SQL o Common data types used in SQL: ▪ INT – for integers. ▪ VARCHAR(x) – for variable-length strings (x is the maximum length). ▪ DATE – for dates. ▪ FLOAT – for decimal numbers. SQL COMMANDS 1. Creating a Table: sql CREATE TABLE tblEmployee ( id INT, Phone INT, empNum VARCHAR(20), title VARCHAR(25), skill1 VARCHAR(30), skill2 VARCHAR(30) );  o Defines a new table with specified fields. 2. Inserting Data into a Table: sql INSERT INTO tblEmployee (id, Phone, empNum, title, skill1, skill2) VALUES (1, 12345, 'E001', 'Manager', 'Java', 'SQL'); o Note: Always ensure all values match the corresponding columns in both order and type. 3. Updating Data in a Table: sql UPDATE tblEmployee SET title = 'Teacher' WHERE phone = 126; o Modifies data in a specific row where a condition is met. 4. Deleting Data: sql DELETE FROM tblEmployee WHERE empName = 'Almond Robert'; o Removes records from a table based on a condition. 5. Selecting Data from a Table: sql SELECT * FROM tblEmployee WHERE title = 'Analyst'; o Retrieves all records where the title is ‘Analyst’. DATABASE SYSTEMS & TYPES 1. DBMS (Database Management System): o Software that manages databases and controls access to data. o Examples: MySQL, PostgreSQL, Microsoft SQL Server. 2. Types of Databases: o Single-user Database: Supports only one user at a time. o Multi-user Database: Allows multiple users to access data simultaneously. o Distributed Database: Data is stored across multiple locations. o Centralized Database: Data is stored in a single location. 3. Database Components: o Tables: Structures that store data. o Fields: Individual data elements in a table. o Records: A collection of related fields. DATA AND METADATA 1. Data: o Raw facts without meaning, such as numbers, dates, or strings. o Example: 12345, John, Marketing. 2. Metadata: o Data about data, defining the structure and properties of the stored data. o Example: A field labeled Name can be defined as VARCHAR(30) to store names of up to 30 characters. OPERATIONS IN SQL 1. Basic Operations:  o Insert: Adds new data into the table. o Update: Modifies existing data. o Delete: Removes data. o Select: Retrieves data based on specified criteria. 2. Advanced Operations: o NULL Values: Used to represent missing or undefined values. o Example: UPDATE tblEmployee SET title = NULL WHERE empName = 'Marc Benz'; 3. Common SQL Errors: o Ensure proper syntax (commas, quotes, and semicolons). o Example error: Missing comma between columns in an INSERT statement. DATABASE DESIGN & STRUCTURE 1. Database Design: o Refers to structuring a database to efficiently store and retrieve data. o Includes defining tables, relationships, and indexes for optimization. 2. Primary Keys & Foreign Keys: o Primary Key: Uniquely identifies each record in a table. o Foreign Key: Establishes relationships between tables. DATABASE USERS & ROLES 1. End-users: Individuals who interact with the database through applications or queries (e.g., sales clerks, managers). 2. Database Administrator (DBA): Manages the database and ensures data integrity and security. 3. System Analysts and Programmers: Design, develop, and maintain databases and applications that use them. PRACTICE QUESTIONS 1. Identify the SQL error in the following statement: sql INSERT INTO tblStudent (studID, lname, fname, course, yr) VALUES (112, 'Santos', 'Mark', 'BSIT', 2, 'B'); o Answer: There are six values being inserted, but only five columns specified. 2. What does the following SQL command do? sql SELECT * FROM tblEmployee WHERE EmpName = 'Almond Robert'; o Answer: Retrieves all columns for the employee named 'Almond Robert'. 3. What type of database supports data at a single location? o Answer: Centralized Database.