Lecture 2: Relational Databases PDF

Lecture 2: Relational Databases Hrishav Tandukar [email protected] CC4057 Introduction to Information Systems Contents Relational data model Entity and attributes Keys and rela...

Lecture 2: Relational Databases Hrishav Tandukar [email protected] CC4057 Introduction to Information Systems Contents Relational data model Entity and attributes Keys and relationships Entity relationship diagram CC4057 Introduction to Information Systems 2 Last week A database is a collection of information that is stored and organized so that it can be easily accessed, managed and updated A database consists of inter-related tables A table consists of rows which consist of related data A row is also known as a tuple and the columns are called attributes A DBMS is a software for creating and managing databases CC4057 Introduction to Information Systems 3 Relational data model Relational databases are collections of one or more relations In practice, relations are tables, the rows of which are individual records of data with same structure Items Suppliers ItemID Name Supplier Price SupplierID Name Phone I1 Xbox-2 S1 500 S1 Microsoft 759-965-845 I2 PS2 S2 450 S2 Sony 856-452-777 I3 Xbox-360 S1 550 S3 Google 555-444-856 I4 Nexus 5X S3 550 I5 Nexus 6p S3 600 CC4057 Introduction to Information Systems 4 SQL syntax to create the tables CREATE DATABASE mystore; USE mystore; CREATE TABLE suppliers (supplier_id VARCHAR(2), name VARCHAR(32), phone VARCHAR(11)); CREATE TABLE items (item_id VARCHAR(2), name VARCHAR(32), supplier VARCHAR(2), price INT); CC4057 Introduction to Information Systems 5 Constraints - Keys Constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table ensuring the accuracy and reliability of the data. Keys are, as their name suggests, a key part of a relational database and a vital part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. They help enforce integrity and help identify the relationship between tables. CC4057 Introduction to Information Systems 6 Key - Types PRIMARY uniquely identify each row in the table the columns must be unique, KEY not null and not empty FOREIGN refers to the primary key in another table relates 2 tables KEY CC4057 Introduction to Information Systems 7  here ItemID can be used to identify each item uniquely and SupplierID can Primary key be used to identify each supplier Uniquely identify each row in a table uniquely, so they are used as primary keys There can be only 1 primary key in a table !! Items Suppliers ItemID Name Supplier Price SupplierID Name Phone I1 Xbox-2 S1 500 S1 Microsoft 759-965-845 I2 PS2 S2 450 S2 Sony 856-452-777 I3 Xbox-360 S1 550 S3 Google 555-444-856 I4 Nexus 5X S3 550 I5 Nexus 6p S3 600 CC4057 Introduction to Information Systems 8  here supplier is a foreign key Foreign key which is used to relate the 2 tables foreign keys are used to relate tables IMPORTANT ! Supplier column here can A table can have multiple foreign only contain the values present in the keys !! SupplierID column in the suppliers table Items Suppliers ItemID Name Supplier Price SupplierID Name Phone I1 Xbox-2 S1 500 S1 Microsoft 759-965-845 I2 PS2 S2 450 S2 Sony 856-452-777 I3 Xbox-360 S1 550 S3 Google 555-444-856 I4 Nexus 5X S3 550 parent table I5 Nexus 6p S3 600 child table CC4057 Introduction to Information Systems 9  A relational database yellow – primary key Customers blue – foreign key CustID Fname Lname Address Phone Email Orders C1 Allan Smith Lalitpur 965-965-965 [email protected] OrderID Customer Item C2 Benny Greb Kathmandu 985-965-659 [email protected] O1 C1 I1 O2 C2 I2 C3 Jeff Hinton Pokhara 856-945-756 [email protected] O3 C3 I2 C4 Mark Johnson Banepa 985-799-755 [email protected] O4 C3 I3 C5 Allan Smith Kathmandu 564-856-965 [email protected] O5 C4 I4 Items O6 C5 I5 ItemID Name Supplier Price Suppliers I1 Xbox-2 S1 500 SupplierID Name Phone I2 PS2 S2 450 S1 Microsoft 759-965-845 I3 Xbox-360 S1 550 S2 Sony 856-452-777 I4 Nexus 5X S3 550 S3 Google 555-444-856 I5 Nexus 6p S3 600 CC4057 Introduction to Information Systems 10 Creating tables with primary & foreign keys CREATE TABLE suppliers (supplier_id VARCHAR(2) PRIMARY KEY, name VARCHAR(32), phone VARCHAR(11)); set supplier_id as the primary key in the suppliers table CREATE TABLE items (item_id VARCHAR(2) PRIMARY KEY, name VARCHAR(32), supplier VARCHAR(2), price INT, FOREIGN KEY (supplier) REFERENCES suppliers(supplier_id)); set item_id as the primary key in the items table and supplier as the foreign key which references to the supplier_id column in the suppliers table CC4057 Introduction to Information Systems 11 Entity – Relationship Diagram An entity-relationship diagram (ERD) is a graphical representation of an information system that shows the relationship between people, objects, places, concepts or events within that system. ERDs are used for data modeling which can help define business processes and can be used as the foundation for a relational database. CC4057 Introduction to Information Systems 12 ERD – example a customer can make multiple orders/a single order belongs to a single customer an item can occur in multiple orders/an order can have only 1 item (in this scenario) an item can have only 1 supplier/a supplier can supply multiple items CC4057 Introduction to Information Systems 13 Relational Diagram another form of ERDs each box is table which contains information about the attributes, primary keys and foreign keys each table is related using crow-foot notation CC4057 Introduction to Information Systems 14 SQL - categories SQL statements can be divided into 4 categories based on their uses Data DML Data Control TCL Definition Data Language Transaction Language Manipulation Control Language Language DDL DCL CC4057 Introduction to Information Systems 15 SQL - categories DDL CREATE, DROP, ALTER DML SELECT, INSERT, UPDATE, DELETE DCL GRANT, REVOKE TCL COMMIT, ROLLBACK CC4057 Introduction to Information Systems 16 SQL – DDL & DML Data Definition Language (DDL) or Data Description Language is used to build and modify the structure of tables and other objects of database. Data Manipulation Language (DML) is used for manipulating data stored in the tables We will be learning to use only these 2 categories of SQL statements CC4057 Introduction to Information Systems 17 Basic SQL commands Command Description CREATE DATABASE [DB NAME]; Create a database SHOW DATABASES; List all databases USE [DB NAME]; Select a database SHOW TABLES; List all tables in a database DESCRIBE [TABLE NAME]; View a table’s field formats DROP DATABASE [DB NAME]; Delete a database DROP TABLE [TABLE NAME]; Delete a table CREATE TABLE [TABLE NAME] ([COLUMN DETAILS]); Create a table INSERT INTO [TABLE NAME] VALUES (value1, … , value Insert rows in a n); table CC4057 Introduction to Information Systems 18 Some more SQL commands Command Description ALTER TABLE ADD COLUMN Add a column to ; an existing table ALTER TABLE DROP COLUMN ; Delete a whole column from a table SELECT * FROM WHERE Filter/Search ; data UPDATE SET Update values column1=value1,column2=value2,... WHERE some_column=some_value; DELETE FROM WHERE some_column=some_value; Delete a specific row DELETE FROM ; Delete all rows CC4057 Introduction to Information Systems 19 Example CC4057 Introduction to Information Systems 20 Example CC4057 Introduction to Information Systems 21 Example CC4057 Introduction to Information Systems 22 Example CC4057 Introduction to Information Systems 23 Example CC4057 Introduction to Information Systems 24 Example CC4057 Introduction to Information Systems 25 Example CC4057 Introduction to Information Systems 26 Example CC4057 Introduction to Information Systems 27 Example CC4057 Introduction to Information Systems 28 Example CC4057 Introduction to Information Systems 29 Example primary key must be unique CC4057 Introduction to Information Systems 30 Example cannot insert values which are not present in parent table’s primary key column CC4057 Introduction to Information Systems 31 Useful links MySQL datatypes - https://dev.mysql.com/doc/refman/5.7/en/data-types.html MySQL comparison operators - https://dev.mysql.com/doc/refman/5.7/en/data-types.html MySQL documentation - https://dev.mysql.com/doc/refman/5.7/en/ CC4057 Introduction to Information Systems 32 End of Lecture 3 CC4057 Introduction to Information Systems 33  Thank you ! Any questions ? CC4057 Introduction to Information Systems 34