Lecture Note Week 1

Note

Learning Objective:

LO1: Understand business analytics

LO2: Understand the concept of database management system

LO3: Understand the concept of SQL

Section 1: Introduction to Database Management System

What is a Database Management System?

A database management system (DBMS) is a software system that enables users to define, create, maintain, and control access to a database. It is a collection of programs that enables users to create and maintain a database. A database is an organized collection of data that can be accessed, managed, and updated. A DBMS provides an interface for users to interact with the database, allowing them to perform operations such as querying, updating, and deleting data.

Why do we need a Database Management System?

A DBMS provides several benefits over traditional file-based systems:

  • Data Integrity: A DBMS enforces data integrity constraints to ensure that data is accurate and consistent. It prevents data duplication and enforces referential integrity between related data.
  • Data Security: A DBMS provides access control mechanisms to restrict unauthorized access to data. It allows users to define roles and permissions to control who can access, modify, and delete data.
  • Data Consistency: A DBMS ensures that data is consistent across the database. It enforces constraints to maintain data consistency and prevents anomalies such as data duplication and inconsistency.
  • Data Recovery: A DBMS provides mechanisms for data backup and recovery. It allows users to restore data to a previous state in case of data loss or corruption.
  • Data Scalability: A DBMS can scale to handle large volumes of data and users. It provides mechanisms for data partitioning, replication, and clustering to distribute data across multiple servers.
  • Data Performance: A DBMS optimizes data access and retrieval to improve performance. It uses indexing, caching, and query optimization techniques to speed up data processing.
  • Data Accessibility: A DBMS provides a centralized repository for data storage and management. It allows users to access data from multiple applications and locations.

What is a Database?

A database is an organized collection of data that can be accessed, managed, and updated. It is a structured set of data stored in a computer system. A database typically consists of one or more tables that contain rows and columns of data. Each table represents a different entity or object, and each row represents a record or instance of that entity. A database can store data in various formats, such as text, numbers, dates, images, and multimedia files.

Types of Databases

There are several types of databases, including:

  • Relational Databases: Relational databases store data in tables that are related to each other through common fields. They use structured query language (SQL) to query and manipulate data. Examples of relational databases include MySQL, PostgreSQL, Oracle, and SQL Server.
  • NoSQL Databases: NoSQL databases store data in a non-tabular format, such as key-value pairs, document stores, column-family stores, and graph databases. They are designed to handle large volumes of unstructured data and provide high scalability and performance. Examples of NoSQL databases include MongoDB, Cassandra, Redis, and Neo4j.
  • Object-Oriented Databases: Object-oriented databases store data as objects, which encapsulate data and behavior. They are designed to model complex data structures and relationships. Examples of object-oriented databases include db4o, ObjectDB, and ObjectStore.

Components of a Database

A database consists of several components, including:

  • Tables: Tables are the primary storage structures in a database. They store data in rows and columns, where each row represents a record and each column represents a field.
  • Columns: Columns are the vertical elements in a table that define the attributes of the data. Each column has a data type that specifies the type of data it can store.
  • Rows: Rows are the horizontal elements in a table that represent individual records. Each row contains data values for each column in the table.

Keys

A key is a field or combination of fields that uniquely identifies a record in a table. There are several types of keys, including:

  • Primary Key: A primary key is a unique identifier for a record in a table. It enforces data integrity and ensures that each record is uniquely identified.
  • Foreign Key: A foreign key is a field in a table that references the primary key of another table. It establishes a relationship between two tables and enforces referential integrity.
  • Composite Key: A composite key is a combination of two or more fields that uniquely identifies a record in a table. It is used when a single field cannot uniquely identify a record.

Section 2: Introduction to SQL

What is SQL?

SQL stands for Structured Query Language. It is a language used to communicate with databases. It is the standard language for relational database management systems. Why we need SQL? Let’s say you are suppose to design for a system that will store the information of students in a university. How would you store the information of students? You can store the information in a spreadsheet, but what if you have to store the information of thousands of students? It will be very difficult to manage the data in a spreadsheet. Also, where do you want to store the information? In a table? Will a table suffice? What if you have to store the information of the courses that the students are enrolled in? How will you manage the data? This is where databases come in.

Databases are used to store large amount of data. SQL is used to communicate with the database!

The database can be of any type like MySQL, PostgreSQL, SQLite, etc. By ignoring all those, let’s focus on the SQL language itself, which is used to communicate with the database. Once you master SQL, you can work with any database. The most popular database is MySQL. It is an open-source database. It is used by many companies like Facebook, Twitter, etc. Oracle is also a popular database. It is used by many big companies. SQL is used to communicate with the database. It is used to perform all types of operations on the database. It is used to create a database, create a table in the database, insert data into the table, update the data, delete the data, etc.

SQL Syntax

SQL syntax is the set of rules that defines how a SQL query should be written. It is the set of rules that all SQL queries should follow. SQL syntax is similar to the English language, which makes it easier to write, read, and understand. SQL syntax is divided into several categories. These categories are:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Query Language (DQL)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

Data Definition Language (DDL)

Data Definition Language (DDL) is used to define the structure that holds the data. It is used to create tables, columns, etc. DDL commands are auto-committed. It means that the changes made by the DDL command are saved to the database automatically. The following are the DDL commands:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • COMMENT

Data Manipulation Language (DML) 👈 Most Important!

Data Manipulation Language (DML) is used to manipulate the data itself. It is used to insert, update, delete, and retrieve data from the database. DML commands are not auto-committed. It means that the changes made by the DML command are not saved to the database automatically. You have to use the COMMIT command to save the changes. The following are the DML commands:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Data Query Language (DQL)

Data Query Language (DQL) is used to retrieve the data from the database. The SELECT statement is the most commonly used DQL command. The following are the DQL commands:

  • SELECT
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY

Data Control Language (DCL)

Data Control Language (DCL) is used to control the visibility of data. It is used to control the access of data. The following are the DCL commands:

  • GRANT
  • REVOKE

Basic SQL Syntax

The SELECT statement is used to retrieve data from a database. The basic syntax of the SELECT statement is as follows:

SELECT column1, column2, ...
FROM table_name;
  • SELECT: Keyword used to select data from a database.
  • column1, column2, ...: Columns that you want to retrieve from the database.
  • FROM: Keyword used to specify the table from which you want to retrieve data.
  • table_name: Name of the table from which you want to retrieve data.

Section 3: Live SQL Oracle

Oracle Live SQL is a free, interactive, web-based tool that allows users to write, run, and share SQL scripts and PL/SQL code. It provides a convenient platform for learning and practicing SQL without needing to install Oracle Database software locally. Key benefits include:

  1. Ease of Use: The web-based interface is user-friendly, making it accessible for both beginners and experienced users to write and execute SQL code.

  2. Educational Resources: Live SQL offers tutorials, sample scripts, and documentation to help users learn SQL and PL/SQL concepts and best practices.

  3. Collaboration: Users can share their scripts and solutions with others, fostering collaboration and knowledge sharing within the community.

  4. Real-Time Execution: Scripts run on an Oracle Database in real-time, providing immediate feedback and results.

  5. Access Anywhere: As a web-based tool, Live SQL can be accessed from any device with an internet connection, making it convenient for users to practice and develop their SQL skills on the go.

Oracle Live SQL is an excellent resource for anyone looking to improve their SQL proficiency, offering hands-on experience with Oracle Database in a flexible and collaborative environment.