Database Systems

Schema

a concept or framework that organizes and interprets information

Data Model

a diagram that represents entities in the database and their relationships

data model

a diagram that represents entities in the database and their relationships

WITH

allows a user to define a table that will only be used in a particular query (not available in all SQL implementations) ex. WITH BIGDEPTS (Dno) AS (SELECTDnoFROMEMPLOYEEGROUP BYDnoHAVINGCOUNT(*) > 5) SELECT Dno, COUNT(*) FROM EMPLOYEE WHERE Salary>40000 AND Dno IN BIGDEPTSGROUP BY Dno;

asc

ascending order

use <database name>

changes the database on which you are working.

AS

command is used to rename a column or table with an alias. An alias only exists for the duration of the query.

Write the SQL statements that define the relational schema for this database. Assume that pid'sare integers, and nameand relare character strings.

create table Person(pid int primary key,name varchar(20)); create table Relationship(pid1 int references Person,rel varchar(20),pid2 int references Person)

Metadata

data that describes other data

drop <database name> <table name>

delete a table

UNION

is used to combine the result-set of two or more SELECT statements.

DISTINCT

is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

EXISTS

is used to test for the existence of any record in a subquery. EX. SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

!=

not equal

ROUND()

rounds a number to a specified number of decimal places.

Write the SQL querythat computes, for each person, the total number of their friends. Your query should return results containing the pid, the name, and the count. Note that your query must return exactly one answer for every person in Person.

select x.pid, x.name, count(*) from Person x left outer join Relationship yon x.pid = y.pid1 and y.rel='friend'group by x.pid, x.name

show databases;

show all databases

describe <table name>

shows table structure

database administrator

the person responsible for coordinating, controlling, and managing the database

Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project.

(SELECT p.pname FROM project p , department d, employee e WHERE d.dnum = p.dnumber and d.Mgr_ssn = e.ssn and e.lname = 'smith') UNION (SELECT pname FROM project , works_on, employee WHERE Pnumber = Pno and essn = ssn and lname = 'smith')

UNIQUE

(adj.) one of a kind; unequaled; unusual; found only in a given class, place, or situation

External schemas

* at the external level to describe the various user views. * Usually uses the same data model as the conceptual schema.

Create a temporary table that has the name, number of employees and total salaries for each department

CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(15), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM (DEPARTMENT JOIN EMPLOYEE ON DNUMBER = DNO) GROUP BY DNAME;

OLTP (online transaction processing)

Database design used for browsing and manipulating business transaction activity

BETWEEN comparison operator

E.g. WHERE(Salary BETWEEN 30000 AND 40000) AND Dno= 5;

Salary between 30000 and 40000

E.g. WHERE(Salary BETWEEN 30000 AND 40000) AND Dno= 5;

SELECT COUNT(*) from <name>

Gets count

Rejected if referential integrity constraint enforced by DBMS and no department tuple with DNUMBER 2 exists

INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, DNO) VALUES ('Robert', 'Hatcher', '980760540', 2);

Retrieve the name of employees who have no dependents.

SELECT fname, lname FROM employee e WHERE not exists (select * from dependent where ssn = essn);

Retrieve the names of all employees who do not have supervisors.

SELECT fname, lname FROM employee e WHERE superssn is NULL

List the name of managers who have at least one dependent.

SELECT fname, lname FROM employee, department WHERE ssn = Mgr_ssn and (select count(*) from dependent where mgr_ssn = ess) >=1;

Retrieve the name and address of all employees who work for the 'Research' department.

SELECT fname,Minit,lname,address From employee e, DEPARTMENT D WHERE d.dname = 'Research' and d.dnumber = e.dno;

Retrieve the names of all employees who have two or more dependents.

SELECT fname,lname FROM employees e WHERE (SELECT COUNT (*) FROM DEPENDENT d WHERE SSN = ESSN) >= 2;

For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.

SELECT pnumber, dnumber, fname,lname,address FROM employee e, department d, project p WHERE e.ssn = d.mgr_ssn and d.dnum = p.dnumber and p.Plocation = 'Stafford';

Select all of combinations of EMPLOYEE SSN and DEPARTMENT DNAME in the database.

SELECT ssn, dname FROM department d, employee e WHERE d.dno = e.dnumber;

Select * from <database name>

Shows all rows

name - data type - null or not null - Primary key

Table format

Left join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

CREATE DATABASE <name of database>

The SQL command to create a database

show tables

The SQL command to list all the tables in the database

AVG

A SQL aggregate function that outputs the mean average for a specified column or expression. Select AVG() From Where LIKE ' ';

Having

A clause applied to the output of a GROUP BY operation to restrict selected rows. cannot be put the aggregate functions in a where class

LIKE comparison operator

A comparison operator that selects records by matching field values to a specific pattern that includes one or more wildcard characters. Examples: WHERE Address LIKE '%Houston,TX%'; WHERE Ssn LIKE '_ _ 1_ _ 8901';

data independence

A condition in which data access is unaffected by changes in the physical data storage characteristics.

DATE data type

A data type that indicates that the data in a field represents a date Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD

Primary Key

A field (or group of fields) that uniquely identifies a given entity in a table The primary key attributes are underlined.

Aggregate function

A function that performs an arithmetic operation on selected records in a database.

Key constraints

A relation must have at least one candidate key such that for every tuple, the value must be unique and no value may be NULL (the latter point is known as the entity integrity constraint)

Internal schema

A representation of an internal model using the database constructs supported by the chosen database.

Conceptual schema

A representation of the conceptual model, usually expressed graphically. See also conceptual model.

Referential Integrity (foreign key)

A set of rules that Access uses to ensure that the data between related tables is valid.

concurrent users

All users can access the database at the same time

Alter table <database name> add( )

Alter table

Superkey

An attribute, or set of attributes, that uniquely identifies a tuple within a relation.

CASE construct

Applicable when querying, inserting or updating tuples ex. UPDATE EMPLOYEE SET Salary = CASE WHEN Dno= 5 THEN Salary + 2000 WHEN Dno= 4 THEN Salary + 1500 WHEN Dno= 1 THEN Salary + 3000

multiple views

Apps support multiple views

Select <name> as <other name> from table

Changes name

Self-Describing Data Models

Combine the description of data with the data values. Examples include XML, key-value stores and some NOSQL systems.

Nested Queries

Complete select-from-where blocks within WHERE clause of another query

CRUD

Create, Retrieve, Update, Delete

Create table <table name>

Creates a table

Correlated

Involving two or more events that are related in some way

Inner join

Most common type of join; includes rows in the query only when the joined field matches records in both tables. Select cats.name, cats.owner, others.name From cats, owners Where cats.owners = owner.id;

Subqueries

Nest inside another query

Conceptual (high-level, semantic) data models

Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models.)

Physical (low-level, internal) data models

Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals

Implementation (representational) data models

Provide concepts that fall between the above two, used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems).

Count the number of distinct salary values in the database.

SELECT COUNT(DISTINCT Salary) FROM employee

Retrieve the social security numbers of all employees who work on project number 1, 2 or 3

SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1,2,3)

For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than £40,000

SELECT DNUMBER, COUNT (*) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER = DNO AND SALARY > 40000 AND DNO IN (SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING COUNT (*) > 5) GROUP BY DNUMBER;

Retrieve the salary of every employee.

SELECT Salary FROM employee e WHERE

For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project

SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER = PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2;

For each project, retrieve the project number, the project name, and the number of employees who work on that project

SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER = PNO GROUP BY PNUMBER, PNAME;

Find the sum of the salaries of all employees of the 'Research' department, as well as the maximum salary, the minimum salary, and the average salary in this department.

SELECT avg(Salary), max(salary), min(salary), SUM(Salary) FROM department d, employee e WHERE dname = 'RESEARCH'

Retrieve the birthdate address of the employee whose name is 'John B. Smith'.

SELECT birthdate,address FROM employee e Where e.fname = 'John' and e.Minit = 'B' and e.lname = 'Smith';

For each department, retrieve the department number, the number of employees in the department, and their average salary.

SELECT dnum ,count(*) avg(salary) from employee e, department d Group by dno

Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.

SELECT e.fname, e.lname FROM employee e WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN.= ESSN and fname = dependent_name

For each employee, retrieve the employee's first and last name and the first and last name of the his or her immediate supervisor.

SELECT e.fname, f.lname, s.fname, s.lname FROM employee AS e , employee AS s WHERE e.super_ssn = s.ssn

Entity Integrity

The property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values.

Data Abstraction

The separation of the logical view of data from its implementation

Join table

This type of table is used to link two tables using two one-to-many relationships instead of two many-to-many relationships.

ON clause

Used in joins to replace where