Databases - University Of Southern California

DatabasesCSCI 201Principles of Software DevelopmentJeffrey Miller, Ph.D.jeffrey.miller@usc.edu

Outline Databases SQL Try It!USC CSCI 201L

Databases Database systems store data and provide a means of accessing,updating, manipulating, and analyzing data Databases are stored in files on a file system, but they are arrangedin a manner that allows for fast queries and updates A Database Management System (DBMS) is designed forprogrammers, not casual usersSQL-BasedMySQLPostgreSQLOracleMicrosoft SQL ServerIBM DB2NoSQLMongoDBFirebaseApache CouchDBApache CassandraUSC CSCI 201L3/14

Relational Databases Relational database management systems (RDBMS) provide three things› Structure – the representation of the data› Integrity – constraints on the data› Language – means for accessing and manipulating olowitzB-Tables are called relationsColumns are called attributesRows are called tuplesConnections are called relationshipsUSC CSCI 201L4/14

Relational tz111A4RajeshKoothrappali223A-332A433B543B-USC CSCI 201L5/14

Primary and Foreign Keys A primary key is a column (or a combination of multiple columns) in a table thatprovides a unique reference to a row in the table A foreign key is a link between two tables that uniquely identifies a row inanother tableClassStudentPrimary eign KeysGrades.classIDGrades.studentID6/14

Outline Databases SQL Try It!

SQL The Structured Query Language (SQL) is the primary languagesupported by DBMSs for accessing and manipulating data Some MySQL SQL statements you should know›››››››››SHOWCREATE DATABASEUSECREATE TABLEINSERTUPDATESELECTDELETEDROP If you are not familiar with SQL, there are many good tutorialsonline and our textbook has a good chapter reference as wellUSC CSCI 201L8/14

SQL Scripts After installing a DBMS, you will be able to run SQLscripts SQL scripts are files that contain a collection of SQLstatements that can be used for recreating databases andpopulating them with initial or testing data› This is often used in the testing phase of software engineeringto test different scenarios without requiring the QA engineersto insert all of the data manuallyUSC CSCI 201L9/14

Sample 72829DROP DATABASE IF EXISTS StudentGrades;CREATE DATABASE StudentGrades;USE StudentGrades;CREATE TABLE Student (studentID INT(11) PRIMARY KEY NOT NULL AUTO INCREMENT,fname VARCHAR(50) NOT NULL,lname VARCHAR(50) NOT NULL);INSERT INTO Student (fname, lname) VALUES ('Sheldon', 'Cooper');INSERT INTO Student (fname, lname) VALUES ('Leonard', 'Hofstadter');CREATE TABLE Class (ClassclassID INT(11) PRIMARY KEY NOT NULL AUTO INCREMENT,studentIDclassIDprefixnumprefix VARCHAR(5) NOT NULL,11CSCI103num INT(4) NOT NULL);22CSCI104INSERT INTO Class (prefix, num) VALUES ('CSCI', 103);INSERT INTO Class (prefix, num) VALUES ('CSCI', 104);CREATE TABLE Grade (GradegradeID INT(11) PRIMARY KEY NOT NULL AUTO INCREMENT,gradeIDclassIDstudentIDclassID INT(11) NOT NULL,111studentID INT(11) NOT NULL,221letterGrade VARCHAR(2) NOT NULL,FOREIGN KEY (classID) REFERENCES class(classID),312FOREIGN KEY (studentID) REFERENCES student(studentID)422);INSERT INTO Grade (studentID, classID, letterGrade) VALUES (1, 1, 'A');INSERT INTO Grade (studentID, classID, letterGrade) VALUES (1, 2, 'A');INSERT INTO Grade (studentID, classID, letterGrade) VALUES (2, 1, 'A');INSERT INTO Grade (studentID, classID, letterGrade) VALUES (2, 2, dterletterGradeAAABUSC CSCI 201L10/14

SQL Visualization Tools A visualization tool connects to a database and allowsexecution of SQL statements› The tool then will graphically display the results Free MySQL Clients››››Command line client (Windows, Mac, Linux)MySQL Workbench (Windows, Mac, Linux)Sequel Pro (Mac)Toad for MySQL (Windows)USC CSCI 201L11/14

Executing SQL There are a few ways to execute SQL statements, whichtypically depend on the DBMS We often want to be able to access databases from withinprograms though› This can be to insert, update, delete, or query the data The Java Database Connectivity (JDBC) drivers allow us toembed SQL in our Java code and execute those statements ona database programmaticallyUSC CSCI 201L12/14

Outline Databases SQL Try it!USC CSCI 201L

SQL Write SQL code to create the following A223A-332A433B553B-USC CSCI 201L14/14

Databases Database systems store data and provide a means of accessing, updating, manipulating, and analyzing data Databases are stored in files on a file system, but they are arranged in a manner that allows for fast queries and updates A Database Management System (DBMS) is designed for programmers, not casual users. USC CSCI 201L. 3/14. MySQL