Week3Lecture_5b6f362d-6f91-49fe-a9df-40fdceb72991_82822_.pdf

Lecture 3: Relational Databases/Working with MySQL Hrishav Tandukar [email protected] CC4057 Introduction to Information Systems Contents constraints (continued..) M...

Lecture 3: Relational Databases/Working with MySQL Hrishav Tandukar [email protected] CC4057 Introduction to Information Systems Contents constraints (continued..) MySQL comparison and logical operators data dictionary CC4057 Introduction to Information Systems 2 Recap 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 Primary key - column which uniquely identifies each row in the table Foreign key - column which refers to the primary key in another table, relates two tables CC4057 Introduction to Information Systems 3 Constraints Some other useful constraints in MySQL are: Constraint Use UNIQUE Ensure that all the values in a column are unique/different NOT NULL Ensure that a column cannot have a NULL value DEFAULT Set a default value for a column when no value NULL values represent missing unknownisdataspecified CC4057 Introduction to Information Systems 4 Auto Increment Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record Useful for generating a unique primary key for each new record inserted into a table CC4057 Introduction to Information Systems 5 Example CREATE DATABASE college; USE college; CREATE TABLE groups (groupID INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(2) UNIQUE, profile VARCHAR(20) DEFAULT "IT"); CREATE TABLE students (studentID INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, DOB DATE NOT NULL, groupID INT, FOREIGN KEY (groupID) REFERENCES groups(groupID)); CC4057 Introduction to Information Systems 6 Example column names INSERT INTO groups (name,profile) VALUES ('C1','computing'); INSERT INTO groups (name,profile) VALUES ('C2','computing'); INSERT INTO groups (name,profile) VALUES ('N1','networking'); INSERT INTO groups (name,profile) VALUES ('N2','networking'); INSERT INTO groups (name) VALUES ('N3'); value for profile not given, so default value will be used INSERT INTO groups (profile,name) VALUES ('multimedia','M1'); auto incremented field is not needed to be specified in the insert statements CC4057 Introduction to Information Systems 7 Example INSERT INTO students (DOB,name,groupID) VALUES ("1997-02-11", "Jeff",1); INSERT INTO students (DOB,name,groupID) VALUES ("1998-05-11", "Anna",2); INSERT INTO students (name,DOB,groupID) VALUES ("Jack","1998-05-11", 3); INSERT INTO students (name,DOB,groupID) VALUES ("Mark","1996-10-1", 4); INSERT INTO students (name,DOB,groupID) VALUES ("Ben","1996-1-1",3); INSERT INTO students VALUES ("","Kate","1997-09-22",1); inserting a row without specifying columns, put empty string value for auto incremented field CC4057 Introduction to Information Systems 8 Example while inserting date always write date in the format YYYY-MM-DD and put inside quotations another useful data type is timestamp which is used to insert the current date and time CC4057 Introduction to Information Systems 9 SQL – WHERE Clause The WHERE clause is used to extract only those records that fulfill a specified criteria Used to filter/search data SELECT column_name, column_name FROM table_name WHERE column_name operator value; CC4057 Introduction to Information Systems 10 SQL – Operators Arithmetic Comparison Logical Operators Operators Operators +, -, *, /, % AND, OR, =,=, !=, NOT, BETWEEN, IN, LIKE CC4057 Introduction to Information Systems 11 Using where clause with date during comparison date can be written as string values or numeric values CC4057 Introduction to Information Systems 12  Example customers blue – primary keys yellow – foreign keys id name address suppliers 1 John Smith Kathmandu name phone 2 Allan Turing Pokhara Google 865-898-644 3 Sarah Jones Banepa Microsoft 661-182-856 4 Jeff Hinton Lalitpur Sony 500-122-999 5 Ashley Stone Chitwan 6 Donald Duck Chitwan item orders s name supplier price order_id customer_id item Nexus-5X Google 200 1 1 Xbox-360 Nexus-6P Google 300 2 1 Nexus-5X PS2 Sony 300 3 2 Nexus-6P Xbox-360 Microsoft 250 4 3 PS2 5 4 PS2 6 4 Nexus-5X CC4057 Introduction to Information Systems 13 Example - Between The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. CC4057 Introduction to Information Systems 14 Example - In The IN operator allows to specify multiple values in a WHERE clause. It performs membership check. CC4057 Introduction to Information Systems 15 Example - Like The LIKE operator is used in a WHERE clause to search for a specified pattern CC4057 Introduction to Information Systems 16 Aggregate functions SUM() – sum of all the values in a column MAX() – maximum value of a column MIN() – minimum value of a column AVG() – average value of a column CC4057 Introduction to Information Systems 17 Arithmetic operations CC4057 Introduction to Information Systems 18 Arithmetic operations AS can be used to give a temporary column name CC4057 Introduction to Information Systems 19 Arithmetic operations A simple query to calculate the discounted amount of all the items. CC4057 Introduction to Information Systems 20 Data Dictionary Describes the structure of the whole database Organized in a spreadsheet format Includes the names and descriptions of the tables and the fields contained in each table along with information of the data type, field length Main purpose: to provide metadata, or information about data Technically, it is a database about a database No one set standard in terms of layout or information CC4057 Introduction to Information Systems 21 Example of a data dictionary Entity Entity Column Column Data type Lengt Primary Foreig Nullable Unique Notes name descriptio name Description h key n key n Employee An ID ID of the INT True False False True Auto employee employee, for the Incremente is unique d someone identification of who works each employee in the company Name Name of the VARCHAR 255 False False False False employee Departmen ID of department VARCHAR 32 False True False False References t where the to ID employee works column of Department s table A separate table is created for each entity of the database !! CC4057 Introduction to Information Systems 22 End of Lecture 4 CC4057 Introduction to Information Systems 23  Thank you ! Any questions ? CC4057 Introduction to Information Systems 24