Download Database Recovery Techniques: ARIES Algorithm and Advanced Recovery Methods and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
21. Introduction to relational model:
Relational Database: Definitions Relational database: a set of relations Relation: made up of 2 parts:
- Instance : a table , with rows and columns. #Rows = cardinality , - Number of rows in relation is known as cardinality. #fields = degree / arity. - Number of attribute (fields) in relation is known as degree or arity.
- Schema : specifies name of relation, name of attribute and data type of each attribute. •E.G. Students ( sid : string, name : string, login : string, age : integer, gpa : real). Can think of a relation as a set of rows or tuples (i.e., all rows are distinct). Example Instance of Students Relation
sid Name login age gpa
53666 Jones jones@cs 18 3.
53688 Smith smith@eecs 18 3.
53650 Smith smith@math 19 3.
Cardinality = 3, degree = 5, all rows distinct Do all columns in a relation instance have to be distinct?
Creating and Modifying Relations Using SQL Relational Query Languages A major strength of the relational model: supports simple, powerful querying of data. Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. The key: precise semantics for relational queries. Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change.
- Create s the Students relation. Observe that the type of each field is specified, and enforced by the DBMS whenever tuples are added or modified. CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL)
- As another example, the Enrolled table holds information about courses that students take. CREATE TABLE Enrolled (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2))
- Tuples are inserted using INSERT command, we can insert row in student table INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, 'Smith', 'smith@ee', 18, 3.2)
- We can delete tuples using the DELETE command. We can delete all Students tuples with name equal to Smith using the command: DELETE FROM Students S WHERE S.name = 'Smith'
- we can modify the column values in an existing row using the UPDATE command. For example, we can increment the age and decrement the gpa of the student with sid 53688: UPDATE Students S SET S.age = S.age + 1, S.gpa = S.gpa - 1 WHERE S.sid = 53688
The SQL Query Language Creating Relations in SQL
Integrity Constraints (ICs) over Relations:
IC: condition that must be true for any instance of the database; e.g., domain constraints. ICs are specified when schema is defined. ICs are checked when relations are modified.
Specifying Foreign Key Constraints in SQL
Let us define Enrolled(studid: string, cid: string, grade: string): CREATE TABLE Enrolled ( studid CHAR(20) , cid CHAR(20), grade CHAR(10), PRIMARY KEY (studid, cid), FOREIGN KEY (studid) REFERENCES Students) Only students listed in the Students relation should be allowed to enroll for courses
General Constraints
There are two general constraints
These are applied on particular table and are checked every
time when that specific table is updated.
These assertions applied on collection on tables and are
checked every time when these tables are updated.
23.Enforcing Integrity constraints:
Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students. What should be done if an Enrolled tuple with a non-existent student id is inserted? ( Reject it) What should be done if a Students tuple is deleted?
- Also delete all Enrolled tuples that refer to it.
- Disallow deletion of a Students tuple that is referred to.
- • (^) Set sid in Enrolled tuples that refer to it to a special valueSet sid in Enrolled tuples that refer to it to a default sid null,.
Similar if primary key of Students tuple is updated Referential Integrity in SQL SQL/92 and SQL:1999 support all 4 options on deletes and updates.
Default is NO ACTION ( delete/update is rejected )
CASCADE (also delete all tuples that refer to deleted tuple) SET NULL / SET DEFAULT (sets foreign keyvalue of referencing tuple)
Example SQL Query
CREATE TABLE Enrolled ( studid CHAR(20) , cid CHAR(20) , grade CHAR(10), PRIMARY KEY (studid, did), FOREIGN KEY (studid) REFERENCES Students ON DELETE CASCADE ON UPDATE NO ACTION)
24.Querying Relational Data:
Formal Relational Query Languages Query is question. A query is formulated for a relation /table to retrieve same useful information from table. Two mathematical Query Languages form the basis for “real” languages (e.g. SQL), and for implementation:
Preliminaries A query is applied to relation instances , and the result of a query is also a relation instance. We can retrieve rows corresponding to students who are younger than 18 with the following SQL query:
SELECT * FROM Students S WHERE S.age < 18 The symbol ,*, means that we retain all fields of selected tuples in the result. Think of S as a variable that takes on the value of each tuple in Students, one tuple after the other. The condition S. age < 18 in the WHERE clause specifies that we want to select only tuples in which the age field has a value less than 18.
We can also combine information in the Students andEnrolled relations. If we want to obtain the names of all students who obtained anA and the id of the course in which they got an A, we could write thefollowing query: SELECT S.name, E.cidFROM Students S, Enrolled E WHERE S.sid = E.studid AND E.grade = 'A'
25.Logical DB Design:
Entity sets to tables: Relationship Sets to Tables In translating a relationship set to a relation, attributes of the relation must include:
Keys for each participating entity set (as foreign keys). This set of attributes forms a superkey for the relation. All descriptive attributes.
Does every department have a manager?
- If so, this is a participation constraint : the participation of Departments in Manages is said to be total (vs. partial ).
- Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value!) Participation Constraints in SQL We can capture participation constraints involving one entity set in a binary relationship, but little else (without resorting to CHECK constraints).
Review: Weak Entities A weak entity can be identified uniquely only by considering the primary key of another ( owner ) entity.
- Owner entity set and weak entity set must participate in a one-to-many relationship set ( owner, many weak entities.
Weak entity set must have total participation in this identifying relationship set.
Translating Weak Entity Sets Weak entity set and identifying relationship set are translated into a single table.
- When the owner entity is deleted, all owned weak entities must also be deleted.
Review: ISA Hierarchies Overlap constraints : Can Joe be an Hourly_Emps as well as a Contract_Emps entity? ( Allowed/
disallowed )
Covering constraints : Does every Employees entity also have to be an Hourly_Emps or a
Contract_Emps entity? (Yes/no)
Binary vs. Ternary Relationships (Contd.) The key constraints allow us to combine Purchaser with Policies and Beneficiary with Dependents. Participation constraints lead to NOT NULL constraints.
26.Introduction To Views:
Views and Security Views can be used to present necessary information (or a summary), while hiding details in underlying relation(s).
Given YoungStudents, but not Students or Enrolled, we can find students s who have are
enrolled, but not the cid’s of the courses they are enrolled in.
View Definition
A relation that is not of the conceptual model but is made visible to a user as a “virtual
relation” is called a view. A view is defined using the create view statement which has the form create view v as < query expression > where is any legal SQL expression. The view name is represented by v.
Once a view is defined, the view name can be used to refer to the virtual relation that the view
generates.
Example Queries A view consisting of branches and their customers Uses of Views Hiding some information from some users Consider a user who needs to know a customer’s name, loan number and branch name, but has no need to see the loan amount. Define a view ( create view cust_loan_data as select customer_name, borrower.loan_number, branch_name from borrower, loan where borrower.loan_number = loan.loan_number ) Grant the user permission to read cust_loan_data, but not borrower or loan Predefined queries to make writing of other queries easier Common example: Aggregate queries used for statistical analysis of data Processing of
Views
When a view is created
- the query expression is stored in the database along with the view name
- the expression is substituted into any query using the view Views definitions containing views
e 1 vi
All of these operations take two input relations, which must be union-compatible :
–Same number of fields. `Corresponding’ fields have the same type.
What is the schema of result?
Cross-Product Each row of S1 is paired with each row of R1. Result schema has one field per field of S1 and R1, with field names `inherited’ if possible.
- Conflict : Both S1 and R1 have a field called sid.
5.Joins:
condition Join : Result schema same as that of cross-product. Fewer tuples than cross-product, might be able to compute more efficiently
Find names of sailors who’ve reserved boat # Solution 1: Find names of sailors who’ve reserved a red boat Information about boat color only available in Boats; so need an extra join: Find sailors who’ve reserved a red or a green boat Can identify all red or green boats, then find sailors who’ve reserved one of these boats: Find sailors who’ve reserved a red and a green boat Previous approach won’t work! Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (note that sid is a key for Sailors):
7.Relational Calculus:
Comes in two flavors: Tuple relational calculus (TRC) and Domain relational calculus (DRC). Calculus has variables, constants, comparison ops , logical connectives and quantifiers.
- TRC : Variables range over (i.e., get bound to) tuples.
- DRC : Variables range over domain elements (= field values).
- Both TRC and DRC are simple subsets of first-order logic. Expressions in the calculus are called formulas. An answer tuple is essentially an assignment of constants to variables that make the formula evaluate to true.
8.Domain Relational Calculus:
Query has the form: DRC Formulas Atomic formula: –, or X op Y, or X op constant
or
- , where p and q are formulas, or
- , where variable X is free in p(X), or
- , where variable X is free in p(X)
- The use of quantifiers and is said to bind X.
- A variable that is not bound is free. Free and Bound Variables
- The use of quantifiers and in a formula is said to bind X.
- A variable that is not bound is free.
Let us revisit the definition of a query: Find all sailors with a rating above 7 The condition ensures that the domain variables I, N, T and A are bound to fields of the same Sailors tuple.
- The term to the left of `|’ (which should be read as such that ) says that every tuple that satisfies T> 7 is in the answer. Modify this query to answer:
- Find sailors who are older than 18 or have a rating under 9, and are called ‘Joe’. Find sailors rated > 7 who have reserved boat #
We have used as a shorthand for
