ACID principle: Atomic, Consistent, Isolated, Durable
Log: keeps all information about a transaction (Old value, new value,
Aggregation: allows us to treat a relationship relationship set as an entity set for
commit or abort). Also ensures redo and undo capabilities
purposes of participation in (other) relationships
Logical data independence: Protection from changes in logical structure of
Attribute: a property or description of an entity.
data
Candidate key: A minimal superkey that does not contain a subset of
Many-to-many relationship: a key constraint that indicates that many of
attributes that is itself a superkey
one relationship can be associated with many of another entity.
Cardinality: number of rows
Nested Queries: A WHERE, FROM or HAVING clause can itself contain an
Concurrency Control: important to keep the CPU humming by working on
SQL query.
several user programs concurrently. Interleaving actions of different user
One-to-many relationship: a key constraint that indicates that one entity
programs can lead to inconsistency, DBMS ensures such problems don’t
can be associated with many of another entity
arise.
Overlap constraint: within an ISA hierarchy, an overlap constraint
Covering Constraint: within an ISA hierarchy, determines where the
determines whether or not two subclasses can contain the same entity.
entities in the subclasses collectively include all entities in the superclass.
Partial Participation Constraints: Every entity in the total set E1 does not
Data Independence: Applications insulated from how data is structured
need to be related to another in the entity E2
and stored.
Participation constraint: a participation constraint determines whether
Data Integrity: Make sure your data can be trusted, avoid unnecessary
relationships relationships must involve certain entities.
duplication, ensure only “correct” value.
Physical data independence: Protection Protection from changes in physical structure
Data Security: Avoid unauthorized access, protect the security and privacy
of data
of your data, access rights maintained by DBA
Primary key:
Data model: a collection of concepts for describing data
attribute values in a given row. No nulls allowed.
Database: A very large integrated collection of data, models real world
Qualification: Comparisons (Attr op const or Attr1 op Attr2, where op is
enterprise
one of
A candidate key selected to uniquely identify all other
<,>,=,…) combined using AND, OR and NOT.
Database Administrator (DBA): They perform the following tasks: (1)
Query languages:
Access control and monitoring, (2) Performance tuning, (3) Disk space
database
management, (4) Concurrency control, (5) Designs logical /physical
Reduced development time: using structured query language gives or fast
schemas, (6) Data availability, availability, crash recovery
“ad hoc” queries
Database Management System (DBMS): the software package designed to
Referential integrity: Foreign key either empty (null “value”) or value
store and manage databases.
matches the primary key of related table. Purpose: Impossible for attribute
It provides the following functions: (1) Uniform data administration, (2)
to have an invalid entry. Possible for attribute to have no value
Reduced application development time, (3) Data independence, (4)
Relation: a table with rows and columns. Each relation has a schema.
Efficient access, (5) Data integrity and security, (6) Concurrent access,
Relation-list: A list of relation names (possibly with a range-variable after
recovery from crashes, (7) Web-based access and distribution.
each name).
Designing a database : (1) Get requirements and data, (2) Create
Relationship: Association among two or more entities
Conceptual model (EER diagram), (3) Create Logical model (Relational
Relationship Set: Collection of similar relationships
model), (4)
Role Indicator: if an entity set plays more than one role, role indicators
“Implement” logical model (tables), (5) “Import” data, (6)
Allow manipulation and retrieval of data from a
Query data – SQL – to create Reports
describe the different purpose in the relationship.
Descriptive Attributes: used to record information about a relationship.
Schema: a description of a particular collection of data, using the a given
Degree (Arity) : Number of fields
data model, defined using DDL
Domain (Data Type): a set of possible values for an attribute, columns in
Secondary key: An attribute or combination of attributes used strictly for
table
data-retrieval purposes
Distinct: an optional keyword indicating that the answer should not
Security mechanism: allows us to enforce a chosen security policy. Two
contain duplicates. Default is that duplicates are not eliminated! eliminated!
main types, discretionary or mandatory access control.
Entity: Real‐world object distinguishable from other objects. An entity is
Security policy: specifies who is authorized to do what
described using a set of attributes
Security Solutions: Suppress obvious sensitive information, authenticate
Entity Set: A collection of similar entities
your users, track what the user knows, disguise data
Foreign key: An attribute or combination of attributes in one table whose
Security Tasks of the DBA : Grant, revoke access rights to users or group of
value must either (a) match the primary key in another table, or
users, ensure transparent security, identify intrusion attempts, define a
(b) be null (contain no value, i.e. empty)
plan, manage high risk statistical queries
Foreign key: Set of fields in one relation that is used to `refer’ to a tuple in
Superkey: An attribute or combination of attributes that uniquely
another relation. (Must correspond to primary key of the second relation.) relation.)
identifies each entity in a table
Like a `logical pointer’. If all foreign key constraints are enforced,
Target-list: A list of attributes of relations in relation-list
referential integrity is achieved.
Total Participation Constraints: Every entity in the total set E1 must be
Indirect attack: Infer results based on one or more query
related to another in the entity E2
Instance: a set of relationships
Transaction: An atomic sequence of database actions ( reads/writes). reads/writes).
Integrity Constraints (IC): condition that must be true for any instance of
View: just a relation, but we store a definition, rather than a set of tuples
the database, specified when schema is defined, checked when
Weak Entity: an entity that cannot be identified uniquely without
relations are modified
considering some primary key attributes of another identifying owner
Key Constraint: each entity is appears in at most one realtionship
entity.
Key/Entity integrity: No null entries in a primary key, all entries are unique. Purpose: Each row or tuple has a unique identity
DROP TABLE Students
Tables for weak entities:
SELECT S.sid
Destroys the relation Students. The schema
FROM Sailors S, Boats B, Reserves R
information and the tuples are deleted.
WHERE S.sid=R.sid AND R.bid=B.bid AND
ALTER TABLE Students
B.color=‘red’
ADD COLUMN firstYear: integer The schema of Students is altered by adding a new field; every tuple in the current instance is extended with a null value in the new field INSERT INTO Students (sid, name, login, gpa) VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2) Inserts the tuple DELETE FROM Students S WHERE S.name = ‘Smith’ Can delete all tuples satisfying some condition
AND S.sid IN (SELECT S2.sid CREATE TABLE Dep_Policy (
FROM Sailors S2, Boats B2, Reserves R2
pname CHAR(20),
WHERE S2.sid=R2.sid AND R2.bid=B2.bid
age INTEGER,
AND B2.color=‘green’)
cost REAL,
Find sid’s of sailors who’ve reserved both a red
ssn CHAR(11) NOT NULL,
and a green boat
PRIMARY KEY (pname, ssn), FOREIGN KEY (ssn) REFERENCES Employees, ON DELETE CASCADE
SELECT S.sname FROM Sailors S WHERE NOT EXISTS
Tables for ISA hierarchies:
((SELECT B.bid
Approach one: Have 3 relations:
FROM Boats B)
Hourly_Emps: Every employee is recorded in
EXCEPT
Referential integrity options
Employees. For h_emps, extra info recorded.
– Default is NO ACTION or RESTRICT
(SELECT R.bid
Delete H_Emps tuple if referenced Employees
(delete/update is rejected)
FROM Reserves R
tuple is deleted). Queries involving all employees
– CASCADE (also delete all tuples that refer to
WHERE R.sid=S.sid)
easy, those involving
deleted tuple)
just Hourly_Emps require a join to get some
– SET NULL / SET DEFAULT (sets foreign key value
attribute. This approach will always work.
of referencing tuple)
Approach Two: Just Hourly_Emps and
Creating a table for entity:
Contract_Emps. Subclasses must be total or
Find sailors who’ve reserved all boats
disjoint CREATE VIEW YoungStudents (name, grade) AS SELECT S.name, E.grade FROM Students S, Enrolled E
Aggregation: COUNT (*), COUNT ( [DISTINCT] A), SUM ( [DISTINCT] A), AVG ( [DISTINCT] A), MAX (A), MIN (A) Aggregate operations cannot be nested. SELECT S.sname, S.age FROM Sailors S WHERE S.age = (SELECT MAX (S2.age)
WHERE S.sid = E.sid and S.age<2 CREATE TABLE Employees (ssn CHAR(11),
This is used to present necessary information, while hiding details in underlying relation(s).
name CHAR(20),
SQL SELECT Query Keywords:
lot INTEGER,
FROM which tables
PRIMARY KEY (ssn))
WHERE which rows
Creating a table for relationship (M to N):
FROM Sailors S2 Find name and age of the oldest sailor(s) SELECT MIN (S.age) FROM Sailors S GROUP BY S.rating
HAVING identify “combined” rows GROUP BY combine rows with related values ORDER BY – sorting the data AS and = are two ways to name fields in result. LIKE is used for string matching. `_’ stands for any one character and `%’ stands for 0 or more
Find the age of the youngest sailor for each rating level SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVING COUNT (*) > 1
CREATE TABLE Works_In(
arbitrary characters.
ssn CHAR(1),
SELECT S.sname
did INTEGER,
FROM Sailors S, Reserves R
since DATE,
WHERE S.sid=R.sid AND R.bid=10
PRIMARY KEY (ssn, did),
Give the names of the Sailors who reserved boat
FOREIGN KEY (ssn) REFERENCES Employees,
with bid = 103
FOREIGN KEY (did) REFERENCES Departments)
Set operations:
WHERE Temp.avgage = (SELECT MIN
Creating a table for relationship(1 to M):
UNION, EXCEPT, INTERSECTION, IN, UNIQUE,
(Temp.avgage)
EXISTS, NOT EXISTS, NOT IN, NOT UNIQUE
FROM Temp
SELECT S.sid
Find those ratings for which the average age is
FROM Sailors S, Boats B, Reserves R
the minimum overall ratings
WHERE S.sid=R.sid AND R.bid=B.bid
SELECT C.Country, C.date-of-issue
Find the age of the youngest sailor with >= age 18, for each rating with at least 2 such sailor
CREATE TABLE Manages(
Find sid’s of sailors who’ve reserved a red or a
ssn CHAR(11),
green boat
since DATE, PRIMARY KEY (did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments
FROM (SELECT S.rating, AVG (S.age) AS avgage FROM Sailors S GROUP BY S.rating) AS Temp
AND (B.color=‘red’ OR B.color=‘green’)
did INTEGER,
SELECT Temp.rating,Temp.avgage
FROM Catalog C WHERE (SELECT count(*) FROM Stamp S WHERE S.CatalogID = C.CatalogID) >= 5;
SELECT * FROM Sailors S
Displays the countries and date of issues of all
WHERE S.rating > ANY (SELECT S2.rating
catalogs that contain more than 4 stamp
FROM Sailors S2 WHERE S2.sname=‘Horatio’) Find sailors whose rating is greater than that of some sailor called Horatio