Chapter5.pdf

CHAPTER 5 Relational Database Management Systems and SQL Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com. History of Relational DBMS & SQL ▪ Relational databases proposed by E.F. Codd in 1970...

CHAPTER 5 Relational Database Management Systems and SQL Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com. History of Relational DBMS & SQL ▪ Relational databases proposed by E.F. Codd in 1970 Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ SQL used in System R, research relational database in early 1970s – D.D. Chamberlin et. al. at IBM San Jose Research Center ▪ SQL used in Oracle-Ellison, Miner & Oakes, released in late 1970s ▪ INGRES, Wong & Stonebraker, UC Berkeley ▪ SQL incorporated into IBM’s SQL/DS in 1981, and DB2 in 1983 ▪ SQL used in Microsoft SQL Server, MySQL, Informix, Sybase, PostgreSQL, Microsoft Access, Teradata, MariaDB, and others Standards ▪ ANSI and ISO published SQL standards in 1986, called SQL-1 Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Minor revision, SQL-89 ▪ Major revision, SQL-2,1992 ▪ Major revision, SQL-3 (multi-part revision), 1999, 2003, 2006, 2008, 2011, 2016, 2019-new data types, object-oriented (OO) facilities, user defined datatypes (UDTs), triggers, support for XML and JSON, window analytic functions, temporal databases, multi-dimensional arrays, row pattern matching, polymorphic tables ▪ Most vendors support standard with slight variations Components of SQL ▪ Data Definition Language – DDL Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ To create and modify database structures ▪ Data Manipulation Language – DML ▪ To store, access, and modify data ▪ Authorization language ▪ To grant privileges to users The relational model example ▪ This is the relational model that will be used in most of the following examples in this Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com chapter: DDL Commands CREATE DATABASE Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com CREATE SCHEMA CREATE TABLE CREATE INDEX ALTER TABLE RENAME TABLE DROP TABLE DROP INDEX CREATE VIEW Note: In MySQL, CREATE DATABASE and CREATE SCHEMA are the same. In other databases, a database holds multiple schemas. CREATE DATABASE, CREATE SCHEMA Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ CREATE DATABASE dbname; ▪ Example: CREATE DATABASE University; To start using this Database (other systems may need Connect): USE University; ▪ In many other systems (not MySQL), you need to create a schema CREATE SCHEMA schema_name [AUTHORIZATION user_name]; CREATE TABLE CREATE TABLE [schema-name.] base-table-name (colname datatype [column constraints] Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com [,colname datetype [column constraints]]... [table constraints] [storage specifications]); Example: CREATE TABLE Student ( stuId VARCHAR(6) PRIMARY KEY, lastName VARCHAR(20) NOT NULL, firstName VARCHAR(20) NOT NULL, major VARCHAR(10), credits INTEGER(3) DEFAULT 0, CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150))); Identifiers (table/attribute name) ▪ No SQL keywords ▪ Table name unique within the database Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Column name unique within the table ▪ In Oracle ▪ Identifiers must be at most 128 bytes long, begin with an alphabetic character, and contain only alphanumeric characters or underscores ▪ Can use either uppercase or lowercase letters, but Oracle always displays them as uppercase ▪ In MySQLLength: ▪ Identifiers can be up to 64 characters. ▪ Case Sensitivity: Varies by OS. On Linux, they’re case-sensitive; on Windows/macOS, they’re not. ▪ Allowed Characters: Identifiers can start with a letter or underscore and contain alphanumeric characters, underscores (_), and dollar signs ($). Datatypes ▪ Each column must have a datatype specified Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Available datatypes vary from DBMS to DBMS ▪ Standards include various numeric types, fixed-length and varying-length character strings, bit strings, and user-defined types ▪ Common Oracle types include CHAR(n), VARCHAR2(n), NUMBER(p,s), FLOAT(p), DATE, TIMESTAMP, BLOB (binary large object), CLOB, XML, JSON, and others ▪ MySQL includes types of INTEGER, NUMERIC, FLOAT, DATE/TIME, CHAR, VARCHAR, BLOB, and others ▪ Microsoft Access supports several types of NUMBER, as well as TEXT, MEMO, DATE/TIME, HYPERLINK, YES/NO, and others Datatypes in MySQL ∙ VARCHAR(n): Used to store variable-length text up to n characters. ∙ CHAR(n): Used to store fixed-length text of exactly n characters. Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ∙ DECIMAL(p, s): Used to store fixed-point numbers with p total digits and s digits after the decimal point. Also can be used as NUMERIC(p, s) ∙ FLOAT(p): Used to store floating-point numbers with p precision in binary digits. ∙ DOUBLE(p): Used to store double-precision floating-point numbers. ∙ INT or INTEGER: Used to store integers (whole numbers). ∙ DATE: Used to store date values in the format YYYY-MM-DD. ∙ TEXT: Used to store large variable-length text (up to 65,535 characters). ∙ MEDIUMTEXT: Used to store even larger text (up to 16,777,215 characters). ∙ DATETIME: Used to store date and time values in the format YYYY-MM-DD HH:MM:SS. ∙ TIMESTAMP: Similar to DATETIME, but can auto-update to the current time. Constraints ▪ Column constraints – in-line constraints Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com [NOT]NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT ▪ Examples: While creating tables, these might show as column constraints (details in slide 15) stuId VARCHAR(6) PRIMARY KEY, lastName VARCHAR(20) NOT NULL, credits INTEGER(3) DEFAULT 0, credits INTEGER(3) CHECK ((credits >= 0) AND (credits < 150)), licenseNo VARCHAR(10) UNIQUE,  Constraints ▪ Table constraints – out-of-line constraints Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com all of the above plus FOREIGN Key … References ▪ Examples: While creating tables, these might show as table constraints (details in slide 16) CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_schedule_room_uk UNIQUE (schedule , room) CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber , stuId) CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) Or simply (no need for constraint identifier, but it makes it easier to alter them later) PRIMARY KEY (classNumber), UNIQUE (schedule, room), PRIMARY KEY (classNumber, stuId), FOREIGN KEY (classNumber) REFERENCES Class (classNumber), Constraints ▪ IDENTITY-based column Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Creates system-generated numeric sequence; useful for primary keys ▪ Example: While creating tables, these might show as column constraints id INT AUTO_INCREMENT PRIMARY KEY, Constraints ▪ ON DELETE option for foreign keys Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Specifies action when foreign key value is deleted in home table ▪ Options: CASCADE, SET NULL, NO ACTION ▪Examples: While creating tables, these might show as table constraints (details in slide 16) CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) ON DELETE CASCADE, ▪ Notes: ▪ CASCADE: Automatically deletes related child records. ▪ SET NULL: Sets foreign key values in the child table to NULL. ▪ NO ACTION: Prevents deletion in the parent table if there are related child records  Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com Creating the Tables for the University Database Creating the Tables for the University Database (Examples for inserting sample data. ‘insert’ is DML instruction and will be discussed later) Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com Indexes ▪ Can create any number of indexes for tables Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Stored in same file as base table ▪ Facilitate fast retrieval of records with specific values in specified column(s) ▪ Keep track of what values exist for the indexed columns, and addresses of records with those values ▪ B+ trees or B trees used ▪ Overhead – system must maintain index CREATE INDEX Command CREATE INDEX indexname ON basetablename(colname[order][,colname[order]]…); Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com Ex. CREATE INDEX Student_lastName_ix ON Student (lastName); CREATE INDEX Fac_dept_name_ix ON Faculty (department ASC, name ASC); ▪ UNIQUE specification enforces unique values for indexed column or combination of columns ▪ Except when specified, column values need not be unique ▪ Order is ASC (default) or DESC ▪ Can have major and minor orders ▪ Oracle allows up to 32 columns in an index ▪ Oracle automatically indexes primary key columns ALTER TABLE Command ▪ To add a new column Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ALTER TABLE basetablename ADD columnname datatype constraints; Ex. ALTER TABLE Class ADD cTitle VARCHAR(30); ▪ Cannot specify NOT NULL, since existing records have no value for this column ▪ Adds new column on right of table ▪ To drop a column ALTER TABLE basetablename DROP COLUMN columnname; Ex. ALTER TABLE Class DROP COLUMN cTitle; ▪ To change a column’s properties ALTER TABLE basetablename MODIFY COLUMN colname [new specifications]; ▪ To rename a column ALTER TABLE basetablename RENAME COLUMN old-colname TO new-colname; Other Changes to Tables ▪ To add a constraint Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ALTER TABLE basetablename ADD CONSTRAINT constraint_defn; ▪ To drop a constraint ALTER TABLE basetablename DROP CONSTRAINT constraint_name; ▪ To rename a table RENAME TABLE old-table-name TO new-table-name; Ex: RENAME TABLE FACULTY TO TEACHERS; ▪ To drop a table DROP TABLE basetablename; Ex. DROP TABLE CLASS; ▪ To drop an index DROP INDEX indexname; Ex. DROP INDEX Student_lastName_ix; Worker & Projects Example Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪Worker table has foreign key that refers to Dept ▪Dept table has foreign key that refers to Worker ▪ In theory, each must precede the other. ▪ Figure 5.3 shows how to create these tables  Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com Inserts Project Example 5.3 DDL Worker Figure and for & SQL DML ▪ Non-procedural, declarative language Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ Can be interactive, can be embedded in host language, or can be stand-alone programming language (SQL/PSMs) ▪ Basic commands SELECT UPDATE INSERT DELETE SELECT Statement SELECT [DISTINCT] colname[AS newname], [,colname..]... Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com FROM tablename [alias] [,tablename]... [WHERE predicate] [GROUP BY group_by_clause | ORDER BY order_by_clause]; ▪ Powerful command – equivalent to RA’s SELECT, PROJECT, JOIN and more… ▪ Result can have duplicates; a multiset ▪ See list of examples, Section 5.4.1-5.4.5  Simple SELECT Statements ▪ Get names, IDs, and number of credits of all Math majors Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT lastName, firstName, stuId, credits FROM Student WHERE major = 'Math';  Simple SELECT Statements ▪ Get all information about CSC Faculty – Using * Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT * FROM Faculty WHERE department = 'CSC'  Simple SELECT Statements ▪ Get the class number of all classes in which students are Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com enrolled—Using DISTINCT to eliminate repeats SELECT DISTINCT classNumber FROM Enroll; ▪ Note WHERE clause is optional  Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com Simple SELECT Statements ▪ Retrieving an Entire Table FROM Student; SELECT *  ORDER BY, Renaming Columns, Complex Conditions ▪ Get names and IDs of all faculty members, in order by name. Call the resulting columns FacultyName and FacultyNumber Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT name AS FacultyName, facId AS FacultyNumber FROM Faculty ORDER BY name; ▪ More columns for minor order: ORDER BY name, department;  ORDER BY, Renaming Columns, Complex Conditions ▪ Get names of all math majors who have more than 30 credits SELECT lastName, firstName Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com FROM Student WHERE major = 'Math' AND credits > 30; ▪ Can use operators =, , =; connectives AND, OR, NOT; ▪ Can use BETWEEN: Ex:...AND credits BETWEEN 30 AND 59;  Join of Multiple Tables; Aliases ▪ Find IDs and names of all students taking Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ART103A (Join of two tables) SELECT Enroll.stuId, lastName, firstName FROM Student, Enroll WHERE classNumber='ART103A' AND Enroll.stuId = Student.stuId;  Join of Multiple Tables; Aliases ▪ Using NATURAL JOIN, no need to specify Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com common columns are equal SELECT stuId, lastName, firstName FROM Enroll NATURAL JOIN Student WHERE classNumber = 'ART103A';  Join of Multiple Tables; Aliases ▪ Find class numbers and the names and majors of all students enrolled in the classes taught by faculty member F110 (Join of Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com three tables, with aliases) SELECT E.classNumber, lastName, firstName, major FROM Class C, Enroll E, Student S WHERE facId = 'F110' AND C.classNumber = E.classNumber AND E.stuId = S.stuId; ▪ Could use NATURAL JOIN instead of join conditions (next slide)  Join of Multiple Tables; Aliases ▪ Find class numbers and the names and majors of all students enrolled in the classes taught by faculty member F110 (Join of Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com three tables, with aliases)  Join of Multiple Tables; Aliases ▪ Get a list of all classes that meet in the same room, with their schedules and room numbers. Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ▪ (This query requires comparing the Class table with itself, and it would be useful if there were two copies of the table so we could do a natural join) SELECT A.classNumber, A.schedule, A.room, B.classNumber, B.schedule FROM Class A, Class B WHERE A.room = B.room AND A.classNumber < B.classNumber ;  Subqueries; Using EXISTS ▪ Find the numbers of all the classes taught by Byrne of the Math department Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT classNumber FROM Class WHERE facId =(SELECT facId FROM Faculty WHERE name= 'Byrne' AND department='Math'); ▪ Can nest subqueries; can use IN instead of =  Subqueries; Using EXISTS ▪ Find the names and IDs of all faculty members who teach a class in room H221. Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com  Subqueries; Using EXISTS ▪ Find all students enrolled in any class (Using EXISTS) Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT * FROM Student WHERE EXISTS (SELECT * FROM Enroll WHERE Enroll.stuId = Student.stuId);  Subqueries; Using EXISTS ▪ Find the names of all students enrolled in CSC201A. Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com (Using EXISTS) SELECT lastName, firstName FROM Student WHERE EXISTS (SELECT * FROM Enroll WHERE Enroll.stuId = Student.stuId AND classNumber = 'CSC201A');  Subqueries; Using EXISTS ▪ Find the names of all students who are not enrolled in Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com CSC201A. SELECT lastName, firstName FROM Student WHERE NOT EXISTS (SELECT * FROM Enroll WHERE Student.stuId = Enroll.stuId AND classNumber = 'CSC201A');  Using NULL; Set Operations ▪ Find the student ID and class number of all Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com students whose grades are recorded in the database. – use IS NOT NULL SELECTstuId, classNumber FROM Enroll WHERE grade IS NOT NULL;  Using NULL; Set Operations ▪ Find the student name and class number of all Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com students whose grades in that class are missing SELECTfirstName, lastName, classNumber FROM Student NATURAL JOIN Enroll WHERE grade IS NULL;  Using NULL; Set Operations ▪ Get IDs of all faculty who are assigned to the History Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com department or who teach in room H221 SELECT facId FROM Faculty WHERE department = 'History' UNION SELECT facId FROM Class WHERE room ='H221';  Aggregate Functions ∙ AVG(column): Returns the average value of a numeric column. ∙ COUNT(column): Returns the number of non-null values in a column. Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com ∙ COUNT(*): Returns the total number of rows. ∙ SUM(column): Returns the sum of a numeric column. ∙ MIN(column): Returns the smallest value in a column. ∙ MAX(column): Returns the largest value in a column. ▪ Common String Functions: ▪ CONCAT(string1, string2,...): Concatenates multiple strings together. ▪ LENGTH(string): Returns the length of a string. ▪ UPPER(string): Converts a string to uppercase. ▪ LOWER(string): Converts a string to lowercase. ▪ TRIM(string): Removes leading and trailing spaces from a string.  Aggregate Functions ▪ Find the total number of students in the database Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT COUNT(stuId)FROM Student; Or simply SELECT COUNT(*)FROM Student;  Aggregate Functions ▪ Find the total number of students enrolled in classes Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT COUNT(DISTINCT stuId)FROM Enroll;  Aggregate Functions ▪ Find names and IDs of students who have less than the average number of credits Copyright © 2021 by Jones & Bartlett Learning, LLC an Ascend Learning Company. www.jblearning.com SELECT lastName, firstName, stuId FROM Student WHERE credits