Data Definition Language (DDL)

Data Definition Language (DDL) :--

1. Used to specify a database scheme as a set of definitions expressed in a DDL
2. DDL statements are compiled, resulting in a set of tables stored in a special file called a data dictionary or data directory.
3. The data directory contains metadata (data about data)
4. The storage structure and access methods used by the database system are specified by a set of definitions in a special type of DDL called a data storage and definition language
5. basic idea: hide implementation details of the database schemes from the users

Data Manipulation Language (DML)
1. Data Manipulation is:
o retrieval of information from the database
o insertion of new information into the database
o deletion of information in the database
o modification of information in the database
2. A DML is a language which enables users to access and manipulate data.
The goal is to provide efficient human interaction with the system.
3. There are two types of DML:
o procedural: the user specifies what data is needed and how to get it
o nonprocedural: the user only specifies what data is needed
 Easier for user
 May not generate code as efficient as that produced by procedural languages
4. A query language is a portion of a DML involving information retrieval only. The terms DML and query language are often used synonymously.


QBE (Query By Example)
Basic Structure
1. QBE has ``two-dimensional'' syntax.
2. Queries are expressed by example.
3. Close correspondence with domain relational calculus
4. Non-procedural.
5. Queries are expressed using skeleton tables.
6. User selects the skeletons needed.
7. User fills in skeletons with example rows.
8. An example row consists of constants and example elements which are really domain variables.
9. Domain variables are preceded by an underscore character.
10. Constants appear without any qualification.

Simple Queries
1. For example, to find all customers having an account at the SFU branch:

o A P. before the variable causes printing.
o A P.ALL. prefix suppresses duplicate elimination.
o A P. in front of the row prints all attributes.
o The domain variable may be omitted if it is not used elsewhere.
o Arithmetic expressions are allowed.
o Comparison operators are allowed, space on left hand side is left blank.
2. To find the names of all branches not located in Burnaby:

3. To find all customers having an account at both the SFU and the MetroTown branch:

4. To find all customers having an account at either branch or both:

5. Find all customers having an account at the same branch as Jones:


Queries on Several Relations
1. Queries on several relations require several skeleton tables.
2. To find the name and city of all customers having a loan at the SFU branch:


3. Find the name of all customers having an account at the SFU branch, but no loan from that branch.
Queries involving negation can be expressed by putting a sign under the relation name beside an example row:



4. To find all customers who have accounts at two different branches:


The Condition Box
1. When it is difficult or impossible to express all constraints on the domain variables within the skeleton tables, the condition box may be used.
2. To add the constraint that we are only interested in customers other than Jones to the above query, we include the condition box:

3. To find all account numbers with balances between $1,300 and $1,500:


4. Logical expressions and and or may appear in the condition box.
5. To find all account numbers where the balance is between $1,300 and $2,000, but is not $1,500:


6. An unconventional use of the or construct allows comparison with several constant values:


The Result Relation
1. If the result of a query includes attributes from several relation schemes, we need a way of displaying the result in a single table.
2. We can declare a temporary result relation including the attributes to be displayed. We put the print command only in that table.
3. To find the customer names and cities and account numbers for all customers having an account at the SFU branch:




Ordering the Display of Tuples
1. The order in which tuples are displayed can be controlled by adding the command AO. (ascending order) or DO. (descending order) to the print command:

2. To sort first by name, and then by balance for those with multiple accounts:


Aggregate Operations
1. QBE includes the aggregate operators AVG, MAX, MIN, SUM and CNT. As QBE eliminates duplicates by default, they must have ALL. appended to them.
2. To find the total balance of all accounts belonging to Jones:

3. All aggregate operators must have ALL. appended, so to override the ALL.\ we must add UNQ. (unique). (NOTE: a number of examples in the text incorrectly show UNQ. replacing ALL.)

4. To compute functions on groups, we use the G. operator. To find the average balance at each branch:

5. To find the average balances at only branches where the average is more than $1,200, we add the condition box:

6. To find all customers who have an account at all branches located in Burnaby, we can do:









Modifying the Database

Deletion


1. We simply use D. instead of the P. operator. Whole tuples may be deleted, or only some columns.
2. Delete all of Smith's account records:

3. Delete the branch-city value for the SFU branch:

4. Delete all loans with loan numbers between 1300 and 1500:



5. Delete all accounts at branches located in Burnaby:





Insertion
1. Insertion uses the I. operator.
2. To insert an account tuple for Smith:

3. If values are missing, nulls are inserted.
4. To provide all loan customers in the SFU branch with a $200 savings account:


Updates
1. We can update individual attributes with the U. operator. Fields left blank are not changed.
2. To update the assets of the SFU branch to $10,000,000:

3. To make interest payments of 5% on all balances: