DB Review Database A database is a collection
DB Review
Database § § A database is a collection of data A database management system (DBMS) is software designed to assist in maintaining and utilizing a large collection of data Internet technologies – Ohad ©
Why Database is better than a file system § Define the structure: I/O efficiency § Ability to query(SQL) § § § High availability Distributed Internet technologies – Ohad ©
Relational Database § Tables Primary Key § Fields (different Types: Int / Boolean / date / file etc. . ) § § Relationships One to many § Many to one § Many to many § § The database is kept on the disk, so anything you create will be there next time you log on. Internet technologies – Ohad ©
Table Name Country ID(PK) Yaron Gam England 1 Kate Omaily USA 2 Jack USA 3 Internet technologies – Ohad ©
Problematic table Name Country Prime Minister Yaron Gam England Gordon Kate Omaily USA Bush Jack USA Bush Internet technologies – Ohad ©
What is the solution § There is a One-to-One relationship between Country and Prime minister, Name Country Yaron Gam Israel Kate Omaily USA Yael Nacsh Israel Naftali spitzer Israel Country Prime Minister Israel Golda USA Bush Internet technologies – Ohad ©
Rules of thumb § § Avoid duplication One-to-One relationship should be in the same table with other fields. Internet technologies – Ohad ©
SQL § § Structured Query Language is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS) Database schema creation § Modification § Database object access control management. § Internet technologies – Ohad ©
Internet technologies – Ohad ©
Create table The basic format of the CREATE TABLE command is: CREATE TABLE Table. Name( Column 1 Data. Type 1 Col. Constraint, … Column. N Data. Type. N Col. Constraint, Table. Constraint 1, … Table. Constraint. M ); Internet technologies – Ohad ©
Example CREATE TABLE Employee( ID INTEGER NOT NULL, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, Dept INTEGER ); Internet technologies – Ohad ©
If you type d Employee you get: Column Type ------------ Modifiers ------ id integer not null fname character varying(20) lname character varying(20) gender character(1) salary integer dept integer not null Internet technologies – Ohad ©
Constraints § § Different types of constraints: * Not Null * Unique * Foreign Key * Default Values * Primary Key * Check Condition Internet technologies – Ohad ©
Foreign Key ID FName LName Gender Sallary Dept 02334 Larry Bird M 230000 12 04556 Magic Johnson M 270000 45 Foreign Key Department Dept Name Man. ID 12 Sales 988 45 Repair 876 Internet technologies – Ohad ©
CREATE TABLE Employee ( ID INTEGER primary key, Fname VARCHAR(20), Lname VARCHAR(20), Gender CHAR(1), Salary INTEGER NOT NULL, Dept. Number INTEGER REFERENCES Department ); CREATE TABLE Department( Dept. Number INTEGER PRIMARY KEY, Name VARCHAR(20), Manager. Id INTEGER ); Internet technologies – Ohad ©
Deleting a table § Syntax: DROP TABLE <table. Name>; § Mind the order of dropping when there are foreign key constraints. Internet technologies – Ohad ©
Insert Column Type Modifiers ------------ id integer not null Fname character varying(20) gender character(1) deptnumber integer Internet technologies – Ohad ©
Insert syntax Syntax: insert into tablename (field 1, field 2…) values (v 1, ’v 2’, …) Examples: § insert into employee (id, fname, gender, deptnumber) values(122, 'Goldman', 'M', 12); § § insert into employee (id, deptnumber) values(123, 13); Internet technologies – Ohad ©
Delete § § Syntax: DELETE FROM Table WHERE Condition; Example: § DELETE FROM Employee WHERE id = 121; DELETE FROM Employee WHERE Salary > 100000; Internet technologies – Ohad ©
Update § § Syntax: UPDATE Table SET Field 1=value 1, , , Field. N=value. N WHERE Condition Example: UPDATE Employee SET Salary = 100000 WHERE Salary > 100000; Internet technologies – Ohad ©
Queries § Syntax: SELECT [Distinct] fields FROM tables WHERE condition ORDER BY field ASC/DESC § condition: A Boolean condition (For example: age>21, or name=‘Yuval’ ). Only rows which return ‘true’ for this condition will appear in the result Internet technologies – Ohad ©
Student. ID Student. Dept. Student. Name Student. Age 1123 Math Moshe 25 2245 Computers Mickey 26 55611 Math Menahem 29 Select student. ID, student. Name From students Where Student. Dept=‘Math’ Student. ID Student. Name 1123 Moshe 55611 Menahem Internet technologies – Ohad ©
The where clause § Numerical and string comparison: !=, <>, =, <, >, >=, <=, between(val 1 AND val 2) § Logical components: AND, OR § Null verification: IS NULL, IS NOT NULL § Checking against a list with IN, NOT IN. Internet technologies – Ohad ©
More examples SELECT sname FROM Sailors WHERE age>=40 AND rating IS NOT NULL ; SELECT sid, sname FROM sailors WHERE sid IN (1223, 2334, 3344) or sname between(‘George’ and ‘Paul’); SELECT sid FROM Sailors WHERE sname LIKE ‘R%’; Internet technologies – Ohad ©
How do we compute All sailors who have reserved a boat Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 Internet technologies – Ohad ©
Join Select sname from sailors, reserves Where sailors. sid=reserves. sid When there is more than one table in the from we compute a cross product Internet technologies – Ohad ©
Stage 1: Sailors x Reserves Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 Internet technologies – Ohad ©
Stage 2: “where sailors. sid=reserves. sid” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 22 Dustin 7 45. 0 58 103 11/12/96 31 Lubber 8 55. 5 22 101 10/10/96 31 Lubber 8 55. 5 58 103 11/12/96 58 Rusty 10 35. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 Internet technologies – Ohad ©
Stage 2: “where sailors. sid=reserves. sid” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 Internet technologies – Ohad ©
Stage 3: “select sname” Sailors Reserves sid sname rating age sid bid day 22 Dustin 7 45. 0 22 101 10/10/96 58 Rusty 10 35. 0 58 103 11/12/96 Internet technologies – Ohad ©
Stage 3: “select sname” Sailors sname Final answer Dustin Rusty Internet technologies – Ohad ©
Order 1. 2. 3. 4. Compute the cross product of the tables Delete all rows that do not satisfy condition. Delete all columns that do not appear in fields. If Distinct is specified eliminate duplicate rows. Internet technologies – Ohad ©
Aggregation § The aggregate operators available in SQL are: § § § COUNT(*) COUNT([DISTINCT] A) SUM([DISTINCT] A) AVG([DISTINCT] A) MAX(A) MIN(A) SELECT Max(S. age) FROM Sailors S Internet technologies – Ohad ©
Nested quires § Query inside a query Select First_Name from Internet_grades where Grade >= (Select Max(Grade) from Infi_grades); Internet technologies – Ohad ©
- Slides: 35