Understanding Relational Databases and Database Management Systems

Relational Databases CSC 242 Professor John Carelli Kutztown University Computer Science Department

Databases A database is an organized collection of data. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data for many users. Examples of DBMS s: Microsoft SQL Server, MySql, PostgreSQL, MongiDB, SQLite Today s most popular database management systems are relational databases. A relational database organizes data in tables Tables refer to each other Professor John Carelli Kutztown University Computer Science Department

Relational Databases A relational database stores data in tables with rows and columns. Each row contains information for a unique item in the table Rows are also referred to as records Each row contains a unique key that identifies that row Each column of a table contains information about the item referenced in a given row. Columns are also referred to as attributes Professor John Carelli Kutztown University Computer Science Department

Relationships Between Tables Primary key Attribute (or attributes) that uniquely identify a given row in a table Used for record retrieval and to facilitate relationships between tables Foreign key Creates a table relationship by referring to the primary key in another table Primary key is often an integer that auto-increments when a new row is added to the table. Professor John Carelli Kutztown University Computer Science Department

Database Languages Three Primary Tasks Data Definition Defines/creates the type(s) of data and relationships Query Retrieve/Read information from DB Data Manipulation Data insertion, deletion, and updating Professor John Carelli Kutztown University Computer Science Department

Relational Database Normalization Normalization is a systematic approach to organizing relational database tables to eliminate data redundancy and data manipulation anomalies Types of anomalies: Update Anomaly Insertion Anomaly Deletions Anomaly Database Normalization Source: Dylan Schwesinger Kutztown University Computer Science Department

SQL Structured Query Language CRUD Create Read Update Delete Features Can perform all primary tasks (CRUD) Provides command-based access to information in tables commands are case-insensitive(case doesn t matter) Declarative Language Describes what tasks to perform not how to perform them left up to the Database Management System Professor John Carelli Kutztown University Computer Science Department

SQL Table Creation/Deletion Create a new table CREATE TABLE Name (Column1 Type1, Column2, Type2, ) (Type# indicates the type of data, i.e. integer, string, real, etc ) Specify a Primary Key CREATE TABLE Name (Column1 Type1 PRIMARY KEY, Column2, Type2, ) Column data restrictions CREATE TABLE Name (Col1 Type1 NOT NULL, Col2 Type2 DEFAULT NULL ) NOT NULL - data cannot be NULL or empty DEFAULT NULL - data defaults to NULL or empty Delete an existing table DROP TABLE Name Professor John Carelli Kutztown University Computer Science Department

Basic SQL Query Commands Selecting column information from a table SELECT column1, column2....columnN FROM table_name Selecting column information that meets a condition SELECT column1, column2....columnN FROM table_name WHERE Item1 = Value1 Selecting column information that meets multiple conditions SELECT column1, column2....columnN FROM table_name WHERE Item1 = Value1 {AND|OR} Item2 = Value2 Professor John Carelli Kutztown University Computer Science Department

More SQL Queries Select everything matching a condition SELECT * FROM table_name WHERE Item1 = Value1 Select items using a wildcard SELECT * FROM table_name WHERE Title LIKE %match% Will match anything containing the string match Professor John Carelli Kutztown University Computer Science Department

Modifying the Database Remove (delete) information from a table DELETE FROM table_name WHERE Item1 = Value1 Insert new items (a row) INSERT INTO table_name (Item1, Item2, ) VALUES (Value1, Value2, ) Modify stored data UPDATE table_name SET Item1=Value1, Item2=Value2 WHERE Item3=Value3 Professor John Carelli Kutztown University Computer Science Department