DBMS Review

Posted on | Post modified: | In

DBMS Exam2 Review

Structured Query Language (SQL) - Lecture 11

SQL is the standard relational database language and is related to the tuple relational calculus.
Standard enables easier switch to other DBMS, queries remain unchanged.

Main objectives

  • Create the database and relation structures
  • Perform basic data management tasks, such as insertion, modification, and deletion of data from the relations
  • Perform both simple and complex queries
  • Be portable between different DBMS
  • Perform the aforementioned services with minimal user efforts
  • Perform the aforementioned services with maximum performance

Components of SQL:

  1. Data Definition Language (DDL): Commands for creating and changing the data structures for the three levels of a database.
  2. Data manipulation language (DML): Update commands for tuples, interactive formulation of queries.
  3. Embeded SQL and Dynamic SQL;
  4. Integrity;
  5. View Definition;
  6. Transaction Control
  7. Authorization.

SQL Data Types:
Start at Page9.

Integrity Constraints and Default Values

Integrity constraints are the most important commands of the DDL:

  • are conditions that restrict the possible database states;
  • ensure the consistency of a database;

It is recommended to specify the not null condition explicitly for each primary key, although this holds implicitly already for each primary key

Default values can be attaching the clause default <value> to the attribute definition. Will be set for every tuple if no explicit value declares. Otherwise, all will be null.

Primary Keys

  • The clause primary key (A1, …, An) specifies that the attributes (A1, …, An) form the primary key of the relation R with respect to R.
  • The set of selected attributes must be unique and minimal;

Structured Query Language (SQL) - Lecture 12

Foreign Keys:

  • A foreign key value references (“points to”) a primary key value in another relation to represent an m:1-relationship of an E-R diagram;
  • Definition of a foreign key by the foreign key clause
  • Foreign keys ensure referential integrity, that is, there are no inconsistent or dangling references
  • A foreign key can or cannot become (part of) the primary key of the relation schema into which it is imported.

Candidate Keys:

  • Any unique and minimal set of attributes that could serve but has not been selected as a primary key;
  • The fact that the attributes A1, …, An form a candidate key is specified by the integrity constraint ++unique++ (A1, …, An).

SQL DDL Commands

  • Examples of Creation of a Relation Schema/Table Schema:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    create table professors
    (pers-id int not null,
     name varchar(30) not null,
     room int unique,
     rank char(2),
     primary key (pers-id));

     create table lectures
     (id    int not null,
      title varchar(30),
      credits int,
      held_by int,
      primary key(id),
      foreign key (held_by) references
      professors(pers-id));
    ```     
    - Modification of a Relation Schema/Table Schema:   
    ```sql
    //Adding an attribute(a new column) to an existing relation
    --All tuples in the relation are assigned null as the value for the new attribute
    --Constraint not null is only allowed if a default value is specified
    alter table <relation name> add <column definition>;

    //Deleting an attribute (a column) from an existing relation
    --Command will be declined if integrity constraints would be violated
    alter table <relation name> drop <column name>;

    //Deletion of the schema and instance (that is, data) of a relation
    drop table <relation name>

    //Deletion of the instance (that is, data) of a relation but not its schema
    delete from <relation name>

  • Creation of an Index:

    • An index is a persistent data structure that provides accelerated access to the rows of a table based on the values of one or more attributes;
    • Goal: Significant improvement of query response time
      1
      2
      create unique index room_index on professors (room);
      drop index <index name>;

  • Creation of Views:

    • A ++view++ is a virtual relation that does not exist persistently in the database but can be produced upon request by a particular user, at the time of request;
    • Views are regarded as derived relations which are defined by queries:
      1
      2
      3
      4
      5
      --create view <view name> [(<attribute name> [, <attribute name>]*)] as <subquery>
      creat view major_students as
          select * from students where sem>4;
      --delet views
      drop view <view name>;

SQL Queries (DML) Commands - Lecture 13

  • Basic Structure of SQL Queries:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    /*A1..An are attributes names(columns), R1..Rn are table names, F is predicate,
    query result is always a single table, no duplicate
    select =  projection
    from = cartesian product
    where =  selection
    distinct = remove duplicate tuples*/
    select distinct A1, A2,..., An
    from R1, R2,... Rn
    where F;
    --More details in Page2, Lecture13

  • Basic RA expressions and SQL:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    --show all from R
    select * from R;

    --Projection A,B from R
    select distinct A, B from R;

    --selection basing on F from R
    select distinct * from R where F;

    --Cartesian product of RxS
    select * from R, S;

    --Union R u S of the relations R(A1,..An) and S(A1..An)
    --This union operation will eliminate duplicates, add "all" after union can keep
    select * from R union select * from S;

    --Difference R - S
    --Same as above, add "all" after except to keep duplicates
    select * from R except select * from S;

  • SQL queries in sigle table:

    1
    2
    3
    4
    5
    6
    7
    8
    --Search Queries
    --where clause - horizontal (row) restriction
    --select clause - vertical (column) restriction
    select pers-id, name from professors where rank = "C4";

    -- Create a students table where the semester number of each student is incremented by 1
    select reg-id, name, sem+1
    from student;

  • SQL queries in multiple tables

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    --Determine the names of professors who hold lectures, and show the titles of the corresponding lectures
    select name, title
    from professors, lectures
    where pers-id = held_by;

    select name, title from professors
    join lectures on pers-id=held_by;

    --Determine the names of professors who hold the lecture titled “maieutics”.
    select name, title
    from professors, lectures
    where pers-id = held_by and
    title = ‘maieutics’;

    --Which students attend which lecture? Output student names and lecture titles. 3 tables nature join
    select name, title
    from students, attends, lectures
    where students.reg-id = attends.reg-id and
    attends.id = lectures.id;

    select name, title
    from students natural join attends natural join lectures

    --Rename, "as" is optional
    select s.name, l.title
    from students as s, attends as a, lectures as l
    where s.reg-id = a.reg-id and a.id = l.id;

    --List all students together with their performed tests by maintaining
    the complete student information.
    --left outer join reserves all the tuples of left tables
    --Right outer join (right outer join) and full outer join (full outer join) are similar
    select s.*, t.* --can be written as  select *
    from students s left outer join test t on s.reg-id = t.reg-id;

  • Set operations:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    --Determine the names of all university employees, i.e., the names of all professors and all assistants. All duplicates are removed
    --add "all" after union to keep duplicates
    (select name from assistants)
    union
    (select name from professors);

    --Find the identifiers of professors who teach “ethics” or “maieutics”.
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘ethics’)
    union
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘maieutics’);

    --Find the identifiers of professors who teach “ethics” and “maieutics”.
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘ethics’)
    intersect
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘maieutics’);

    --Find the identifiers of professors who teach “ethics” but not “maieutics”.
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘ethics’)
    except
    (select p.pers-id from professors p, lectures l
    where p.pers-id = l.held_by and l.title = ‘maieutics’);

    /*
    Operations union, except, and intersect produce tables as sets of tuples,
    elimination of duplicates
    Operations union all, except all, and intersect all maintain duplicates in
    result table
    */

SQL Queries Commands - Lecture 14

  • Update of Tuples in a Table
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Syntax: update <relation name>
set <attribute name> = <expression>
[, <attribute name> = <expression>]*
[where <condition>];

--Change room number (currently 232) of professor Russel to 115 update professors set room = 115 where name = ‘Russel’

--The construct case is used to perform several updates in a single update statement
--Example: Reorganization of the individual assistants’ offices into open-plan offices
update assistants
set room = case
	when room >= 100 and room < 120 then 417
	when room >= 120 and room < 140 then 438
	else 455
end;

--Deletion of Tuples in a Table
--Delete students who study longer than 8 semesters
delete from students where sem > 8;
--Delete all tests tuples
delete from tests;
--Lecture “foundations” with the identifier 5001 was suddenly canceled; delete all registrations
delete from attends where id = 5001;

  • Aggregate Functions:

    1
    2
    3
    4
    5
    --Aggregate functions are functions that take a collection (list) of values as
    --input and return a single value as output
    --5 bult-in: count, avg, sum, max, min;
    --Query 4: Calculate the number of different semesters the students are in, and store the result in a new attribute named “diff_sem”.
    select count(distinct sem) from students;

  • Grouping
    Page12, Lecture14

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    --Determine the number of hours per week in which each professor has given lectures.
    select held_by, sum(hpw) as number
    from lectures
    group by held_by;

    --Determine the number of hours per week of those lectures held by
    --professors who predominantly give long lectures (> 3 hours per week on average).
    select held_by, sum(hpw) as number
    from lectures
    group by held_by
    having avg(hpw) > 3;
    /*
    General form
    select [distinct] {* | <column expression> [as <new column name>] [, …]}
    from <table name> [as <variable] [, …]
    [where <condition>]
    [group by <column list>
    having <condition>]]
    [order by <column list>];

    Sequence of processing
    select: specifies which attributes are to appear in the output
    from: specifies the table or tables to be used
    where: filters the tuples subject to some condition
    group by: forms groups of tuples with the same grouping attribute value
    having: filters the groups subject to some condition
    order by: specifies the order of the output
    */

SQL Queries Commands - Lecture 15

  • Nested Queries

    • Teset for Set Membership in the WHERE clause

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      key words are "in" and "not in"
      --Query 1: Output the names of students who have taken a test.
      select name
      from students
      where reg-id in (select reg-id from tests);
      --Query 2: Find the names of all professors that are not involved in teaching.
      select name
      from professors
      where pers-id not in (select held_by from lectures);
      --Query 3: Select the names of assistants whose names are neither ‘Platon’ nor ‘Newton’.
      select name
      from assistants
      where name not in ('Platon', 'Newton');
      --Query 4: Find the names of all professors who gave a test
      --and also taught the lecture that was tested.
      select name from professors
      where pers-id in (select held_by from lectures
            where (id, held_by) in (select id, pers-id from tests));

    • Set Comparisons in the WHERE Clause

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      Key words are "some" and "all"
      --Query 5: Find the names of all students whose semester number is greater
      --than at least one of the semester numbers of the students ‘Fichte’
      --(10th semester) or ‘Carnap’ (3rd semester).
      select name from students
      where sem > some (select sem from students
          where name = ‘Fichte’ or name = ‘Carnap’);

      --Find the students with the largest number of semesters.
      select name from students
      where sem >= all (select sem from students);
      --Query 7: Find the identifiers of professors who teach the most.
      select held_by
      from lectures
      group by held_by
      having sum(hpw) >= all (select sum(hpw)
                              from lectures
                              group by held_by);

    • Test for Empty Tables in the WHERE Clause

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      --Determine the identifiers of all students who have taken a test.
      select reg-id
      from students as s
      where exists (select *
                    from tests as t
                    where t.reg-id = s.reg-id);
      --Output the names of professors who do not hold lectures.
      select name
      from professors
      where not exists (select *
                        from lectures
                        where pers-id = held_by);
      --“Table A contains table B” is the same as “not exists(B except A)”
      --Output the names of students who attend all lectures offered by professor Curie.
      select s.name
      from students as s
      where not exists ((select id from lectures, professors
                        where pers-id = held_by and name = ‘Curie’)
                        except
                        (select l.id from attends as a, lectures as l
                        where l.id = a.id and a.reg-id = s.reg-id)
                        );

    • Test for the Absence of Duplicate Tuples in the WHERE Clause

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      --The unique construct returns the value true if the table as the result of the
      --argument subquery contains no duplicate tuples
      --Determine the names of professors who have at most one assistant working for them.
      select name from professors as p
      where unique (select a.boss from assistants a where p.pers-id = a.boss);
      --The not unique construct returns the value true if the table as the result of
      --the argument subquery contains duplicate tuples
      --Determine the names of professors who have at least two assistants working for them.
      select name from professors as p
      where not unique (select a.boss from assistants where p.pers-id = a.boss);

    • Subqueries in the FROM Clause

      1
      2
      3
      4
      --Output the identifiers of those lectures that are attended by more than 20 students.
      select id
      from (select id, count(*) as number from attends group by id)
      where number > 20;

    • The WITH Clause for Defining Temporary Tables

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      --A way of defining a temporary table whose definition is available only to the
      --query in which the with clause is used
      --Output the titles of those lectures that are attended by more than 20 students.
      with attendance_rate(id, number) as
      (select id, count(*)
          from attends
          group by id)
          select l.title
      from lectures as l, attendance_rate as a
      where l.id = a.id and a.number > 20;

    • Scalar Subqueries in the WHERE, SELECT, or HAVING Clause

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      --A scalar subquery returns a single tuple that consists of a single column and
      --a single row and that is interpreted and used as a single value
      --Determine the name and semester number of those students with a semester number less than the average.
      select name, sem
      from students
      where sem < (select avg(sem) from students);
      --List the name and semester number of all students whose
      --semester number is greater than the average semester number,
      --and compute by how much their semester number is greater than
      --the average.
      select name, sem, sem – (select avg(sem) from students) as semDiff
      from students
      where sem > (select avg(sem) from students);

Views Disadvantages & QBE - Lecture 16

  • Views Disadvantages:

    • Structure restriction
      • The structure of a view is determined at the time of its creation
      • The structure of view will be corresponded to the attributes of base table
      • No update if attributes are modified.
    • Performance
      • View resolution that is complex will take long time
      • View Materializtion as a solution. Store the view result as a temporary table in database.

  • Autorization Page3 - Page8

  • Queries by Example (QBE)
    • Has a two-dimensional Syntax
    • Queries are expressed “by example”
    • Is based on the domain relational calculus: variables are bound to attribute domains (domain variables).

Database schema used in examples:
customer(++cname++, caddr, account)
order(++cname++, ++product++, amount)
vender(++vname++, vaddr, ++product++, price)

customer cname caddr account
— P. P. <0
  • Language elements
    • Commands: eg. P.(print), I.(insert), D.(delete), U.(update)
    • Example elements, domain variable: _X, _Meyer
    • Constants: Smith, 123
    • Boolean (e.g. and, or, not), arithmetic (e.g. +, -, *, /) and relational operators(e.g. =, <, >, <>, >=, <=)

QBE will perform duplicate elimination automatically;
Using all to suppress duplicate elimination. -> P.ALL.

Find for each product the name(s) of the cheapest vendor.
|vender|vname|vaddr|product|price|
|:-:|:-:|:-:|:-:|:-:|
|null|P._n|null|_W|_p|
|非|_m|null|_w|<_p|

QBE - Lecture 17 - Page1-25

  • Condition Box: contains expression of general constraints with and and or
  • 非符号：用在表头代表『不存在』，用在变量代表『不等于』
  • Aggregation: SUM., AVG., MAX., MIN., CNT.
    • Usually follows with “ALL.”, with duplicates
    • add “UN” before “ALL.” to remove duplicates.
  • Grouping: G.
  • Ordering: in ascending order: P.AO.; in descending order: P.DO.
  • “I”, “U”, “D” for insertion, update, deletion of tuples.
  • Creation of Table schemas, Page24