Database Management System

1. Database Management System Module 3:

2. Complex Constraints • In this we specify complex integrity constraints included in SQL. • It relates to integrity constraints. • Number of constraints are involved. • Constraints over single a table • Domain Constraints • Assertions

3. Constraints over single a table • It is possible to specify complex constraints over a single table using table constraints, which have the form CHECK conditional-expression • Example to ensure that rating must be an integer in the range 1 to 10 we could use the following statements

4. Constraints over single a table • CREATE TABLE Sailors ( sid INTEGER, sname CHAR (10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK (rating >=1 AND rating <= 10) )

5. Domain Constraint • A user can define a new domain using CREATE DOMAIN statement which make use of CHECK constraints CREATE DOMAIN ratingval INTEGER DEFAULT 0 CHECK (VALUE>=1 AND VALUE<=10 )

6. Assertions: ICs over Several Tables • Table constraints are associated with a single table. • Table constraints hold only if the associated table is nonempty. • When a constraints involves two or more tables, the constraint mechanism is sometimes cumbersome and not quite desired.

7. Assertions • Suppose that we wish to enforce the constraint that the number of boats plus the number of sailors should be less than 100 (condition required for small sailing club) • This can be solved by creating assertion as follows:

8. Assertions CREATE ASSERTION smallClub CHECK ((SELECT COUNT (S.sid) FROM Sailors S)+ (SELECT COUNT (B.sid) FROM Boats B) < 100)

9. Triggers • A trigger is a procedure that is automatically invoked by the DBMS in response to specified changes to the database, and is typically specified by the DBA. • A database that has a set of associated triggers is called an active database

10. Triggers • A trigger description contains three parts • Event – A change to the database that activates the triggers. • Condition – A query or test that is run when the trigger is activated. • Action – A procedure that is executed when the trigger is activated and its condition is true.

11. Trigger: Example • The trigger called init.count initializes a counter variable before every execution of an INSERT statement that adds tuples to the Students relation. • Another trigger called incr.count increments the counter for each inserted tuple that satisfies the condition age < 18

12. Trigger: Example CREATE TRIGGER init.count BEFORE INSERT ON Students DECLARE count INTEGER; BEGIN count :=0; END

13. Trigger: Example CREATE TRIGGER init.count AFTER INSERT ON Students WHEN (new.age < 18) FOR EACH ROW BEGIN count :=count + 1; END

14. Functional Dependencies • A functional dependency is a relationship of one attribute or field in a record to another. • In a database, we often have the case where one field defines the other. • For example, we can say that Social Security Number (SSN) defines a name.

15. Functional Dependencies • This means that if I have a database with SSNs and names, and if I know someone's SSN, then I can find their name. • By using the word “defines” we are saying that for every SSN we will have one and only one name.

16. Functional Dependencies • And hence we can come to a conclusion that we have defined name as being functionally dependent on SSN. • The idea of a functional dependency is to define one field as an anchor (link) from which one can always find a single value for another field.

17. Functional Dependencies • Another example, suppose that a company assigned each employee a unique employee number. Each employee has a number and a name. Names might be the same for two different employees, but their employee numbers would always be different and unique because the company defined them that way.

18. Functional Dependencies • We write a functional dependency (FD) connection with an arrow: SSN → Name Or EmpNo → Name • The expression SSN → Name is read "SSN defines Name" or "SSN implies Name"

19. Functional Dependencies • Consider the table below:

20. Functional Dependencies (FD) • You have two people named Fred! It is usual that Name will not be unique and it is commonplace for two people to have the same name. • However, no two people have the same EmpNo and for each EmpNo, there is a Name.

21. Functional Dependencies (FD) • The FD X → Y means that for every occurrence of X you will get the same value of Y. • consider another example

22. Functional Dependencies (FD) • Here, we will define two FDs: SSN → Name and School → Location. • Further, we will define this FD: SSN → School. • First, have we violated any FDs with our data? Because all SSNs are unique, there cannot be a FD violation of SSN → Name. Why? Because a FD X → Y says that given some value for X, you always get the same Y. Because the X's are unique, you will always get the same value. The same comment is true for SSN → School.

23. Functional Dependencies (FD) Note: • If we define a functional dependency X → Y and we define a functional dependency Y → Z, then we know by inference that X → Z. • We can define SSN → School. We also can define School → Location, so we can infer that SSN → Location.

24. Functional Dependencies (FD) • The inference we have illustrated is called the transitivity rule of FD inference. Transitivity rule:- Given X → Y Given Y → Z Then X → Z

25. Functional Dependencies (FD) • A primary key constraint is a special case of an FD. • The attributes in the key play the role of X, and the set of all attributes in the relation plays the role of Y.

26. QUERY-BY-EXAMPLE (QBE)

27. QUERY-BY-EXAMPLE (QBE) • Query-by-Example (QBE) is another language for querying (and, like SQL, for creating and modifying) relational data. • It is different from SQL, and from most other database query languages, in having a graphical user interface that allows users to write queries by creating example tables on the screen.

28. QUERY-BY-EXAMPLE (QBE) • QBE is especially suited for queries that are not too complex and can be expressed in terms of a few tables. • QBE, like SQL, was developed at IBM and QBE is an IBM trademark, but a number of other companies sell QBE-like interfaces, including Paradox.

29. QUERY-BY-EXAMPLE (QBE) • Some systems, such as Microsoft Access, offer partial support for form-based queries and reflect the influence of QBE.

30. BASIC QBE QUERIES • For instance if we want to print all fields in some relation, we can place P. under the name of the relation. This notation is like the SELECT * convention in SQL. It is equivalent to placing a P. in every field:

31. BASIC QBE QUERIES • Selections are expressed by placing a constant in some field: • Placing a constant, say 10, in a column is the same as placing the condition =10.

32. BASIC QBE QUERIES • This query is very similar in form to the equivalent DRC query {I,N, 10,A | I,N, 10,A ∈ Sailors}