CS代考 Relational Model

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