Database Management System

1. Database Management System Module 2

2. Class Hierarchy • This classify entities into subclasses. • Two types • Specialization-Process of specifying subclasses of an entity set (superclass) sharing some characteristics. Superclass is defined first followed by subclasses. • Generalization – Opposite of specialisation. Define first subclasses • Identified by the word ISA. (ISA - Relationship)

3. Class Hierarchy: Example

4. Aggregation • Allows us to indicate that a relationship set participate in another relationship. • E.g. Projects is sponsored by one or more department. Employee in the dept assigned to monitor sponsorship. Hence monitor is relationship set associates sponsor relationship set

5. Aggregation name ssn age Employees Monitors Until since Started_on dname pid pbudget did budget Sponsors Projects Department

6. Conceptual Design For Large Enterprise • For a large enterprise the design requires the collaboration amongst several designer, and span data and application code used by a number of user group. • Important thing is to ensure that it takes on board all user requirements.

7. Relation Database Structure • The overall concepts of developing E-R model is to enable designer to create the relational database model. • In a relation model, a database consists of one or more relations, where each relation is a table with rows and columns.

8. Relational Model • The essence is to simplify an understanding of contents of the database toward users. • And also it permits the use of simple, high-level languages to query the data. • Complex queries can be performed. • It underpins the logical database design

9. Relational Model: Key issues • Data representation. • Expression of integrity constraints. • Data creation and modification. • Querying and manipulating data. • Database design in the relational model • Achieving logical and physical data independence.

10. Relation Model: Data representation • The main construct of representing a data in the relational model is a relation. • A relation consists of a relation schema and relation instance. • The relation instance is a table at a particular moment.

11. Relation Model • The relation schema describes the column heads of the table. • The schema specifies the relation’s name, the name of each field (or column, or attribute), and the domain of each field.

12. Domain • A domain is referred to in a relation schema by the domain name and its associated values. • Example of a relation schema: Students (sid: string, login: string, age: integer, gpa: real) • i.e. the field named sid has a domain name string. The set of values associated with domain string is the set of all character strings.

13. Relation Instance • An instance of a relation is a set of tuples or records in which each tuple has the same number of the fields as the relation schema. • Or is a table in which each tuple is a row and all rows have the same number of fields.

14. Key constraints • Example of general constraints is that no two students should the same student id (Sometimes called IC (Integrity Constraint)) • Key constraint is a statement that certain minimal subset of the fields of a relation is a unique identifier for a tuple.

15. Key constraints • A candidate key for the relation is a set of fields that uniquely identifies a tuple according to a key constraints. • Two types of keys • Primary key constraints - values which uniquely identify each of the rows in a table. • Foreign key constraints – enforce referential integrity of database.

16. Example Primary Key Definition using DDL • CREATE TABLE Students ( sid CHAR(20), name CHAR(30), login CHAR (20), age INTEGER, UNIQUE (name, age), CONSTRAINT StudentKey PRIMARY KEY (sid) )

17. Key constraints • The primary keys ensure that no duplicate rows are allowed in a relation. • Foreign key ensures that when one of the two or more relations is modified the data in other relations remain consistent.

18. Key constraints • Consider you have another relation identified by the following schema: Enrolled (sid: string, cid: string, grade: string) • The sid field of Enrolled relation is called a foreign key

19. Illustration of Foreign Key Constraints • In this every sid value that appears in the instance of the Enrolled table appears in the primary key column of Student table

20. Mapping Entity Sets to Table • An entity set can be mapped to a relation in a straightforward way. • E.g. A possible instance of the employees entity set is name age ssn Employee

21. Mapping Entity Sets to Table

22. Class Activity (15 mins) • Using your working experience, • Extract two entities that will be constituting your database system. • Develop E-R model incorporating some relationship. • Develop a conceptual design fom your model. (Relational Model) • Extract individual relation with its appropriate attributes.

23. Relational Algebra

24. Relational Algebra • Is the one the two formal queries language associated with the relational model. • Queries in algebra are made up of collection of operators • A fundamental property is that every operator in the algebra accepts (one or two) relation instances as argument and returns relation instance as the results.

25. Relational Algebra • This property makes it easy to compose operators to form a complex query . • Each relational query describes a step-by-step procedure for computing desired answer based on the order in which operators are applied in the query. • Relational algebra act as plan for evaluating a query due to its procedural in nature.

26. Relational Algebra • The inputs and outputs of queries are relations. • A query is evaluated by using instances of each input relation and it produces an instances of the output relation.

27. Selection and Projection • Relational algebra includes operators to select rows from a relation (δ) and to project columns ( ). • Consider the instance of the accounting relation in th figure below

28. Selection and Projection • By projection we can retrieve rows corresponding to customer account by using operator as follows

29. Selection and Projection • The selection operator sigma specifies the tuples to retain through a selection condition. • In general, the selection is boolean combination (i.e, an expression using the logical connectives Λ and V) of attributes and comparison operators (<,=,>,≠,≤,≥,<=,=>)

30. Selection and Projection • The projection operator pi allows us to extract columns from relation; for example, we can find out all accounting type and balance by using the following statement: • The expression above evaluates to the relation shown below

31. Selection and Projection • The subscript Acc_Type, balance specifies the field to be retained and other fields are projected out

32. Since the result of a relational algebra is always a expression, we can substitute expression wherever a relation is expected • Example we can compute account type and balance by combining two of the proceeding queries as follows:

33. Exercise • Write down an expression will evaluate the student name in the student database. • Review Set Operations • Union • Intersection • Set-difference • Cross-product