Relational Model
Copyright By PowCoder代写 加微信 powcoder
SQL Data Definition and Data Types
Specifying Constraints in SQL
INSERT, DELETE, and UPDATE Statements in SQL
Structured Query Language
Core specification
Plus specialized extensions
SQL standard
Considered one of the major reasons for the commercial success of relational databases
High-level, declarative programming language
User specifies what they want, not how to do it
Comprehensive database language
Statements for data definition, queries and updates
Defining views, specifying authorization
Setting up integrity constraints and transaction controls
SQL is mainly divided into four sub language
Data Definition Language(DDL)
Data Manipulation Language(DML)
Transaction Control Language(TCL)
Data Control Language(DCL)
SQL Commands
Create, Drop, Rename, Alter (Truncate)*
Update, Delete, Insert
Commit, Rollback (Savepoint)*
(Grant, Revoke)*
* Not covered this semester
SQL Terminology
Equivalent to relational model’s relation
Equivalent to tuple
Equivalent to attribute
CREATE statement
Used for data definition
Identified by a schema name
Can be thought of as a grouping of elements into one database idea
Constraints
Authorizations (Roles)
COMPANY Relational DB Schema
Company UML Schema
* MySQL Workbench
Database Creation
SQL uses the CREATE DATABASE statement to create new database:
CREATE DATABASE COMPANY;
CREATE SCHEMA COMPANY;
Note that all SQL commands end in a semi-colon
Only authorized users can create schemas/databases/DB objects
Generally the DBA controls who can make changes
Table Creation
Remember – in SQL, Tables = Relations
CREATE TABLE statement
CREATE TABLE COMPANY.EMPLOYEE ( … );
Schema_Name.Table_Name creates a table in a specific schema
Most relational databases allow you to specify your schema beforehand – MySQL:
USE COMPANY;
CREATE TABLE EMPLOYEE ( … );
Table Creation
CREATE TABLE EMPLOYEE ( … );
What goes in those parentheses?
Attributes and data types!
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Super_ssn CHAR(9),
Dno INT NOT NULL);
SQL Data Types
INT – integer
Also SMALLINT (sometimes BIGINT/LONG)
REAL – floating point
Also DOUBLE PRECISION, FLOAT
DECIMAL(i,j) – formatted floating point decimal
fixed length character string (length n)
only used when the exact length of text data is known, such as keys or hashes
VARCHAR(n)
variable length character string (up to length n)
most efficient use of space for smaller text data
SQL Data Types
What you would expect – true vs. false
Sometimes represented as a TINYINT (0 or 1)
YYYY-MM-DD
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS
Storage and retrieval formats can be customized
SQL Data Types
Name used with the attribute specification
Makes it easier to change the data type for a domain that is used by numerous attributes
Improves schema readability
CREATE DOMAIN SSN_TYPE AS CHAR(9);
Specifying Constraints in SQL
Basic constraints:
Key and referential integrity constraints
Restrictions on attribute domains and NULLs
Constraints on individual tuples within a relation
Relational Integrity Constraints
Constraint rules are conditions on the DB that ensure integrity on our data for any state of the DB:
Single relation
Domain constraints
attribute values from domain, or NULL
Entity integrity constraints
Primary Keys cannot be NULL
Key constraints
Candidate keys must be unique for each tuple
Two or more relations
Referential integrity constraints
Each foreign key must match one primary key value in related table
Constraints
What about keys?
Specify primary key during table creation
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY(Ssn));
Specifying Key and Referential Integrity Constraints
PRIMARY KEY clause
Specifies one or more attributes that make up the primary key of a relation
Dnumber INT PRIMARY KEY;
UNIQUE clause
Specifies alternate (secondary) keys
Dname VARCHAR(15) UNIQUE;
Constraints
Or, specify primary keys after the fact (with ALTER TABLE):
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL);
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Ssn);
Constraints
We’ve seen the NOT NULL constraint
Forces user to supply a non-NULL values for those attributes
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL);
Specifying Attribute Constraints and Attribute Defaults
NULL is not permitted for a particular attribute
Default value
DEFAULT
Constraints
We can also add a DEFAULT value for our tuples
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL DEFAULT 3);
Specifying Constraints on Tuples Using CHECK
CHECK clauses at the end of a CREATE TABLE statement
Apply to each tuple individually
Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
CHECK (Dept_create_date <= Mgr_start_date);
Constraints
Some SQL implementations allow us to craft CHECK constraints
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL CHECK(Dno>0 AND Dno<21));
Constraints
Some SQL implementations allow us to craft CHECK constraints for the whole table
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL,
CONSTRAINT SSNCHK CHECK (Ssn != Super_ssn));
Constraint Violations
Basic operations for changing the database:
INSERT a new tuple in a relation
DELETE an existing tuple from a relation
MODIFY an attribute of an existing tuple
Specifying Key and Referential Integrity Constraints (cont’d.)
FOREIGN KEY clause
Default operation: reject update on violation
Attach referential triggered action clause
Options include SET NULL, CASCADE, and SET DEFAULT
Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE
CASCADE option suitable for “relationship” relations
Constraints
Specify foreign keys during table creation
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY(Ssn),
FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber));
Constraints
Or specify foreign keys after the fact
CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY(Ssn));
ALTER TABLE EMPLOYEE ADD FOREIGN KEY(Super_ssn) REFERENCES EMPLOYEE(Ssn);
Constraints
Naming Constraints
Keyword CONSTRAINT
Name a constraint
Useful for later altering
Defaults and Referential Integrity Triggers
COMPANY Schema Creation (DDL)
COMPANY Schema Creation (DDL)
COMPANY Schema Creation (DDL)
The CREATE TABLE Command in SQL (cont’d.)
Base tables (base relations)
Relation and its tuples are actually created and stored as a file by the DBMS
Virtual relations
Created through the CREATE VIEW statement
SQL Data Manipulation (DML)
Three commands used to modify the database:
SQL Inserts
First we need to put data into the table
INSERT statement
Basic statement: put values in in the same order as the CREATE TABLE statement used to create the table:
INSERT INTO EMPLOYEE
VALUES (‘Bob’,’Jones’,’999999999’,’1973-01-01’,
‘123 Fake St., Columbus, OH’,
‘333445555’,5);
Requires that we know all of the values or use NULL for values we don’t have
SQL Inserts
Can use a modified version of INSERT to specify explicit attributes:
INSERT INTO EMPLOYEE(Fname,Lname,Ssn)
VALUES (‘Bob’,’Jones’,’999999999’);
Anything not in the list is set to NULL (or default value if set)
Be careful – at a minimum, all primary key values must be set in any insert
SQL Deletes
We can delete rows from tables using the DELETE statement
DELETE FROM EMPLOYEE
WHERE Fname=‘John’ AND
Lname=‘Smith’;
This would remove all records for anyone named “ ”
DELETE FROM EMPLOYEE;
This would remove all records from the EMPLOYEE table
SQL Updates
We can change values in the table using the UPDATE statement
UPDATE EMPLOYEE
SET Address=‘234 Imaginary Blvd., Columbus OH’
WHERE Ssn=‘999999999’;
This would update the address for a single employee
UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;
This would give everyone in department 5 a 10% raise!
SQL Instruction Summary
SQL Instruction Summary
Using www.SQLiteOnline.com
Set up your database
Create tables
May not need to “create schema”
Include PK/FK, constraints, and triggered actions
Import data from CSV files into appropriate tables
Make dummy data for values you don’t have
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com