Course Objectives Database Construction Design Construction and Usage SQL DDL and DML Relational Algebra Interfacing Usage Course Objectives – Construction When the course is through, you should – Given a database schema with related SQL Data Definition Language constraints, implement the database in a relational (SQL) DBMS Case convention Creating and dropping tables • SQL is completely case insensitive. • Relations become tables, attributes become columns. Upper-case or Lower-case makes no difference. We will use case in the CREATE TABLE tablename ( < list of table elements> following way: ); – UPPERCASE marks keywords of the SQL • Get all info about a created table: language. – lowercase marks the name of an attribute. DESCRIBE tablename ; Oracle specific! – Capitalized marks the name of a table. • Remove a created table: DROP TABLE tablename ; 1
Table declaration elements Example • The basic elements are pairs consisting of Example: a column name and a type. CREATE TABLE Courses ( • Most common SQL types: code CHAR(6), – INT or INTEGER (synonyms) name VARCHAR(50) – REAL or FLOAT (synonyms) ); – CHAR( n ) = fixed-size string of size n . – VARCHAR(n) = variable-size string of up to Created the table courses: size n . code name Declaring keys Example • An attribute or a list of attributes can be CREATE TABLE Courses( code CHAR(6), declared PRIMARY KEY or UNIQUE name VARCHAR(50), – PRIMARY KEY: At most one per table, never PRIMARY KEY (code) NULL. Efficient lookups in all DBMS. ); – UNIQUE: Any number per table, can be Or NULL. Could give efficient lookups (may vary CREATE TABLE Courses( in different DBMS). code CHAR(6), • Both declarations state that all other name VARCHAR(50), attributes of the table are functionally CONSTRAINT CoursesPK PRIMARY KEY (code) determined by the given attribute(s). ); Foreign keys Foreign keys • General: • Referential constraints are handled with FOREIGN KEY course REFERENCES Courses(code) references, called foreign keys : FOREIGN KEY attribute • If course is Primary Key in Courses: REFERENCES table(attribute) FOREIGN KEY course REFERENCES Courses • Give a name to the foreign key: CONSTRAINT ExistsCourse FOREIGN KEY course REFERENCES Courses 2
Example Value constraints CREATE TABLE GivenCourses ( code CHAR(6), • Use CHECK to insert simple value constraints. period INT, – CHECK ( some test on attributes ) numStudents INT, teacher VARCHAR(50), CREATE TABLE GivenCourses ( PRIMARY KEY (code, period), FOREIGN KEY (code) REFERENCES Courses(code) code CHAR(6), ); period INT CHECK (period IN (1,2,3,4)), numStudents INT, CREATE TABLE GivenCourses ( code CHAR(6) REFERENCES Courses(code), teacher VARCHAR(50), period INT, FOREIGN KEY (code) REFERENCES Courses(code), numStudents INT, PRIMARY KEY (code, period) teacher VARCHAR(50), ); PRIMARY KEY (code, period) ); Naming constraints Example CREATE TABLE GivenCourses ( • Default error messages are horrible. code CHAR(6) REFERENCES Courses(code), • Naming constraints makes them a lot period INT, easier to read and understand. numStudents INT, teacher VARCHAR(50), PRIMARY KEY (code, period), CONSTRAINT constraint-name CONSTRAINT ValidPeriod CHECK (period in (1,2,3,4)) constraint ); CONSTRAINT ValidPeriod CHECK (period in (1,2,3,4)) Example Example: DESCRIBE CREATE TABLE GivenCourses ( code CHAR(6) REFERENCES Courses(code), • Legal: period INT, – INSERT INTO GivenCourses numStudents INT, VALUES (’TDA357’,4,93,’Rogardt); teacher VARCHAR(50), PRIMARY KEY(code,period), • Not Legal: CONSTRAINT ValidPeriod CHECK (period in (1,2,3,4)) ); – INSERT INTO GivenCourses VALUES (’TDA357’,7,93,’Rogardt); DESCRIBE GivenCourses; – ERROR at line 1: Name Null? Type • ORA-02290: check constraint CODE NOT NULL CHAR(6) (NIBRO.VALIDPERIOD) violated PERIOD NOT NULL NUMBER(38) NUMSTUDENTS NUMBER(38) TEACHER VARCHAR2(50) 3
Exam – SQL DDL Course Objectives ”A grocery store wants a database to store information about products and suppliers. After studying their domain you have come up with the following database Design schema. …” Construction • Write SQL statements that create the relations as tables in a DBMS, including all constraints. Interfacing Usage Course Objectives – Usage When the course is through, you should – Know how to change the contents of a SQL Data Manipulation Language: database using SQL Modifications Inserting data Inserting data (alt.) INSERT INTO tablename INSERT INTO tablename (some of the attributes) VALUES ( values for attributes ); VALUES ( values for attributes ); INSERT INTO Courses INSERT INTO Courses VALUES (’TDA357’, ’Databases’); (name, code) VALUES (’Databases’, ’TDA357’); code name TDA357 Databases code name TDA357 Databases 4
Deletions Quiz code name DELETE FROM tablename TDA357 Databases WHERE test over rows ; TIN090 Algorithms DELETE FROM Courses DELETE FROM Courses WHERE code = ’TDA357’; WHERE code = ’TDA357’; code name TIN090 Algorithms DELETE FROM Courses; Quiz: What does this statement do? DELETE FROM Courses; Updates Quiz code per #st teacher UPDATE tablename TDA357 2 87 Niklas Broberg SET attribute = ... TDA357 4 93 Marcus Björkander WHERE test over rows TIN090 1 64 Devdatt Dubhashi UPDATE GivenCourses UPDATE GivenCourses SET teacher = ’Rogardt Heldal’ WHERE code = ’TDA357’ SET teacher = ’Rogardt Heldal’ AND period = 4; WHERE code = ’TDA357’ code per #st teacher AND period = 4; TDA357 2 87 Niklas Broberg TDA357 4 93 Rogardt Heldal TIN090 1 64 Devdatt Dubhashi Summary Course Objectives • SQL Data Definition Language – CREATE TABLE , attributes Design – Constraints Construction • PRIMARY KEY • FOREIGN KEY … REFERENCES • CHECK • SQL Data Manipulation Language Interfacing – INSERT, DELETE, UPDATE Usage 5
Course Objectives – Usage When the course is through, you should Queries: – Know how to query a database for relevant data using SQL SQL and Relational Algebra Querying ”Algebra” • To query the database means asking it for • An algebra is a mathematical system information. consisting of: – ”List all courses that have lectures in room – Operands: variables or values to operate on. VR” – Operators: symbols denoting functions that operate on variables and values. • Unlike a modification, a query leaves the database unchanged. Relational Algebra Relational operators (1) • An algebra whose operands are relations • Selection (or variables representing relations). – Choose rows from a relation • Operators representing the most common – State condition that rows must satisfy operations on relations. σ condition (T) – Selecting rows Examples: – Projecting columns σ seats>100 (Rooms) – Composing (joining) relations σ (code=”TDA143” AND day=”Friday”) (Lectures) 6
Relational operators (2) Relational operators (3) • Projection R 1 x R 2 – Choose columns from a relation – Cartesian product – State which columns (attributes) – Combine each row of R 1 with each row of R 2 R 1 ⋈ ⋈ condition R 2 ⋈ ⋈ π A (T) – join operator Examples: – Combine row of R 1 with each row of R 2 if the π code (Courses) condition is true π name,seats (Rooms) R 1 ⋈ ⋈ condition R 2 = σ condition (R 1 x R 2 ) ⋈ ⋈ SQL The Query Compiler • SQL = Structured Query Language • SQL query is parsed to produce a parse tree that represents the query. – The querying parts are really the core of SQL. The DDL and DML parts are secondary. • Parse tree is transformed to a relational • Very-high-level language. algebra expression tree (or similar). – Specify what information you want, not how to • Generate a physical query plan. get that information (like you would in e.g. – Use algebraic laws to improve query plan by Java). generating many alternative execution plans • Based on Relational Algebra and estimating their cost. – Choose algorithm to perform each step. Selection Example: course per teacher • Selection = Given a relation (table), TDA357 2 Niklas Broberg GivenCourses = TDA357 4 Rogardt Heldal choose what tuples (rows) to include in the TIN090 1 Devdatt Dubhashi result. SELECT * FROM GivenCourses σ C (T) SELECT * FROM T WHERE C; WHERE course = ’TDA357’; – Select the rows from relation T that satisfy course per teacher Result = condition C. What? TDA357 2 Niklas Broberg – � = sigma = greek letter s = s election TDA357 4 Rogardt Heldal 7
Recommend
More recommend
