LECTURE NOTES DBMS PART 3(2 MARKS)

DBMS PART 3(2 MARKS)

21. Explain the relationship between entity, entity class, and entity instance.

An entity is something that can be identified in the users' work environment, something that

the users want to track. Entities of a given type are grouped into entity classes. An entity

instance is the representation of a particular entity.

22. Explain the difference between attributes and identifiers.

Entities have attributes. Attributes are properties that describe the entity's characteristics.

Entity instances have identifiers. Identifiers are attributes that name, or identify, entity

instances.

23. Name and describe three types of binary relationships.

1:1 - a single entity instance of one type is related to a single-entity instance of another type.

1:N - a single entity instance of one type is related to many-entity instances of another type.

M:N - many-entity instances of one type relate to many-entity instances of another type.

24. What is the archtetype/instance pattern?

The archetype/instance pattern occurs when one entity tracks occurrences of another entity. A

common example is the relationship between CLASS and SECTION, where the actual

occurrence of a class happens when students register for a SECTION of that CLASS. The

archetype/instance pattern is implemented as an ID-dependent relationship.

25. What is a recursive relationship? Give an example not used in the text.

A recursive relationship is a relationship between an entity and itself. For example, given the

entity PERSON, a recursive relationship could be used to show a PERSON and his or her

SIBLINGs (brothers and sisters).

26. What are the steps for transforming an entity into a table?

The steps are: (1) specify the primary key, (2) specify candidate keys, (3) specify column

properties including null status, data type, default value (if any), and data constraints (if any),

and (4) verifying normalization.

27. Define a surrogate key, describe the ideal primary key and explain how

surrogate keys meet this ideal.

The ideal primary key is short, numeric and fixed. A surrogate key is a unique, DBMS-supplied

identifier intended to be used as the primary key of a table. Further, the DBMS will not allow

the value of a surrogate key to be changed. The values of a surrogate key have no meaning to

the users and are usually hidden on forms and reports. By design, they are short, numeric and

fixed and thus meet the definition of the ideal primary key

28. Define and discuss data constraints.

Data constraints on a column are the limits put on the values the data can have. There are

four types of data constraints: (1) domain constraints, which define a limited set of values for

the column, (2) range constraints, which specify that the values must fall within a certain

range, (3) intrarelation constraints, which define what values the column can have based on

values of other columns in the same table, and (4) interrelation constraints, which define

values the column can have based on values of columns in other tables.

29. In general, how are recursive relationships handled in a database design?

A recursive relationship is a relationship among entities of the same class, and is represented

in the same way as other relationships are. The rows of the tables can take two different roles,

however. Some are parent rows, and others are child rows. Further, the table will contain both

its own primary key and the foreign key that links back to the table itself. If a row has no

parent, then the value of the foreign key column in that row will be null. If the row has a

parent, then there must be a foreign key value in that row that corresponds to the primary

key value of another row in the table.

30. What is a cascading update?

Referential integrity constraints require that foreign key values in one table correspond to

primary key values in another. If the value of the primary key is changed -- that is, updated --

the value of the foreign key must immediately be changed to match it. Cascading updates will

set this change to be done automatically by the DBMS whenever necessary.

31. What is a SQL view? Briefly explain the use of views.

A SQL view is a virtual table built from other tables or views. Views are used to (1) hide

columns or rows, (2) show the results of computed columns, (3) hide complicated SQL syntax,

(4) layer built-in functions, (5) provide a level of indirection between application programs and

tables, (6) assign different sets of processing permissions to tables, and (7) to assign different

sets of triggers to the same table.

32. Explain the "paradigm mismatch" between SQL and application programming

languages.

SQL statements return a set of rows, while an application program works on one row at a

time. To resolve this mismatch the results of SQL statements are processed as pseudofiles,

using a cursor or pointer to specify which row is being processed.

33. Name four applications for triggers.

(1) providing default values, (2) enforcing data constraints, (3) updating views and (4)

enforcing referential integrity

34. What are stored procedures, and how do they differ from triggers?

A stored procedure is a program that is stored within the database and is compiled when used.

They can receive input parameters and they can return results. Unlike triggers, their scope is

database-wide; they can be used by any process that has permission to use the database

stored procedure.

35. What are the advantages of using stored procedures?

The advantages of stored procedures are (1) greater security, (2) decreased network traffic,

(3) the fact that SQL can be optimized and (4) code sharing which leads to less work,

standardized processing, and specialization among developers.

36. Why is database redesign necessary?

Database redesign is necessary for two reasons. First, redesign is necessary both to fix

mistakes made during the initial database design. Second, redesign is necessary to adapt the

database to changes in system requirements. Such changes are common because information

systems and organizations do not just influence each other they create each other. Thus, new

information systems cause changes in systems requirements.

37. What is the difference between a correlated subquery and a regular subquery?

A correlated subquery appears deceptively similar to a regular subquery. The difference is that

a regular subquery can be processed from the bottom up. In a regular subquery, results from

the lowest query can be determined and used to evaluate the upper-level query. In contrast,

in a correlated subquery, the processing is nested; that is, a row from an upper query

statement is used in comparison with rows in a lower level query. The key distinction of a

correlated subquery is that the lower-level select statements use columns from upper-level

statements.

38. What is a dependency graph?

A dependency graph is a diagram that is used to portray the connections between database

elements. For example, a change in a table can potentially impact relationships, views,

indexes, triggers, stored procedures, and application programs. These impacts need to be

known and accounted for before making database changes.

39. Explain how to add a NOT NULL column to a table.

First, add the column as NULL. Then use UPDATE to add data to every row. Finally use an

ALTER TABLE . . . ALTER COLUMN statement to change the column constraint to NOT NULL.

40. You have two tables, EMPLOYEE and COMPUTER that are in a one-to-one

relationship. The foreign key is EmpNumber in COMPUTER which references

EmpNumber as the primary key of EMPLOYEE. Explain what must be done to convert

the one-to-one EMPLOYEE-COMPUTER relationship to a one-to-many relationship

where one employee can have more than one computer.

In the one-to-one relationship, there will be a constraint on EmpNumber as a foreign key in

COMPUTER stating that EmpNumber must be unique. To convert the relationship to a one-tomany

relationship, just drop this constraint.